Trying to doing it all in SQL
Hi, I am trying to do the following with one SQL query instead of 3 could you please help me
CREATE TABLE `Chat` (
`mesg_num` bigint(20) NOT NULL auto_increment,
`message` mediumtext NOT NULL,
`date` datetime default NULL,
`from_userid` int(11) NOT NULL default '0',
`to_userid` int(11) NOT NULL default '0',
`public` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`mesg_num`),
KEY `from_userid` (`from_userid`),
KEY `to_userid` (`to_userid`)
)
CREATE TABLE `user_bio` (
`id` int(11) NOT NULL default '0',
`user_Fname` varchar(20) NOT NULL default '',
`user_Lname` varchar(20) NOT NULL default '',
`birthday` date NOT NULL default '0000-00-00',
`town` varchar(20) NOT NULL default '',
`zipcode` varchar(5) NOT NULL default '0',
`yim` varchar(30) NOT NULL default '',
`aim` varchar(30) NOT NULL default '',
`hobbies` mediumtext NOT NULL,
`description` longtext NOT NULL
)
right now I issue the following queries to build a page
$message = select * from Chat where from_userid="$uid" or to_userid="$userid"
(NOTE $uid and $userid are values to identify people.)
Now what I currently do is translate the from_userid and to_userid in to human readable format
$from_info = select user_Lname, user_Fname from user_bio where id=from_userid
$to_info = select user_Lname, user_Fname from user_bio where id=to_userid
Then I display this all in HTML
echo $from_info['user_Lname']." ".$to_info['user_Rname']." ".$message['message'];
I have figured out how to eliminate one query with a join but then I still have to do a lookup for the other users information.
I am using MYSQL 4.1