Save PHP array to MySQL?

by

There is no good way to store an array into a single field.

You need to examine your relational data and make the appropriate changes to your schema. See example below for a reference to this approach.

If you must save the array into a single field then the serialize() and unserialize() functions will do the trick. But you cannot perform queries on the actual content.

As an alternative to the serialization function there is also json_encode() and json_decode().

Consider the following array

$a = array(
    1 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
    2 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
);

To save it in the database you need to create a table like this

$c = mysql_connect($server, $username, $password);
mysql_select_db('test');
$r = mysql_query(
    'DROP TABLE IF EXISTS test');
$r = mysql_query(
    'CREATE TABLE test (
      id INTEGER UNSIGNED NOT NULL,
      a INTEGER UNSIGNED NOT NULL,
      b INTEGER UNSIGNED NOT NULL,
      c INTEGER UNSIGNED NOT NULL,
      PRIMARY KEY (id)
    )');

To work with the records you can perform queries such as these (and yes this is an example, beware!)

function getTest() {
    $ret = array();
    $c = connect();
    $query = 'SELECT * FROM test';
    $r = mysql_query($query,$c);
    while ($o = mysql_fetch_array($r,MYSQL_ASSOC)) {
        $ret[array_shift($o)] = $o;
    }
    mysql_close($c);
    return $ret;
}
function putTest($t) {
    $c = connect();
    foreach ($t as $k => $v) {
        $query = "INSERT INTO test (id,".
                implode(',',array_keys($v)).
                ") VALUES ($k,".
                implode(',',$v).
            ")";
        $r = mysql_query($query,$c);
    }
    mysql_close($c);
}

putTest($a);
$b = getTest();

The connect() function returns a mysql connection resource

function connect() {
    $c = mysql_connect($server, $username, $password);
    mysql_select_db('test');
    return $c;
}

More Related Contents:

  1. Sign in form always showing "Wrong Credentials"
  2. Prevent creating duplicate entries in PHP
  3. i have stored array in database as a string how can retrieve that array?
  4. Insert array into MySQL database with PHP
  5. Sum values of multidimensional array by key without loop
  6. I have an array of integers, how do I use each one in a mysql query (in php)? [duplicate]
  7. Notice: Array to string conversion in
  8. mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object
  9. How get all values in a column using PHP?
  10. mysql select query within a serialized array
  11. PDO fetch one column from table into 1-dimensional array
  12. Get all mysql selected rows into an array
  13. Select from mysql table WHERE field=’$array’?
  14. MySQL query using an array [duplicate]
  15. How to insert json array into mysql database
  16. Why do I get “Resource id #4” when I apply print_r() to an array in PHP? [duplicate]
  17. PHP/SQL Insert Error when using Named Placeholders
  18. Create PHP array from MySQL column
  19. Save array in mysql database
  20. MySQL PHP – SELECT WHERE id = array()? [duplicate]
  21. Perform logic operations on boolean array values
  22. save user images in MYSQL database by which method? [closed]
  23. How to Remove Array Element and Then Re-Index Array?
  24. PHP Array Merge two Arrays on same key
  25. Do SQL connections opened with PDO in PHP have to be closed
  26. mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in php [duplicate]
  27. Algorithm for generating a random number
  28. Chunk and transpose a flat array into rows with a specific number of columns
  29. return one value from database with mysql php pdo
  30. How can I pass an array of PDO parameters yet still specify their types?

Leave a Comment