What's wrong with that MySQL Query?

Question

I'm not familiar with Joins. The background about my SQL query is a friendlist with 3 rows: ID, ID2 and status. I also have a userlist with the rows ID and username. I want to check, if ID or ID2 from friendlist equals to the Session ID, after that I want to get the ID, which is not equal to the Session ID. I also need to check, if the status is 1 (friendship accepted). The friendID needs to be compared with the ID from userlist. After that I want to read out the username. I tried to solve the problem like this:

$sql = mysql_query("SELECT username AS u, status AS u FROM users 
JOIN friends AS u1 ON u.ID=u1.ID 
JOIN friends AS u2 ON u.ID=u2.ID2 
WHERE u1.status=1 AND u1.ID='".$_SESSION['ID']."' OR u2.ID2='".$_SESSION['ID']."'");
while($row = mysql_fetch_array($sql)){
    
    echo $row['username'];
    
    }

Silent · Accepted Answer

It seems like you want to use "u" as an alias for the "users" table, but you haven't actually done that. You've instead made it an alias for two different columns in the select clause. I'd suggest you try something like this instead:

SELECT u.username, u.status
FROM users u
JOIN friends u1 ON u.ID = u1.ID
JOIN friends u2 ON u.ID = u2.ID

...etc.

Brian K · Answer

1) If your solution will ever need to scale significantly, don't use "OR". It will bypass your indexes and search the entire table, which will get progressively slower as your table grows.

2) Why are you looking at the user and two other friends at once?

3) You are not (currently) displaying the status (since you only want status=1), so you don't need to SELECT it.

4) What you called "rows" in your question (ID, ID2, username, etc) are "columns" or "fields". A "row" is either a database record or a query result record, as used in your "while" loop.

Try a query like the following instead:

SELECT username FROM users u
JOIN friends f ON u.ID = f.ID
WHERE f.status=1 and f.ID2=$_SESSION[id]
UNION SELECT username FROM users u
JOIN friends f ON u.ID=f.ID2
WHERE f.status=1 and F.ID=$_SESSION[id]

(I'm assuming from your code that if users 1 and 2 are friends, you'll have an entry for 1, 2 or 2, 1 but not both.)

grinner · Answer

i think of of extra effective records is important from an errors log. yet listed decrease than are some subject concerns to earnings: a million) If the table has a customary and/or unique index on it, are you a) violating this forte, and b) no longer getting the errors? to earnings, see that the txt and direction cost you're attempting to insert would not already exist indoors the table. 2) are there different required fields you on the instantaneous are no longer specifying? 3) The text cloth fabric values in txt and direction might desire to be embedded in expenditures. whilst the strings are resolved with the help of skill of very own homestead internet site, that's what's sent to MySQL: insert into table values( ABC, DEF ) and what you probable desire is this: insert into table values( "ABC", "DEF" ) 4) You factor out that no longer the finished code is optimal right here. now and back, that's superb for brevity. yet in accordance to danger are you waiting to instruct what your connect merchandise looke like? i'm thinking if would be you engaged a transaction which you probable did no longer dedicate (so it implicitly rolled back?)

What's wrong with that MySQL Query?

3 Antworten