Solved

PHP MySQL and joining two table results

Posted on 2016-09-17
4
68 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 500 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 78

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 78

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to count occurrences of each item in an array.
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 …

726 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