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

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

LVL 31
Olaf DoschkeSoftware DeveloperAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor 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.
0
 
Ray PaseurCommented:
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

0
 
Olaf DoschkeSoftware DeveloperAuthor 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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Olaf DoschkeSoftware DeveloperAuthor 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.
0
 
Olaf DoschkeConnect With a Mentor Software DeveloperAuthor 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.
0
 
Olaf DoschkeSoftware DeveloperAuthor 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.
0
 
Ray PaseurConnect With a Mentor Commented:
See if this provides any insights...
http://www.php.net/manual/en/pdostatement.fetchall.php
0
 
Olaf DoschkeSoftware DeveloperAuthor 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.
0
 
Olaf DoschkeSoftware DeveloperAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.