[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP MySQL and joining two table results

Posted on 2016-09-17
4
Medium Priority
?
85 Views
Last Modified: 2016-09-18
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.
0
Comment
Question by:maccaj51
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 2000 total points
ID: 41803387
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
0
 
LVL 81

Expert Comment

by:arnold
ID: 41803412
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.
0
 
LVL 81

Expert Comment

by:arnold
ID: 41803415
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.
0
 

Author Closing Comment

by:maccaj51
ID: 41803623
Thanks very much.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 22 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question