Code for 'any' variable?

Question

I want to consolidate the following code. Although I'm not sure how to reference SELECT * FROM any table ending in listing.

     $this->displaylistings=mysql_query("SELECT * FROM nordstromlisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM saksfifthavenuelisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM guesslisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM gnclisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM urbanoutfitterslisting WHERE ShopperID = '$tnis->ShopperID'
 UNION ALL SELECT * FROM bananarepubliclisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM americaneaglelisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM neimanmarcuslisting WHERE ShopperID = '$this->ShopperID'
 UNION ALL SELECT * FROM footlockerlisting WHERE ShopperID = '$this->ShopperID'");

Thanks in advance :D

Answer 1

You can't specify wildcards in table names, as far as I know. So you code is probably as small as it's going to get.

If you don't need real-time access, you can periodically write the result to another table, and reference that table only.

Answer 2

To my knowledge there isn't a way to refer to tables with wildcards.

You might want to consider changing the schema so that you have a single listing table with a column to indicate what type of listing it is (saksfifthavenue, guess, ...).

You could then simply query against that single table.

This would also normalize your schema a little better.

Answer 3

You can query by wildcard with the SHOW TABLES command. In your case you would do:

SHOW TABLES LIKE '%listing'

That works on the command line or in PHP:

 $find_tables_query = "SHOW TABLES LIKE '%listing'";
$result = mysql_query($find_tables_query)
    or die ("Cannot make query. " . mysql_error());

$queries = array();
while ($row = mysql_fetch_row($result)) {
    $queries[] = 'SELECT * FROM '
        . $row[0]
        . ' WHERE ShopperID=\'$this->ShopperID\'';
}
$combined_query = implode("\nUNION ALL\n",$queries);

// for debugging, print query:
print "<pre>";
print $combined_query;
print "</pre>";