PHP MySQL and joining two table results

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.

maccaj51Asked: 2016-09-17

Who is Participating?

LVL 35

Terry WoodsIT GuruCommented: 2016-09-17

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

LVL 82

arnoldCommented: 2016-09-17

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.

LVL 82

arnoldCommented: 2016-09-17

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.