0

How can I modify the following query code to include the table names in the results?

The $query searches 4 tables in the database and I want the results to include which table they came from. All 4 tables have identical fields.

I then want to output the results as shown in the $results_array[] array below.

Thanks in advance.

$query = mysql_query("select * from $TableName_1 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select * from $TableName_2 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select * from $TableName_3 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select * from $TableName_4 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
order by Post_Date desc LIMIT $items_to_query");

while($row = mysql_fetch_assoc($query)){
 $table_name = ?????????????????????;
 $db1 = $row['Post_Date']);
 $db2 = $row['Post_Title'];
 $db3 = $row['Author']);

 $results_array[] = '<div>'.$table_name.' - '.$db1.' - '.$db2.' - '.$db3.'</div>'."\n";
}
Sammy
  • 877
  • 1
  • 10
  • 23

1 Answers1

-1
$query = mysql_query("select *, '$TableName_1' as table_name from $TableName_1 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select *, '$TableName_2' as table_name from $TableName_2 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select *, '$TableName_3' as table_name from $TableName_3 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
UNION select *, '$TableName_4' as table_name from $TableName_4 where Topic = '$topic' AND 
((convert(`Post_Title` using utf8) like '%$term_1%') OR 
 (convert(`Post_Title` using utf8) like '%$term_2%') OR 
 (convert(`Post_Title` using utf8) like '%$term_3%') OR 
 (convert(`Post_Title` using utf8) like '%$term_4%')) 
order by Post_Date desc LIMIT $items_to_query");
Wing Lian
  • 2,387
  • 16
  • 14
  • Thank you, but how would I get the table names out in the $results_array[] ? – Sammy Dec 16 '12 at 01:33
  • I just edited it with quotes to get the $tableName var as a string rather than the table. use $row['table_name']; – Wing Lian Dec 16 '12 at 01:34
  • I tried it but the table name is not retrieved. I added $table = $row['table_name']; within the while loop and $table to the $results_array[] but I only got blank data. – Sammy Dec 16 '12 at 01:51
  • Any other options to Help ? – Sammy Dec 16 '12 at 02:02