[RESOLVED] Putting a 2 field query in one array

slightlyeskew

Hey All. I've asked similar questions like this before but I'm having trouble implementing previous answers. I'm trying to query and return two fields into one array with a numeric key. The attempt:

error_reporting(E_ALL);
require_once 'MDB2.php';
$db = MDB2::connect();
    if (MDB2::isError($db)) { die("Can't connect: " . $db->getMessage()); }

//$loopcounter = 0;
$names = $db->query("SELECT Last_Name,First_Name FROM ShareholderNames WHERE ShareholderID < 10000 AND ShareholderID != 0 ORDER BY Last_Name ASC;");

$name = $names->fetchRow();

Now I know this doesn't retrieve all of the data, but it does work for the first entry. I'm not sure how to properly loop this so that all the entries are stored in $name[] with a numeric key. I tried using fetchAll in the method below:


$name = $names->fetchAll();
foreach ($name as $a => $b)
{
	foreach ($b as $c) {	
	}
}	
echo $c[5];

This prints nothing. I think that's because $c isn't really an array but a copy of an array. What's the easiest way to do this? As always, thanks for the advice!

halojoy

http://www.installationwiki.org/MDB2#Fetching_Data
installationwiki.org/MDB2#Fetching_Data
fetchAll() will give you an array of all records at once

This is different from fetchRow(), which will only fetch 1 row each time.
When using fetchRow() we will need to loop to get several rows.
With fetchAll() it is already done. Because all rows are already there!

<?php
error_reporting(E_ALL);
require_once 'MDB2.php';
$db = MDB2::connect();
    if (MDB2::isError($db)) { die("Can't connect: " . $db->getMessage()); }

$result = $db->query("SELECT Last_Name,First_Name FROM ShareholderNames WHERE ShareholderID < 10000 AND ShareholderID != 0 ORDER BY Last_Name ASC;");

$names = $result->fetchAll(); 

echo '<pre>'.print_r($names, true).'</pre>'; // see contents of $names array

?>

If you want to printout all names in a normal list:

$names = $result->fetchAll(); 

foreach($names AS $person){
    echo $person['Last_Name']. ', ' .$person['First_Name']. '<br />';
}

slightlyeskew

Thanks so much for the response halo! The first method really helped me visualize the array structure. I'm now just accessing the desired element with

echo $names[0][0]

Or whichever I want etc. Although you have completely solved my issue, I also tried the second method and when compiled I received a streaming screen of undefined indexes. Is there a reason that happened? Thanks so much for the advice!

halojoy

The second method maybe should work using

foreach($names AS $person){
    echo $person[0]. ', ' .$person[1]. '<br />';
}

But I do not know exactly how your script does.

Setting Fetch Mode

One more setting you'd probably want to define upfront is the fetch mode, or the way results will be returned to you. You can have them as an enumerated list (default option), associative arrays, or objects. Here are examples of setting the fetch mode:

$mdb2->setFetchMode(MDB2_FETCHMODE_ORDERED);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$mdb2->setFetchMode(MDB2_FETCHMODE_OBJECT);

Probably the friendliest and the most common fetch mode is the associative array, because it gives you the results as arrays where the keys are the names of the table columns. To illustrate the differences, consider the different ways of accessing the data in your result sets:

echo $result[0]; // ordered/enumerated array, default in MDB2
echo $result['name']; // associative array
echo $result->name; // object

There is one more fetch mode type, which is MDB2_FETCHMODE_FLIPPED. It's a bit exotic and its behavior is explained in the MDB2 API documentation as:

"For multi-dimensional results, normally the first level of arrays is the row number, and the second level indexed by column number or name. MDB2_FETCHMODE_FLIPPED switches this order, so the first level of arrays is the column name, and the second level the row number."

http://www.installationwiki.org/MDB2#Setting_Fetch_Mode

So, echo $result[0]; // ordered/enumerated array, default in MDB2
But you can change this mode in beginning of script.

My second method uses associative array.
Often short called ASSOC.

Putting this together with my second method, should work

<?php
error_reporting(E_ALL);
require_once 'MDB2.php';
$db = MDB2::connect();
    if (MDB2::isError($db)) { die("Can't connect: " . $db->getMessage()); }

$db->setFetchMode(MDB2_FETCHMODE_ASSOC);

$result = $db->query("SELECT Last_Name,First_Name FROM ShareholderNames WHERE ShareholderID < 10000 AND ShareholderID != 0 ORDER BY Last_Name ASC;");

$names = $result->fetchAll();

foreach($names AS $person){
    echo $person['Last_Name']. ', ' .$person['First_Name']. '<br />';
} 
?>

slightlyeskew

Thanks for the response Halo!