CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
Worldctzen
Worldctzen

Reputation: 3

Using a single PHP array vs multiple MySQL calls

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.

Upvotes: 0

Views: 104

Answers (1)

Jeff
Jeff

Reputation: 9664

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             [email protected]
vice-president  Vice-President   John             [email protected]
treasurer       Treasurer        Tyler             
member          Member           Clem             [email protected]
member          Member           Blato            [email protected]
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' => 
        '[email protected]'
    ],
    'vice-president' => [
        'OfficerTitle' => 'Vice-President', 
        'OfficerName' => 'John', 
        'OfficerEmail' => 
        '[email protected]'
    ],
    'treasurer' => [
        'OfficerTitle' => 'Treasurer', 
        'OfficerName' => 'Tyler', 
        'OfficerEmail' => ''
    ],
    'member' => [
        'OfficerTitle' => 'Member', 
        'OfficerName' => 'Clemo', 
        'OfficerEmail' => '[email protected]'
    ],
    'member' => [
        'OfficerTitle' => 'Member', 
        'OfficerName' => 'Blato', 
        'OfficerEmail' => '[email protected]'
    ],
    '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:[email protected]">Mike</a>
Title = Vice-President , mail = <a href="mailto:[email protected]">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' => '[email protected]',
)

Upvotes: 1

Related Questions