Getting column values as array, Zend_Db, Mysql, php

Question

I have the following database tables:

table: person Columns: id, first_name, age, city, state
table: study Columns: id, name, description, notes
table: person_studies Columns: person_id, study_id, notes

I need to get all the study names that a particular person is involved in:

person.id, person.first_name, person.city, study.name

This is the query I wrote to get the data for a person.id = 14:

select person.id, person.first_name, study.name from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

Since there could be multiple studies a person could be involved in, I am getting more than one row in the result set. I need to implement this using Zend_Db api's.

The questions are:

1. Is there any better way to write this query?

    `$result = $this->fetchAll($select);`

    [0] => Array
    (
      [person.id] => 14
      [first_name] =>Jamie
      [study_names] => Array 
                       (
                          [0] => biotechnology;
                          [1] => mathematics;
                          [2] => aeronautics;
                       )

    )

Any help would be greatly appreciated.

Thanks

user_stackoverflow · Accepted Answer · 2012-06-08 18:56:11Z

1

I could not find a better way to write the query, but as far as the second question goes, I found the following solution of using group_concat().

The final query looks like this now:

select person.id, person.first_name, person.city, person.state, group_concat(study.name separator '|') as studynames from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

We can also specify other clauses within group_concat eg distinct . Please refer to link http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat for detailed description about this clause.

answered Jun 8, 2012 at 18:56

user_stackoverflow

7742 gold badges10 silver badges19 bronze badges

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

Comments

Thomas Fussell · Accepted Answer · 2012-06-07 00:06:28Z

0

The query looks fine, though you seem to have missed one of the columns (assuming you actually need to use person.city):

select person.id, person.first_name, person.city, study.name from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

instead of:

select person.id, person.first_name, study.name from person
left join person_studies on person.id = person_studies.person_id
left join study on person_studies.study_id = study.id 
where person.id=14;

As for the results, I don't know of any way to get it formatted directly how you want. If you don't mind a little code, you can try taking the results you're getting and format them. Assuming you're using PDO (often used with Zend), it would look like this:

$statement = $db->query(<<query from above goes here>>);
$result = $statement->fetchAll(PDO::FETCH_ASSOC);

$personStudies = array();

foreach($result as $row)
{
    $personId = $row['person.id'];
    $studyName = $row['name'];
    $personStudies[$personId]['first_name'] = $row['first_name'];
    $personStudies[$personId]['study_names'][] = $studyName;
}

foreach($personStudies as $personId=>$person)
{
    $formattedRow = array();
    $formattedRow['person.id'] = $personId;
    $formattedRow['first_name'] = $person['first_name'];
    $formattedRow['study_names'] = $person['study_names'];

    $formattedResults[] = $formattedRow;
}

And $formattedResults should look just how you specified. Otherwise, you can do the query in two parts:

$statement = $db->query('SELECT person.first_name FROM person WHERE person.id=14 LIMIT 1');
$person = $statement->fetch(PDO::FETCH_ASSOC);
$name = $person['first_name'];

$statement = $db->query('SELECT study.name FROM person_studies, study WHERE person_studies.study_id = study.id AND person_studies.person_id=14'); // Same as a left join
$studyNames = $statement->fetch(PDO::FETCH_COLUMN, 0);

$formattedRow = array();
$formattedRow['person.id'] = 14;
$formattedRow['first_name'] = $name;
$formattedRow['study_names'] = $studyNames;
$formattedResults[] = $formattedRow;

which should give the same results. I haven't tested the code, but it should be close to what you need.

answered Jun 7, 2012 at 0:06

Thomas Fussell

4683 silver badges9 bronze badges

2 Comments

user_stackoverflow Over a year ago

Thank you for your reply @Thomas . I was hoping to find a solution where I do not have to post process the sql results. I did find a solution to do so, as mentioned in my answer. Appreciate your taking time to help answer this question.

Thomas Fussell Over a year ago

I'm glad you found a solution. I thought there might be an SQL way to do it. I'll keep that function in mind.

Collectives™ on Stack Overflow

Getting column values as array, Zend_Db, Mysql, php

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related