Need help with mysql query

I need help with php code for sql querys that searches 2 tables based on date range and item. Below is not a attempt to code, its was the best way for me to explain


1. SELECT jobid FROM jobs WHERE date (is between 8/*/2009 and 9/*/2009)

Then

2. SELECT * FROM bids WHERE jobid= (the jobids found in the first mysql_query) and item=$_POST['item']


Database Tables, Field names and data examples


JOBS
jid|jobid|description|date
1|232|Part time work|9/15/2009

BIDS
bid|jobid|item|user|price
1|232|floor cleaning|micky|8


Form Mockup (Just for a mental picture so you see what Im trying to do)


Thanks for anyhelp

 

SELECT * FROM jobs

INNER JOIN bids 
ON bids.jobid = jobs.jobid

WHERE jobs.date BETWEEN 2009-08-01 and 2009-09-31

Code (markup):

This will select all fields for date between the specified range.

 

Thanks that worked! So I tested this query in phpMyadmin and it worked

SELECT *
FROM jobs
INNER JOIN bids ON bids.jobid = jobs.jobid
WHERE jobs.dotdate
BETWEEN '1/20/2009'
AND '9/15/2009'
AND bids.item = 'floor cleaning'
AND bids.user = 'micky'

Code (markup):

BUT Im having a problem getting my php code to work [see below]

$result = mysql_query("SELECT * FROM jobs 
INNER JOIN bids ON bids.jobid = jobs.jobid
WHERE jobs.dotdate BETWEEN '.$date1.' and '.$date2.' 
AND bids.item='$item' AND bids.user='$user'");

$num_rows = mysql_num_rows($result);
if($num_rows != 0){
	$sum = '0.0000';
	while($row = mysql_fetch_assoc($result)){
	$sum = ($sum + $price);

	$bids_id = $row['bids_id'];
	$jobid = $row['jobid'];
	$item = $row['item'];
	$description = $row['description'];
	$user = $row['user'];
	$price = $row['price'];
	}
}

Code (markup):

Any Idea what Im doing wrong?

 

Just at a really quick glance the periods next to the dates might be causing the issue...

$result = mysql_query("SELECT * FROM jobs
INNER JOIN bids ON bids.jobid = jobs.jobid
WHERE jobs.dotdate BETWEEN '{$date1}' and '{$date2}'
AND bids.item='{$item}' AND bids.user='{$user}'");

 

include a line mysql_query($sql) or die(mysql_error()); that will help you debug what the error is in the sql query

 

As RD2S said, remove those dots:

$result = mysql_query("SELECT * FROM jobs 
INNER JOIN bids ON bids.jobid = jobs.jobid
WHERE jobs.dotdate BETWEEN '$date1' and '$date2' 
AND bids.item='$item' AND bids.user='$user'");

$num_rows = mysql_num_rows($result);
if($num_rows != 0){
	$sum = '0.0000';
	while($row = mysql_fetch_assoc($result)){
	$sum = ($sum + $price);

	$bids_id = $row['bids_id'];
	$jobid = $row['jobid'];
	$item = $row['item'];
	$description = $row['description'];
	$user = $row['user'];
	$price = $row['price'];
	}
}

PHP:

 

A general word of wisdom: when you're working with PHP, a good way to debug is:

$query="SELECT ... (insert query text here);"
echo $query; // Or something of the sort, then comment it out when your code goes live
$result=mysql_query($query);

Code (markup):

Then paste the output of your query into MySQL or PHP My Admin or whatever tool you're using, and debug the query. This separates the MySQL debugging from the PHP debugging and will greatly speed up your efforts, and will save you the need for visiting forms like this, as much as we love having you here!

=)