Insert Array into MySQL Database With PHP

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)";

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

Insert array value into MySQL by PHP

You are looping incorrectly currently your insert will execute 9 times. Whereas you want it to execute three times.

Example of current execution:

http://sandbox.onlinephpfunctions.com/code/ef1821fb0fe7dcc96a3f48e9ec8453
8cdfb5db62

You need to use the key to pair up both array values, you could do that with a for loop or by using the key in the foreach, http://php.net/manual/en/control-structures.foreach.php.

<?php
$reponse = array (1, 0 ,0);
$chimp = array (1, 0, 0);
foreach ($reponse as $key => $value) {
    $values= mysql_real_escape_string($value);
    $valuesch= mysql_real_escape_string($chimp[$key]);
    $query = mysql_query("INSERT INTO reponses (nom_reponse,id_question,id_categorie,correct2) VALUES ('$values','$last_id','$categorie','$valuesch')") 
        or die(mysql_error());
    }
}

I don't know where $last_id or $categorie are coming from so make sure those values are correct.

Here's how the loop will process now. http://sandbox.onlinephpfunctions.com/code/1b8af87984c348a326a64e2d453ddcdf17b65b5f

I didn't touch the SQL, looks right to me presuming your column names are correct.

How to insert array of data into mysql using php

I would avoid to do a query for each entry.

if(is_array($EMailArr)){

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ";

    $valuesArr = array();
    foreach($EMailArr as $row){

        $R_ID = (int) $row['R_ID'];
        $email = mysql_real_escape_string( $row['email'] );
        $name = mysql_real_escape_string( $row['name'] );

        $valuesArr[] = "('$R_ID', '$email', '$name')";
    }

    $sql .= implode(',', $valuesArr);

    mysql_query($sql) or exit(mysql_error()); 
}

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

How to insert an array into SQL using PHP?

If you're having an array , you need to use a foreach loop to know the length content of insertion.

This is an example of insertion:

if(is_array($array)){

    $sql = "INSERT INTO some_table (id, name, online) values ";

    $valuesArr = array();
    foreach($array as $row){

        $id= (int) $row['id'];
        $email = mysql_real_escape_string( $row['name'] );
        $name = mysql_real_escape_string( $row['online'] );

        $valuesArr[] = "('$id', '$email', '$name')";
    }

    $sql .= implode(',', $valuesArr);

    mysql_query($sql) or exit(mysql_error()); 
}

PHP not inserting array into MySQL database

There is an error in your SQL. or die(mysql_error()) does not belong there.

I suspect that you mean to write:

$sql="insert into `movies` values(NULL,'$title','$year','$format','$type','$genre','$desc','$actors','$imi','$imr','$pos','$comments','$price')";
$result=mysqli_query($con,$sql) or die(mysqli_error());

But note that your script is vulnerable to SQL injection attacks

Please read up on prepared statements. Your code will become something like this:

// create a statement with placeholders for variables
$statement = mysqli_prepare($con, "insert into `movies` values(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

// bind the variables to the placeholders
// note: I do not know what datatypes you're expecting, so have assumed strings. 
// modify the 'ssssssssssss' as required
mysqli_stmt_bind_param($statement, 'ssssssssssss', $title, $year, $format, $type, $genre, $desc, $actors, $imi, $imr, $pos, $comments, $price);

// execute the statement or show error (on production environment
// consider logging instead of displaying errors
mysqli_stmt_execute($statement) or die(mysqli_error());

// clean up the statement
mysqli_stmt_close($statement);

How to insert an array into mysql database?

At first, use proper variable interpolation:

mysql_query("INSERT into utenti(city) VALUES ('{$dati["data"]["location"]}')") or die (mysql_error());

Secondly, mysql_ extension is deprecated, use MySQLi or PDO_MySQL instead.

---

Related Topics