Creating a multidimensional array with PHP and MySQL

Question

I am new to PHP and looking for efficient ways to return data from a database. Suppose 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 obtain data would be to create a multidimensional array, for example:

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

I can't seem to figure out how this will be built 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 on the same line, however for an associative array like Contact, I would like to have the ability to get a handle to each key in the array using $ user ['Contact'] ['Email'].

From a “best practice” point of view, I would suggest that building such an array in a single query is much better than hitting the database several times to retrieve this data.

Thanks!

Neil

Answer 1

You can build this array in one pass through the data returned by your request. 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 

Syntax $user["Interests"][] = $data is valid PHP code. This is equivalent to array_push($user["Interests"], $data) .

Answer 2

I assume that you will have to run individual queries and manually create the array yourself. I personally do not know any MySQL database resources in PHP that return similar rows in a multidimensional array as you described.

Answer 3

The best way to do 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 do not forget to include at 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()