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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Olaf DoschkeSoftware 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
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PaseurCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.