CODEWITHSUNDEEP
phpmysql
Rocco The Taco
Rocco The Taco

Reputation: 3787

MySQL JOIN pass PHP variable to two tables

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'

Upvotes: 0

Views: 439

Answers (2)

sahar21
sahar21

Reputation: 186

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'

Upvotes: 1

Barmar
Barmar

Reputation: 781592

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

Upvotes: 1

Related Questions