Question:

Is now 6am in the morning and i'm still struggling to execute a query with the CodeIgniter PHP framewok. Hope you guys can help me

Code:

$query=' UPDATE `STUDY_LIST_AUX` INNER JOIN `study_report` ON `STUDY_LIST_AUX.study_iuid`=`study_report.study_iuid` SET `STUDY_LIST_AUX.report_date`=DATE_FORMAT(`study_report.report_date`,\'%Y-%m-%d %h:%i:%s\'), `STUDY_LIST_AUX.report_status` = `study_report.report_status` '; if ($this->db->query($query)) { echo "True!<br><br>"; } else { echo "False<br><br>"; };

Error:

<blockquote>

<strong>A Database Error Occurred</strong>

<em>Error Number: 1064</em>

<em>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE STUDY_LIST_AUX INNER JOIN study_report ON `STUDY_LIST_AUX.study_iu' at line 22</em>

</blockquote>

I've tried everything, backticks, normal ticks, quote marks, but the error persists. On phpmyadmin the query run successfully.

Any suggestions or ideas will be much appreciated

Thanks in advance guys :)

You can update your query code using this below Active Record code

$data = array('s.report_date' => 'DATE_FORMAT(`study_report.report_date`,\'%Y-%m-%d %h:%i:%s\')','s.report_status' => 'sr.report_status'); $this->db->update('STUDY_LIST_AUX s JOIN study_report sr on s.study_iuid = sr.study_iuid',$data);

This'll help you...

try this. use double quote so you don't have to worry the quote inside

$query="UPDATE STUDY_LIST_AUX INNER JOIN study_report ON STUDY_LIST_AUX.study_iuid = study_report.study_iuid SET STUDY_LIST_AUX.report_date = DATE_FORMAT(study_report.report_date,'%Y-%m-%d %h:%i:%s'), STUDY_LIST_AUX.report_status = study_report.report_status";

You have some syntax issue in the query ex:

`STUDY_LIST_AUX.study_iuid`

If you are using backticks then it should be as

`STUDY_LIST_AUX`.`study_iuid`

The correct query should be

$query = " update `STUDY_LIST_AUX` sla join `study_report` sr on sr.study_iuid = sla.study_iuid set sla.report_date = date_format(sr.report_date,'%Y-%m-%d %h:%i:%s'), sla.report_status = sr.report_status ";