Solved

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

Posted on 2012-04-11
9
344 Views
Last Modified: 2012-04-19
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

0
Comment
Question by:Olaf Doschke
  • 6
  • 3
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37832040
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
 
LVL 29

Author Comment

by:Olaf Doschke
ID: 37832054
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
 
LVL 29

Author Comment

by:Olaf Doschke
ID: 37832101
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
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 0 total points
ID: 37832517
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 37833751
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
 
LVL 29

Author Comment

by:Olaf Doschke
ID: 37833906
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 37834002
See if this provides any insights...
http://www.php.net/manual/en/pdostatement.fetchall.php
0
 
LVL 29

Author Comment

by:Olaf Doschke
ID: 37841761
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
 
LVL 29

Author Closing Comment

by:Olaf Doschke
ID: 37864810
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Apache/PHP: Cannot make phpinfo() and phpmyadmin work 4 33
PHP - AJAX and MySQL it works only if the value is a number 12 33
MySQL  on Tomcat 8 23
PHP: concatenate query 12 33
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now