I ran in to problems a while ago aliasing tables like that because not all of my primary keys were unique...my solution to the problem, another option, would be to construct a loop that runs through an array of table names...it may not be the most efficient way, but it works...
below is a chunk of code from my script that might help. It was designed as a search function, but you may find parts of it useful.
/ Here we define the $table array. All the tables to be searched should be in here. /
$table = array("table1","table2");
$table_num = count($table);
/
This for loop builds the query to be run on the tables,
then executes the query on each table and returns the results.
The loop code is from here to the end of this file.
/
/ This line beginning with 'for' basically says 'Do this for each table' /
for ($i = 0; $i < $table_num; ++$i) {
/* This $statement variable is sufficient if there's only one search word */
$statement = "SELECT tbl, page, title, description FROM $table[$i] WHERE keywords LIKE '%$search_words[0]%' ";
/* This chunk of code adds to $statement if there is more than one search word.
for each occurrence of an additional search word the phrase
'OR keywords LIKE additional-word' is added to $statement */
if ($search_num > 1){
for ($idx = 1; $idx < $search_num; ++$idx){
$statement .= " OR keywords LIKE ";
$statement .= "'%$search_words[$idx]%' ";
}
}
/ This is the MySQL info. "server", "username", "password" --- notice username and database name are identical /
$db = mysql_pconnect("foo", "bar", "foobar");
mysql_select_db("bar", $db);
/ Here is where the query is actually submitted to MySQL for a result.
Notice: it doesn't query the whole DB at once, rather it queries whichever table the for loop is on /
$result = mysql_query($statement, $db);
$num_of_rows = mysql_num_rows($result);
/ This little widget assembles each result and spits it out to the browser.
Each matching row in the table is spit into an array called $row, similar to a CSV file.
$row[0] is the 'table' field, $row[1] is the 'page' field, $row[2] is the 'title' field, etc./
if($num_of_rows > 0) {
while ($row = mysql_fetch_row($result)) {
print "<p>$row[0]<br>
$row[1]<br>
$row[2]</p>;"
}
}
}
