Solved

php MySQL Query for 2 tables to get 1 result

Posted on 2014-03-09
9
694 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 34

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 34

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wordpress type image upload 10 29
.htaccess file settings 4 35
Apostophes in PHP generated form 6 17
regex expression 9 22
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article discusses four methods for overlaying images in a container on a web page
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 dynamically set the form action using jQuery.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now