在mysql查询中使用PHP表单 [英] Using PHP forms in mysql queries

问题描述

另一个（基本）问题在这里。我需要知道如何使用html表单来过滤使用PHP的mysql查询的结果。我一直在看w3schools，我可以看到如何在一个页面上使用$ _POST可以输出到另一个页面。但我看不到如何将$ _POST放入我的查询中。例如我有一页如下：

 < form action =orderlist.phpmethod =post> 
 OrderNo：< input type =intname =order_no/> 
< input type =Submit/> 
< / form> 
  

这是一个用户可以输入order_no并点击提交的页面，链接到'orderslist .php'页面。在该页面上，我有以下代码：

  $ result = mysql_query（SELECT * FROM orders）; 
 
 echo< table border ='5'> 
< tr> 
 order_no< th> 
 ord_date< 
 est_completion_date th 
 th status 
 invoice_date th 
 th th th $ gt; ; inv_amount< th> 
 th name> 
< th> fName< / th> 
< th> lName< / th> 
< /< ; / TR>中; 
 
 //使用'while'在轮流中检查$ result中的每一行：
 while（$ row = mysql_fetch_array（$ result））
 {
回声< tr>; 
回显< td> 。 $ row ['order_no']。 < / TD> 中; 
回显< td> 。 $ row ['ord_date']。 < / TD> 中; 
回显< td> 。 $ row ['est_completion_date']。 < / TD> 中; 
回显< td> 。 $ row ['status']。 < / TD> 中; 
回显< td> 。 $ row ['invoice_date']。 < / TD> 中; 
回显< td> 。 $ row ['inv_amount']。 < / TD> 中; 
回显< td> 。 $ row ['name']。 < / TD> 中; 
回显< td> 。 $ row ['fName']。 < / TD> 中; 
回显< td> 。 $ row ['lName']。 < / TD> 中; 
回声< / tr>; 
} 
 echo< / table>; 
  

这会输出从'orders'到表格的所有内容，但我希望它只输出行用户在前一页输入的order_no。我该怎么做呢？是这样的：

  $ result = mysql_query（SELECT * FROM orders WHERE order_no = $ _POST [order_no]）; 
  

非常感谢

解决方案

对于初学者，您应该切换到PDO或mysqli - mysql _ * 函数正在被弃用。

在你的问题上：是的，你就是这么做的，使用 WHERE 子句。然而，将一个变量直接连接到 $ _ POST 之外直接连接变得非常不安全。

这就是说，至少你应该这样做：

  mysql_query（'SELECT * FROM orders WHERE order_no ='.mysql_real_escape_string（ $ _POST [order_no]））; 
  

另一件事...不要使用 SELECT * 。您应该始终列出您期望从数据库中获得的列 - 这样，如果出现问题（如列已更改），查询可以让您知道。有了 SELECT * ，你会得到一个神奇的数据抓包 - 你不知道它是什么，更重要的是，如果它有你的代码依赖的值。

所以，把它放在一起：

  $ pdo = new PDO （mysql：host = localhost; dbname = database，'-username-'，'-password-'）; 
 $ sth = $ pdo-> prepare（'
 SELECT 
`order_no`，
`ord_date`，
`est_completion_date`，
`status `，
`invoice_date`，
`inv_amount`，
`name`，
`fName`，
`lName` 
 FROM 
订单
其中
 order_no =：order_no 
'）; 
 $ sth-> execute（array（'：order_no'=> $ _ POST [order_no]））; 
 while（$ order = $ sth-> fetch（））{
 echo< tr>; 
回显< td> 。 $ order-> order_no。 < / TD> 中; 
回显< td> 。 $ order-> ord_date。 < / TD> 中; 
回显< td> 。 $ order-> est_completion_date。 < / TD> 中; 
回显< td> 。 $ order->状态。 < / TD> 中; 
回显< td> 。 $ order-> invoice_date。 < / TD> 中; 
回显< td> 。 $ order-> inv_amount。 < / TD> 中; 
回显< td> 。 $ order->名称。 < / TD> 中; 
回显< td> 。 $ order-> fName。 < / TD> 中; 
回显< td> 。 $ order-> lName。 < / TD> 中; 
回声< / tr>; 
} 
  

编辑：最后一个注释， '重新使用是无效的; type =int不是公认的输入类型。 下面是HTML 4的类型列表 ，和 HTML 5列表：

正如您所看到的，HTML 5确实有一个新的输入类型number。它没有被完全采用，但是如果你想使用它：

< input type =numbername =order_no/> ;

但是，请注意，并非所有浏览器都会将输入限制为数字： http://caniuse.com/#feat=input-number

文档

• mySql SELECT - http://dev.mysql.com/doc/refman/5.0/en/select.html a>


