Showing posts with label resultset. Show all posts
Showing posts with label resultset. Show all posts

Monday, October 17, 2011

MySQL resultset as an associative array

To save time and resources, when a php application is going to require the same resultset often, the resultset can be saved in a session as an associative array. Then when required in the script the saved session data can be referenced instead of querying the database again.

To return a resultset as an array named $ac_arr requires the following mysql...

$result=mysql_query("SELECT name,startdate,enddate FROM accounts") or die(mysql_error());

while(($ac_arr[] = mysql_fetch_assoc($result)) || array_pop($ac_arr));

The array_pop() function simply removes the extra element that would otherwise be appended to the array.

This can then be stored in a session variable and later referenced without re-querying the database...


$_SESSION['ac_array']=$ac_arr;

To list the keys and values of elements in the array

<?php
$acs=$_SESSION['ac_array'];
foreach($acs as $keys=>$values)
{
foreach ($values as $key => $value) {
echo $keys." : ".$key.":".$value."<br />";
}
}
?>

The above will display a list like this
0 : name : AC1
0 : startdate : 2011-08-16
0 : enddate : 2011-12-16
1 : name : AC2
1 : startdate : 2014-07-25
1 : enddate : 2024-07-25
2 : name : AC3
2 : startdate : 2012-05-10
2 : enddate : 2022-05-10

so you can reference any element in the array by calling
$acs[0][name] (will return AC1)
$acs[2][startdate] (will return 2012-05-10)