0

I got help from this post but need help on one more thing :) I have twelve drop downs with the option to select more than one value. On submit, these values are posted to another page where I do a mysql query to a database. If I select one value from each dropdown, it works. However, if I select more than one, only one is queried.

Here's the query output if I select multiple values:

SELECT * 
  FROM dummy_table 
 WHERE Role = 'Student' 
    OR Name = '**George,Sheila**' 
    OR City = 'New York';

I'd like it to be Name='George OR Sheila' so I can pull people with both those names, or other values.

<?php 
foreach($_POST as $key=>$option){
    $countValue = count($option);

    for($i=0; $i<$countValue; $i++){
        $queryString_start_with_comma .= ",$option[$i]";

        if($i >1){
            $queryString_start_with_comma .= ",$option[$i] OR";
        }
    }

    $queryString_remove_extra_comma= preg_replace("/,/", "", $queryString_start_with_comma, 1);
    $query_string_with_and .= " OR $key = '$queryString_remove_extra_comma'"; 

    unset($queryString_start_with_comma);
}

if ($sql_post_parameters == "AND") {
    $query_string_second_part_ready = preg_replace("/AND/", "", $query_string_with_and, 1);
}
else {
    $query_string_second_part_ready = preg_replace("/OR/", "", $query_string_with_and, 1);
}


$query_string= "SELECT * FROM dummy_table WHERE $query_string_second_part_ready";

TL;DR: I want to separate values pulled from a dropdown's POST with "OR" so I can query both in the database.

Thank you! :)

Improve this question
6
  • The SQL syntax for name would be either name in ('George','Sheila') or name = 'George' OR name = 'Sheila' You can use explode and implode to reformat the name field before constructing the query. Commented May 3, 2017 at 20:57
  • what are you using to run your query ? PDO or mysqli ? Commented May 3, 2017 at 21:03
  • 1
    See stackoverflow.com/a/28909923/1491895 for a better way to build the WHERE clause dynamically. Commented May 3, 2017 at 21:06
  • BTW, you should definitely avoid construct your query directly from user input, you're wild open to SQL injection Commented May 3, 2017 at 21:07
  • @Blag I am using mysqli - just didn't want to add more code than needed. Commented May 3, 2017 at 21:21

2 Answers 2

Reset to default
1

As already said here SQL and PHP filter sqli_* function is not that good when it come to deal with user inputs (ok, they are really bad in fact, you have to sanitize and build the query by yourself, not user friendly at all)

Security first, here is a way with prepared statement in PDO and a white list of trusted parameters :

        // DB connect
$db = new PDO('mysql:host=localhost;dbname=DB_name', 'user', 'pwd');

$where = array();
$param = array();

foreach($_POST as $key => $option) {
    if (
        !empty($option) 
        and in_array($key, array('Role','Name','City'))) 
    {
        if (is_array($option)) {
            foreach($option as $k => $optval) {
                $where[] = "`".$key."` = ?";
                $param[] = $optval;
            }
        }
        else {
                $where[] = "`".$key."` = ?";
                $param[] = $option;
        }
    }
}

$query_string = "SELECT * FROM dummy_table";

if(!empty($where))
    $query_string .= " WHERE ".implode(' OR ',$where);

    // we prepare our request
$stmt = $db->prepare($query_string);
    // we execute with our parameters
$stmt->execute($param);
echo '<pre>';
print_r($stmt->fetchAll());
echo '</pre>';
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you for the advice about using PDO over mySQLi!
@Francesca have you tried this code ? (I don't have anything to test it right now) ; check that the column(s) you want to allow in search are in the in_array() condition
I am receiving this result when I am running it: "SELECT * FROM dummy_table WHERE 'Role' = ? OR 'Name' = ? OR 'County' = ?"
@Francesca update, wrong use of variable in the else part and forgot to rule out empty POST. Should be ok now.
I'm sorry, I'm still receiving 'SELECT * FROM dummy_table WHERE 'Role' = ? OR 'Name' = ? OR 'County' = ?' The only thing I can think of is that I'm not adding all the variables to "in_array($key, array('Role','Name','City')" -- I just left it at three to test it. It's not my database -- I can still retrieve the post values with the code I posted in my initial question.
|
0

Give this a try. It takes each field and the splits up the options, creating one condition per option value. It then puts them back together with ORs.

$where_a = array();
foreach($_POST as $key => $option) {
    $val_a = explode(',',$option);
    foreach($val_a as $k => $optval) {
        $where_a[] = "`".$key."` = '".$optval."'";
    }
}
$where = implode(' OR ',$where_a);

$query_string = "SELECT * FROM dummy_table WHERE ".$where;
Improve this answer

9 Comments

SQL Injection. just DON'T for heaven sake
@Blag: Agreed, but this is more a question about how to create the SQL query, and for purposes of illustration, simplified.
And you know, as I do, that this is exactly what he'll straight use, without looking about security or anything. You're the one who know, don't teach way that should never be used ;)
@Blag: So where's your answer? ;-)
Nah, I knew that it was insecure- just didn't want to add more code than needed ;)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.