?
Solved

php MySQL Query for 2 tables to get 1 result

Posted on 2014-03-09
9
Medium Priority
?
719 Views
Last Modified: 2014-03-09
Hi-

I have a web form that collects 3 pieces of information (first name, last name, tesla) which are passed to a php script.  I need the script to query 2 tables:

Table1: ID; FirstName; LastName
Table2: Tesla

If the two tables have matching entries for the user form, then I want the MySQL query to return the ID from table 1

e.g.:

Form submitted:
FirstName: Jack
LastName: Spratt
Tesla: 23

Table1: ID: 123; FirstName: Jack; LastName: Spratt
Table2: Tesla: 18

Because there is no matching row for Tesla (i.e. 18), I want the query to return 0 rows; if the user had entered "23", then I want the query to return "123" (the ID from matching row of table1)

What MySQL query will do this?
0
Comment
Question by:SAbboushi
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 58

Assisted Solution

by:Gary
Gary earned 800 total points
ID: 39916271
select ID from Table1 
inner join Table2
where
FirstName='Jack' AND
LastName='Spratt' AND
Tesla=18

Open in new window


Not an ideal way to do a join.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39916274
Is there a relation between those tables?
Cause if it's not, I think you're going to need 2 queries, and run the second query only if the first returns more than 0 results.

$query1 = "SELECT tesla FROM Table2 WHERE tesla=" . $_POST['tesla'];
$query2 = "SELECT ID FROM Table1 WHERE FirstName = '" . $_POST['FirstName'] . "' AND LastName = '" . $POST['LastName'] . "'";

Open in new window

HTH,
Dan
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 1200 total points
ID: 39916278
Gary, if those tables have each 100 rows, MySQL will have to search among the 10.000 rows from that JOIN.
If each table has 1.000 rows, the resulting joined table will have 1.000.000 rows. Things will go out of hand very quickly.

Dan
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:Gary
ID: 39916283
That's why I said
Not an ideal way to do a join.

But we can only guess at the reasoning and the final result. The logic of this makes no sense anyway - why bother searching for a number in a table that only stores a number (probably more to it than at first seems)
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39916287
OK, that's an understatement :)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39916296
@SAbboushi: I hope you are open to some design suggestions.  Please post the CREATE TABLE statements for these tables so we can see what is really in the table definitions.  And post the HTML form that collects this information.  With that information in front of us we may be able to help you redesign this application so it will work correctly and efficiently.
0
 

Author Closing Comment

by:SAbboushi
ID: 39916302
Thanks - I now realize the need for redesign.
0
 

Author Comment

by:SAbboushi
ID: 39916315
Hmmm... not exactly what I asked for, but how about:

select
(
select ID from Table1
where
FirstName='Jack' AND
LastName='Spratt'
) as ID,
(
select ID from Table2
WHERE
Tesla=18
) as tesla

If either column is NULL, then I know "validation" has failed.

Can you see any downside to this approach?
0
 
LVL 58

Expert Comment

by:Gary
ID: 39916318
That works too
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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 dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 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