PHP MySQL PDO selecting rows on array with id's

Question

I stored some multiple id values from a form[] in a session.

I tried to use these id's within a mysql pdo database query as follow:

$noot = str_repeat("?,", count($_SESSION['uniqueID'])-1) . "?";

$printen = 1;
$query = $db->prepare("SELECT DISTINCT ru.GB,g.Name,g.Address,g.Place,ri.Ricode,lo.nr,lo.uniqueID,GROUP_CONCAT(ru.control SEPARATOR '<BR>') AS control,GROUP_CONCAT(ru.BestEmail SEPARATOR '<BR>') AS email,GROUP_CONCAT(ru.ont_info SEPARATOR '<BR>') AS ont,GROUP_CONCAT(ru.sup SEPARATOR '<BR>') AS Lev,GROUP_CONCAT(ru.OrderID SEPARATOR '<BR>') AS orderID,GROUP_CONCAT(DISTINCT ru.rID SEPARATOR '<BR>') AS RID,GROUP_CONCAT(ru.Pack SEPARATOR '<BR>') AS packet FROM rutrans AS ru JOIN routes AS ri ON ru.rID = ri.rID 
 LEFT OUTER JOIN lock AS lo
             ON ri.Ricode = lo.ricode AND ru.GB = lo.location
    LEFT OUTER JOIN gbc AS g
             ON ru.GB = g.CodeB          
WHERE  lo.uniqueID IN ($noot)");

$query->execute($_SESSION['uniqueID']);

//$data = $query->fetchAll();

And try to get the results like this:

foreach($queryRT as $rowRT) { 

 (I echo database results within a table for each row here,... or try to do this)

}

The following error occures:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in

When I add

$data = $query->fetchAll();

to the query the page is totally blank.

When I try something to show like this:

print_r($data);

Only the following is being displayed on the page:

Array ( )

When I try something like this:

print_r($_SESSION['uniqueID']);

It shows the array as follow:

Array ( [B1601222016164345] => B1601222016164345 
        [B1601222016161121] => B1601222016161121 
        [B1601262126474345] => B1601262126474345 
        [B1602011856554345] => B1602011856554345 
        [B1602101921434345] => B1602101921434345 
        [Z1602102002034345] => Z1602102002034345
        [Z1602102026544345] => Z1602102026544345 
      )

Obviously I am doing something totally wrong. Is there anybody who would have an idea to solve this problem?

"invalid parameter number"? check that $noot is actually being produced properly. you maybe be generating (say) ?,?,?, but then providing only 2 values for those placeholders. — Marc B
– Marc B, Commented Feb 11, 2016 at 16:42
As the error suggests you are passing the wrong number of arguments. Your'e setting as many ? as placeholder for your values and then give one array as Argument. Pass the values from the session each as own argument — Andreas Schrammel
– Andreas Schrammel, Commented Feb 11, 2016 at 16:42

Dave Chen · Accepted Answer · 2016-02-11 16:53:40Z

3

$_SESSION['uniqueID'] needs to have numbers for its index if you are using ? as placeholders.

Docs

The keys from input_parameters must match the ones declared in the SQL. Before PHP 5.2.0 this was silently ignored.

Try this:

$query->execute(array_values($_SESSION['uniqueID']));

edited Feb 11, 2016 at 16:53

answered Feb 11, 2016 at 16:48

Dave Chen

11k8 gold badges42 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KThkek Over a year ago

Hi,$query->execute(array_values($_SESSION['uniqueID']));

KThkek Over a year ago

Hi, Thank you all, I understand it a bit. $query->execute(array_values($_SESSION['uniqueID'])); shows indeed more results when using print_r($data), though it looks a bit odd, probably caused by the way the query is set up, with all the joints. It worked without "WHERE" though. If I may ask, is there also any clever way to get the same sort of result from this as I normally have when using foreach($query as $row) { echo specific row data } kind regards

Dave Chen Over a year ago

Not that I can think of. I think your way of generating a placeholder is pretty clever already.

KThkek Over a year ago

Hi, thank you all for the insight. And to help me further Dave. Kind regards!

Collectives™ on Stack Overflow

PHP MySQL PDO selecting rows on array with id's

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related