PHP MYSQL import excell serialized data

Question

Hi,

I have a column with serialized data in mysql table.

How to import excell file where I have column like colors where are stored serilized data for checboxes in mysql

and need to import from excel file column

colors colors colors white yellow blue -> serialized into 1 column in mysql

structure of excell file can be different.

thanks

mysql table
id | name | colors
1 | house | serialized(yelow,blue...)

excell file
name | color | color
house| yellow | blue

I'm not sure if is the right way to handle excell file maybe something like this:

name | color
house| (yellow;blue)

Answer 1

Try to use Spreadsheet_Excel_Writer: link text

Answer 2

Convert the spreadsheet to CSV. You can read the file contents with fgetcsv(), and save it to the db serialized.

Some code:

$dbh = new PDO($connectionstring, $username, $password);
$handle = fopen('foo.csv', 'r');
fgetcsv($handle); // omit the first line
while($row = fgetcsv($handle)) {
  $name = array_shift($row);
  $stmt = $dbh
    ->prepare('INSERT INTO table(name, data) VALUES(:name, :data);');
  $stmt->bindParam(':name', $name, PDO::PARAM_STR);
  $stmt->bindParam(':data', serialize($row), PDO::PARAM_STR);
  $stmt->execute();
}