[SOLVED] Best way to query more than one table in a single statement?

[suttercain]

I have about 6 tables all with the same column names but seperated by year. Table names are 1994data, 1995data, 1996data etc.

 

Is there an efficient way to query through each of these table when doing a search? Is table join the only way?

 

<?php
$engfam = mysql_real_escape_string($_POST['efn']);
	$sql = mysql_query("SELECT * FROM 1994data WHERE engfam ='".$engfam."'") or die(mysql_error());
?>

 

Thanks

 

SC

[The Little Guy]

SELECT * FROM 1994data, 1995data, 1996data WHERE month = '8'

[suttercain]

Hi Little Guy,

 

I had tried that and got 'Column 'engfam' in where clause is ambiguous'

[socratesone]

$query = "SELECT * FROM 1994data, 1995data, 1996data" .
            " WHERE (" .
               " 1994data.engfam = '$engfam' OR " .
               " 1995data.engfam = '$engfam' OR " . 
               " 1996data.engfam = '$engfam'" .
            ")";

[suttercain]

Hi Socratesone,

 

That ended up dumping all the results into the Search result.

[suttercain]

Figured it out using an array and for loop:

 

$year_arr = array("1994","1995","1996");
    $arr_size = count($year_arr);


	if (isset($_POST['efn'])) {
	$engfam = mysql_real_escape_string($_POST['efn']);
	$flag = 0;
              for ($i = 0; $i < $arr_size; $i++) 
              {
                //SQL Statement
                $sql = mysql_query("SELECT * FROM " .$year_arr[$i]. "data WHERE ENGFAM = '$engfam'") or die(mysql_error());
		  

while ($row = mysql_fetch_array($sql)) {
echo "Engine Family Name: " . $row['engfam'] ."<br>";
		}
	}
}