How can I create an associated array result via mysqli in a better way than this?

Olaf Doschke
Olaf Doschke used Ask the Experts™
on
I use a dbaccess class based on mysqli to query data into associated arrays via prepared statements. This works fine, but in the core step to add each retrieved record $row into an array $rows via $rows[] = $row I end up with N times the same record in the final array, if not using a workaround.

That workaround is to use $rows[] = unserialize(serialize($row)); (see comment in the code). But is there a better option to get an array of the records without that workaround?

The output I get from the following code is:
Array
(
    [0] => Array
        (
            [id] => 7
            [name] => Olaf
        )

    [1] => Array
        (
            [id] => 7
            [name] => Olaf
        )
... repeatedly the same record

Open in new window


Changing to the workareound code (comment "known solution") the output is correct:
 
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Jens
        )

    [1] => Array
        (
            [id] => 2
            [name] => Sebastian
        )
... all the correct and different rows....

Open in new window


Here's the code. To test and replicate the problematic behaviour, add connection credentials to the __construct event and change $sql to query a small table of your database and make the appropriate change to bind_result() of course.

<?php

$dbaccess  = new dbaccess();
$rows      = $dbaccess->getRows();

echo "<pre>";
print_r($rows);
echo "</pre>";

class dbaccess extends mysqli
{
	public function __construct()
	{
           // connect to mysql here via parent::__construct( host, user, pass, dbname)
	}	

	public function getRows()
	{
		$rows   = array();
		$row    = array 
			( 'id' => 0
			, 'name' => ''
			);

		$sql = 'SELECT id, name FROM sometable';

		if ($stmt = $this->prepare($sql))
		{
			$stmt->execute();
			$stmt->store_result();
			if ($stmt->num_rows>0)
			{
				$stmt->bind_result($row['id'], $row['name']);

				while  ($stmt->fetch())
				{
					$rows[] = $row; // no error, but in the end all $rows entries are the same last $row, see print_r($rows) output;
                                        // known solution: $rows[] = unserialize(serialize($row));
                                        // is there anything better to do here, than using unserialize(serialize()) ?
				}
			}
			$stmt->free_result();
			$stmt->close();
		}
                return $rows;
        }
}
?> 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
Your query might use SELECT DISTINCT, or you could try creating an associative array, something like this, to make the duplicate keys overwrite each other.
while  ($stmt->fetch())
{
   $rows[ $row['id'] . $row['name'] ] = $row;
}

Open in new window

Olaf DoschkeSoftware Developer

Author

Commented:
I'm not having any problems with duplicate records or keys. This is merely an effect of adding $row by reference instead of by value, so far I have already identified the problem.

To see yourself, you can simply print_r($row) within the loop fetching all rows. There is no double id, mysql won't allow that for a primary key field as it is. I didn't mention it, but take that for granted.

Bye, Olaf.

Edit: Actually forget about associative in my question. $rows[] = $row; creates jsut a normal array, which I would be fine with.

If I print_r($rows) after each row I see at first it has only 1/Jens in it, then after the second $rows[]=$row has run print_r prints 2/Sebastian twice and in the end i get seven times 7/Olaf.

this makes me think $rows[]=$row is adding a new element, but by reference, so each $rows[] element has the same $row in it, which always is the current values of $row.
Olaf DoschkeSoftware Developer

Author

Commented:
Ray,

I tried your suggestion anyway, now the array ends up this way:
Array
(
    [1Jens] => Array
        (
            [id] => 7
            [name] => Olaf
        )

    [2Sebastian] => Array
        (
            [id] => 7
            [name] => Olaf
        )
...
    [7Olaf] => Array
        (
            [id] => 7
            [name] => Olaf
        )
)

Open in new window


So you see from the array element names, each $row had the correct data in it, while creating the array element for that row, nevertheless the read back from each element  are the last fetched row.

Bye, Olaf.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Olaf DoschkeSoftware Developer
Commented:
From what I found elsewhere, I think I turn to PDO (at least for future projects), as that works flawless this way:
   //part of a class definition extending PDO
	public function getRows()
	{

          $sql = 'SELECT id, name FROM sometable';
          $stmt = $this->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
          $stmt->execute();
          return $stmt->fetchAll();
        }

Open in new window

This is even leaner code.

I'd still like to see what you, Ray, or anybody else has to say about the mysqli bind_result() problem. It's really like I would do $rows[]=&$row;

From what I read about performance benchmarks, there seems to be no problem with PDO vs MySQLi (eg see http://jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks).

So, is there some PDO first read you would suggest additional to the PHP.net manual on PDO?

Bye, Olaf.
Most Valuable Expert 2011
Top Expert 2016
Commented:
I don't think the performance issues are going to arise from the choice of MySQL or MySQLi or PDO.  If you have good indexes on all columns used in WHERE, GROUP, ORDER and JOIN, and use EXPLAIN SELECT on all complex queries, you should be OK.  I would also avoid the use of SELECT * and always use LIMIT whenever you do not require a complete table scan.  For the method above, I might write it more like this...
   //part of a class definition extending PDO
	public function getRows($sql)
	{
          $stmt = $this->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
          $stmt->execute();
          return $stmt->fetchAll();
        }

Open in new window

The expected response is an array of either arrays or objects representing the rows.
Olaf DoschkeSoftware Developer

Author

Commented:
Thanks Ray,

in regard of generalising this I will apply more than your suggestion anyway. This also omits any error handling, it was just to illustrate how this creates a result array I can use. Actually I find each row added as associated element with field name and as indexed elements, and I need to learn about how to control what PDO creates via fetchall().

I'll often make use of paramterising WHERE clauses, so I have more advanced changes in mind for this than passing in the $sql. Actually I could also use PEAR components, but I also do this for deepening my knowledge and learning.

I'll wait a bit for another answer or explaination of the mysqli behaviour and a suggestion to do something else, as I am stuck with this in some current project. It's not a nightmare for the 10-20 rows I typically only fetch, but it's annoying me.

Bye, Olaf.
Most Valuable Expert 2011
Top Expert 2016
Commented:
See if this provides any insights...
http://www.php.net/manual/en/pdostatement.fetchall.php
Olaf DoschkeSoftware Developer

Author

Commented:
In regard to fetchall: Yes that helps.

I'll still keep this open for all the other still open questions. Should I add table creation code to my sample code, for the ease or replicating the problem?

Bye, Olaf.
Olaf DoschkeSoftware Developer

Author

Commented:
OK, since there are no further answers and suggestions, I take it for granted there is no other solution with mysqli. Sometimes no answer is the answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial