?
Solved

PHP MySQL and joining two table results

Posted on 2016-09-17
4
Medium Priority
?
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 79

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 79

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

800 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