php - 使用 mysql 和 php 将数据插入数据库

Question

肖恩，PHP代码:

<?php 
$name = $_POST["name"];
    echo $name;

if (is_array($_POST["categories"]))
{
 foreach ($_POST["categories"] as $col)
    echo "<BR>\n".$col;
}
else
 echo "<BR>no color was chosen.";

$pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$pdo->exec('SET NAMES "utf8"');
$tbl_cols = array("Lifestyle","Beauty","Business"); // column names in roller table.
if (is_array($_POST["categories"])){ // check if array
 foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
          if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
              $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
              $pdo->execute(array($_POST['name']));
          }
 }
}
exit(); 
?>

我遇到问题: fatal error :调用/Users/ronr 中未定义的方法 PDO::execute()...

Answer 1

您没有执行查询 -

$sql="INSERT INTO roller
      ('$col') VALUES ('$_POST[name]') ";

此外，由于您使用的是 PDO，因此您应该使用准备好的语句来防止 SQL 注入(inject)。由于列不能在准备好的语句中使用，因此您需要将其列入白名单。请参阅Reference - frequently asked questions about PDO

$query = $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
$query->execute(array($_POST['name']));

编辑

如果您想将 $_POST["name"] 插入到每个表列 ($_POST["categories"])，您可以执行以下操作 -

<?php 
 $pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $pdo->exec('SET NAMES "utf8"');
 $tbl_cols = array("col1","col2","col3", ...); // column names in roller table.
 if (is_array($_POST["categories"])){ // check if array
     foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
              if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
                  $query = $pdo->prepare("INSERT INTO roller (`$col`) VALUES (?) ");
                  $query->execute(array($_POST['name']));
              }
     }
 }
 exit(); 
?>

或者，如果您想在一个查询中而不是在循环中执行此操作，请尝试类似 -

<?php 
 $pdo= new PDO('mysql:host=localhost;dbname=ronre', 'roon', 'abc12345');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $pdo->exec('SET NAMES "utf8"');
 $tbl_cols = array("col1","col2","col3", ...); // column names in roller table.
 if (is_array($_POST["categories"])){ // check if array
     foreach ($_POST["categories"] as $col){  // loop through each $_POST["categories"]
              if(in_array($col,$tbl_cols)){ // make sure it is safe by whitelisting it
                          $cols[]=$col; // create an array of safe column names
              }
     }
 }
 $name = array_fill(0, count($cols), $_POST['name']); // create an array of $_POST['name'] with same amount as $cols
 $num_of_vals  = str_repeat('?,', count($cols) - 1) . '?'; // create n number of ? same as $cols / $name   
 $cols = implode("`, `", $cols); // implode the $cols to get a csv of $cols
 $query = $pdo->prepare("INSERT INTO roller (`$cols`) VALUES ($num_of_vals) ");
 $query->execute(array($name));
 exit(); 
?>