0

I am trying to put together a MySQL query which will search 2 tables, table1 and table2 and select all results which are less than 30 days old in either table.

my tables:

table1

id   |   user_id   |    name     |     age    |    date    |


table2

id   |   user_id   |    name     |     age    |    date    |     account     |     sortcode    |

I am echoing out the results like so:

    <?php require_once 'config.php'; ?>

<?php
$table1 = 'supplier_bank_details';
$table2 = 'supplier_invoices';

$query = "SELECT *, $table1 as TABLE from $table1 where 
date > NOW() - INTERVAL 30 DAY and user_id = '{$_SESSION['id']}' ORDER BY date DESC
UNION
SELECT *, $table2 as TABLE from $table2 where
date > NOW() - INTERVAL 30 DAY and user_id = '{$_SESSION['id']}' ORDER BY date DESC";
$result = mysql_query($query) or die( mysql_error() );
while($row = mysql_num_fields($result)){


if($result === $table1) {

echo 'this result is from table1';
echo $row['name'];
echo $row['age'];

}else{

if($result === $table2) {

echo 'this result is from table2';
echo $row['name'];
echo $row['age'];


} } }
?>

so basically I am trying to put in place a condition which checks which table the results are coming from and echo's out 'result is from table 1/2' along with the values in that table.

Does anyone know how I could do this as I am pretty new to MySQL query. Thanks in advance,

James Daley
  • 259
  • 2
  • 8
  • 20

1 Answers1

0

You should use union for this purpose. In addition you can hard code the value of the table($table1 or $table2) with the result set itself.

Select id, user_id, name, age, date, TABLE
from
(
    SELECT id, user_id, name, age, date, $table1 as TABLE from $table1 where 
    date > NOW() - INTERVAL 30 DAY and user_id = '{$_SESSION['id']}' 
    UNION
    SELECT id, user_id, name, age, date, $table2 as TABLE from $table2 where
    date > NOW() - INTERVAL 30 DAY and user_id = '{$_SESSION['id']}' 
)
ORDER BY date DESC
Sridhar DD
  • 1,972
  • 1
  • 10
  • 17
  • thanks for this, what would my php if statements be like? because if(MySQL_table_name = 'table1') { produces a suntax error at the moment, what should I be using instead? thanks – James Daley Feb 11 '15 at 09:58
  • Check and remove Remove the mysql_table_name string from the disable_functions at php.ini* file – Sridhar DD Feb 11 '15 at 10:31