Unable to copy a row to the same table(php-mysql)

Question

I am trying to copy row(s) from a table using MySQL query but its showing error like

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 'order,creater_id,created_date,updated_id,updated_date)
            SELECT activ' at line 1

   function copyActivityByActivityId($activity_details_option, $time_hour, $time_min, $distance, $order, $creater_id, $updated_id, $aid) {
        $this->load->database();
        $this->load->helper('date');
        $datestring = "%Y-%m-%d: %H:%i:%s";
        $time = time();
        $today = mdate($datestring, $time);
        $this->db->trans_begin();
        $this->db->query("INSERT INTO calorie_activity (activity_name,mets,parent_id,distance_unit,cals,activity_details_option,time_hour,time_min,distance,order,creater_id,created_date,updated_id,updated_date)
            SELECT activity_name,mets,parent_id,distance_unit,cals,'$activity_details_option',$time_hour,$time_min,'$distance','$order','$creater_id','$today','$updated_id','$today' FROM calorie_activity where id='".$aid ."'");

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return false;
        } else {
            $this->db->trans_commit();
            return true;
        }
    }

why am getting syntax error? any help

Joe T · Accepted Answer · 2014-07-17T18:26:39.490

1

The word 'order' is a reserved word in mysql. In order to use it as a column name, you need to wrap it in backquotes.

Something like this should work:

$this->db->query("  
INSERT INTO calorie_activity  
  (activity_name,
   mets,
   parent_id,
   distance_unit,
   cals,
   activity_details_option,
   time_hour,
   time_min,
   distance,
   `order`,
   creater_id,
   created_date,
   updated_id,
   updated_date)  
SELECT 
  activity_name,
  mets,
  parent_id,
  distance_unit,
  cals,
  '$activity_details_option',
  $time_hour,
  $time_min,
  '$distance',
  '$order',
  '$creater_id',
  '$today',
  '$updated_id',
  '$today' 
  FROM calorie_activity where id='".$aid ."'");

edited Jul 17 '14 at 18:26

answered Jul 17 '14 at 18:05

Joe T

2,300
1
19
31

ok i updated my post to show wrapping the columns in backticks. – Joe T Jul 17 '14 at 18:10
what you said is right but your answer code is not seems as same i wanted... I removed order columns & it run successfully but i do not know how to format that order columns – Dan Jul 17 '14 at 18:17
@JoeT Maybe would be escaping the only column that needed it better for the understanding. Breaking up the statements into a multiline one would help too. – VMai Jul 17 '14 at 18:22
i have renamed 'order' column into 'ordering' to avoid future obstacles – Dan Jul 17 '14 at 18:24

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

There is a post that may help you doing what you want: In MySQL, can I copy one row to insert into the same table?

Hope this helps.

edited May 23 '17 at 12:29

Community

1
1

answered Jul 17 '14 at 18:02

Rekwyem

26
3

link you showed me is different... want using Codeigniter & also i am copying only few columns(NOT the primary key column) – Dan Jul 17 '14 at 18:05

Unable to copy a row to the same table(php-mysql)

2 Answers2