PDO mySql query not executing in for loop the second time up while calling

Question

I have an issue, I'm looping threw a set of values and then creating a PDO mySql query with every loop, now the problem is the first query is executing and returning results, but the second upwards aren't returning results. If I manually execute the queries on the server they return results. This is weird, maybe I'm doing something wrong here. My code below

if($num_results > 0){ for($i=0;$i<$num_results;$i++){ $sql_sub = "SELECT * FROM menu_config WHERE client_id =".$client_id ." AND id =".$data[$i]['root_menu_id']; $results_s = $pdo->query($sql_sub); $data_s = $results_s->fetchAll(PDO::FETCH_ASSOC); $sub_menu_title = "<strong>".$data[$i]['title']."</strong>"; if(empty($data_s[0]['title'])){ $main_menu_title = '<span style="color:#FF0000;font-weight:bold;">No Main Menu Assigned to Sub Menu</span>'; }else{ $main_menu_title = $data_s[0]['title']; } $men_title = $data[$i]['title'] } }

Do you get any exceptions from php/pdo? Aren't you rewriting $main_menu_title and $men_title only with last iteration of your for statement? — Juraj Blahunka
– Juraj Blahunka, Commented Feb 8, 2010 at 9:14
No Exceptions, I tried print_r($data_s); after every loop and returns only values with the queries — Elitmiar
– Elitmiar, Commented Feb 8, 2010 at 9:17
what about trying to explicitly fetch a single row? $row = $pdo->query($sql_sub)->fetch(); and then print_r($row) — Juraj Blahunka
– Juraj Blahunka, Commented Feb 8, 2010 at 9:25

munch · Accepted Answer · 2010-02-08 09:29:35Z

(this may be a little more than you asked for)

You seem to be missing out on some good things that prepared statements do.

First off, you don't usually want to pass the values directly into the query. (sometime's it's necessary, but not here). By doing that, you take out all the good stuff that protects from sql injection. Instead you want to send them as parameters after you've prepared the query.

Secondly, when in a loop, you can save yourself time and resources if you're running the same query over and over by preparing the statement, and then only changing the values you send to to that prepared statement using the PDOStatement::bindParam() function.

Thirdly, fetchAll() does not take a 'fetch_style' of PDO::FETCH_ASSOC. fetch() does. But I think you can get by with the default or none using fetchAll. You'll have to check into that and see what you need. Here are the fetchAll docs

$sql_sub = "SELECT * FROM menu_config WHERE client_id = :client_id AND id = :id "; $query = $pdo->prepare($sql_sub); for($i=0;$i<$num_results;$i++){ $query->bindParam(':client_id', $client_id); $query->bindParam(':id', $data[$i]['root_menu_id']); $query->execute(); $data_s = $query->fetchAll(); $sub_menu_title = "<strong>".$data[$i]['title']."</strong>"; if(empty($data_s[0]['title'])){ $main_menu_title = '<span style="color:#FF0000;font-weight:bold;"> No Main Menu Assigned to Sub Menu</span>'; }else{ $main_menu_title = $data_s[0]['title']; } $men_title = $data[$i]['title']; }

very nice rewrite, it's always better and safer to use prepared statements

Collectives™ on Stack Overflow

PDO mySql query not executing in for loop the second time up while calling

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related