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:
 => array('FirstName'=>'Jim','LastName'=>'Smith','Meta'=>array('Title'=>'Mr','Age'=>'24'))
 => 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.
(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.