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

nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

Am I missing something? What's wrong about this:

Code: Select all

SELECT 
  `m_type`,`m_title`,`m_url`,`m_description`
FROM
  `t_media`

Code: Select all

SELECT 
  `title`,`url`,`content`
FROM
  `t_pages`
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

I don't know how to sort them to make show the last 10 items inserted. not from each table, but last 10 items inserted into both combined.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

ok heres what i Got

Code: Select all

$rssquery="SELECT m_type,m_title,m_url,m_description, m_date FROM t_media WHERE m_submapproved='Y' ORDER BY m_id DESC LIMIT 10";
	//echo $query;
	$rssqresult = mysql_query($rssquery);
	$array = array();
	while ($rssline = mysql_fetch_object($rssqresult))	
	{
		$array[] = $rssline;
	}
	
	$rssquery="SELECT title,url,content,date FROM t_pages ORDER BY id DESC LIMIT 10";
	//echo $query;
	$rssqresult = mysql_query($rssquery);
	
	while ($rssline = mysql_fetch_object($rssqresult))	
	{
		$array[] = $rssline;
	}
how do I go about sorting them?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How in the world do you propose to merge two completely unrelated datasets, then sort() them?
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

SOMETHING LIKE THIS =) all it does is print <BR> no dates. whats wrong?

Code: Select all

$rssquery="SELECT m_type,m_title,m_url,m_description, m_date FROM t_media WHERE m_submapproved='Y' ORDER BY m_id DESC LIMIT 10";
	//echo $query;
	$rssqresult = mysql_query($rssquery);
	$array1 = array();
	$array2 = array();
	$newarray = array();
	
	while ($rssline = mysql_fetch_object($rssqresult))	
	{
		$array1[] = $rssline;
	}
	
	$rssquery="SELECT title,url,content,date FROM t_pages ORDER BY id DESC LIMIT 10";
	//echo $query;
	$rssqresult = mysql_query($rssquery);
	
	while ($rssline = mysql_fetch_object($rssqresult))	
	{
		$array2[] = $rssline;
	}
	
	for($x =0; $x < 10; $x++)
	{
		if ($array1[$x]->m_date < $array2[$x]->date1)
			for($i = 10; $i > $x; $i--)
			{
				$array1[$i+1] = $array1[$i];
			}
			$array1[$x] = $array2[$x];
	}
	
	for($x =0; $x < 10; $x++)
	{
		if ($array1[$x]->m_date <> "")
			echo $array1[$x]->m_date ."<BR>";
		else
			echo $array1[$x]->date1 . "<BR>";
	}
Post Reply