Store associative array in database using serialize in php

Question

I already asked this before and now I know how to serialize it. But now the problem is I am having 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 ''address1t', 'address2t', 'cityt', 'provincet', 'postalCODEt', 'contactNOt', 'em' at line 1

This is my associative array:

Array ( [0] => Array ( [recNAME] => NAME1 [company] => [address1] => ADDRESS1 [address2] => [city] => CITY1 [province] => PROVINCE1 [postalCODE] => 0 [contactNO] => 926XXXXX [email] => [email protected] [commodity] => COMMODITY1 [type] => [weight] => 0 [length] => 0 [width] => 0 [height] => 0 [decVAL] => 0 [specINSTRUC] => [shipREF] => REF1 [payMETHOD] => CashOnDelivery [packNO] => 200XXXX-XXX [trackNO] => 200XXXX-XXX ) [1] => Array ( [recNAME] => NAME2 [company] => [address1] => ADDRESS2 [address2] => [city] => CITY2 [province] => PROVINCE2 [postalCODE] => 0 [contactNO] => 905XXXXXX [email] => [email protected] [commodity] => COMMODITY2 [type] => [weight] => 0 [length] => 0 [width] => 0 [height] => 0 [decVAL] => 361 [specINSTRUC] => I'm in the above given address from M-F, 11AM-6PM, call or text me for confirmation [shipREF] => 200XXXXX [payMETHOD] => CashOnDelivery [packNO] => 200XXXX-XXX [trackNO] => 200XXXX-XXX ) )

And the serialized array looks like this:

a:4:{i:0;a:21:{s:7:"recNAME";s:18:" NAME1";s:7:"company";s:0:"";s:8:"address1";s:57:"ADDRESS1";s:8:"address2";s:0:"";s:4:"city";s:8:"CITY1";s:8:"province";s:8:"PROVINCE1";s:10:"postalCODE";s:1:"0";s:9:"contactNO";s:10:"9XXXXXXXX";s:5:"email";s:21:"[email protected]";s:9:"commodity";s:13:"COMMODITY1";s:4:"type";s:0:"";s:6:"weight";s:1:"0";s:6:"length";s:1:"0";s:5:"width";s:1:"0";s:6:"height";s:1:"0";s:6:"decVAL";s:2:"99";s:11:"specINSTRUC";s:0:"";s:7:"shipREF";s:9:"200XXXX";s:9:"payMETHOD";s:14:"CashOnDelivery";s:6:"packNO";s:14:"200XXXX-XXX";s:7:"trackNO";s:14:"200XXXX-XXX";}i:1;a:21:{s:7:"recNAME";s:32:" NAME2";s:7:"company";s:0:"";s:8:"address1";s:52:"ADDRESS1";s:8:"address2";s:0:"";s:4:"city";s:6:"CITY2";s:8:"province";s:12:"PROVINCE2";s:10:"postalCODE";s:1:"0";s:9:"contactNO";s:10:"9XXXXXX";s:5:"email";s:23:"[email protected]";s:9:"commodity";s:7:"Spyder ";s:4:"type";s:0:"";s:6:"weight";s:1:"0";s:6:"length";s:1:"0";s:5:"width";s:1:"0";s:6:"height";s:1:"0";s:6:"decVAL";s:3:"361";s:11:"specINSTRUC";s:83:"I'm in the above given address from M-F, 11AM-6PM, call or text me for confirmation";s:7:"shipREF";s:9:"200XXXXX";s:9:"payMETHOD";s:14:"CashOnDelivery";s:6:"packNO";s:14:"200XXXX-XXX";s:7:"trackNO";s:14:"200XXXX-XXX";}}

And this is my code on serializing it:

$newstring = serialize($array);
@mysql_query("INSERT INTO `temp` (`recNAMEt`, `companyt`, 'address1t', 'address2t', 'cityt', 'provincet', 'postalCODEt', 'contactNOt', 'emailt', 'commodityt', 'typet', 'weightt', 'lengtht', 'widtht', 'heightt', 'decVALt', 'specINSTRUCt', 'shipREFt', 'payMETHODt', 'packNOt', 'trackNOt' ) VALUES('', $newstring)") or die('Error: ' . mysql_error());

This is my first time to serialize and I am still getting a handle on things. I still don't know what went wrong because this code worked for a normal array. I tried doing this:

VALUES('', '', $newstring)")

still no luck. The recNAMEt, companyt, etc are column names in my table. Please help. Thanks and regards.

You cannot do it like this. Read documentation about: php.net/manual/en/mysqli.prepare.php — sectus
– sectus, Commented Jan 10, 2014 at 4:47
I saw it here: namepros.com/webmaster-tutorials/…. I'm learning on how to use serialize in storing array to my database. — nicole101
– nicole101, Commented Jan 10, 2014 at 5:28

sanjeev · Accepted Answer · 2014-01-10 05:44:43Z

3

You are wrong @nicole

First of all serialize convert's an array into string

So there are two way's for

a) Have a single column in the database and insert the serialized array in that column \

For example:

Instead of this

  @mysql_query("INSERT INTO `temp` (`recNAMEt`, `companyt`, 'address1t', 'address2t', 'cityt', 'provincet', 'postalCODEt', 'contactNOt', 'emailt', 'commodityt', 'typet', 'weightt', 'lengtht', 'widtht', 'heightt', 'decVALt', 'specINSTRUCt', 'shipREFt', 'payMETHODt', 'packNOt', 'trackNOt' ) VALUES('', $newstring)") or die('Error: ' . mysql_error()); 


    You can have

  @mysql_query("INSERT INTO `temp` (`details` ) VALUES($newstring)") or die('Error: ' . mysql_error());

and later own when you fetch the result from database unserailzed the value & you will get back you array

b) This is bit more tricky but will solve the purpose of your's this will generate your query

             $sql = sprintf(
              'INSERT INTO `temp`(%s) VALUES ("%s")',
               implode(',',array_keys($_newstring )),
               implode('","',array_values($_newstring ))
              );
            mysql_query($sql);

