[SOLVED]MySQL Query with Left Join - selecting multiple rows
Moderator: General Moderators
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
[SOLVED]MySQL Query with Left Join - selecting multiple rows
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?
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?
Last edited by The Monkey on Fri Aug 05, 2005 6:46 pm, edited 1 time in total.
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
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).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...
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...
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
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
--
-- 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
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
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.
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.