Trouble With A Table Join

[stevew]

I would like to join these two SELECT statements into one.

 

$sql="SELECT logoutdate FROM users WHERE name LIKE '$name'";
$result=mysql_query($sql);
while ($rows = mysql_fetch_assoc($result)){
$logoutdate=$rows['logoutdate'];
}
2012-10-12 07:25:22

$sql="SELECT datesent FROM messages WHERE name LIKE '$name' ORDER by ASC datesent";
$result=mysql_query($sql);
while ($rows = mysql_fetch_assoc($result)){
$datesent=$rows['datesent'];
}
2012-10-12 07:20:39

 

This is my attempt...but it does not return the users.logoutdate

 

$sql="SELECT messages.datesent, users.logoutdate ON messages,users WHERE messages.name=users.name
ORDER by ASC datesent";
$result=mysql_query($sql);
while ($rows = mysql_fetch_assoc($result)){
$datesent=$rows['datesent'];
$logoutdate=$rows['logoutdate'];
}
2012-10-12 07:20:39

[Jessica]

1. You should be using a user id, rather than a name, as a unique identifier for each row.

2. You haven't specified what tables to select from. A better way to do it is:

SELECT messages.datesent, users.logoutdate 
FROM users 
LEFT JOIN messages 
ON users.user_id = messages.user_id 
WHERE users.user_id = $user_id
ORDER by ASC datesent

 

3. You are not capturing mysql errors, see my signature.

 

4. Your entire intent seems flawed, however. You realize this will get you every datesent in the messages table for that user, with the ONE single user's log out time? Why would you want that?

Edited October 12, 2012 by Jessica

[stevew]

I plan to use upon login:

if ($datesent>$logoutdate){
echo "you have new messages";
}

 

4. Yes...each user will only have one logout date in users.logoutdate

however, users will have multiple rows in messages.name,datesent etc as they will receive multiple

messages from other users.

 

1. I use name as the unique id because to pair the messages to "joe smith" in messages.name to user 'joe smith' in users.name. but I supposeI could INSERT user_id of the sender instead of the name, and then just use where messages.user_id = users.id.....

 

2. will experiment.

 

3. thx

 

 

P.S

I would prefer return all the matching rows from messages.datesent so I can then use:

 

$numrows = mysql_num_rows($sql);

print"you have $numrows new messages etc...";

Edited October 12, 2012 by stevew

[Jessica]

1. You should be using a user id, rather than a name, as a unique identifier for each row.

 

If you can't figure out how to do the rest of what you're saying based on the query I gave you, then you need to go back to the basics.

 

[stevew]

1. I use name as the unique id because to pair the messages to "joe smith" in messages.name to user 'joe smith' in users.name. but I suppose I could INSERT user_id instead of name.

[Jessica]

YA THINK.

[stevew]

 

but

 

 

$sql="SELECT messages.datesent, users.logoutdate
FROM users
LEFT JOIN messages
ON users.name = messages.name
WHERE users.name = $name
ORDER by ASC datesent";

 

The issue still remains that when I use separate SELECT statements as in my original post it returns both results...but the JOIN still does not.

Edited October 12, 2012 by stevew

[Jessica]

*headdesk*.

 

I did tell you why. Your query was never going to work. What is different about yours and mine? Did you read the link in my signature I directed you to?

[kicken]

I plan to use upon login:

if ($datesent>$logoutdate){
echo "you have new messages";
}

 

There is no need to select a list of all the dates, you can do that comparison in SQL when you join the table, and as a result only select the rows of new messages (or a count if you dont need the data).

SELECT
   the,
   columns,
   you,
   need
FROM users
INNER JOIN messages ON users.name = messages.name AND messages.datesent >= users.logoutdate
WHERE 
   users.name = $name
ORDER 
   by ASC datesent

 

 

1. I use name as the unique id because to pair the messages to "joe smith" in messages.name to user 'joe smith' in users.name. but I supposeI could INSERT user_id of the sender instead of the name, and then just use where messages.user_id = users.id.....

 

What happens when you have two different 'Joe Smith's sign up for your site? Just going to let them view each others messages? Names are not unique, even a first+last combination. Never use it as a key for linking records.

[stevew]

There is no need to select a list of all the dates, you can do that comparison in SQL when you join the table, and as a result only select the rows of new messages (or a count if you dont need the data).

SELECT
the,
columns,
you,
need
FROM users
INNER JOIN messages ON users.name = messages.name AND messages.datesent >= users.logoutdate
WHERE
users.name = $name
ORDER
by ASC datesent

What happens when you have two different 'Joe Smith's sign up for your site? Just going to let them view each others messages? Names are not unique, even a first+last combination. Never use it as a key for linking records.

 

We are using usernames not names so no duplicates...although I may switch over to id anyway for messages.

 

WHERE name LIKE '$name' && datesent>logoutdate ORDER by ASC datesent";

This is in the messages SELECT statement but I omitted the comparison from the JOIN statement initially while tesing. The message notification is working as intended as two separate SELECT statements...so I will leave it as is until I can figure this particular JOIN out.

 

Thanks.

Edited October 12, 2012 by stevew