3 Replies - 483 Views - Last Post: 11 September 2013 - 01:46 PM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

PDO Looping Results

Posted 11 September 2013 - 11:37 AM

Hey guys - I haven't posted in a while but I'm working on a new project that seems to have me up against a wall.

I'm trying to write a script that can query my table, and add states to an array based on a preset variable.

(ie a=select box, b=div, c=list format/ul-li)

I am learning PDO and I'm having a little bit of trouble wrapping my head around the concept of the arrays it creates. I'm using execute because of the advantages I've read about but this piece of code just creates an option list with "A" and stops. I'm sure I'm not reading the results right so any input would be greatly appreciated.

Some other notes - My db is connected as I wrote some login scripts that work. My table names and field names are correct as this worked when it was using mysql_result.

Thanks in advance!!

  $StQry = $db->prepare("SELECT state FROM build_geography WHERE GeoType = 2 ORDER BY state");  
  $StQry->execute();
  $Res = $StQry->fetch(PDO::FETCH_ASSOC);
  
  $Msg = "";
  
  foreach($Res as $St){
    $Msg = $Msg . "<option value = '{$St['state']}'>{$St['state']}</option>";
  }
  
  echo $Msg;



Is This A Good Question/Topic? 0
  • +

Replies To: PDO Looping Results

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3554
  • View blog
  • Posts: 10,335
  • Joined: 08-June 10

Re: PDO Looping Results

Posted 11 September 2013 - 11:48 AM

you’re looping over the wrong object (i.e. an array rather than the PDOStatement object). you should loop over $StQry (foreach() triggers PDO’s fetch() method automatically)

note, if the query is static, there is no need for a prepared statement.

$StQry = $db->query("SELECT state FROM build_geography WHERE GeoType = 2 ORDER BY state");  
foreach($StQry as $St){
  $Msg .= sprintf("<option value='%1$s'>%1$s</option>", $St['state']);
}
echo $Msg;


Was This Post Helpful? 1
  • +
  • -

#3 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: PDO Looping Results

Posted 11 September 2013 - 11:50 AM

Looks like I have a little more learning to do :)

Thank you - I'm going to include this in my googling and attempts but I may very well be back with more shortly!
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 799
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: PDO Looping Results

Posted 11 September 2013 - 01:46 PM

As Dormilich said, you're better off looping over the PDOStatement than a fetched array. If you are going to fetch the entire set as an array though, you can do, and your original code is wrong. The reasons you might want to fetch the entire array include:
  • You are caching the results of the query in PHP so that you don't have to poll the DB each time you call the query
  • You are doing some sort of complex array manipulation of the fecthed data that you couldn't do with using the DB query (e.g. - mapping the data from two-dimensional flat data to multi-dimensional data)
  • You are transforming the fetched data into another format, for example to JSON to push it over into Javascript

If this is the case, then your original code should be this (note carefully line 3):
$StQry = $db->prepare("SELECT state FROM build_geography WHERE GeoType = 2 ORDER BY state");  
$StQry->execute();
$Res = $StQry->fetchAll();

foreach($Res as $St){
	// Do your array manipulation here
}


This post has been edited by e_i_pi: 11 September 2013 - 01:47 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1