How to solve this error in MySql Query syntax in codeigniter?

Question

This is The Error I am getting..

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 'Sensor = 'sklfj', Lens = 'lkjsdf', IR = 'lkjdsf', Audio = 'kjlasf', `WDR' at line 1

UPDATE `hdtvicameras_spec`
SET `Resolution` = 'd',
 `Image` `Sensor` = 'sklfj',
 `Lens` = 'lkjsdf',
 `IR` = 'lkjdsf',
 `Audio` = 'kjlasf',
 `WDR` = 'lkjsf1',
 `ICR` = 'klasjf',
 `IP` `Rating` = 'lkjsdf',
 `Zoom` = 'ljs'
WHERE
    `product_id` = '46'

Product_model.php

$this->db->where('product_id', $product_id);
$this->db->update($tbname, $spec_array);

After var_dump($spec_array) this is what I get

array(10) { 
    ["Resolution"]=> string(5) "first" 
    ["Image Sensor"]=> string(6) "second" 
    ["Lens"]=> string(6) "third " 
    ["IR"]=> string(6) "fourth" 
    ["Audio"]=> string(6) "fifthe" 
    ["WDR"]=> string(6) "sisxth" 
    ["ICR"]=> string(5) "seven" 
    ["IP Rating"]=> string(5) "eight" 
    ["Zoom"]=> string(4) "nine" 
    ["SD Card"]=> string(3) "ten" 
}

All those key values in array are fetched from database which is specification names of any product.

looks like there is some error with space in key name. Can anybody help me solve this?

Here you need to change Image Sensor to ImageSensor or some thing else in to your $spec_array. — Mayank Vadiya
– Mayank Vadiya, Commented Nov 30, 2016 at 7:16
@MayankVadiya thanks... i used this function str_replace(' ','",$specification); So that if there will be any space it will be replaced by '' .. so that it will not cause any trouble in future... :) — ravi
– ravi, Commented Nov 30, 2016 at 12:21

Mayoori - gaja digital agency · Accepted Answer · 2016-11-30 08:00:40Z

3

use query like this and check:

UPDATE `hdtvicameras_spec`
SET `Resolution` = 'd',
 `Image Sensor` = 'sklfj',
 `Lens` = 'lkjsdf',
 `IR` = 'lkjdsf',
 `Audio` = 'kjlasf',
 `WDR` = 'lkjsf1',
 `ICR` = 'klasjf',
 `IP Rating` = 'lkjsdf',
 `Zoom` = 'ljs'
WHERE `product_id` = '46'

Since you added quotes, for fields names having more then 1 word, its showing error.

edited Nov 30, 2016 at 8:00

answered Nov 30, 2016 at 6:56

Mayoori - gaja digital agency

2842 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek Gurjar · Accepted Answer · 2016-11-30 06:52:39Z

1

I think you have error here:

`Image` `Sensor` = 'sklfj',

After image you didn't give the value to it.

answered Nov 30, 2016 at 6:52

Abhishek Gurjar

7,47610 gold badges41 silver badges47 bronze badges

2 Comments

ravi Over a year ago

Actually Image Sensor is only one property... it defines whether camera contains image sensor or not.... Can we use Space in key names??

Abhishek Gurjar Over a year ago

so use the backtick correctly ImageSensor like this

A J · Accepted Answer · 2016-11-30 06:58:11Z

1

Since Image Sensor is a column with space in it, therefore you are getting an error. CI is adding ` to every field with a space. It is not a good practice to use a space in your columns in MySQL.

In order to solve this problem, either you should remove space or write a query in below mentioned form and pass it in $this->db->query().

UPDATE `hdtvicameras_spec` 
SET `Resolution` = 'd', `Image Sensor` = 'sklfj', `Lens` = 'lkjsdf', `IR` = 'lkjdsf', `Audio` = 'kjlasf', `WDR` = 'lkjsf1', `ICR` = 'klasjf', `IP Rating` = 'lkjsdf', `Zoom` = 'ljs' 
WHERE `product_id` = '46'

answered Nov 30, 2016 at 6:58

A J

4,01214 gold badges40 silver badges55 bronze badges

1 Comment

ravi Over a year ago

Ohh... Got that.. I changed every table containing space and replaced it with '_' ... Thanks.. :)

Collectives™ on Stack Overflow

How to solve this error in MySql Query syntax in codeigniter?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related