Query multiple MySQL tables is not working.

[00stuff]

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.

(Table)

qc_reports

 

(Fields)

id

report_date

report_lot_number

report_po

report_supplier

report_buyer

report_inspectedby

report_pulptemprange

report_carrierconditions

report_supplierclaim

report_carrierclaim

report_temprecorder

report_temprange_N

report_temprange_M

report_temprange_B

report_suppliercontact

report_contactedby

report_time

report_comments

 

(Table)

qc_lots

 

(Fields)

id

report_id

lot_temprange

lot_commodity

lot_rpcs

lot_brand

lot_terms

lot_cases

lot_orgn

lot_estnum

lot_avgnum

 

This is the query that I'm trying to do.

 

<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
	if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
	if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
	if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
	if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
	if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
	if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
	 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
	if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

	$sql = substr($sql, 0, -4);

	$query = mysql_query($sql);


	$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
	<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td> </td></tr>
	<tr>
	<td> </td>
	</tr>
<?php
	while ($row = mysql_fetch_assoc($query))
{
	$id = stripslashes($row['id']);
	$report_lot_number = stripslashes($row['report_lot_number']);
	$report_po = stripslashes($row['report_po']);
	$report_date = stripslashes($row['report_date']);

	echo "<tr>
		<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
	</tr>";	
}
	echo '</table><br><br><br><hr><br><br>';

}

?>

 

All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

 

I'm stuck and need some guidance. Can someone help please?

[cunoodle2]

honestly I would try echoing the results first to the screen.  It is hard to tell if possibly your logic is wrong

[00stuff]

Is there a way I can join both tables and then query only the results that meet all the criteria? I thought there was a way to do something similar. Any ideas at all?