Page 1 of 1

MySQL query result into an Array

Posted: Tue Sep 14, 2010 10:49 am
by IGGt
I think I'm going nuts here, but I must be missing something really obvious.
I have a MySQL query that returns a list of results:

Code: Select all

SELECT
   id AS 'ID', 
   date AS 'Date' 
from table tab1;
From here I want to display 'date', but use 'ID' for reference (e.g. IF ID=xxx Print Date).

So I figure I need to put the result into an Array, and then I can just call off the relevant part to display, but I am getting duplicates of everything.

I have:

Code: Select all

$a = 0;							
$dataArray = array();
if (isset($runid)) {
		mysql_select_db($db, $connection) ;
		$result = mysql_query ($query, $connection);
		while ($row = mysql_fetch_assoc($result))
			foreach ($row AS $attribute )
				{
					$dataArray[$a]['id'] = $row['id'] ;
					$dataArray[$a]['date'] = $row['Date'];
					$a = $a++;
        		}	
} else {
		print "<p>No Data Found</p>"; };
I was expecting to get the equivalent of:
Array (
[0] => Array ( [id] => 123456 [date] => 2010-09-07 06:30:02 )
[1] => Array ( [id] => 789012 [date] => 2010-09-06 05:45:08 )
[2] => Array ( [id] => 345678 [date] => 2010-09-08 03:20:21 )

Instead I am getting
Array (
[0] => Array ( [id] => 381357 [date] => 2010-09-07 06:30:02 )
[1] => Array ( [id] => 381357 [date] => 2010-09-07 06:30:02 )
[2] => Array ( [id] => 381358 [date] => 2010-09-08 03:20:21 )
[3] => Array ( [id] => 381358 [date] => 2010-09-08 03:20:21 )

As you can see, the data is duplicated, so that [0] and [1] are the same, as are [2] and [3] etc.

Am I missing something really obvious here?

Re: MySQL query result into an Array

Posted: Tue Sep 14, 2010 11:11 am
by AbraCadaver
Move $a = $a++; out of the foreach() into the while(). But I don't see why you're doing this. This should be the same:

Code: Select all

mysql_select_db($db, $connection) ;
$result = mysql_query ($query, $connection);

while ($row = mysql_fetch_assoc($result))
		$dataArray[] = array('id'=>$row['id'],'Date'=>$row['Date']);
}
Or if you only SELECT id, Date in your query, then this:

Code: Select all

while ($row = mysql_fetch_assoc($result))
		$dataArray[] = $row;
}

Re: MySQL query result into an Array

Posted: Tue Sep 14, 2010 11:22 am
by JakeJ
You don't need a for each loop inside a while loop.

Code: Select all

$qry = mysql_query("SELECT * from table");
while ($row = mysql_fetch_array($qry) {
  echo $row['date'];
}
It will iterate through each of the rows.

Re: MySQL query result into an Array

Posted: Wed Sep 15, 2010 4:29 am
by IGGt
cheers guys,

It was the end of a long day (that's my excuse anyway). When I see it written out the way you have I can see that I was just making it far more complicated than it needed to be.


cheers again.