JOIN Broken

[tomyknoker]

I previously had the following...

"SELECT
tblmember.*,
tblstock.*,
tblmanager.mng_Firstname,
tblmanager.mng_Lastname
FROM tblmembers
LEFT JOIN tblemployment ON (tblmember.ID = tblstock.MembersID)
LEFT JOIN tblmanager ON (tblmember.mngNBR = tblmanager.mngNBR)
WHERE `FirstName` LIKE '%$firstName%'
AND `LastName` LIKE '%$lastName%'
AND `Email` LIKE '%$email%'
AND `EmployerLocationName` LIKE '%$venue%'
AND `State` LIKE '%$state%'
AND `MemberApproved` LIKE '%$status%'
AND `mngNBR` LIKE '%$mng%'";However this is now a query from a search from hence all the variables, and now I don't think this line is recognised... LEFT JOIN tblrepresentatives ON (tblmembers.mngNBR = tblrepresentatives.mngNBR), I have tried this LEFT JOIN tblrepresentatives ON (tblmembers.mngNBR LIKE '%$mng% = tblrepresentatives.mngNBR LIKE '%$mng%) but doesn't work either... Any help would be greatly apprecaited!

[Fumigator]

Do you know what the difference is between a LEFT (outer) JOIN and an INNER JOIN and do you know why you are using an outer join?

[tomyknoker]

So I can keep my unmatched rows, why? are you saying this query is incorrect because it works flawlessy on another php page I have, just not here and the only reason I can see is this section but not too sure...

WHERE `FirstName` LIKE '%$firstName%'
AND `LastName` LIKE '%$lastName%'
AND `Email` LIKE '%$email%'
AND `EmployerLocationName` LIKE '%$venue%'
AND `State` LIKE '%$state%'
AND `MemberApproved` LIKE '%$status%'
AND `mngNBR` LIKE '%$mng%'

[Fumigator]

That the query works elsewhere is a helpful bit of info you failed to mention in your post....

How bout you echo the query text (with all those variables filled in).

[tomyknoker]

I just get mysql_num_rows(): supplied argument is not a valid MySQL result resource and mysql_fetch_array(): supplied argument is not a valid MySQL result resource, meaning the query has an error, but I don't get it as it works fine previously... Ok echoed out the full query with all the search fields entered... It echoes correctly too as the mngNBR is filled in with the number...

blmember.*,
tblstock.*,
tblmanager.*
FROM tblmember
LEFT JOIN tblmanager ON (tblmember.mngNBR = tblmanager.mngNBR)
LEFT JOIN tblstock ON (tblmember.ID = tblstock.MembersID)
WHERE FirstName LIKE '%test%'
AND LastName LIKE '%test%'
AND Email LIKE '%me@mydomain.com%'
AND EmployerLocationName LIKE '%test%'
AND State LIKE '%test%'
AND MemberApproved LIKE '%A%'
AND mngNBR LIKE '%1%' AND JoinDate >= '2006-12-25' && JoinDate <= '2006-12-25' AND MemberDate >= '2006-12-25' && MemberDate <= '2006-12-25' AND DATE(loginDate) BETWEEN '2006-12-25' AND '2006-12-25' ORDER BY `LastName`

If I remove


tblmanager.*
LEFT JOIN tblmanager ON (tblmember.mngNBR = tblmanager.mngNBR)


then it works, very strange...

[guelphdad]

I'll bite, lets throw everything else away except for the joins themselves:


FROM tblmembers
LEFT JOIN tblemployment ON (tblmember.ID = tblstock.MembersID)
LEFT JOIN tblmanager ON (tblmember.mngNBR = tblmanager.mngNBR)


where is tblstock? you have referenced three tables here tblmembers, tblemployment and tblmanager

you should get in the very good habit of referring to a table and then the next table you need and then specifying the join between those tables and those tables only and then move on to the next table.

So when you reference

tableA INNER JOIN tableB
for instance, the very next thing you should have in your query is the ON condition of those two tables ...

tableA INNER JOIN tableB
ON tableA.column=tableB.column

because it is much easier to see when you make mistakes as you have above when you do so. The () are then not even necessary.