[Resolved] query only returning one record

jonnyblaze_t14

I'm sure there is a really obvious answer to this question, but I'm stumped...

This query:

select messages.*, _users.name from messages,_users where messages.user_id=2 and _users.user_id=messages.from order by sent

is only returning one row

what it's trying to do is grab every message for user #2, take the sender's user id, compare it to the users table, and return the sender's user name too. but like i said, it's only returning the first record for me

what am i doing wrong?

pete_bisby

You're asking where messages.user_id=2 .... shouldn't that be _users.user_id=2?

Also, try and user INNER JOIN in your query - it will run quicker, especailly when you start to get many records in your tables.

Another thing, which I'm sure you've probably already done, make sure you have indexes on your from and to fields within your messages table.
It will improve the speed of searches, seen as these are the fields that base your relationship with the user table.

[SQL]
SELECT messages.*, users.name
FROM messages INNER JOIN users ON users.user_id=messages.from
WHERE users.user_id=2;
[/SQL]

jonnyblaze_t14

thanks for getting back to me so quickly pete

that still only returned the first record

maybe the structure would help:

messages :
  message_id bigint(20)
  user_id int(15)
  from int(15)
  sent bigint(14)
  viewed int(1)
  title varchar(50)
  message text

and the only thing that matters in _users is:
  user_id bigint(20)
  name varchar(15)

This is an internal messaging service, so
user_id would be the user_id of the TO: field
from would be the user_id of the FROM: field

so what i'm trying to have it do is also return _users.name that matches up with the from field, where the TO: is the current user (in this exaple, user #2)

jonnyblaze_t14

i hate to bump, but i'm still stuck. anbody know what i'm doing wrong?

thanks

pete_bisby

Sorry about not replying sooner - I had to do some urgent work!

You're main problem is your field names - you can't have "from" as a field name, because it is an SQL reserved word.

If you think of a query:

SELECT message_id, user_id
FROM messages
WHERE message_id=1;

You already use the word FROM within the query to begin describing which tables to use. If you try and include a fieldname called "from" into the query, it will no longer make any sense. For example:

SELECT message_id, from, user_id
FROM messages
WHERE message_id=1;

Prefix your fieldnames with a letter (e.g. "m" for the messages table, and "u" for the users table), to avoid any confusion. This will probably be enough for your queries to run correctly.

jonnyblaze_t14

thanks for all your help pete!

grrr, two stupid mistakes in a row.... my test data was faulty, the to field contained user id's that weren't in the users table

mysql did allow a field titled "from", probaby only because i was refering to it as a part of messages (messages.from) and not just as "from"... but you make an excellent point, i'm not sure how that slipped by me (probably because i designed that table at 3 am, lol)

two noobish mistakes, i should have known better

thanks again pete