PHP MySQL Left Join Query with WHERE Statement

General PHP Help
#1

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

#2

[php]WHERE ccilog.formattedpaymentdate “’.$datestart.’”’
[/php]

should be

[php]WHERE ccilog.formattedpaymentdate = “’.$datestart.’”’
[/php]
it looks like you forgot the = sign

#3

Yes I did forget it in this code posted here, but not in the working code, the = should have been >.

[php]http://www.jewelleryandgifts4u.co.uk/test/example.html[/php]

Above is an example of what I am trying to do and nothing I do seems to work. All I want to do is diplay the rows that have a ccilog.formattedpaymentdate > “’.$datestart.’” but I seem to miss rows when I try this.

#4

Have you tried to pull the dates and put them in a timestamp value and then compare? Is it that you are getting results but the the wrong dates are coming back? cause the query looks good to me.(scratching head :D) I think if you convert to timestamp you will not have a trouble of greater than a date…
Let me know if I am understanding you correctly.

#5

OK I found it wasn’t the join that is the problem, see code below:

[code] // store the number of columns from the results
$columns = (($___mysqli_tmp = mysqli_num_fields($result)) ? $___mysqli_tmp : false);

// Build a header row using the mysql field names 

$rowe = mysqli_fetch_assoc($result);
$acolumns = array_keys($rowe);
$csvstring = '"=""' . implode('""","=""', $acolumns) . '"""';
$header_row .= $csvstring; 

// Below was used for MySQL, Above was added for MySQLi
//$header_row = '';
//for ($i = 0; $i < $columns; $i++) {
//  $column_title = $file["csv_contain"] . stripslashes(mysql_field_name($result, $i)) . $file["csv_contain"];
//  $column_title .= ($i < $columns-1) ? $file["csv_separate"] : '';
//  $header_row .= $column_title;
//  } 
$csv_file .= $header_row . $file["csv_end_row"]; // add header row to CSV file 

// Build the data rows by walking through the results array one row at a time 
$data_rows = ''; 
while ($row = mysqli_fetch_array($result)) { 
  for ($i = 0; $i < $columns; $i++) { 
    // clean up the data; strip slashes; replace double quotes with two single quotes 
    $data_rows .= $file["csv_contain"] .$file["csv_equ"] .$file["csv_contain"] .$file["csv_contain"] . preg_replace('/'.$file["csv_contain"].'/', $file["csv_contain"].$file["csv_contain"], stripslashes($row[$i])) . $file["csv_contain"] .$file["csv_contain"] .$file["csv_contain"];
    $data_rows .= ($i < $columns-1) ? $file["csv_separate"] : ''; 
  } 
  $data_rows .= $this->csv_end_row; // add data row to CSV file 
} 
$csv_file .= $data_rows; // add the data rows to CSV file 
 
if ($this->debugFlag) { 
  echo "Step 4 (repeats for each attachment): CSV file built. \n\n"; 
} 
 
// Return the completed file 
return $csv_file; [/code]

The problem I am having is when building a header row for the column titles mysqli doesn’t use field_names so I am fetching the column titles by using mysqli_fetch_assoc() and then implode() the array, adding the ,'s etc for the csv.

This works but when I produce the csv I am deleting the first $data_rows line when the header is active, when I remove my header part of the script and leave the header as null I get all data rows and a blank header (As expected).

So I must be missing something when joining my header to array to the $csv_file.

Can anyone point me in the right direction?

Sponsor our Newsletter | Privacy Policy | Terms of Service