使用PHP将数组数据值插入MySQL数据库表

问题描述：

我正在处理基于庞大数据的项目。我为PHP中的每个数据创建了数组。现在我想将它插入到MySQL数据库中。使用PHP将数组数据值插入MySQL数据库表

<?php 
$servername = "localhost"; 
$username = "xxxx"; 
$password = "xxxxx"; 
$db_name = "xxxxxxx"; 

// Create connection 
$link = mysql_connect($servername, $username, $password); 

if(!$link){ 
echo "Failed" .mysql_connect_error(); 
} 

mysql_select_db($db_name); 

$data = array (
     [1] => array(
      'eng_title' => "john", 
      'content' => "john", 
      'category' => "john", 

     ), 
     [2] => array(
      'eng_title' => "john", 
      'content' => "john", 
      'category' => "john", 
     ), 
     [3] => array(
      'eng_title' => "john", 
      'content' => "john", 
      'category' => "john", 
     ), 
    ); 

if(is_array($data)){ 

    $sql = "INSERT INTO `table_a`(`eng_title`, `content`, `category`) values"; 

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

     $eng_title = mysql_real_escape_string($row['eng_title']); 
     $content = mysql_real_escape_string($row['content']); 
     $category = mysql_real_escape_string($row['category']); 


     $valuesArr[] = "('$eng_title', '$content', '$category')"; 
    } 

    $sql .= implode(',', $valuesArr); 
    echo $sql; 
    mysql_query($sql) or exit(mysql_error()); 

} 

?>

这是我执行代码时出现的消息。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

我在其他程序中使用MySQLi，我想我正在使用最新版本的MySQL，这就是为什么上述程序无法正常工作。

从$ sql中删除'（当你执行echo时，你能给我们看什么样的$ sql） –

在你的foreach中插入你的sql查询。 – aldrin27

答

正如我认为你在“内爆”中错过了一些东西。试试这个 -

if(is_array($data)){ 

    $sql = "INSERT INTO `table_a`(`eng_title`, `content`, `category`) values"; 

    $valuesArr = array(); 
    $last=count($valuesArr); 
    $i=0; 
    foreach($data as $row){ 

     $eng_title = mysql_real_escape_string($row['eng_title']); 
     $content = mysql_real_escape_string($row['content']); 
     $category = mysql_real_escape_string($row['category']); 


     $sql .= "('$eng_title', '$content', '$category')"; 
     if($i<$last) 
      $sql .=" , "; 
    $i++; 
    } 


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

}

他需要“，”不能插入多行的空格 –

谢谢@ELOUFIRHatim，现在纠正.. –

请提醒一下，mysql_ *函数已被弃用多年。考虑使用mysqli_ *代替。 –

答

使用PDO最好，看看这里：Choosnig an API

要与PDO执行INSERT语句，您可以到这里看看更多的例子：PDO - Manual

所以对于你的情况，你可以用两种方法做到：

1.在每个回路上插入一行：

<?php 

$data = array (
    1 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 

    ), 
    2 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 
    ), 
    3 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 
    ), 
); 

$servername = "localhost"; 
$username = "xxxx"; 
$password = "xxxxx"; 
$db_name = "xxxxxxx"; 
$pdo = new PDO("mysql:host=$servername;dbname=$db_name", "$username", "$password"); 
foreach ($data as $key => $value) { 
    $eng_title = mysql_real_escape_string($value['eng_title']); 
    $content = mysql_real_escape_string($value['content']); 
    $category = mysql_real_escape_string($value['category']); 

    $query = $db->prepare('INSERT INTO table_a(eng_title, content, category) VALUES(:title, :content, :category)'); 
    $query->bindParam("title", $eng_title); 
    $query->bindParam("content", $content); 
    $query->bindParam("category", $category); 
    $query->execute(); 
}

2.创建一个SQL语句并执行它：

<?php 

$data = array (
    1 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 

    ), 
    2 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 
    ), 
    3 => array(
     'eng_title' => "john", 
     'content' => "john", 
     'category' => "john", 
    ), 
); 

$servername = "localhost"; 
$username = "xxxx"; 
$password = "xxxxx"; 
$db_name = "xxxxxxx"; 
$pdo = new PDO("mysql:host=$servername;dbname=$db_name", "$username", "$password"); 

$query = "INSERT INTO table_a(eng_title, content, category) VALUES"; 

$counter = 0; 
$arraySize = sizeof($data); 
foreach ($data as $key => $value) { 
    $eng_title = mysql_real_escape_string($value['eng_title']); 
    $content = mysql_real_escape_string($value['content']); 
    $category = mysql_real_escape_string($value['category']); 

    $counter++; 
    $query .= "($eng_title, $content, $category)"; 
    if($counter < $arraySize) 
     $query .= ","; 
} 
$pdo->query($query);

希望它会帮助你。

使用PHP将数组数据值插入MySQL数据库表

相关推荐