Selecting from multiple tables at once?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Selecting from multiple tables at once?

Post by psychotomus »

how can I select data from multiple tables at once?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Joins and unions.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

so how do I select latest 10 items from 2 different databases.

1st table is called t_media with field m_submdate
2nd table is called t_pages with field date

they both use sql date and is insert with date NOW() when added to database.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Oh and using date as a field name is a really bad idea, it is a reserved word in mysql (and most other databases)
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

that code doesnt help much. i need the last 10 items inserted into the tables. not last 10 from each table.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

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. :evil:
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

you have it retreiving 20 records, not 10. thats why im' saying its not helpfull.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

psychotomus wrote:so how do I select latest 10 items from 2 different databases.
Well you say that. So forgive me for thinking you wanted the 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

Post by psychotomus »

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).
how you do that?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Do both databases use the same credentials to log in? If so, post the SHOW CREATE TABLE for each and tell us how they interrelate please.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

I think he'd be looking to do a UNION query, because the tables aren't really related.

But I dont think you can query both databases with the one connection in PHP/MySQL
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

feyd | Please use

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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Even a UNION is stretching it here. Query separately, mix and process in PHP.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

I only need to get

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.
Post Reply