Solved

php MySQL Query for 2 tables to get 1 result

Posted on 2014-03-09
9
703 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

Expert Comment

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do uses indexes to maximize MySQL Searches 14 54
PHP Curl to output a url 7 45
PHP Mail error 3 26
PHP delete contents of file- before writing to it 6 17
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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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.

791 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