4

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" => "[email protected]", 
                    "Phone" => "(555) 555-5555"));

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

Improve this question
2
  • 1
    If you're looking for best practices, you should look at PDO (php.net/pdo). Commented Nov 27, 2011 at 21:14
  • UserInterests is the only part that truly looks like it's one-to-many, if UserContact only references one row per user. If that's the case, you will need to get UserInterests in another query, or possibly use a subselect and concatenate all of the interest results into one field in the first query. Commented Nov 27, 2011 at 21:27

3 Answers 3

Reset to default
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

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()

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.