Welcome Guest, Not a member yet? Register   Sign In
db->query HELP :(
#1

[eluser]Linderman[/eluser]
I have this:
Code:
$query = "INSERT INTO ci_images (id, imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ('', '$data[imagetype]', '$data[usage]', '$data[usage_id]', '$data[size_num]', '$data[size]', '$data[name]', '$data[fullpath]', '$data[comment]')";

$r = $this->db->query($query);

when executed i got:

A Database Error Occurred

Error Number: 1064

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 'usage, usage_id, size_num, size, name, fullpath, comment) VALUES('', 'o' at line 1

I am sure that $data array contain all the needed elements. I even tested with hard coded values ... but this error continuing to appear???

When i use
Code:
$this->db->insert('ci_images', $data);
- everything is OK , but , please explain me , why
Code:
$this->db->query($query);
does not work???
#2

[eluser]carvingCode[/eluser]
Chances are some of your of your data items are not escaped properly. $this->db->insert() escapes properly and that's why it works.
#3

[eluser]Linderman[/eluser]
[quote author="carvingCode" date="1272991431"]Chances are some of your of your data items are not escaped properly. $this->db->insert() escapes properly and that's why it works.[/quote]

ok but i even test it with hardly coded values, like:
Code:
$query = "INSERT INTO ci_images (id, imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ('', 'test', 'test', '1', '1', 'test', 'test', 'test', 'test')";

where i have 'test' its varchar, and where i have '1' its INT ... but it still gives the error. There are NO special characters like you see .. maybe the problem is in my PHP syntax, not in CI.
#4

[eluser]danmontgomery[/eluser]
[quote author="Linderman" date="1272991805"]maybe the problem is in my PHP syntax, not in CI.[/quote]

The problem is in the MySQL query, it says so in the error message:

Quote:You have an error in your SQL syntax

Enable the profiler (http://ellislab.com/codeigniter/user-gui...iling.html) to see the query actually being run.
#5

[eluser]Kenshiro[/eluser]
The only thing i could see wrong with your query is the ID field, as it's an auto field i guess, just leave it out of your query...

Code:
$query = "INSERT INTO ci_images (imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ( '$data[imagetype]', '$data[usage]', '$data[usage_id]', '$data[size_num]', '$data[size]', '$data[name]', '$data[fullpath]', '$data[comment]')";

$r = $this->db->query($query);

Also numeric data should be treated as numeric, that meane, '$data['usage_id']' should be written $data['usage_id'] (without the quotes), lastly as you are using " (double quotes) to have your array parsed directly i think errors might arise as well, cause $data[imagetype] should actually be $data['imagetype'] or else imagetype is assumed to be a constant or var by php (when it's not).

So i would actually write the query this way :

Code:
$query = 'INSERT INTO ci_images (imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ( "'.$data[imagetype].'", "'.$data[usage].'", '.$data[usage_id].', '.$data[size_num].', "'.$data[size].'", "'.$data[name].'", "'.$data[fullpath].'", "'.$data[comment].'")';

$r = $this->db->query($query);

//even better if you are building with mysql and don't plan on switching of DB engine anytime soon there is a simple way to write the sql query in an update syntax manner.

$query = 'INSERT INTO ci_images SET imagetype = "'.$data['imagetype'].'", usage = "'.$data['usage'].'", usage_id = '.$data['usage_id'].', size_num = '.$data['usage_id'].', size = "'.$data['size'].'", name = "'.$data['name'].'", fullpath = "'.$data['fullpath'].'", comment = "'.$data['comment'].'"';

$r = $this->db->query($query);

// Or lastly use the Active record way...
#6

[eluser]theprodigy[/eluser]
Apart from what has already been said, you may want to consider back-ticking the column names (this is part of the "escaping" that carvingCode mentioned). By back-ticking (`) the column names, this will make sure that if any of your column names are mysql reserved words, it recognizes them as column names and doesn't try to render whatever command they happen to be a part of.

Using Kenshiro's updated syntax, change:

Code:
$query = 'INSERT INTO ci_images (imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ( "'.$data[imagetype].'", "'.$data[usage].'", '.$data[usage_id].', '.$data[size_num].', "'.$data[size].'", "'.$data[name].'", "'.$data[fullpath].'", "'.$data[comment].'")';
to:
Code:
$query = 'INSERT INTO ci_images (`imagetype`, `usage`, `usage_id`, `size_num`, `size`, `name`, `fullpath`, `comment`) VALUES ( "'.$data[imagetype].'", "'.$data[usage].'", '.$data[usage_id].', '.$data[size_num].', "'.$data[size].'", "'.$data[name].'", "'.$data[fullpath].'", "'.$data[comment].'")';
#7

[eluser]Linderman[/eluser]
Hello! Thank you all for your replies! You great

I tried everything you said but i still got this problem. Finally i made it with theprodigy and Kenshiro advices and it works!!! It seems that some of column name is reserved word in MYSQL, but when i back ticking the column names .. the problem is GONE...

One more time , thank you all, Respect \/

the final solution:
[quote author="theprodigy" date="1273011851"]Apart from what has already been said, you may want to consider back-ticking the column names (this is part of the "escaping" that carvingCode mentioned). By back-ticking (`) the column names, this will make sure that if any of your column names are mysql reserved words, it recognizes them as column names and doesn't try to render whatever command they happen to be a part of.

Using Kenshiro's updated syntax, change:

Code:
$query = 'INSERT INTO ci_images (imagetype, usage, usage_id, size_num, size, name, fullpath, comment) VALUES ( "'.$data[imagetype].'", "'.$data[usage].'", '.$data[usage_id].', '.$data[size_num].', "'.$data[size].'", "'.$data[name].'", "'.$data[fullpath].'", "'.$data[comment].'")';
to:
Code:
$query = 'INSERT INTO ci_images (`imagetype`, `usage`, `usage_id`, `size_num`, `size`, `name`, `fullpath`, `comment`) VALUES ( "'.$data[imagetype].'", "'.$data[usage].'", '.$data[usage_id].', '.$data[size_num].', "'.$data[size].'", "'.$data[name].'", "'.$data[fullpath].'", "'.$data[comment].'")';
[/quote]
#8

[eluser]cahva[/eluser]
And the problem was the usage field which is a reserved word.




Theme © iAndrew 2016 - Forum software by © MyBB