Create a Multidimensional Array with PHP and MySQL

Question

I am new to PHP and am looking at efficient ways to return data from the database. Lets say I have a UserProfile table that has a one to many relationship with UserInterest and UserContact:

Select p.Id, p.FirstName, p.LastName, i.Name as Interests, c.Email, c.Phone
from UserProfile p
left join UserInterest i on p.Id = i.UserProfileId
left join UserContact c on p.Id = c.UserProfileId
where p.Id = 1

An efficient way to retrieve data would be to create a multidimensional array such as:

$user = array(  "FirstName" => "John", 
                "LastName" => "Doe", 
                "Gender" => "Male", 
                "Interests" => array(
                    "Hiking", 
                    "Cooking"), 
                "Contact" => array(
                    "Email" => "[e-mail]", 
                    "Phone" => "[phone]"));

I can't seem to get my head around how this would be constructed in PHP. For simple data like interests I could use group_concat(i.Name) as Interests in the query to return interests back as a comma separated list in a single row, however, for an associative array such as Contact, I'd like to be able to get a handle on each key in the array using $user['Contact']['Email'].

From a "Best Practices" standpoint, I would assume that constructing an array like this in one query is a lot better than hitting the database multiple times to retrieve this data.

Thanks!

Neil

Answer 1

You can construct this array in one pass through the data returned by your query. In pseudo-code:

for each row
     $user["FirstName"] = row->FirstName;
     $user["LastName"] = row->LastName;
     $user["Interests"][] = row->Interests;
     $user["Contact"]["Email"] = row->Email;
     $user["Contact"]["Phone"] = row->Phone;
next

The syntax $user["Interests"][] = $data is valid PHP code. It is equivalent to array_push($user["Interests"], $data).

Answer 2

My guess is that you would have to run separate queries and manually construct the array yourself. I don't personally know of any MySQL database resources in PHP that return rowsets like that in a multidimensional array the way you described.

Answer 3

A better way of doing this could be:

$query = "SELECT p.Id, p.FirstName, p.LastName, i.Name as Interests,
          c.Email, c.Phone
          FROM UserProfile p
          LEFT JOIN UserInterest i ON p.Id = i.UserProfileId
          LEFT JOIN UserContact c ON p.Id = c.UserProfileId
          WHERE p.Id = 1";

$res = GIDb::pg_query($query);

if(pg_num_rows($res) > 0)
{
    while($data = pg_fetch_assoc($res))
    {
        $id = $data['Id'];
        $f_name = $data['FirstName'];
        $l_name = $data['LastName'];
        $interests = $data['Interests'];
        $email = $data['Email'];
        $phone = $data['Phone'];
    }
}

//You can also directly use $data['Id'] etc.

Also don't forget to include in the beginning of the php code : include_once(dirname(__FILE__)."/../../class.GIDb.php");

EDIT :
This is for Postgres.
For MySQL use: mysql_query(), mysql_fetch_assoc(), mysql_num_rows()