Join Issues

[radar]

For the life of me I cant figure out joins perfectly...

 

So, I have 2 stables..  1 is named deals, the other is named orders

 

I created this SQL query:

$sql = "SELECT SQL_CALC_FOUND_ROWS deal.id, deal.title, deal.min_qty, deal.start_date, deal.secondary,
					count(o.id) as orders_cnt
					FROM deals as deal
					LEFT JOIN orders as o on o.d_id = deal.id
					WHERE start_date = ".$now." AND end_date = ".$end."
					GROUP BY o.d_id, deal.id ORDER BY start_date";

 

this comes back as finding no rows...

 

i have the code for $now and $end just before the sql query here:

 

$now = date('Y-m-d');
					$end = date('Y-m-d', strtotime("$now +1 week"));

 

im sure this isnt is malformed.  What I need to do, is retrieve everything from the database, count the orders from the database where start_date is equal to or greater than today, but is equal to or less than $end

 

any help figuring this out appreciated.

[radar]

was quite simple actually..  where start_date >= '".$now."' AND start_date <= '".$end."' did the trick.

[shlumph]

Based on

where start_date is equal to or greater than today, but is equal to or less than $end

And assuming that start_date is DATETIME, try this:

WHERE start_date >= '{$now}' AND start_date <= '{$end}'

[radar]

Okay so I got the functionality working the way I need it, but im noticing that periodically i get an error saying that i cant pass the result to mysql_num_rows...  looking at this code can anyone see why?  when i get the error if i refresh the screen, I immediately get the results queried back.

 

$sql = "SELECT SQL_CALC_FOUND_ROWS deal.id, deal.title, deal.min_qty, deal.start_date, deal.end_date, deal.status, 
					deal.secondary, count(o.id) as orders_cnt
					FROM deals as deal
					LEFT JOIN orders as o on o.d_id = deal.id
					WHERE start_date >= '".$now."' AND start_date <= '".$end."'
					GROUP BY o.d_id, deal.id ORDER BY start_date LIMIT ".SmartyPaginate::getCurrentIndex().", ".SmartyPaginate::getLimit();
					$data = mysql_query($sql);
					$cnt = mysql_num_rows($data);
					$data = $aws->sql_md_array($data, $cnt);