Comparing Multiple Dates from Two Different Tables.

HairyArse

Hi guys,

bit of a tricky one here - at least for me anyway.

I have two tables named: products and deliveries.
products has a field called Delivery which I want to query and deliveries has a field called Date_Delivered which I want to query.

The where clause to select the records is a field called Nsn which is in both tables

I want to compare the date values from each which I will do using the DATEDIFF Sql function.

BUT. For the deliveries tables there may be multiple deliveries made against that order.

I have tried the code:

SELECT DATEDIFF(products.Delivery, deliveries.Date_Delivered) 
FROM products, deliveries
WHERE products.Nsn='8455-99-132-8888' AND Deliveries.Nsn='8455-99-132-8888'

But this does not seem to work. Can someone firstly tell me if this is along the right lines, and if so correct it for me.

Will this work even though there may be multiple deliveries?

HairyArse

I've tried to simplify this by breaking down into the three indivual queries and running them in a seperate php file until I get the code working then I can insert it into my system, can anyone see what is wrong with the code below:

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?
//connect
$dbh=mysql_connect ("localhost", "wyedyamf_rhorne", "216399Bb") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("wyedyamf_wyedean"); 


$query  = "SELECT Delivery FROM products WHERE Nsn='8455-99-132-8888'";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) 
{
	foreach ($row as $field=>$value) 
	{
	echo ("$value<br><br>");
	$enddate=$value;
	}
}

$query1  = "SELECT Date_Delivered FROM deliveries WHERE Nsn='8455-99-132-8888'";
$result1 = mysql_query($query1);

while ($row1 = mysql_fetch_assoc($result1)) 
{
	foreach ($row1 as $field1=>$value1) 
	{
	echo ("$value1<br><br>");
	$startdate=$value1;
	echo("The start date is $startdate and the end date is $enddate");


		$queryb  = "SELECT DATEDIFF('$startdate', '$enddate')"; 
		$results = mysql_query($queryb);
		while ($datedifference = mysql_fetch_array($results))
 		{
		 	  {
			  echo("$datedifference");
			  };

		 };

	}
}	

mysql_close($dbh); 
?>	
</body>
</html>

I get the error message

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/wyedyamf/public_html/mod_ww/datediff.php on line 42

Which means there is something wrong with the query called queryb but I can't see the problem :S