89353

CodeIgniter - MySQL Error 1064 (Update table1 inner join table2(…))

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 :)

Answer1:

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...

Answer2:

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";

Answer3:

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 ";

Recommend

  • Failed to update work status Exception in Python Cloud Dataflow
  • Zend Framework 2, Module Redirect
  • How to startActivity for Result from Activity under Group Activity Under Tabs?
  • Cannot convert a char value to money. The char value has incorrect syntax
  • PHP Regex Look Around Help
  • Why does PHP appear to evaluate this condition incorrectly?
  • Use of qualified name in function parameter
  • Contact form problem - I do receive messages, but no contents (blank page)
  • What is Closure Compiler?
  • how to pass class attribute and value to markdown syntax
  • Syntax error near unexpected token 'elif'
  • how to read a file in prolog?
  • Getting syntax error in mysql-php. You have an error in your SQL syntax;
  • ZipList with Scalaz
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Question about instantiating object
  • Email verification using google app script and google forms
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Display Images one by one with next and previous functionality
  • Upload files with Ajax and Jquery
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Cant find why the layout is getting smaller
  • How to stop GridView from loading again when I press back button?
  • Turn off referential integrity in Derby? is it possible?
  • Linking SubReports Without LinkChild/LinkMaster
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • To Get the radio button value in ruby on rails
  • java string with new operator and a literal