Using PHP forms in mysql queries

Question

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

Answer 1

Your form should be something like this:

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

To get any value that is typed by user in the form you should use type="text". There is nothing like type="int" in standard HTML.

Don't get confused, the Input TYPE in HTML is not the same as one you use in Programming languages to declare Data type. Here TYPE is just to let the browser know that its a text field / Radio Button etc. To understand Valid Input Types better read this w3.org recommendation on HTML forms.

On orderlist.php you can query to retrieve the required data:

if(isset($_POST['order_no'])) {
    $orderNo = mysql_real_escape_string($_POST['order_no']);
    $result = mysql_query("SELECT * FROM orders WHERE order_no = $orderNo");
    while($row = mysql_fetch_array($result)) {
        //code to print table.   
    }
}

Note:

This type of code is Vulnerable to easy attacks, and it is never recommended to put user input directly into SQL query, it should always be filtered first.

Answer 2

Try this:

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

This takes the value of $_POST['order_no'] and somewhat sanitizes it. You then apply the value of $orderNumber to MySQL.

However, you're much better off using PDO or MySQLi. Both protect you (if used correctly) from SQL injection. Currenly, your code is VERY prone to SQL injection.

Answer 3

Without injection vulnerability (require 'mysql_connect' before) :

if(isset($_POST['order_no']))
{
    $orderNo = mysql_real_escape_string($_POST['order_no']);

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

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

    if(mysql_num_rows($result) == 0)
    {
        echo '<tr><td colspan="9">Order not found</td></tr>';
    }
    else
    {
        while($row = mysql_fetch_assoc($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>";
}

Answer 4

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