Solved

php MySQL Query for 2 tables to get 1 result

Posted on 2014-03-09
9
707 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 200 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 300 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
Industry Leaders: 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 110

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to count occurrences of each item in an array.
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…

679 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