How would I effectively rewite this query?

[Dustin013]

I am playing with some forum code and I was trying to display newest post from each category by simply including a query on the page. However, after I queried more than 4 categories for the newest post I noticed the page was loading very slowly. I decided I would make a master query that fires off once every 15min and updates an XML file. Instead of the index.php making all the queries I though it would be best if the index.php simply read the contents of the XML file. My problem is I can't seem to figure out how to get the below code to effectively make all the queries and append the information to the XML file. With the code below I could simple write out each query and tell it to save each query as a separate XML file. However, I am guessing there is a better way. If someone could offer me some assistance or reading material that would be greatly appreciated. I would also like to find out how to append the XML file instead of rewriting it every time it gets accessed.

 

<?php
include 'include/config.php';
include 'include/connect.php';
mysql_select_db($dbname, $conn) or die ($dbname . " Database not found.");


$query = "SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost1." LIMIT ".$newcount."";
$query2 = "SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost2." LIMIT ".$newcount."";
$query3 = "SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost3." LIMIT ".$newcount."";
$query4 = "SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost4." LIMIT ".$newcount."";
$query5 = "SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost5." LIMIT ".$newcount."";

//This is only executing $query obviously. How do I include all the other queries?
$result = mysql_query($query) or die("Error!");
$num = mysql_num_rows($result);

if ($num != 0) {

$file= fopen("results.xml", "w");

$_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";

$_xml .="<results>\r\n";

while ($row = mysql_fetch_array($result)) {

if ($row["_POST_TITLE"]) {
$_sml .-"\t\t<content>\r\n";
$_xml .="\t\t<title>" . $row["_POST_TITLE"] . "</title>\r\n";
$_xml .="\t\t<id>" . $row["ID"] . "</id>\r\n";
$_xml .="\t\t<category>" . $row["META_DPAGE_NME"] . "</id>\r\n";
$_xml .="\t</content>\r\n";
} else {

    $_xml .="\t<page title=\"Nothing Returned\">\r\n";
    $_xml .="\t\t<file>none</file>\r\n";

$_xml .="\t</page>\r\n";
} }

$_xml .="</results>";

fwrite($file, $_xml);

fclose($file);

echo "XML has been written.  <a href=\"results.xml\">View the XML.</a>";

} else {

echo "No Records found";

} 
?>

[Dustin013]

Ok updated code below... I used the UNION statement to join the queries together. However, I want to limit each query to 10 or $newcount in this case... I tried added LIMIT ".$newcount." to each SELECT statement before the UNION statement. That only returned 10 results from the first query... the same happens when I add it to the last statement. If I remove the LIMIT statement I get a massive XML file because it literally adds to the XML file every post in the database. I did figure out using  $file= fopen("results.xml", "a"); would append a XML file... duh!

 

<?php
include 'include/config.php';
include 'include/connect.php';
mysql_select_db($dbname, $conn) or die ($dbname . " Database not found.");


$query = 
"SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost1." UNION 
SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost2." UNION
SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost3." UNION
SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost4." UNION
SELECT ".$id.",".$postcategory.",".$posttitle." FROM ".$dbpost5." [b]LIMIT ".$newcount."[/b]";

echo $query;
$result = mysql_query($query) or die("Error!");
$num = mysql_num_rows($result);

if ($num != 0) {

$file= fopen("results.xml", "a");

$_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";

$_xml .="<results>\r\n";

while ($row = mysql_fetch_array($result)) {

if ($row["_POST_TITLE"]) {
$_sml .-"\t\t<content>\r\n";
$_xml .="\t\t<title>" . $row["_POST_TITLE"] . "</title>\r\n";
$_xml .="\t\t<id>" . $row["ID"] . "</id>\r\n";
$_xml .="\t\t<category>" . $row["META_DPAGE_NME"] . "</id>\r\n";
$_xml .="\t</content>\r\n";
} else {

    $_xml .="\t<page title=\"Nothing Returned\">\r\n";
    $_xml .="\t\t<file>none</file>\r\n";

$_xml .="\t</page>\r\n";
} }

$_xml .="</results>";

fwrite($file, $_xml);

fclose($file);

echo "XML has been written.  <a href=\"results.xml\">View the XML.</a>";

} else {

echo "No Records found";

} 
?>