Getting data from 3 tables

[cerberus47813]

I'm trying to get data from 3 tables that doesn't have anything in common except for the active column.

What is suppose to happen is that the code needs to goes through the 3 tables and picks out the latest 5 titles from them.

 

Here is my code

$str = "";

$query_text = "SELECT * FROM news N, blog B, comments C WHERE N.active='1' AND B.active='1' AND C.active='1'";

$query = mysql_query($query_text);

while( $result = mysql_fetch_object($query)){
    $news = new News($result->id);
    $news->setFromDatabase();
    $date = new DateTime(''.$news->publish.'');
    $str.='	<div><a href="'.getSEOLink(31).'?article='.$news->id.'">';
    $str.=' <span>'.$date->format('F dS, Y').'</span>';
    $str.=' '.shortText($news->content, 80).' <a class="news-readmore" href="#"></a>';			
    $str.='</a></div>';


}

return $str;

If there is anything that doesn't make sense please let me know

[cyberRobot]

Have you looked into using UNION?

http://dev.mysql.com/doc/refman/5.0/en/union.html

[cerberus47813]

I have but then I got an error that said

 

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/xxxxxx.com/httpdocs/classes/Page.php on line 2353

 

and on line 2353 is

while( $result = mysql_fetch_object($query)){

This was the union statement

$query_text = "SELECT * FROM news  WHERE active='1'  
                        UNION ALL
                        (SELECT * FROM blog  WHERE active='1' ) 
                        UNION ALL
                        (SELECT * FROM comments  WHERE active='1' )";

[cyberRobot]

Have you tried using mysql_error() to see if MySQL is throwing errors?

http://www.php.net/manual/en/function.mysql-error.php

 

It's difficult to tell since you're using * to select all columns, but do each of the tables contain the same number of columns? Note that you'll be better off to list out the columns. That way you can make sure the result set contains what you expect. Keep in mind the following quote from the manual:

 

 

 

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)