Using a single PHP array vs multiple MySQL calls

Question

I've been searching on this topic for a couple days, but I'm not finding a solution, so maybe it's not possible?

Currently I have page that makes dozens of MySQL calls for name and email information.

<?php echo get_officer_listing("president"); ?>

these call the function get_officer_listing($position)

$sql = "SELECT OfficeTitle, OfficerName, OfficeEmail FROM officers WHERE officeshort = \"$position\"";
$officer = mysql_query($sql);
while($field = mysql_fetch_array($officer)) {
    $title = $field["OfficeTitle"];
    $name = $field["OfficerName"];
    $email = $field["OfficeEmail"];
}
if (empty($name)) {$name = "Vacant";}
if (empty($email)) {$mailto = $name;}
    else {$mailto = '<a href="mailto:'.$email.'">'.$name.'</a>';}

echo '<b>'.$title.'</b><br>'.$mailto.'<br>';

}

This works for returning a single officer listing with Office name and Officer name as a mailto link.

What I would like to do instead is call the officer table once and store all relevant records in a multivariable associative array indexed on the officerShortName. Then, at each officer listing, I could echo the array values like

<?php
$OfficeShortName = 'president';
echo $array[$OfficeShortName]['position'];
echo $array[$OfficeShortName]['OfficerName'];
echo $array[$OfficeShortName]['OfficeEmail'];
?>

So far I've been able to pull the records into an array and print all of them out in the While loop. But I haven't been able to call a single record's values after the loop.

Am I just not understanding how arrays work? Or is there a way to do this? Any help would be appreciated.

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 02 '16 at 21:48
Have you tried http://php.net/manual/en/function.array-search.php? — Jay Blanchard, Feb 02 '16 at 21:50
Sounds like you could put your data into session arrays which would survive outside of the loop they were loaded in http://www.w3schools.com/php/php_sessions.asp and http://php.net/manual/en/session.examples.basic.php so instead of array[] it would be $_SESSION['officers'][0]; also http://stackoverflow.com/questions/33825308/store-mysqli-array-in-php-session gives a good demo using mysqli — Steve, Feb 02 '16 at 21:54
:Lots of other useful links here http://stackoverflow.com/questions/29611072/store-mysqli-query-result-in-session — Steve, Feb 02 '16 at 22:01

Jeff · Accepted Answer · 2016-02-03T07:40:47.297

You are almost there, but you need to keep track of an array inside of your while() loop.

Here is some example code that shows how to print all rows as well as a row for a single type of position

Since you didnt provide your schema, I am estimating that your database looks like this

OfficerShort    OfficerTitle    OfficerName       OfficerEmail   
------------    -------------    --------------   ----------------
president       President        Mike             mike@nowhere.com
vice-president  Vice-President   John             john@nowhere-else.com
treasurer       Treasurer        Tyler             
member          Member           Clem             clem@foo.com
member          Member           Blato            blato@bar.com
member          Member           Casper

The following code, re-uses and builds upon what you started. You should really use PDO objects for you database connection

$sql = "SELECT OfficerTitle, OfficerName, OfficeEmail FROM officers WHERE officeshort = \"$position\"";
$officerResult = mysql_query($sql);

$allOfficers[$postion] = [];

while($row = mysql_fetch_array($officerResult)) {
    $title  = $row["OfficerTitle"];
    $name   = !empty($row["OfficerName"]) ? $row["OfficerName"] : 'Vacant';
    $email  = $row["OfficerEmail"];
    $mailto = !empty($row['OfficerEmail']) ? '<a href="mailto:'.$email.'">'.$name.'</a>' : $name;

    $allOfficers[$postion]['OfficerTitle'] = $title;
    $allOfficers[$postion]['OfficerName']  = $name;
    $allOfficers[$postion]['OfficerEmail'] = $mailto;
}

The $allOfficers array will now be populated like so :

$allOfficers = [
    'president' => [
        'OfficerTitle' => 'President', 
        'OfficerName' => 'Mike', 
        'OfficerEmail' => 
        'mike@nowhere.com'
    ],
    'vice-president' => [
        'OfficerTitle' => 'Vice-President', 
        'OfficerName' => 'John', 
        'OfficerEmail' => 
        'john@nowhere-else.com'
    ],
    'treasurer' => [
        'OfficerTitle' => 'Treasurer', 
        'OfficerName' => 'Tyler', 
        'OfficerEmail' => ''
    ],
    'member' => [
        'OfficerTitle' => 'Member', 
        'OfficerName' => 'Clemo', 
        'OfficerEmail' => 'clem@foo.com'
    ],
    'member' => [
        'OfficerTitle' => 'Member', 
        'OfficerName' => 'Blato', 
        'OfficerEmail' => 'blato@bar.com'
    ],
    'member' => [
        'OfficerTitle' => 'Member', 
        'OfficerName' => 'Casper', 
        'OfficerEmail' => ''
    ]
];

You could view all of the data at once like this :

foreach ($allOfficers as $postion => $data) {
    $title  = $data["OfficerTitle"];
    $name   = !empty($data["OfficerName"]) ? $data["OfficerName"] : 'Vacant';
    $email  = $data["OfficerEmail"];
    $mailto = !empty($email) ? '<a href="mailto:'.$email.'">'.$name.'</a>' : $name;

    $mailto = !empty($email) ? '<a href="mailto:'.$email.'">'.$name.'</a>' : $name;
    echo "Title = " . $title . " , mail = "  . $mailto . "\n";
}

// output

Title = President , mail = <a href="mailto:mike@nowhere.com">Mike</a>
Title = Vice-President , mail = <a href="mailto:john@nowhere-else.com">John</a>
Title = Treasurer , mail = Tyler
Title = Member , mail = Casper

If you wanted to only see 1 position at a time, you could do so by

var_export($allOfficers['vice-president']);

// output

array (
  'OfficerTitle' => 'Vice-President',
  'OfficerName' => 'John',
  'OfficerEmail' => 'john@nowhere-else.com',
)

Your answer would work for a single officer $position value. The intent is to populate the array for all officer positions. Then at each officer listing, be able to supply the keyword for that office, and get back the prepared officer listing from the array, rather than each listing querying from the database. Is that even possible? — Worldctzen, Feb 03 '16 at 06:43
I added some additional code, sample data and sample output to provide clarity — Jeff, Feb 03 '16 at 07:41
thanks for the assist! Needed some tweaking, but got it working as desired. You got me over a mental hump on how arrays are addressed. — Worldctzen, Feb 05 '16 at 02:18

Using a single PHP array vs multiple MySQL calls

1 Answers1