php - 如何从 URL 获取变量并插入数据库

Question

因此，我尝试从 URL ( http://example.com/pb.php?id=123&affiliate=abd123&lp1=dun.com&lp2=dun2.com&lp3=dun3.com ) 获取变量，并且我尝试了此代码，但收到此错误

Prepare failed: (1136) Column count doesn't match value count at row 1 Fatal error: Call to a member function bind_param() on boolean in /home/recondes/public_html/postback.php on line 25

还有

<?php

define("MYSQL_HOST", "localhost");
define("MYSQL_PORT", "3306");
define("MYSQL_DB", "db");
define("MYSQL_TABLE", "tbl");
define("MYSQL_USER", "user");
define("MYSQL_PASS", "pass");
$mysqli = new mysqli(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
if ($mysqli->connect_errno) 
{
  echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$id = $_GET['id'];
$affiliate =            $_GET['affiliate'];
$lp1 =           $_GET['lp1'];
$lp2 =           $_GET['lp2'];
$lp3 =           $_GET['lp3'];


if (!($stmt = $mysqli->prepare("INSERT INTO ".MYSQL_TABLE." VALUES (id, affiliate, lp1, lp2, lp3);"))) 
{
  echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$stmt->bind_param('dds', $id, $affiliate, $lp1, $lp2, $lp3 );
if (!$stmt->execute()) 
{
  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
else
{
  printf("%d Row updated, added ".$id." to ".$affiliate." .\n", mysqli_stmt_affected_rows($stmt));
}
?>

Answer 1

您的查询未列出要插入的列，因此它希望您为所有表列提供值。您尚未显示表架构，但它不仅仅只有 5 列。

您还缺少将由 bind_param() 填充的占位符。我怀疑您在 VALUES() 中列出的值旨在作为表列。所以尝试一下:

if (!($stmt = $mysqli->prepare("INSERT INTO ".MYSQL_TABLE." (id, affiliate, lp1, lp2, lp3) VALUES (?, ?, ?, ?, ?)"))) 

此外，在调用 bind_param 时，指定数据类型的字符串需要具有与参数一样多的字母。所以应该是:

$stmt->bind_param('dssss', $id, $affiliate, $lp1, $lp2, $lp3 );

最后，当您在一个步骤中遇到错误并打印错误消息时，您应该停止该脚本而不是继续下一步。如果 prepare() 失败，那么使用准备好的语句就没有意义。