Page 1 of 1
[SOLVED]MySQL Query with Left Join - selecting multiple rows
Posted: Fri Aug 05, 2005 2:23 pm
by The Monkey
SELECT chat.message, users.username FROM `chat` LEFT JOIN `users` ON chat.authorID = users.userID WHERE chat.time > ':1'
With :1 being the last time a message list refresh was requested.
However, this query only returns one row per query, no matter how many messages have been inserted into the database since the last refresh call. How should I change my query to retrieve all of the messages which have a time field greater than the variable?
Posted: Fri Aug 05, 2005 2:28 pm
by feyd
your chat.time field seems odd, why store it in that fashion?
Posted: Fri Aug 05, 2005 2:34 pm
by The Monkey
`time` decimal(20,8) unsigned NOT NULL default '0.00000000'
It really should be something like post_time... but it basically stores the time (including the microseconds, as just seconds isn't accurate enough when refreshing every 800 milliseconds) of when the message was inserted into the database.
Posted: Fri Aug 05, 2005 2:44 pm
by feyd
then are you sure you didn't mean "WHERE chat.time > '.1'" instead of :1 ?
if you unquote the value, and place a zero before the .1, I think it should be fine...
Posted: Fri Aug 05, 2005 2:51 pm
by The Monkey
feyd wrote:then are you sure you didn't mean "WHERE chat.time > '.1'" instead of :1 ?
if you unquote the value, and place a zero before the .1, I think it should be fine...
Ah! I am sorry. The :1 is replaced with the last time the message list was refreshed (a unix timestamp to the 8th decimal place).
I should have stated as such.
mysql_query('SELECT chat.message, users.username FROM `chat` LEFT JOIN `users` ON chat.authorID = users.userID WHERE chat.time > 1123262485.45690000');
However, when I remove
users.username AND
LEFT JOIN `users` ON chat.authorID = users.userID from the query, it works as I expected, selecting all of the messages where chat.time > last_time_refreshed. The LEFT JOIN is somehow adding a "limit 1" or some odd thing...
Posted: Fri Aug 05, 2005 3:12 pm
by feyd
have you tried INNER JOIN?
Posted: Fri Aug 05, 2005 4:12 pm
by The Monkey
INNER JOIN isn't working.
It still only selects one message from each author.
Posted: Fri Aug 05, 2005 4:14 pm
by feyd
post an export of the table structures for both tables and some corresponding data bits for each, and what you're getting returned for that dataset.
Posted: Fri Aug 05, 2005 4:34 pm
by The Monkey
Here you are. I'm not being as helpful as I could be, I'm sorry.
--
-- Table structure for table `chat`
--
CREATE TABLE `chat` (
`messageID` bigint(20) NOT NULL auto_increment,
`message` mediumtext NOT NULL,
`authorID` tinyint(3) NOT NULL default '0',
`time` decimal(20,8) unsigned NOT NULL default '0.00000000',
PRIMARY KEY (`messageID`)
) TYPE=MyISAM AUTO_INCREMENT=154 ;
--
-- example data (large timestamps so that they are always queried)
--
INSERT INTO `chat` VALUES (148, 'Message # 1', 2, 2123263785.17569995);
INSERT INTO `chat` VALUES (149, 'Message # 2', 3, 2123263785.17569995);
INSERT INTO `chat` VALUES (150, ''Message # 3'', 1, 2123263785.17569995);
INSERT INTO `chat` VALUES (151, 'Message # 4', 2, 2123263785.17569995);
INSERT INTO `chat` VALUES (152, 'Message # 5', 1, 2123263785.17569995);
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`userID` tinyint(3) NOT NULL auto_increment,
`username` varchar(32) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
PRIMARY KEY (`username`),
KEY `user_id` (`userID`),
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=4 ;
--
-- Example data for users
--
INSERT INTO `users` VALUES (1, 'Username of userID # 1', 'MD5_PASSWORD_THING');
INSERT INTO `users` VALUES (2, 'Username of userID # 2', 'MD5_PASSWORD_THING');
INSERT INTO `users` VALUES (3, 'Username of userID # 3', 'MD5_PASSWORD_THING');
--
-- Query
--
SELECT chat.message, users.username FROM `chat` LEFT JOIN `users` ON chat.authorID = users.userID WHERE chat.time > 1123262485.45690000
--
-- Resulting data
--
username: "Username of userID 2"; message: "Message # 4"
username: "Username of userID 3"; message: "Message # 2"
username: "Username of userID 1"; message: "Message # 5"
--
While there are two more messages in the database with the authorID of 2 and 1, they aren't returned because a message has already been queried which has that authorID.
I hope I explained everything clearly, thank you for your help so far.
- Nathaniel
Posted: Fri Aug 05, 2005 4:47 pm
by feyd
your results don't really make sense... if you were selecting against the user table first (in the FROM clause) or used an ORDER BY clause it'd make sense...
side note: you can only have a maximum of 127 users in your user table with that size user ID...
Posted: Fri Aug 05, 2005 6:48 pm
by The Monkey
Oh, good grief Nathaniel. You should know better than to use a field which will be the same as an array KEY!
My query queried everything just fine, but I was using the authorID field as the messages array key (don't ask me why... I don't know). So, it never was my query.
Thanks for all your help. No, I didn't realize that tinyint(3) would only give me 127 users. I assumed 999 (3 numerals long). I'll change that, thank you.
