MySQL: multiple coloumn names after JOIN's

Hello,

I have a problem when using JOIN's. I have a situation like this:
Table Users
--ID (int, Primary Key, ...)
--name
--email
--...

Table Messages
--FromUser_ID
--ToUser_ID
--Title
--Content
--...

Now I want to display certain messages, but I also need the users data. So I used JOIN's (SELECT * FROM Messages m LEFT JOIN Users u1 ON u1.ID=m.FromUser_ID ...). This works but since I join both users (FromUser_ID and ToUser_ID), I can't use mysql_fetch_assoc() any more, because all field-names of the Users-table occure twice. I could do everything with mysql_fetch_row() using the field indices, but I don't like this way because the coloumn count of the tables may vary and I don't want to recalculate the indices all the time.
So there was only the possibility of using aliases. Because I need all fields of the two users (this table has around 100 coloumns), it would be very nasty to create an alias for each coloumn.

My question: is there any decent way to solve my problem? I basically search something like "SELECT u1.* AS FirstUser_*, u2.* AS SecondUser_* ..."; adding some text at the beginning of all the field names. But I tried all variations and this didn't work (always syntax errors occured).

Thank you in advance :-)

bye Erik

Hmm, interesting.

It's hard to see what you are trying to do, but I think you will need to select the individual table fields by name as part of your statement.

Don't use wildcards for SELECT * or SELECT tablename.*

Surely you're not going to display a form or page with all 100 values for both users. If so, you should consider going back to the drawing board from a user interface perspective. Why not have your initial join select only the name, ID, and one or two other key pieces of data for each user and display the title and to/from values with links to a detail page for each user, which would key off the user ID and require a much easier query without the need for aliasing?

Hello :-)

thank you for your tips. In fact, I found a way to do this without having need to display everything at once. And of course I could use multiple queries. I just wanted to know whether there is any possibility to do this with one query.

Thank you

bye, Erik

Can we know how you did it? Is it still one query? What if you used mysql_fetch_row() instead of mysql_fetch_assoc() ? Would that work, since the fetch_row array is keyed numerically, to avoid duplicate keys?

I know, I know, you ask one question and you get four in return... But I, for one, am curious!

Hello

as I said in my first post, mysql_fetch_row would work perfectly. But my problem is that the number of rows of the users table varies. So if I used mysql_fetch_row, I would have to get all coloumns first to calculate the indices of them.
In fact, I didn't solve the problem but just selected two fields via aliases and displayed all the other things on a special page per user.

But I had an idea know. Look at this query:
SELECT us1.*, us2.*, m.* FROM (messages m LEFT JOIN users us1 ON us1.ID=m.FromUser_ID) LEFT JOIN users us2 ON us2.ID=m.ToUser_ID ORDER BY m.Creation_Date DESC

This created my problem, you know. But I think it is possible to break it up into a maximum of three queries:
SELECT m.* FROM messages m ORDER BY m.Creation_Date DESC

SELECT us.* FROM messages m LEFT JOIN users us ON us.ID=m.FromUser_ID ORDER BY m.Creation_Date DESC

SELECT us.* FROM messages m LEFT JOIN users us ON us.ID=m.ToUser_ID ORDER BY m.Creation_Date DESC

So this is quite easy. In my opinion this is a decent way because the data is nicely seperated and using mysql_fetch_assoc for all three queries is no problem. And 3 queries are not too many. I was just too stupid to find this way. In my first solution, I looped through all the messages and started an extra query to get each specific user. Of course that is not good when having a few hundred messages. But using those 3 queries it will still be fast und nicely accessible I think

Greets, Erik