MySQL Left Join Query with WHERE statement

I am having problems outputting the data I require:

$start_of_month = mktime(00, 00, 00, date('m'), 01);
$datestart = date("Y-m-d H:i:s",$start_of_month);

'SELECT
orderheader.ordernumber
, ccilog.sessionid
, ccilog.orderid
, orderheader.userid
, users.emailaddress
, orderheader.webbrandcode
, orderitems.productcode
, orderitems.productname
, orderheader.datecreated
, ccilog.formattedpaymentdate
, orderheader.voucherpromotioncode
, orderheader.vouchercode
, orderheader.itemtotalsell
, orderheader.shippingtotalsell
, orderheader.totalbeforediscount
, orderheader.voucherdiscountvalue
, orderheader.totaldiscount
, orderheader.totalsell
, orderheader.totaltax
, orderheader.total
, orderitems.subtotal
, orderitems.discountvalue
, ccilog.amount 
FROM orderheader 
LEFT JOIN ccilog ON orderheader.id=ccilog.orderid 
LEFT JOIN orderitems ON ccilog.orderid=orderitems.orderid 
LEFT JOIN users ON orderheader.userid=users.id 
WHERE ccilog.formattedpaymentdate > "'.$datestart.'"'

I have a script that turns this query into a csv file. The whole script works great until I put in the where statement, an I don't get whaty I expect.

I have three rows that contain the dates greater than the start of the month date $datestart, but for some reason only 2 of them show and I just cant work out why?

My dates are setup in the same format is $datestart (eg 2011-07-07 13:31:28) and the 3 rows have the same dates but slightly different times, about 4 mins apart.

I'm stuck, can someone give me a bit of advice?

Thanks

EDIT: Here's the data:

---- Orderheader - 
row1 id:122
row2 id:123 
row3 id:124 

---- ccilog - 
Row1 orderid:122 formattedpaymentd开发者_如何转开发ate:2011-07-07 13:23:52
Row2 orderid:123 formattedpaymentdate:2011-07-07 13:28:24 
Row3 orderid:124 formattedpaymentdate:2011-07-07 13:31:28 

I'm sure that the type is a date type but can't work out how to see that for sure.

Move your WHERE condition into the ON clause:

LEFT JOIN
        ccilog
ON      ccilog.orderid = orderheader.id
        AND ccilog.formattedpaymentdate > $datestart

WHERE is evaluated (logically) after the joins, so the filter gets applied to NULL values produced by the LEFT JOIN and the condition never holds.

If the problem's really how you describe, then I'd guess that one of your three rows in ccilog that match your criteria has an orderid that doesn't have a corresponding id in the orderheader table (and if you wanted to include it, you'd really need a RIGHT JOIN between those tables, not a LEFT JOIN...)

Otherwise I'm with Quassnoi, as there's not much point in LEFT JOINing to a table and then specifiying WHERE criteria where a column in that table must be non-NULL.

Really, we'd need to see your data to figure out exactly what's going on -- at least the primary/foreign keys and the formattedpaymentdate column, including the types involved (if formattedpaymentdate is actually a date type column rather than a character column, for example, that could be confusing the issue.)