Save PHP Array to MySQL

Save PHP array to MySQL?

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;
}

How to Store Array in Mysql Using PHP

Use json_encode() to store data in mysql table.

<?php
$name= "prashant";
$lname= "kumbhar";
$test = "testing";

$array = array('fname'=>$name,
               'lname'=>$lname,
               'test' => $test
         );

$res = json_encode($array);
echo "Convert from array to json :" .$res;

echo "\n\nFrom json to array:\n";
print_r(json_decode($res));

output

Convert from array to json :
{"fname":"prashant","lname":"kumbhar","test":"testing"}

From json to array:
stdClass Object
(
    [fname] => prashant
    [lname] => kumbhar
    [test] => testing
)

At the time of retrieve data in array form then use json_decode()

Working Demo : Click Here

Save array in mysql database

You can store the array using serialize/unserialize. With that solution they cannot easily be used from other programming languages, so you may consider using json_encode/json_decode instead (which gives you a widely supported format). Avoid using implode/explode for this since you'll probably end up with bugs or security flaws.

Note that this makes your table non-normalized, which may be a bad idea since you cannot easily query the data. Therefore consider this carefully before going forward. May you need to query the data for statistics or otherwise? Are there other reasons to normalize the data?

Also, don't save the raw $_POST array. Someone can easily make their own web form and post data to your site, thereby sending a really large form which takes up lots of space. Save those fields you want and make sure to validate the data before saving it (so you won't get invalid values).

save array to mysql using php

If you're planning on saving all of the array as one string in the database you can just use mysqli_query, and mysqli_real_escape_string:

Saving all of the array as one string:

$arr = ["002012295915", "00971595502", "8885555512", "5555648583", "4085555270", "0562825196", "01147220964"];
$con = mysqli_connect("localhost","root","pass","db");
mysqli_query($con,"insert into TABLE values('".mysqli_real_escape_string($con,$arr)."')");

And if you want to print it later just use the select from TABLE where.

Saving each value by itself (without regex knowledge):

    //LET'S MAKE THE ARRAY MORE BEAUTIFUL FOR THE LOOP WITHOUT REGEX.

    $arr = str_replace('[','',$arr); 
    $arr = str_replace(']','',$arr);
    $arr = str_replace('"','',$arr);

//NOW THE ARRAY LOOKS LIKE THAT: 002012295915, 00971595502, 8885555512, 5555648583, 4085555270, 0562825196, 01147220964
//WE JUST NEED TO SPLIT IT NOW


$new_Array = explode(',',$arr); //DEVIDE ARRAY ELEMENT BY ','

//OK NOW WE JUST NEED TO PUT EACH ONE AS A SINGULAR VALUE IN THE DATABASE.

 $con = mysqli_connect("localhost","root","pass","db");

foreach($new_Array as $data)
{
   mysqli_query($con,"INSERT INTO TABLE VALUES('$data')");

}

Using PHP to insert array into MySQL database?

• To iterate over array you have to use foreach operator.
• To perform multiple inserts you have to use prepared statements

So despite what in all other hastily written answers said, the code should be

$stmt = $con->prepare("INSERT INTO `table` (`id`, `name`, `status`) VALUES (?,?,?)");
$stmt->bind_param("sss", $id, $name, $status);
foreach ($data as $row)
{
    $id = $row['id'];
    $name = $row['name'];
    $status = $row['status'];
    $stmt->execute();
}

PHP How to save data from array to mysql using laravel 5

First, you need to convert you input data to another format:

$cyldata = $_POST['cylinder']; //this is the post from top.

$num_elements = 0;

$sqlData = array();

while($num_elements < count($cyldata['serie'])){
    $sqlData[] = array(
        'serie'         => $cyldata['serie'][$num_elements],
        'type'          => $cyldata['type'][$num_elements],
        'admission'     => $cyldata['admission'][$num_elements],
        'seriesap'      => $cyldata['seriesap'][$num_elements],
        'invoice'       => $cyldata['invoice'][$num_elements], // you miss this field, aren't you?
        'created_at'    => Carbon\Carbon::now(), // only if your table has this column
        'updated_at'    => Carbon\Carbon::now(), // only if your table has this column
    );
    $num_elements++;
}

Second, use the Fluent query builder to do a batch insert:

DB::table('table_name')->insert($sqlData);

Note: the created_at and updated_at appear here if your table has these field. When working with Eloquent model, these field is updated automatically. However, we do not use Eloquent, so that we have to assign the value to these field manually.

Save JSON content in Mysql with PHP array [debug]

It's a php array() that you are trying to put in your database. You can encode that part back into a json string and save that string in the database.

$ship_facts           = json_encode($arr['ships'][$i]['ship_facts']);

How to save PHP array in database by converting array into JSON?

If you really need to store json in the database, you can use json_encode and json_decode.

Insert php array into mysql table

Corrected code:

$matstring=implode("','",$matrix[1]);

mysql_query("INSERT INTO Australia (Price, Company, Days, Weight) VALUES ('$matstring')");

(i.e. delete the second line from original code and put double quotes around the argument of mysql_query)

Appreciate user1847757's help - as s/he pointed out, $matstring itself was correct, but the single quotes inside of VALUES(' ') were being joined to the single quotes added to $matstring in the 2nd line of my original code, resulting in VALUES(''65.70','Coles','34 days','14'')

Thanks all for your help & suggestions

Insert array into MySQL database with PHP

You can not insert an array directly to MySQL as MySQL doesn't understand PHP data types. MySQL only understands SQL. So to insert an array into a MySQL database you have to convert it to a SQL statement. This can be done manually or by a library. The output should be an INSERT statement.

Update for PHP7

Since PHP 5.5 mysql_real_escape_string has been deprecated and as of PHP7 it has been removed. See: php.net's documentation on the new procedure.

---

Original answer:

Here is a standard MySQL insert statement.

INSERT INTO TABLE1(COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2..)

If you have a table with name fbdata with the columns which are presented in the keys of your array you can insert with this small snippet. Here is how your array is converted to this statement.

$columns = implode(", ",array_keys($insData));
$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode("', '", $escaped_values);
$sql = "INSERT INTO `fbdata`($columns) VALUES ($values)";

---

Related Topics