PHP MYSQL Join Query. SELECT WHERE AND IN OR Logic Error
Hey all. I think I have a logic error in my qry. The output is correct but in triplets. I’ve been staring at this for a long time and not seeing it. Can开发者_如何学运维 someone shed some light on this? Thanks!!
Also wanted to add this info as well.
- $userid = 1
- $UserIDAList = (1,1,6)
- $UserIDBList = (2,3,1)
PHP-Code:
$result = mysql_query("SELECT TBL_ContactsList.ContactID, TBL_ContactName.FirstName FROM TBL_ContactsList, TBL_ContactName WHERE ((TBL_ContactName.NameID != $userid) AND (TBL_ContactsList.ContactID != $userid)) AND ((TBL_ContactName.NameID IN ($UserIDAList) OR TBL_ContactName.NameID IN $UserIDBList)))");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf ("ID: %s Name: %s", $row[0], $row[1]);
echo "<br/>";
}
Only the SQL-Query (readability):
SELECT TBL_ContactsList.ContactID, TBL_ContactName.FirstName
FROM TBL_ContactsList, TBL_ContactName
WHERE ((TBL_ContactName.NameID != $userid) AND (TBL_ContactsList.ContactID != $userid))
AND ((TBL_ContactName.NameID IN ($UserIDAList) OR TBL_ContactName.NameID IN $UserIDBList)))
Output:
ID: 2 Name: Joe
ID: 3 Name: Joe
ID: 4 Name: Joe
ID: 2 Name: Jimbo
ID: 3 Name: Jimbo
ID: 4 Name: Jimbo
ID: 2 Name: Mike
ID: 3 Name: Mike
EDIT: Here is what I ended up using. (can't figure out indent on here.)
But now I am missing an entry from the db.
$result = mysql_query("
SELECT cl.ContactID, cn.FirstName
FROM TBL_ContactName AS cn
INNER JOIN TBL_ContactsList AS cl
ON cl.ContactID = cn.NameID
WHERE
cn.NameID != $userid
AND (
cn.NameID IN ($UserIDBList) OR cn.NameID IN ($UserIDAList)
)
");
The output looks like this.
But when I put LEFT JOIN I get this. Close but still missing ID.
ID: 2 Name: Joe ID: 3 Name: Jimbo ID: Name: MikeAny ideas?? THanks!
You should indent and shortcut your SQL for better readability
$result = mysql_query("
SELECT cl.ContactID, cl.FirstName
FROM TBL_ContactsList cl
JOIN TBL_ContactName cn
WHERE (
cn.NameID != $userid AND
cl.ContactID != $userid
) AND (
cn.NameID IN ($UserIDAList) OR
cn.NameID IN ($UserIDBList)
)
");
If I got your SQL structure correctly, changing SQL like this should probably fix it. At least it's a proper usage.
$result=mysql_query("SELECT cl.ContactID, cn.FirstName FROM TBL_ContactsList cl INNER JOIN TBL_ContactName cn ON cn.NameID=cl.ContactID WHERE cn.NameID != $userid AND (CN.NameID IN ($UserIDAList) OR CL.NameID IN ($UserIDBList))");
please give feedback, if this works out for you:
SELECT cl.ContactID, cn.FirstName
FROM TBL_ContactName AS cn
JOIN TBL_ContactsList as cl
ON cn.NameID = cn.ContactID
WHERE cn.NameID != $userid
AND (cn.NameID IN ($UserIDAList) OR cn.NameID IN ($UserIDBList));
精彩评论