Solved

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

Posted on 2012-04-11
9
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 110

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
LVL 110

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 110

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Simple function not working 7 43
Use Select Query to Return Results as a Form 9 41
EditableGrid how to fetch rows from MySql in php 14 44
MySQL programmer starter 25 20
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

740 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