Elmer Fudd

Figured it out: <?php $query = "SELECT * FROM categories"; $result = mysql_query($query) or die ("Couldn't execute query."); $counter=1; while ($row = mysql_fetch_assoc($result)) { $cat=$row['categoryabbrev']; $querynested = "SELECT * FROM $cat RIGHT JOIN sources ON $cat.sourceabbrev=sources.sourceabbrev WHERE $cat.sourceabbrev='$sourceabbrev' and $cat.dateused between '$datebegin' and '$dateend'"; $articleresults = mysql_query($querynested) or die ("Couldn't execute query."); while ($row=mysql_fetch_assoc($articleresults)) { foreach ($row as $colname => $value) { $array_multi[$counter][$colname] = $value; } $counter++; } } foreach ($array_multi as $key => $row) { $dateused[$key] = $row['dateused']; } array_multisort($dateused, SORT_DESC, $array_multi); //Use returned data echo "<table cellspacing='0' border='0' cellpadding='0' width='95%'>"; echo "<tr><th align=\"left\">PN.com Date</th><th align=\"left\">Media Date</th><th align=\"left\">Media Title</th><th align=\"left\">Media Name</th>"; foreach ($array_multi as $key => $row) { $dateused1=$row["dateused"]; $dateused2=strtotime($dateused1); $dateused3=date("Y-m-d", $dateused2); if ($dateused3== "1969-12-31") { $dateused3="None"; } $mediadate1=$row["mediadate"]; $mediadate2=strtotime($mediadate1); $mediadate3=date("Y-m-d", $mediadate2); if ($mediadate3== "1969-12-31") { $mediadate3="None"; } echo "<tr> <td width='12%'>$dateused3</td> <td width='12%'>$mediadate3</td> <td width='55%'><a href=\"{$row["storyurl"]}\">{$row["mediatitle"]}</a></td> <td width='21%'><a href=\"{$row["sourceurl"]}\" class=\"sourcename\"> {$row["sourcename"]}</a></td> </tr>"; } echo "</table>"; ?>

The following code loops through all the tables, however only the results from the last table looped through ('crt') show up when the results are printed out. Is there any way to add on to a query array inside a loop? It keeps getting overwritten. I can move the echo code inside the loop to print all the results from all ten tables, which is close to what I want, however they are in date sequence by table, not globally for the whole list. To do the date sort I need all the results in the array at the same time. $query = "SELECT * FROM categories"; $result = mysql_query($query) or die ("Couldn't execute query."); while ($row = mysql_fetch_array($result)) { $cat=$row['categoryabbrev']; echo $cat; $querynested = "SELECT * FROM $cat RIGHT JOIN sources ON $cat.sourceabbrev=sources.sourceabbrev WHERE $cat.sourceabbrev='$sourceabbrev' and $cat.dateused between '$datebegin' and '$dateend' ORDER BY dateused DESC"; $articleresults = mysql_query($querynested) or die ("Couldn't execute query."); }

Table 'categories' (categoryabbrev are content table names): categoryname categoryabbrev Current Events ce Commentary & Analysis ca Personal Finance & Business pfb Economics ec Context cntxt Family & Home fh Health hlth Security & Preparedness sp Technology tech Culture, Recreation & Travel crt Table 'crt' (simplified), all other tables listed above have identical structure: currentslot dateused mediadate pntitle sourceabbrev storyurl 0 14-Jan 11-Jan How To Travel Well On A Weak Dollar Forbes http://www.forbes.com/home/travel/2008/01/07/ 0 14-Jan 14-Jan Fear on Mexican Beaches Near San Diego ABC http://travel.discovery.com/ Tables 'sources': sourcename sourceabbrev sourceurl ABC News ABC http://abcnews.go.com/ About.com About http://www.about.com/ Associated Content AC http://www.associatedcontent.com/ What I want to do is loop through the tables listed in the 'categories' table, searching for stories in all 10 tables with a common sourceabbrev, like 'ABC' within a specific date range. Right now I use the following: $sourceabbrev=media source name passed in $datebegin=begin date passed in $dateend=end date passed in $query = " (SELECT * FROM ce RIGHT JOIN sources ON ce.sourceabbrev=sources.sourceabbrev WHERE ce.sourceabbrev='$sourceabbrev' and ce.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM ca RIGHT JOIN sources ON ca.sourceabbrev=sources.sourceabbrev WHERE ca.sourceabbrev='$sourceabbrev' and ca.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM pfb RIGHT JOIN sources ON pfb.sourceabbrev=sources.sourceabbrev WHERE pfb.sourceabbrev='$sourceabbrev' and pfb.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM ec RIGHT JOIN sources ON ec.sourceabbrev=sources.sourceabbrev WHERE ec.sourceabbrev='$sourceabbrev' and ec.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM cntxt RIGHT JOIN sources ON cntxt.sourceabbrev=sources.sourceabbrev WHERE cntxt.sourceabbrev='$sourceabbrev' and cntxt.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM fh RIGHT JOIN sources ON fh.sourceabbrev=sources.sourceabbrev WHERE fh.sourceabbrev='$sourceabbrev' and fh.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM hlth RIGHT JOIN sources ON hlth.sourceabbrev=sources.sourceabbrev WHERE hlth.sourceabbrev='$sourceabbrev' and hlth.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM sp RIGHT JOIN sources ON sp.sourceabbrev=sources.sourceabbrev WHERE sp.sourceabbrev='$sourceabbrev' and sp.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM tech RIGHT JOIN sources ON tech.sourceabbrev=sources.sourceabbrev WHERE tech.sourceabbrev='$sourceabbrev' and tech.dateused between '$datebegin' and '$dateend') UNION (SELECT * FROM crt RIGHT JOIN sources ON crt.sourceabbrev=sources.sourceabbrev WHERE crt.sourceabbrev='$sourceabbrev' and crt.dateused between '$datebegin' and '$dateend') ORDER BY dateused DESC"; This works, but has to be changed if I change the content tables. I know there is an elegant way to do this, just not what it is. The site is www.prudentnews.com.

I have a table named categories that contains the names of 10 database tables that have specific topic content. I need to query all ten tables to list data in each table that has a common value in one of the table columns. The ten content tables all have identical structure, with 11 columns. I use ten content tables because that is how the spreadsheet I update nightly is organized and it makes the workflow a lot quicker. I then use the 'Excel MySql Converter' program to convert the spreadsheet to a MySql file and import that into the production server. Right now I use a query statement with 9 UNIONs to accomplish this. It is not very elegant, and if I modify the content tables, I have to recode this section. I know there must be an elegant way to loop this with a nested loop of some kind, but have not been able to figure it out from the web or the books I have. Thanks for any suggestions.