mysql 准备语句参数和排序查询 [英] mysql prepared statement parameters and ordering queries

问题描述

我有一个表单，其目的是将当前显示的记录放入一个类别中.我正在通过 php 使用以下 html 代码:

I have a form, the purpose of which is to place the currently displayed record into a category. I am using the following html code via php to do so:

<form name="categoryForm">
  <input name="radiobutton" type="radio" value="fakeapproved" />Fake (Approved)<p>
  <input name="radiobutton" type="radio" value="fakesuspected" />Fake (Suspected)<p>
  <input name="radiobutton" type="radio" value="keyword" />Forbidden Keywords<p>
  <input name="radiobutton" type="radio" value="parallelimport" />Parallel Imports
  <input name="Submit" type="submit" value="Update" onclick="handleClick(".$pk.");return false"/>
</form>

目前，我只有一个 AUCTIONS 表，带有一个类别列，并且该列设置为我的表单中定义的类别之一.

At the moment, I simply have an AUCTIONS table, with a category column, and this column is set to one of the categories defined in my form.

这种方法对于我需要处理的数据无效，因此我计划将其更改为每个类别都有一个单独的列，可以设置为 true 或 false.

This approach is not effective for what I need to do with the data, so I am planning to change it to have a separate column for each category, which can be set to either true or false.

我想知道的是，是否可以在我的 sql 查询中使用在我的表单中定义并通过我的 javascript 函数获取的文本.

What I would like to know, is if it is possible to use the text defined in my form and obtained via my javascript function, in my sql query.

例如，更新拍卖设置 $textfromfrom = true

For example, update auctions set $textfromfrom = true

目前，我正在使用以下准备好的语句:

At the moment, I am using the following prepared statement:

if($cmd=="addcat"){
  $alterQuery = "UPDATE auctions SET category = ? WHERE article_no= ?";
  if ($altRecord = $con->prepare($alterQuery)) {
    $altRecord->bind_param("ss", $subcat, $pk);
    $altRecord->execute();
    $altRecord->close();
    echo "true";
  } else {
    echo "false";
  }
}

有没有办法替换

$alterQuery = "UPDATE auctions SET category = ? WHERE article_no= ?";

与$alterQuery = "更新拍卖 SET ? = true WHERE article_no= ?";

with $alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";

是否也可以在之后立即执行单独的查询，即:

Would it also be possible to execute a separate query straight after, i.e.:

if($cmd=="addcat"){
  $alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";
  $insertQuery = "INSERT into users (username, ?) values ?, true";
  if ($altRecord = $con->prepare($alterQuery)) {
    $altRecord->bind_param("ss", $category, $pk);
    $altRecord->execute();
  if ($insRecord = $con->prepare($insertQuery)) {
    $insRecord->bind_param("ss", $category, $username);
    $insRecord->execute();
    $insRecord->close();
  }
  $altRecord->close();
  echo "true";
} else {
  echo "false";
}

我使用上述方法的理由如下:

My reasoning for using the above approach is as follows:

拍卖数据库是从其他来源导入的，我根本无法更改结构，只能在末尾添加类别.不得更改主键等.

The auctions database is imported from another source, and I cannot change the structure at all, except to add categories on to the end. Primary keys and such must not be changed.

只有4个类别

单个拍卖可能属于多个类别

An individual auction may belong to more than one category

拍卖表只处理拍卖.我需要一个用户表，其中主要包含新用户输入.

The auctions table only deals with auctions. I will need a users table, which will consist of primarily new user input.

用户表必须能够为每个用户显示他们参与拍卖的类别.

The users table must be able to show for each users, the categories they have had auctions in.

每个用户的 users 表中的记录不得超过一条.用户名将用作主键.

There must not be more than one record in the users table per user. The username will function as the primary key.

推荐答案

$alterQuery = "更新拍卖 SET ? = true WHERE article_no= ?";

$alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";

不，您不能对列名使用查询参数.查询参数可以仅用于代替 SQL 表达式中的文字值.

No, you can't use query parameters for column names. Query parameters can be used only in the place of a literal value in an SQL expression.

您必须使用动态 SQL.即，将 SQL 语句形成为字符串，并将您的应用程序变量作为列名插入到该字符串中.

You'll have to use dynamic SQL. That is, form an SQL statement as a string, and interpolate your application variables into this string as column names.

这篇关于mysql 准备语句参数和排序查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！