[SOLVED] Obtain field data from 3 tables even if one has no data

[Chappers]

Hi,

 

I'm struggling to understand about using JOIN commands with MySQL, which my searches have indicated I need in order to achieve what I'm trying. Scoured the net but can't find anything that suits my particular intentions.

 

I have three tables and each one has a field which contains a person's username, so for anyone who registers to my site, there will be this, with username james as an example:

TABLE  -    FIELD      -    ENTRY

MEMBERS -> USERNAME -> james

MESSAGES -> TOUSER -> james

PROFILES -> USERNAME -> james

And a variable when user is logged in which is $user and contains their username (in this case james).

 

I tried:

SELECT members.username, messages.touser, profiles.username FROM members, messages, profiles WHERE members.username = '$user', messages.touser = '$user', profiles.username = '$user'

but it doesn't work and anyway I don't know how to then extract the different fields from the result to echo on the page. I've also since found that if one of those fields is empty for any reason, the statement returns nothing even though the other two may contain the username data. This is a problem because until a user is sent a message from another user, the touser field is empty.

 

One forum entry I found said to look up OUTER LEFT JOIN, which I did and my eyes crossed. After getting the three instances of the same username, I'd like to be able to echo each individually. Would someone please be kind enough to help me with this. I really would appreciate any kind of assistance.

 

Many thanks,

James

[Barand]

With a normal join like yours you only get a row where there is a match.

 

With A LEFT JOIN B you get all data from A and any matching data from B. If there is no matching record in B then NULL values are return in those columns selected from table B

[Chappers]

The way I was doing it wouldn't work anyway, though. I've now got it to actually return results by removing commas at the end and putting AND instead.

SELECT members.username, messages.touser, profiles.username
FROM members, messages, profiles
WHERE members.username = '$user' AND messages.touser = '$user' AND profiles.username = '$user'

But, I couldn't output the array properly because

$person = $username['members.username'];
echo $person;

outputs nothing, because it seems the [members.username] isn't liked. If I just put [username], then it works but it's not differentiating between the username obtained from the members table or the one from the profiles table as they both have the row name username. Doing a print_r on the array shows that there are only two fields in the array, presumably because there are two called username in two different tables. I thought it'd be recorded which table the data came from so I could then distinguish by using $username['members.username'] and $username['profiles.username'], but no such info in array and just doesn't work when tried.

 

With the A LEFT JOIN B example you gave me, that deals only with two tables so I don't know how to apply to three. Also, by matching data, does that mean the row names must be the same, so that the fact the messages table row is called touser, and the members one is called username, means that won't work?

 

Basically, I want the data from a field regardless of whether the field is empty or not, I don't want it to output nothing solely because one of the tables returns no match. Also, I must be able to distinguish between the 3 returned results and use them separately. I'm confused by it all, and now I've read about UNION and am wondering if that's what I should be using.

[Barand]

I don't see any sense in your query. As you are matching on username then the values are all going to be the same on each row. ???

 

But

 

SELECT members.username as membername, messages.touser, profiles.username as profilename

FROM members

INNER JOIN profiles ON members.username = profiles.username

LEFT JOIN messages ON members.username = messages.touser

WHERE members.username = '$user'

[Chappers]

Thanks for the reply. I don't see where the words membername and profilename in members.username as membername and profiles.username as profilename comes into things, because they aren't used afterwards?

 

I'll try and explain what I'm trying to do. I have an administration page on the site, just for me, where I can delete members from my site if necessary. When someone joins the site, their chosen username is inputted into two tables immediately: members and profiles. If they are ever sent a message by another member, their username is then inputted into a table called messages inside a field named touser. That way, I'm using the chosen username of the member as the common thread in each table, so by entering their username, I can bring up all the information stored for that member in all three tables.

 

If I want to delete them, I want to remove all the data in the tables associated with their username, including any messages sent to them and stored in the messages table, hence why I'm using the touser field for their username, because any messages they get will be sent to their username.

 

Having used three separate mysql queries (I don't know how to use one statement to delete the information from all three tables), I then use three separate queries to try and obtain their username from the three tables. Having done that, I then use if statements to determine if any username was found in the tables that matches their chosen username, and if there was, then it displays "$user not deleted".

 

This is just in case anything goes wrong, because if, say, the username I've chosen to delete doesn't properly match the username in any of the tables because of a misspell or capitalised character in the wrong place, the query returns no error, and so I need to do the above kind of check. But, this means having three instances of the same username returned, so I can print out a message for each, such as "$user not deleted from members table" or "$user not deleted from profiles table". Again, this is in case of some unforeseen error which somehow causes the user to be successfully deleted from, say, two tables, but not the third.

 

If I could start using only one query to get the data from all three tables, then I can minimise code and just make it easier to work on. Although, if the one query gets data from all three tables, I don't know how to use PHP to take the data from the returned array. With what I was using before, it wasn't recognising $anyvariablename = $myresults['members.username']; because the array seemed to contain no information stating that the username field had come from the members table. In other words, after fetching the data in an array with $myresults = mysql_fetch_array($result); I am stuck and don't know what to put to get three individual variables containing the individual usernames obtained from the three tables.

[Barand]

but it's not differentiating between the username obtained from the members table or the one from the profiles table as they both have the row name username.

 

solution is column aliases

I don't see where the words membername and profilename in members.username as membername and profiles.username as profilename comes into things, ebcause they aren't used afterwards?

 

Single query delete

DELETE members.*, messages.*, profiles.*
FROM members
INNER JOIN profiles ON members.username = profiles.username
LEFT JOIN messages ON members.username = messages.touser
WHERE members.username = '$user'

[Chappers]

Thanks so much for your help, it's working perfectly now. It took me a moment to understand how to use the aliases when taking the data from the array, but then I realised it's $myvariable = $myresults['membername']; etc.

 

Incidentally, will what you've given me work even if one table doesn't have any data in the field? I ask because the touser field may be empty for some members, but I still need it to grab whatever is in the other two tables and make it available for me to use.

[Barand]

if there are no messages then the the touser column for that user should contain NULL