First, let me apoligize. This is a rather long-winded question. But, I think maybe I'm missing something here? I've just starting using PDO for all my database queries. I'm using fetchall which returns multidimensional arrays. This seems to complicate things a bit when making use of the results. I looked at both fetch and fetchall and it seems like I want to be using fetchall. Correct me if I'm wrong.

public function getData($sql,$params) {
		// run a select query
		try {
		$this->stmt = $this->conn->prepare($sql);
		$this->stmt->execute($params);
		return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
		}
		catch(PDOException $e) {
			throw new Exception('DATABASE ERROR: ' . $e->getMessage());
		}
	}

So, lets say I want to generate a list of locations. The old way would be to run a query and extract the results with a while loop. They would extract as the column name from the database. Pretty simple.

PDO seems a bit more complicated. The query returns as a multidimensional array like so.


Array
(
    [0] => Array
        (
            [location_id] => 1
            [location_city] => City 1
            [state_name] => State 1
        )

[1] => Array
    (
        [location_id] => 2
        [location_city] => City 1
        [state_name] => State 1
    )

[2] => Array
    (
        [location_id] => 3
        [location_city] => City 1
        [state_name] => State 1
    )

[3] => Array
    (
        [location_id] => 6
        [location_city] => City 1
        [state_name] => State 2
    )

and so on...

I then have to run a foreach loop to straighten things out.

foreach ($locations as $shops) {
	$arr[$shops['state_name']][$shops['location_id']] = $shops['location_title'];
}

which turns the previous array into:

Array
(
    [State 1] => Array
        (
            [1] => City 1
            [2] => City 2
            [3] => City 3
        )
        [State 2] => Array
        (
            [1] => City 4
            [2] => City 5
        )
)

Once I get it here, it's more foreach loops to pick the data apart even further. This seems to start making code fatten out quite a bit.

I previously used dropdown functions to generate lists. I would pass an associative array to the function, it would do it's thing and create the dropdown. With PDO and fetchall, I'm having to adjust the array either inside the dropdown function or before it gets there.

I guess I really just want to know if I'm going about this right or if I'm missing something what is it (besides a deeper understanding of PHP, that's a given 🙂).

    If you are accustomed to using a while loop with mysql_fetch_assoc, you can do the same thing with PDO. Instead of using fetchall, use fetch instead.

      Also note that using a while loop with mysql_fetch / PDO::fetch give you the exact same structure as iterating over the array returned by fetchAll

      
while ($row = mysql_fetch_assoc())
{
  printf('<pre>%s</pre>', print_r($row,1));
}


$allRows = $pdo->fetchAll();
# Here, you still iterate over one row at the time
foreach ($allRows as $row)
{
  printf('<pre>%s</pre>', print_r($row,1));
}

      There should be no difference in output. The only difference is from where you retrieve each row: directly from the result set resource or from an array containing the same data.

      In other words, you could change your previous while loop into a foreach loop and keep whatever code you had in the while loop previously and retain functionality.

        Thanks. It seems to just be preference and maybe a new way of doing things for myself. I just wanted to be sure I was understanding things correctly. Thanks for the input.

          I'd be willing to bet that, if you are a good programmer and properly free db resources when you are done with them, then using fetchAll will result in your server working harder and consuming more memory because it goes through the extra effort to loop through your resource and then creates a potentially huge PHP in memory that contains all of the records you requested. If you requested 100,000 records, that could be quite a bit of memory.

            Write a Reply...