PHP MySQL and joining two table results

maccaj51
maccaj51 used Ask the Experts™
on
Hi Experts,

I have two tables that rows are linked by the 'PersonRef' field.

Table 'person' example:
PersonRef     FirstName      LastName
12                   Jim                   Smith
23                  Alex                  Lewis
34                  Linda                 Smith

Table 'person_meta' example:
PersonRef     Field               Value      
12                   Age                   24
12                   Title                   Mr
23                   Age                   24
23                   Title                   Mr
34                   Age                   24
34                   Title                   Mrs

I am looking for a sql query (mysqli_connect) that would return any person who is 'Age' = '24' and 'LastName' = 'Smith' but in an array with this format:
[0] => array('FirstName'=>'Jim','LastName'=>'Smith','Meta'=>array('Title'=>'Mr','Age'=>'24'))
[1] => array('FirstName'=>'Linda','LastName'=>'Smith','Meta'=>array('Title'=>'Mrs','Age'=>'24'))

So basically searching the two tables and linking the data. Removing PersonRef 23 as they don't have a LastName Smith.

Any help would be gratefully received. I've been going round in circles with Joins.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Guru
Most Valuable Expert 2011
Commented:
Formatting the result would be best put in a separate question. Get the query sorted first, and then worry about formatting the results after that. Try this query:

select FirstName, LastName, MetaTitle.Value Title, MetaAge.Value Age
from person
join person_meta MetaTitle on MetaTitle.PersonRef = person.PersonRef
join person_meta MetaAge on MetaAge.PersonRef = person.PersonRef
where MetaAge.Value = '24'
and LastName = 'Smith'

Open in new window


Note that unless you use left joins (which I haven't), you'll only get a row returned for a person when there exists both a title record in person_meta and an age record in person_meta
Distinguished Expert 2017

Commented:
using a case/if statement within the join statement.



Select p.firstname,p.adtname,
(Get the title) as title,
(Get the age) as age
From person p join person_meta f on f.petsonref=p.personref
Where f.field='Age' and f.value=24 and p.lastname='Smith'
Group by p.lastname,p.firstname

This way if one is missing, you would still get the results.
Distinguished Expert 2017

Commented:
Here is the reference to case
https://dev.mysql.com/doc/refman/5.7/en/case.html

For age
(Case
 When f.field='Age' then f.value
      Else ''
End case) as Age
For Title
(Case
  When f.field='Title' then f.value
   Else ' '
End case) As Title

If mo title, you will get an empty string in the corresponding column, but if there is no Age, your query will return no values.

Author

Commented:
Thanks very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial