MySQL JOIN pass PHP variable to two tables

Question

I have the following MySQL query in PHP that passes a variable to complete the query:

SELECT * from mobile_tech WHERE uid=$uid  order by timestamp DESC limit 0,1

I have the following MySQL JOIN that provides data from two tables:

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=gbl_qemplisting.EmpNo AND date(timestamp)='$currentday' 
group by uid

I need to combine these two queries into one with a JOIN and still passing the $uid variable to complete the query.

I've tried the following and it did not work:

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=$uid AND gbl_qemplisting.EmpNo=$uid AND date(timestamp)='$currentday'

It would help if you post an example of input rows from the two tables and the output you want the query to produce. You have a GROUP BY but no aggregate functions (COUNT(),SUM(),MAX() etc) so it isn't clear what the intended result should be. Is there to be only one row returned? How many would be expected from the gbl_qemplisting table? — Michael Berkowski
– Michael Berkowski, Commented Jun 3, 2016 at 19:05
I want to output the values specified in the select from the two tables. The original JOIN achieves this by comparing and matching to produce the values indicated. I need to produce those same values but instead of matching I need to pass the PHP variable to achieve the same result. Group By is not necessary. — Rocco The Taco
– Rocco The Taco, Commented Jun 3, 2016 at 19:16
What does "did not work" mean? What happens when you input the query directly into MySQL? — miken32
– miken32, Commented Jun 3, 2016 at 19:17
The original query produces one row per employee because of the GROUP BY. The last query will return the cross product between the two tables. If one table has 5 rows for the employee, and the other has 2 rows, you'll get 10 rows. — Barmar
– Barmar, Commented Jun 3, 2016 at 19:38

Barmar · Accepted Answer · 2016-06-03 19:41:06Z

1

Your query will return a cross product between the mobile_tech and gbl_emplisting rows for $uid. If you just want one row, as in the first query, use ORDER BY and LIMIT similarly.

SELECT mobile_tech.latitude, mobile_tech.longitude, mobile_tech.timestamp, mobile_tech.uid, gbl_qemplisting.EmpNo, gbl_qemplisting.FirstName, gbl_qemplisting.LastName 
FROM mobile_tech, gbl_qemplisting
WHERE mobile_tech.uid=$uid AND gbl_qemplisting.EmpNo=$uid AND date(timestamp)='$currentday'
ORDER BY mobile_tech.timestamp
LIMIT 1

answered Jun 3, 2016 at 19:41

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rocco The Taco Over a year ago

Excellent, really appreciate the help!

sahar21 · Accepted Answer · 2016-06-03 20:54:28Z

1

Please try with this and say the result

SELECT mobile_tech.latitude, 
mobile_tech.longitude, 
mobile_tech.timestamp,
mobile_tech.uid,
gbl_qemplisting.EmpNo,
gbl_qemplisting.FirstName,
gbl_qemplisting.LastName
FROM mobile_tech inner join gbl_qemplisting
        on mobile_tech.uid=gbl_qemplisting.EmpNo 
where mobile_tech.uid=$uid AND date(timestamp)='$currentday'

answered Jun 3, 2016 at 20:54

sahar21

1862 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL JOIN pass PHP variable to two tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related