answered Jan 10, 2014 at 5:44

sanjeev

4,6512 gold badges22 silver badges28 bronze badges

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

1 Comment

nicole101 Over a year ago

ow ok. so serialize is use in saving an array in one column. What Im trying to do is save the datas in the array to the corresponding table columns. I guess I'll find another way then. I'll try (b). Thanks a lot! :D

Akash · Accepted Answer · 2015-02-02 12:01:07Z

Let's say there is a form with multiple checkboxes and user can select more than one checkbox at a time, then we need to store those values in array format in a single column of the table.

Look at the below code snippet to see how this can be done in only few line of code-:)

HTML Code

<form action="path_to_action_page" method="post">
<input type="checkbox" name="value[]" value="value1">Value1
<input type="checkbox" name="value[]" value="value2">Value2
<input type="checkbox" name="value[]" value="value3">Value3
<input type="checkbox" name="value[]" value="value4">Value4
<input type="checkbox" name="value[]" value="value5">Value5

PHP Code to retrieve the form data:-

$data= $_POST['value'];
$values= serialize($data);

Code to insert the data in database:-

//code for database connection

$insert_query = "INSERT INTO TABLE_NAME(col_name) VALUES('$values')";
$result_insert = mysql_query($insert_query);

Code to retrieve the values from database:-

$query = "SELECT col_name FROM TABLE_NAME WHERE condition_to_search_the_database";


$result = mysql_query($query);
 while($values = mysql_fetch_array($result)){


$values= unserialize($values['col_name']);



  foreach($values as $value)


 {
  //code to play with $value
  e.g. - print $value;


 }}

Collectives™ on Stack Overflow

Store associative array in database using serialize in php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related