Multiple variables in prepared statement PHP

Question

I'm having an issue preparing a SQL statement:

$statement = $conexion->prepare(
    'SELECT * FROM celulares 
    WHERE (MARCA = :marca )

    AND 

    (CATEGORIA = :categoria1 OR CATEGORIA = :categoria2 OR CATEGORIA = :categoria3) 

    AND 

    (CATEGORIA2 = :categoria1 OR CATEGORIA2 = :categoria2 OR CATEGORIA2= :categoria3) 

    AND 

    (CATEGORIA3 = :categoria1 OR CATEGORIA3 = :categoria2 OR CATEGORIA3 = :categoria3)');

Giving placeholders values with this:

$statement->execute(array(':categoria1' => $categoria1,
                      ':categoria2' => $categoria2,
                      ':categoria3' => $categoria3,
                      ':marca' => $query
                 ));

$query value may variate when my application begins depending on some results:

if ($entrada == "LG") {
            if ($query == "") {
                    $query = "LG";
            } else {
                    $query = $query . ' OR MARCA = "LG" ';
            }
    }
if ($entrada == "APPLE") {
            if ($query == "") {
                    $query = "APPLE";
            } else {
                    $query = $query . ' OR MARCA = "APPLE" ';
            }
    }

    if ($entrada == "HUAWEI") {
            if ($query == "") {
                    $query = "HUAWEI";
            } else {
                    $query = $query . ' OR MARCA = "HUAWEI" ';
            }
    }

I tried a lot of things, but none of those worked out it returns an empty array, the only one who works was changing this line of my prepared statement:

WHERE (MARCA = :marca OR MARCA = :marca2 OR MARCA = :marca3 )

And as many "MARCA" as results, i think it's not the best way to do it

UPDATED:

Now trying with IN Statement in my Query (Thanks you all for helping me)

Now it looks like:

$marcas  = array("LG", "HUAWEI"); (Static values for test)
$inQuery = implode(',', array_fill(0, count($marcas), '?'));

$statement = $conexion->prepare(
'SELECT * FROM celulares 
WHERE (MARCA = IN (' . $inQuery . '))

AND 

(CATEGORIA = :categoria1 OR CATEGORIA = :categoria2 OR CATEGORIA =  :categoria3) 

AND 

 (CATEGORIA2 = :categoria1 OR CATEGORIA2 = :categoria2 OR CATEGORIA2= :categoria3) 

AND 

(CATEGORIA3 = :categoria1 OR CATEGORIA3 = :categoria2 OR CATEGORIA3 =       :categoria3)');


foreach ($marcas as $k => $marca) {
$statement->bindValue(($k+1), $marca);

}

$statement->bindValue(':categoria1', $categoria1);
$statement->bindValue(':categoria2', $categoria2);  
$statement->bindValue(':categoria3', $categoria3);

$statement->execute();

Getting: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Trying to fix it

Take a look at [Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) and [FIND_IN_SET() vs IN()](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) — mrun, Mar 13 '17 at 07:33
I think you need to use IN statement in your query and switch-case in php code. — Alex Slipknot, Mar 13 '17 at 07:33
i don't think you can set a parameter value in a preparedStatement when this value is actually an SQL statement,like this one `$query = $query . ' OR MARCA = "APPLE" '`, this will look like sql-injection and that's one reason PS was created. to protected/escape ... so ur query value might be inserted as text (escaped) i suggest you pass an array as `$query` list of `MARCAs`, then you use it to build your SQL: `"MARCA = :val1 OR MARCA = :val2"` .. .etc based on marcas array length, and then set the parameters `:val1 , :val2 ...` and execute. — Yazan, Mar 13 '17 at 08:44

score 0 · Answer 1 · answered Mar 13 '17 at 10:51

You can simplify your query:

SELECT * FROM celulares 
WHERE (MARCA = :marca )
AND (:categoria1,:categoria2,:categoria3)
  IN (
    (CATEGORIA,CATEGORIA2,CATEGORIA3),
    (CATEGORIA,CATEGORIA3,CATEGORIA2),
    (CATEGORIA2,CATEGORIA,CATEGORIA3),
    (CATEGORIA2,CATEGORIA3,CATEGORIA),
    (CATEGORIA3,CATEGORIA,CATEGORIA2),
    (CATEGORIA3,CATEGORIA2,CATEGORIA)
  )

This way you only pass in the categories once, and compare it against the six possible permutations of three categories.

That being said, this is a sign that your database is in very poor shape. Generally speaking having any kind of "column2", "column3" system is a sign that you need to restructure your database - the kind of queries you end up with, like the above, are only going to get worse.

Specifically, in this case, just adding CATEGORIEA4 would increase the amount of permutations you need to define from 6 to 24!!

EDIT: I completely missed the part about :marca and IN - I was too focussed on the bad state of the database with regard to categories, sorry!

Thanks you Niet, i'm new in this world as you can see, categories are selected by user and he can choose just three so there won't be another possibilities, i'm sure there is another way better to do it, i'll find out what can i do about that — Gabriel Alejandro Ricardo, Mar 13 '17 at 11:03

score 0 · Accepted Answer · answered Mar 14 '17 at 00:10

Well, i fix it, probably it's not the best way to solve it but i have this now:

I fill array with entries from POST

$query = array();
$index = 0;

foreach ($_POST as $entrada) {
switch($entrada) {
case "SAMSUNG":
$query[] = "SAMSUNG";
break;

case "LG":
$query[] = "LG";
break;

case "APPLE":
$query[] = "APPLE";
break;

case "HUAWEI":
$query[] = "HUAWEI";
break;
}


}
$inQuery = str_repeat('?,', count($query) - 1) . '?';

Here's my new query: Problem was that i was mixing "?" with placeholders (:) which not is recommended

     $statement = $conexion->prepare(
    "SELECT * FROM celulares 
     WHERE ( MARCA IN($inQuery)) 

    AND 

    (CATEGORIA = ? OR CATEGORIA = ? OR CATEGORIA = ?) 

    AND 

    (CATEGORIA2 = ? OR CATEGORIA2 = ? OR CATEGORIA2= ?) 

    AND 

    (CATEGORIA3 = ? OR CATEGORIA3 = ? OR CATEGORIA3 = ?)");

Then i bindValues like that

$c = 0; 
foreach ($query as $q => $queries) {
        $c++;
        $statement->bindValue(($q+1), $queries);

}


$statement->bindValue($c+1, $categoria1);
$statement->bindValue($c+2, $categoria2);
$statement->bindValue($c+3, $categoria3);
$statement->bindValue($c+4, $categoria1);
$statement->bindValue($c+5, $categoria2);
$statement->bindValue($c+6, $categoria3);
$statement->bindValue($c+7, $categoria1);
$statement->bindValue($c+8, $categoria2);
$statement->bindValue($c+9, $categoria3);

$statement->execute();

$resultados = $statement->fetchAll();

I did many test with a lot of querys and it's working fine, probably it's a "dirty" solution but i'll continue learning

Thanks u all for helping me!

Multiple variables in prepared statement PHP

2 Answers2