[SOLVED] Why won't this array/query work?

[forumnz]

<?php
$arr = json_decode($form, true);
$sql = "INSERT INTO acc_rec_invoice SET hash_key = '$hk', inv_key = '$ik', ";
foreach ($arr as $k => $v) {
        $sql .= "$k = '$v', ";
    }
    $sql = substr($sql, 0, -2);
mysql_query($sql);
?>

 

This is the snippet of the code.

When I echo $sql, the output is:

INSERT INTO acc_rec_invoice SET hash_key = '', inv_key = '4d3ace164d2fc28cd7efee6eb28fc1fd', toperson = 'Sam Walker', date1 = '10 May 2009', date2 = '24 May 2009', invnumber = '006', refer = 'Products', tax_s = 'inclusive', ext-comp-1007 = '1122', ext-comp-1008 = 'Nissan Light', ext-comp-1009 = '2', ext-comp-1010 = '69', ext-comp-1011 = 'acc3', ext-comp-1012 = 'tax3'

 

Should work shouldn't it? I do have all the fields in the database.

 

Thanks

[Ken2k7]

INSERT INTO has no SET.

 

Learn to Google!

[forumnz]

Actually Ken2k7, Thorpe helped me with a similar issue yesterday and he did suggest INSERT INTO table SET...

 

It worked. Anyone have any suggestions?

[gevans]

Hey forumnz

 

SET is used with an UPDATE query, and INSERT should look something like this for you;

 

INSERT INTO acc_rec_invoice 
(hash_key, inv_key, toperson, date1, date2, invnumber, refer, tax_s, ext-comp-1007, ext-comp-1008, ext-comp-1009, ext-comp-1010, ext-comp-1011, ext-comp-1012)
VALUES ('', '4d3ace164d2fc28cd7efee6eb28fc1fd', 'Sam Walker', '10 May 2009', '24 May 2009', '006', 'Products', 'inclusive', '1122', 'Nissan Light', '2', '69', 'acc3', 'tax3');

 

If you change your code to get your sql to look like that you're laughing!

[Ken2k7]

haha didn't know that. Maybe I should learn to Google. Sorry dude.

 

Have you tried using backticks on the column names?

[gevans]

Or check what the query is actually doing wrong;

 

mysql_query($sql) or die(mysql_error());

[forumnz]

That's cool Ken2k7 - you learnt something new!

 

Thanks gevans. I did that and I get:

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 '-comp-1007 = '1122', ext-comp-1008 = 'Nissan Light', ext-comp-1009 = '2', ext-co' at line 1

 

I can't see anything wrong though. I don't need to use backticks (I had a similar query yesterday that worked)

[Ken2k7]

Use backticks.

[gevans]

Right now I can't see anything wrong either. Are you using phpmyadmin? If so open it up hit the 'SQL' tab and paste this in there

 

INSERT INTO acc_rec_invoice SET hash_key = '', inv_key = '4d3ace164d2fc28cd7efee6eb28fc1fd', toperson = 'Sam Walker', date1 = '10 May 2009', date2 = '24 May 2009', invnumber = '006', refer = 'Products', tax_s = 'inclusive', ext-comp-1007 = '1122', ext-comp-1008 = 'Nissan Light', ext-comp-1009 = '2', ext-comp-1010 = '69', ext-comp-1011 = 'acc3', ext-comp-1012 = 'tax3'

 

that's your original query from post number 1, if the syntax is ok it will insert, if not, well, we'll go from there

[Ken2k7]

Read - http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

 

The set of alphanumeric characters from the current character set, “_”, and “$” are not special.

 

Hyphen is a special character.

[forumnz]

Thanks guys! The - just needed replacing with something else (I just deleted it).

 

Much appreciated,

Sam