[SOLVED] Looping through tables listed in another table?

[Elmer Fudd]

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.

[jt]

Hard to say without some specific examples.  Can you maybe show what you do now, or show the layout of 3-4 of the content tables and what you are needing to do with them?

[Elmer Fudd]

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.

[Elmer Fudd]

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.");

 

}

 

[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>";

?>