Selecting from multiple tables at once?
Posted: Mon Feb 12, 2007 11:31 pm
how can I select data from multiple tables at once?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
$Connection1 = mysql_connect($Host,$User,$Pass);
mysql_select_db($Database1);
$Connection2 = mysql_connect($Host,$User,$Pass);
mysql_select_db($Database2);
//first query to database 1
$FirstQuery = "SELECT * FROM t_media ORDER BY m_submdate DESC LIMIT 10";
$FirstResult = mysql_query($FirstQuery,$Connection1);
//then query to database 2
$SecondQuery = "SELECT * FROM t_pages ORDER BY `date` DESC LIMIT 10";
$SecondResult = mysql_query($SecondQuery,$Connection2);
//then do whatever you need with mysql_fetch_array() passing the relevant
//result.
Well you say that. So forgive me for thinking you wanted the latest 10 items from 2 different databases.psychotomus wrote:so how do I select latest 10 items from 2 different databases.
how you do that?mikeq wrote:
The code will still help. With the result sets put them into 1 array and then sort the array using the dates and pick off the first 10 from that array (ordered by date descending).
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
the tables are not related at all. heres dump's of the 2 tables schemes
[syntax="sql"]CREATE TABLE `t_media` (
`m_id` int(11) NOT NULL auto_increment,
`m_type` varchar(7) NOT NULL default '',
`m_title` varchar(128) NOT NULL default '',
`m_url` varchar(128) NOT NULL default '',
`m_description` varchar(255) NOT NULL default '',
`m_author` text NOT NULL,
`m_authorurl` text NOT NULL,
`m_file` varchar(128) NOT NULL default '',
`m_embedcode` text NOT NULL,
`m_thumbnail` varchar(128) NOT NULL default '',
`m_userid` mediumint(8) unsigned NOT NULL default '0',
`m_submdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`m_rating` float unsigned NOT NULL default '0',
`m_nrratings` mediumint(8) unsigned NOT NULL default '0',
`m_nrcomments` smallint(5) unsigned NOT NULL default '0',
`m_nrviews` mediumint(8) unsigned NOT NULL default '0',
`m_tags` varchar(255) NOT NULL default '',
`m_sponsor_url` varchar(128) NOT NULL default '',
`m_sponsor_text` varchar(128) NOT NULL default '',
`m_submapproved` char(1) NOT NULL default '',
`extra2` char(1) NOT NULL default '',
`extra3` char(1) NOT NULL default '',
PRIMARY KEY (`m_id`),
KEY `m_type` (`m_type`),
KEY `m_userid` (`m_userid`),
FULLTEXT KEY `search` (`m_title`,`m_description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2982 ;
CREATE TABLE `t_pages` (
`id` int(6) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`content` blob NOT NULL,
`tags` varchar(150) NOT NULL default '',
`author` varchar(100) NOT NULL default '',
`views` int(6) NOT NULL default '0',
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]Code: Select all
`m_type` varchar(7) NOT NULL DEFAULT '',
`m_title` varchar(128) NOT NULL DEFAULT '',
`m_url` varchar(128) NOT NULL DEFAULT '',
`m_description` varchar(255) NOT NULL DEFAULT '',Code: Select all
`title` varchar(100) NOT NULL DEFAULT '',
`url` varchar(100) NOT NULL DEFAULT '',
`content` blob NOT NULL,
`