Solved

php MySQL Query for 2 tables to get 1 result

Posted on 2014-03-09
9
710 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
[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
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

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!

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

717 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