php mysqli 插入变量查询

php mysqli insert variables query

提问by Chris

Ok so here is the question. I am trying to insert a variable into my query that is pre-defined. However it is not working. The query works if I just give it a value, but when I insert a variable into it, it fails. help?

好的，这里是问题。我正在尝试将一个变量插入到我的预定义查询中。但是它不起作用。如果我只是给它一个值，查询就可以工作，但是当我向其中插入一个变量时，它就会失败。帮助？

$connection = new mysqli('localhost', 'user', 'pass', 'db'); 

$username = "test";

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if ($result = $connection->query("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('".$username."', 'test', 'test', 'test', 'test', 'test')")){

  echo "success";
  $result->close();

}
else {
  echo "error";
}

$connection->close();
?>

If I replace $username with any value, it works.. Am I missing something here?

如果我用任何值替换 $username ，它就可以工作..我在这里遗漏了什么吗？

回答by Joseph

Hello this is for anyone who might still need accomplish what was asked in original question.

您好，这适用于可能仍需要完成原始问题中提出的问题的任何人。

A reason why someone possibly might want to not use prepared statements--from: http://www.php.net/manual/en/mysqli.quickstart.statements.php

有人可能不想使用准备好的语句的原因——来自：http: //www.php.net/manual/en/mysqli.quickstart.statements.php

"Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement."

“使用准备好的语句并不总是执行语句的最有效方式。只执行一次的准备好的语句比非准备好的语句导致更多的客户端-服务器往返。”

//you will want to clean variables properly before inserting into db
$username = "MyName";
$password = "hashedPasswordc5Ujs";

$q = "INSERT INTO `users`(`username`, `password`) VALUES ('".$username."', '".$password."')";

if (!$dbc->query($q)) {
    echo "INSERT failed: (" . $dbc->errno . ") " . $dbc->error;
}    
echo "Newest user id = ",$dbc->insert_id;

Cheers!

干杯!

回答by prodigitalson

Since ther was some discussion above i thought id provide the following examples in pdo and mysqli for comparison:

由于上面有一些讨论，我认为 id 在 pdo 和 mysqli 中提供了以下示例以进行比较：

MySQLi:

MySQLi:

$connection = new mysqli('localhost', 'user', 'pass', 'db'); 

$username = "test";

if ($connection->errno) {
    printf("Connect failed: %s\n", $connection->error);
    exit();
}

$username = 'test';

$stmt = $connection->prepare("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES (?,'test', 'test', 'test', 'test', 'test')");

$stmt->bind_param('s', $username_value);
$username_value = $username; // not we could simply define $username_value = 'test' here

if ($result = $stmt->execute()){

  echo "success";
  $stmt->free_result();

}
else {
  echo "error";
}

$connection->close();

PDO:

公共事业单位：

try {

$db = new PDO($dsn, $user, $pass);
$username = 'test';

$stmt = $db->prepare("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES (?,'test', 'test', 'test', 'test', 'test')");

$stmt->execute(array($username));

echo 'Success';
}
catch(PDOException $e)
{
  echo $e->getMessage();
}

回答by Satyapriya Mishra

In this case, looking at the context of your question it is better to assign the username variable with some data like $username=$_POST['username'];

在这种情况下，查看您的问题的上下文，最好为用户名变量分配一些数据，例如 $username=$_POST['username'];

This might help...otherwise avoid the double quotes and simply put down $username

这可能会有所帮助...否则避免双引号并简单地放下 $username

回答by Gash

Its been a long time and probably you've already found out the answer but just in case, it turns out that its actually a simple problem where you put Double quotes and dots in the mysqli query statement at VALUES('".$username"'), but if you just leave it in single quotes and just write the variable name inside the quotes like, VALUES('$username'), it will work. I think it applies for new versions of php though not sure i.e. Change

已经很长时间了，可能您已经找到了答案，但以防万一，事实证明它实际上是一个简单的问题，您在 VALUES('".$username" 处的 mysqli 查询语句中放置了双引号和点')，但如果你只是把它放在单引号中，然后把变量名写在引号内，比如 VALUES('$username')，它就会起作用。我认为它适用于新版本的 php 虽然不确定 ie Change

"INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('".$username."', 'test', 'test', 'test', 'test', 'test')"

to

到

"INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('$username', 'test', 'test', 'test', 'test', 'test')"

Notice in the VALUE field my variable is not enclosed in double quotes or concatenated in periods i.e. VALUES ('$username'), since it will save the periods as a value.

请注意，在 VALUE 字段中，我的变量没有用双引号括起来，也没有用句点连接，即 VALUES ('$username')，因为它会将句点保存为值。

this works for me but I've noticed a problem in running the query with the same values again, it brings an error but it can be avoided by adding a column in your database table for an auto increment id to make sure that a value is being changed every time you run the query

这对我有用，但我注意到再次使用相同的值运行查询时出现问题，它会带来错误，但可以通过在数据库表中添加一列用于自动增量 id 以确保值是每次运行查询时都会更改

Hope this helps

希望这可以帮助

回答by zakir57

The best answer to it is we must assign the variable we want into another variable. For example:

最好的答案是我们必须将我们想要的变量分配给另一个变量。例如：

$username = $_POST['username'];

$a = $username;

mysqli_query("INSERT INTO tablename (username,test, test, test) VALUES ('$a', 'test', 'test');