Selecting from multiple tables at once?
Moderator: General Moderators
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Selecting from multiple tables at once?
how can I select data from multiple tables at once?
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
You could do it as 2 connections to the databases
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.
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
well unless I am reading your question incorrectly, you say the records are added with a date of NOW(). Therefore the last 10 items inserted into your database will have the most recent dates.
So ordering in descending order will bring back all your records with the most recent entered at the top, you then limit this to 10. Giving you the 10 last inserted records in your tables.
The last 10 inserted and the last 10 records in your table order by date are exactly the same thing.
But hey if the code doesnt help you I'll remove it. A little courtesy goes a long way.
So ordering in descending order will bring back all your records with the most recent entered at the top, you then limit this to 10. Giving you the 10 last inserted records in your tables.
The last 10 inserted and the last 10 records in your table order by date are exactly the same thing.
But hey if the code doesnt help you I'll remove it. A little courtesy goes a long way.
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
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.
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).
That will give you the latest 10 items whether the last 10 were in the first database, the second database or a combination of both.
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
feyd | Please use
feyd | Please use[/syntax]
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 ;feyd | Please use[/syntax]
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]-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
I only need to get
from the first table
and
from the second table.
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 '',from the first table
and
Code: Select all
`title` varchar(100) NOT NULL DEFAULT '',
`url` varchar(100) NOT NULL DEFAULT '',
`content` blob NOT NULL,
`from the second table.