Page 1 of 2

Selecting from multiple tables at once?

Posted: Mon Feb 12, 2007 11:31 pm
by psychotomus
how can I select data from multiple tables at once?

Posted: Tue Feb 13, 2007 8:11 am
by feyd
Joins and unions.

Posted: Thu Feb 15, 2007 1:27 am
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.

Posted: Thu Feb 15, 2007 1:36 am
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.

Posted: Thu Feb 15, 2007 1:37 am
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)

Posted: Thu Feb 15, 2007 1:41 am
by psychotomus
that code doesnt help much. i need the last 10 items inserted into the tables. not last 10 from each table.

Posted: Thu Feb 15, 2007 1:49 am
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:

Posted: Thu Feb 15, 2007 2:00 am
by psychotomus
you have it retreiving 20 records, not 10. thats why im' saying its not helpfull.

Posted: Thu Feb 15, 2007 2:05 am
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.

Posted: Thu Feb 15, 2007 2:12 am
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?

Posted: Thu Feb 15, 2007 7:22 am
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.

Posted: Thu Feb 15, 2007 10:04 am
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

Posted: Thu Feb 15, 2007 6:02 pm
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]

Posted: Thu Feb 15, 2007 6:55 pm
by feyd
Even a UNION is stretching it here. Query separately, mix and process in PHP.

Posted: Thu Feb 15, 2007 7:06 pm
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.