• mysql_query （ DEPRECATED ） - http://php.net/manual/en/function.mysql-query.php


• PDO - http://www.php.net/manual/en/ book.pdo.php


• PDO :: prepare - http://www.php.net/manual/en/pdo.prepare.php


• PDOStatement :: fetch - http://www.php.net/manual /en/pdostatement.fetch.php


• PDOStatement :: execute - http://www.php.net/manual/en/pdostatement.execute.php

Another (basic) question here. I need to know how to use html forms to filter the results of a mysql query using PHP. I have been looking at w3schools and I can see how using $_POST on one page can output to another page. But I cannot see exactly how to put the $_POST into my query. For example I have one page as below:

<form action="orderlist.php" method="post">
OrderNo: <input type="int" name="order_no" />
<input type="Submit" />
</form>

This is a page where the user can enter an order_no and click submit, which links to the 'orderslist.php' page. On that page I have the following code:

$result = mysql_query("SELECT * FROM orders");

echo "<table border='5'>
<tr>
<th>order_no</th>
<th>ord_date</th>
<th>est_completion_date</th>
<th>status</th>
<th>invoice_date</th>
<th>inv_amount</th>
<th>name</th>
<th>fName</th>
<th>lName</th>
</tr>";

// -- Use 'while' to check each row in $result in turn:
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['order_no'] . "</td>";
  echo "<td>" . $row['ord_date'] . "</td>";
  echo "<td>" . $row['est_completion_date'] . "</td>";
  echo "<td>" . $row['status'] . "</td>";
  echo "<td>" . $row['invoice_date'] . "</td>";
  echo "<td>" . $row['inv_amount'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['fName'] . "</td>";
  echo "<td>" . $row['lName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

This outputs everything from 'orders' into a table, but I want it to only output the row with the order_no that the user entered on the previous page. How do I do this? Is it something like:

$result = mysql_query("SELECT * FROM orders WHERE order_no = $_POST[order_no]"); 

Thank a lot

解决方案

For starters, you should switch to PDO or mysqli -- the mysql_* functions are in the process of deprecation.

On to your question: yes, that's how you do it, with a WHERE clause. However, it is very (very very very) insecure to concatenate a variable directly out of $_POST without sanitation.

That said, at a minimum you should do this:

mysql_query('SELECT * FROM orders WHERE order_no = '.mysql_real_escape_string($_POST[order_no])); 

Another thing... don't use SELECT *. You should always list the columns you expect to get from the database -- that way, if there is a problem (like the columns have changed), the query can let you know. With SELECT * you get back a magical grab-bag of data -- you have no idea what it is, and more importantly, if it has the values your code relies on.

So, putting it together:

$pdo = new PDO("mysql:host=localhost;dbname=database", '-username-', '-password-');
$sth = $pdo->prepare('
    SELECT 
        `order_no`,
        `ord_date`,
        `est_completion_date`,
        `status`,
        `invoice_date`,
        `inv_amount`,
        `name`,
        `fName`,
        `lName`
    FROM 
        orders 
    WHERE 
        order_no = :order_no
');
$sth->execute(array(':order_no'=>$_POST[order_no]));
while ($order= $sth->fetch()) {
  echo "<tr>";
  echo "<td>" . $order->order_no . "</td>";
  echo "<td>" . $order->ord_date . "</td>";
  echo "<td>" . $order->est_completion_date . "</td>";
  echo "<td>" . $order->status . "</td>";
  echo "<td>" . $order->invoice_date . "</td>";
  echo "<td>" . $order->inv_amount . "</td>";
  echo "<td>" . $order->name . "</td>";
  echo "<td>" . $order->fName . "</td>";
  echo "<td>" . $order->lName . "</td>";
  echo "</tr>";
}

Edit: One last note, the input you're using is not valid; type="int" is not a recognized input type. Here's a list of types for HTML 4, and a list for HTML 5:

As you can see, HTML 5 does have a new input type "number". It is not fully adopted, but if you wanted to use it:

<input type="number" name="order_no" />

Be aware, however, that not all browsers will restrict the input to numeric: http://caniuse.com/#feat=input-number

Documentation

• mySql SELECT - http://dev.mysql.com/doc/refman/5.0/en/select.html
• mysql_query (DEPRECATED) - http://php.net/manual/en/function.mysql-query.php
• PDO - http://www.php.net/manual/en/book.pdo.php
• PDO::prepare - http://www.php.net/manual/en/pdo.prepare.php
• PDOStatement::fetch - http://www.php.net/manual/en/pdostatement.fetch.php
• PDOStatement::execute - http://www.php.net/manual/en/pdostatement.execute.php

这篇关于在mysql查询中使用PHP表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！