Putting array containing multiple arrays into mysql table

Question

What I have:

An array with 30 keys, some keys contain only one value, while others contain several (one key contains 28 values). So firstly I don't even know if this will work. But I have tried using other examples I've found on here only to come up null.

say like such

'name' => somemovie
cast => person1, person2, person3, person4, person5, person6
poster => poster1, poster2, poster3
year => someyear

and so on and so forth

My question is: Is it possible to put these in a mysql table and if so how? I'm using PHP 5.3.

This is the code I have, and the bottom half I used some years ago, for a simple array and it worked fine. Not so much anymore.

$imdb = new Imdb();
$movieArray = $imdb->getMovieInfo("$cat");
     extract ($movieArray);
$mysqli = new mysqli("localhost", "custom_xxxx", "xxxxxxxxxx", "custom_xxxx");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo $mysqli->host_info . "\n";
$result = mysqli_insert_array("imdb", $movieArray);

if( $result['mysqli_error'] ) {
    echo "Query Failed: " . $result['mysqli_error'];
} else {
    echo "Query Succeeded! <br />";
    echo "<pre>";
    print_r($result);
    echo "</pre>";
}

?>

Answer 1

You can use serialize function but I would recommend against it. Create three tables with following columns which would depend on your data model but it would be something like this:

• movies (movie_id,year, poster_id, actor_id) - store movies here
• actors (actor_id, name, dob) - stores actors info here.
• posters (movie_id) - store poster info here.

Now, for data entry into the database. You need to something like:

$imdb = new Imdb();
$movieArray = $imdb->getMovieInfo("$cat");
     extract ($movieArray);
$mysqli = new mysqli("localhost", "custom_xxxx", "xxxxxxxxxx", "custom_xxxx");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo $mysqli->host_info . "\n";
//the following code assumes $movieArray  like this:
// $movieArray = Array(
//'name' => somemovie,
//'cast' => array(person1, person2, person3, person4, person5, person6)
//'poster' => array(poster1, poster2, poster3)
//'year' => someyear)
foreach($movieArray as $row){
          foreach($row['cast'] as $name){
                    mysqli_query("INSERT INTO actor values($name['name'], $name['dob'])");
          }
          foreach($row['poster'] as $name){
           //insert into poster table 
          }

          //add foreach loop for each table.
}
if( $result['mysqli_error'] ) {
    echo "Query Failed: " . $result['mysqli_error'];
} else {
    echo "Query Succeeded! <br />";
    echo "<pre>";
    print_r($result);
    echo "</pre>";
}

this is not the actually code as you haven't shown the array structure but i think you will get the idea. basically, for multidimensional array, you need to use foreach loop & inset the data into database tables using insert statement.

For retrieval you will need to use SELECT statement. lastly, if you don't have exposure to SQL & databases, check SQL Introduction

Answer 2

if you just want to store the array as object and dont want to run sql queries on it than serialize the object and save it as binary mysql.

serialization: http://php.net/manual/de/arrayobject.serialize.php

mysql-binary: http://dev.mysql.com/doc/refman/5.1/de/binary-varbinary.html