Combining and printing 2 MySQL SELECT queries?

[ianh]

Hi,

 

In my code below I have 2 SELECT queries that are returning 2 different records and it works fine. However I feel my code is somewhat over repeated and probably using up unneccessary resource on the database. Is there a better way I can combine and print the 2 queries with less code? Perhaps somehow returning both records in the same array?

 

 

<?php

require_once (dirname(__FILE__). '/../conf.php');


$connection = mysql_pconnect(DB_HOST,DB_USER,DB_PASSWORD);
  mysql_select_db(DB_DATABASE,$connection);


$result = mysql_query("SELECT title, abstract, body FROM Articles WHERE category_id = ".$str_categoryid." AND id = ".$str_articleid, $connection);


        $fielddata = @ mysql_fetch_array($result);

        echo $fielddata['title']."\n\n";
        echo "<p><strong>".$fielddata['abstract']."</strong></p> \n\n";
        echo $fielddata['body'];


$result2 = mysql_query("SELECT title, abstract, body FROM Articles WHERE category_id = ".$str_categoryid." AND id > ".$str_articleid." ORDER BY order_num ASC LIMIT 1", $connection);


        $fielddata = @ mysql_fetch_array($result2);

        echo $fielddata['title']."\n\n";
        echo "<p><strong>".$fielddata['abstract']."</strong></p> \n\n";
        echo $fielddata['body'];

      ?>

[wildteen88]

Could you explain what you're trying to do with two queries?

[ianh]

Could you explain what you're trying to do with two queries?

 

Bascially I want the queries to display the results in the same recordset.

The results are in effect 2 articles which I want to display side by side.

[ianh]

I think I solved it by having 1 UNION query, so I therefore now have 1 query instead of 2, eliminating the need for processing 2 recordsets.

 

Code changed to:

 

<?php

require_once (dirname(__FILE__). '/../conf.php');


$connection = mysql_pconnect(DB_HOST,DB_USER,DB_PASSWORD);
  mysql_select_db(DB_DATABASE,$connection);


//#### UNION query - get first article and the next highest article with lower order_num ####
$result = mysql_query("(SELECT title, abstract, body FROM Articles WHERE category_id = ".$str_categoryid." AND id = ".$str_articleid.") UNION (SELECT title, abstract, body FROM Articles WHERE category_id = ".$str_categoryid." AND id > ".$str_articleid." ORDER BY order_num DESC LIMIT 1)", $connection);


while ($fielddata = mysql_fetch_array($result)) {
         echo $fielddata['title']."\n\n";
         echo "<p><strong>".$fielddata['abstract']."</strong></p> \n\n";
         echo $fielddata['body'];
}

?>

 

This displays the 2 records in the same record set, and uses far less code.

 

Hope this helps someone.