将变量插入 MySQL 数据库 - Inserting Variable into MySQL database

Inserting Variable into MySQL database

本文关键字：数据库 MySQL 插入变量 | 更新日期: 2023-09-27

我正在尝试将变量插入mysql数据库。我已经阅读了有关如何执行此操作的各种帖子，但是这些似乎对我不起作用，以下代码不会向数据库写入任何内容（变量似乎会破坏查询），但是如果我不使用变量，谁能告诉我我在这里做错了什么？

$dbhost = 'zzzzzzzzz';
$dbuser = 'xxxxxxxxx';
$dbpass = 'yyyyyyyyy';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

// VARIABLE SETUP;
$variable = 'variable';
$variable = mysql_real_escape_string($variable);
if(! $conn ) {
   die('Could not connect: ' . mysql_error());
}
$sql = 'INSERT INTO db_variables_insert'.
       '(time, variable) '. 
       'VALUES ( "2016-02-19 04:23:44", '$variable')';
mysql_select_db('wwwwww');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
   die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully'n";
mysql_close($conn);

首先，对于您的问题，您可以尝试：

$sql = "INSERT INTO db_variables_insert".
       "(time, variable) ". 
       "VALUES ( '2016-02-19 04:23:44', '".$variable."')";

但是，您可以将其重写为更理智的预处理语句，例如：

/* mysqli_* to connect - ** See note on PDO below ** */
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Prepared statement, stage 1: prepare */
$sql = "INSERT INTO db_variables_insert (time, variable) VALUES (?,?)";
if ($stmt = $mysqli->prepare($sql)) {
    /* Prepared statement, stage 2: bind parameters and execute */
    $time = '2016-02-19 04:23:44';
    // Assuming you already define $variable here
    if ($stmt->bind_param("ss", $time, $variable)) {
        /* Here this bit   ^ can be "i" (integer), "s" (string) etc  */
        $execute = $stmt->execute();
        if($execute === FALSE) {
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }
        /* ^ you are done executing the sql if no errors */
    } else {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }
} else {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* explicit close recommended */
$stmt->close();

笔记：

/** Recommended: Connect with PDO **/
$conn = new PDO('mysql:host=localhost;dbname=my_db;charset=UTF-8','user', 'pwd');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

^ 有用的访问和练习：

面向 MySQL 开发人员的 PDO 教程
准备好的声明

你需要使用 . 在 PHP 中连接字符串，你不能只把变量放在字符串旁边。如果variable列是一个字符串，则需要将其值放在引号中。

$sql = 'INSERT INTO db_variables_insert'.
       '(time, variable) '. 
       'VALUES ( "2016-02-19 04:23:44", "' . $variable'")';

您也可以将其编写为带有变量替换的单个字符串，在 PHP 字符串周围使用双引号。

$sql = "INSERT INTO db_variables_insert
        (time, variable)  
        VALUES ( '2016-02-19 04:23:44', '$variable')";