Multiple PHP values in mysql query - how to separate multiple options to query database

Question

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! :)

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. — Sloan Thrasher, May 03 '17 at 20:57
See http://stackoverflow.com/a/28909923/1491895 for a better way to build the `WHERE` clause dynamically. — Barmar, May 03 '17 at 21:06
BTW, you should definitely avoid construct your query directly from user input, you're wild open to [SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Blag, May 03 '17 at 21:07
@Blag I am using mysqli - just didn't want to add more code than needed. — Francesca, May 03 '17 at 21:21
mysqli is really sh*tty for parametrized query, you should try PDO, the prepare/execute is way easier to use... — Blag, May 03 '17 at 21:27

score 1 · Accepted Answer · edited May 23 '17 at 12:26

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>';

edited May 23 '17 at 12:26

Community

1
1

answered May 03 '17 at 21:21

Blag

5,818
2
22
45

Thank you for the advice about using PDO over mySQLi! – Francesca May 04 '17 at 17:17
@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 – Blag May 06 '17 at 10:44
I am receiving this result when I am running it: "SELECT * FROM dummy_table WHERE 'Role' = ? OR 'Name' = ? OR 'County' = ?" – Francesca May 08 '17 at 13:58
@Francesca update, wrong use of variable in the `else` part and forgot to rule out empty POST. Should be ok now. – Blag May 08 '17 at 14:58
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. – Francesca May 08 '17 at 17:42
@Francesca could you edit your post with the code you test now that give you the `SELECT...`? I think you're misusing it somehow (I test my code and it was ok). In fact `SELECT * FROM dummy_table WHERE 'Role' = ? OR 'Name' = ? OR 'County' = ?` is really how your SQL should look like. the trick is you bind your `$var` in the `->execute()` – Blag May 08 '17 at 17:48
Ah, you were correct - I had edited the execute query and it was not functional. I got it working now :) thank you so much for helping me. – Francesca May 08 '17 at 19:38

score 0 · Answer 2 · answered May 03 '17 at 21:07

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;

answered May 03 '17 at 21:07

Sloan Thrasher

4,953
3
22
40

1

SQL Injection. **just DON'T for heaven sake** – Blag May 03 '17 at 21:08
@Blag: Agreed, but this is more a question about how to create the SQL query, and for purposes of illustration, simplified. – Sloan Thrasher May 03 '17 at 21:12
1

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 May 03 '17 at 21:14
@Blag: So where's your answer? ;-) – Sloan Thrasher May 03 '17 at 21:15
Nah, I knew that it was insecure- just didn't want to add more code than needed ;) – Francesca May 03 '17 at 21:20
here it is, with PDO ;) – Blag May 03 '17 at 21:22
When I used this code, this is the result I got: "SELECT * FROM dummy_table WHERE ". I am individually posting each variable, as in $Name=$_POST, $Role=$_POST ... etc. – Francesca May 04 '17 at 17:24
What to you mean by "_individually posting each variable_"? Isn't the form being submitted with all of the fields? – Sloan Thrasher May 04 '17 at 17:53
I am doing "$Role=$_POST; $City=$_POST; $TargetPop=$_POST; $TargetIssues=$_POST; .. etc; " because at one point only the first letter of a post value was being returned for some values :/ I switched it to all post values set to one variable and I am still not getting any results. – Francesca May 04 '17 at 18:29

Multiple PHP values in mysql query - how to separate multiple options to query database

2 Answers2