Using Php Forms In Mysql Queries

September 08, 2024 Post a Comment

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 $_PO

Solution 1:

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

Solution 2:

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>";
}

Solution 3:

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.

Solution 4:

Your form should be something like this:

<form action="orderlist.php" method="post">
    OrderNo: <inputtype="text" name="order_no" />
    <inputtype="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.

Html5 Works