Hello everyone,

I'm having trouble posting a serialized array in a database. It keeps giving me a syntax error, but I can't find where the issue resides. If anyone can give me any ideas, I would appreciate it.

Site in question:

http://drawninwardmedia.com/nursecore/new_full_app.php

Error received when form is submitted:

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 ':3:{i:0;s:4:"Mesa";i:1;s:6:"Tucson";i:2;s:9:"Arli ngton";}) VALUES (NULL, 'F', NU' at line 1

The PHP at the head of the document is way too long to post here, but the code specific to serializing the array is posted below.

PHP at the head of the page:

$interestedin=serialize($_POST['interested_in']);

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "register")) {
$insertSQL = sprintf("INSERT INTO applications (id, type,
...
checklist2_cert_other2_dateattended, $interestedin) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['id'], "int"),
GetSQLValueString($_POST['type'], "text"),
...
GetSQLValueString($_POST['Date_Attended'], "text"),
GetSQLValueString($interested_in, "text"));

As I said before, the extent of the PHP I have to make this page run is too much text to post here, but if you need to see it to troubleshoot I can send it to you or post it in logical segments.

Thanks in advance for any help you can give me.

-Andrew Deacon-

try using mysql_real_escape_string with the serialized array

PHP Code:

GetSQLValueString(mysql_real_escape_string($interested_in), "text")); 


It is giving me the same syntax error. Thanks for the suggestion.

Would it be better for me to use an implode function rather than serialize the array?

Your error is that you are swaping the column definition and the values of those columns.
An insert statement is formed that way:

insert into table_name (field_name1, field_name2, field_name3) values ('string_value', integer_value, 'another_string_value');

Your error message seems to indicate that you have put the serialized value in place of the field name.
And don't forget to escape the serialized value, or an single quote in a property of the object will break your query.

But, on a side note, are you really sure you want to do it that way ?
Because, if you need to search the value of a property of the object, you will not be able to do so in a reliable way.
Now, I'm asking because there are times where saving a serialized value makes sense.

Originally Posted by tripy

Your error is that you are swaping the column definition and the values of those columns.

Oh I didn't notice you are using variable in the columns definitions

checklist2_cert_other2_dateattended, $interestedin)

probably you need to change it to

checklist2_cert_other2_dateattended, interestedin)

use base64_encode() to convert the data into simple text and base64_decode() at the time of reading. That's how binary data is sent in emails.

mysql_escape_string() may not fix the ";" in json strings.

Another popular technique is to store use an array2xml function and store the XML, which usually is not expected to contain characters that conflict with mysql.