PHP INSERT error when imploding an array with count greater than 2

Question

I have this weird problem when trying to INSERT a row into my mysql database.

This is the idea. A range of exercise is listed and you can add a quantity and check the days you want to add this exercise to (Sun, Mon, Tue, Wed, Thu, Fri, Sat) and that adds it to your diary.

This is my code.

$addDiary = array();
$addTimestamp = array();
$addDiary[] = '('.$current_user->id.', '.$_POST['exid_'.$i].', '.$_POST['qty_'.$i].', '.$calories_mod.', '.implode('.', $addTimestamp).')';             
$queryAddDiary = mysql_query("INSERT INTO LHNZ_FT_DIARY (USER_ID, EXERCISE_ID, TIME, CALORIES, TIMESTAMPS) VALUES ".implode(',', $addDiary));

I have echoed out the addDiary array and the addTimestamp array and it looks fine to me.

$addTimestamp: 1311206400.1311292800.1311379200

$addDiary: (2, 1, 1, 678, 1311206400.1311292800.1311379200)

That query works beautifully if my $addTimestamp array has a length of 1 or 2. However when the length is greater than 2, it chucks this error.

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 '.1311379200)' at line 1

Now. This error is shown if I use '.' or ':' as my implode for $addTimestamp. If I use a letter, or '_' it chucks this error.

Unknown column '1311206400_1311292800_1311379200' in 'field list'

Any ideas how I can use $addTimestamp with a length greater than 2 (meaning I may do that exercise three days a week... this is where it errors).

Thanks,

leighton

It's a bad idea to use POST data directly in your query - as Kami says, you are open to SQL injection. Please, don't use bit.ly/phpmsql mysql_* functions in new code. They are no longer maintained and are wiki.php.net/rfc/mysql_deprecation officially deprecated. Learn about php.net/pdo PDO or php.net/mysqli MySQLi instead. — Revent
– Revent, Commented Mar 30, 2013 at 18:08

Kamil Szot · Accepted Answer · 2011-07-17 09:09:04Z

1

String values in MySQL queries must be enclosed in single quotes.

Instead of:

$addDiary[] = '('.$current_user->id.', '.$_POST['exid_'.$i].', '.$_POST['qty_'.$i].', '.$calories_mod.', '.implode('.', $addTimestamp).')';

Do:

$addDiary[] = '('.$current_user->id.', '.$_POST['exid_'.$i].', '.$_POST['qty_'.$i].', '.$calories_mod.", '".implode('.', $addTimestamp)."')";

Although I strongly suggest that you also pass every value through mysql_real_escape_string() before gluing it to sql query.

answered Jul 17, 2011 at 9:09

Kamil Szot

17.9k8 gold badges66 silver badges67 bronze badges

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

Collectives™ on Stack Overflow

PHP INSERT error when imploding an array with count greater than 2

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related