Filtering database query based on form input

Question

Hello I can't get my script fully operational.

I have it calculating properly but now need a query for fuel type.

<?php

include 'mysql_connect.php';

$query = "SELECT * FROM fuel_price WHERE FuelType='Oil'" ;

$result = mysql_query($query);
$price= mysql_fetch_array($result);


if(isset($_POST['submit'])){

 echo "The Price Today is  ";

 echo "£"; echo $_POST['qtylitres'] * $price ['Price'];



 } else {

echo "Please select value";

}

?>

I need to to check fueltype selected on form and calculate total accordingly.

eg $query = "SELECT * FROM fuel_price WHERE FuelType='{$_POST['fueltype'];}'" ;

Please help anyone under pressure.

Thanks

Yikes! `WHERE FuelType='{$_POST['fueltype'];}'" ;` Read: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Johan, Aug 30 '11 at 12:14
you are just about there: $query = "SELECT * FROM fuel_price WHERE FuelType='".mysql_real_escape_string($_POST['fueltype'])."'"; isn't this working for you? — galchen, Aug 30 '11 at 12:14
possible duplicate of [php mysql multiply database value with form selection](http://stackoverflow.com/questions/7232428/php-mysql-multiply-database-value-with-form-selection) — Your Common Sense, Aug 30 '11 at 12:18

Shef · Accepted Answer · 2011-08-30T13:44:49.837

1

include 'mysql_connect.php';

if(isset($_POST['submit'])){
    if($_POST['inputEmail'] == ''){
        echo 'Please enter an email address';
    } else{
        // show price
        $fuelPriceQuery = sprintf("SELECT `Price` FROM fuel_price WHERE FuelType = '%s' LIMIT 1",
                    mysql_real_escape_string($_POST['fueltype']));

        $fuelPriceResult = mysql_query($fuelPriceQuery);
        $price           = mysql_fetch_array($fuelPriceResult, MYSQLI_ASSOC);
        echo 'The Price Today is £'.($_POST['qtylitres'] * $price['Price']);

        // insert email
        $addEmailQuery  = sprintf("INSERT INTO `subscribe`(`Email`) VALUES('%s')",
                            mysql_real_escape_string($_POST['inputEmail']));
        $addEmailResult = mysql_query($addEmailQuery);
        if($addEmailResult){
            echo 'You have successfully subscribed';
        } else{
            echo 'Sorry, we could not subscribe you at this time. Please try again.';
        }
    }
} else {
    echo "Please select value";
}

A couple of things to note:

Always make sure to escape the user input by using mysql_real_escape_string, if you are not using prepared statements such as PDO, MySQLi, etc...
I added the LIMIT clause to the query so mysql_fetch_array will work, because if it returns more than one row, then you would have to handle it in a loop.
It is not necessary to use multiple echos, in fact it is better if you use as few as possible.

edited Aug 30 '11 at 13:44

answered Aug 30 '11 at 12:19

Shef

44,808
15
79
90

you forgot the `order by` without that you will probably select the first fuel price from 1928. – Johan Aug 30 '11 at 12:21
@Johan: Huh? How do you know the table schema? :) – Shef Aug 30 '11 at 12:24
happy days. Thanks a million. I know this is bit much to ask but how do I add email address to different table in same query?? eg $u = "INSERT INTO subscribe `Email`='$_POST[inputEmail]'"; Thanks – phpnoob Aug 30 '11 at 12:43
@nuubee, that's an SQL injection hole you're posting there, right after some people have gone into great detail about how and why **not** to do that, please stop doing that. – Johan Aug 30 '11 at 12:46
@Shef, limit 1 without an order by is pointless, it just selects a "random" row. – Johan Aug 30 '11 at 12:47
@Johan: Yes, I agree it's pointless, but the OP has not shared the table schema so we can tailor the query to OP's requirements. – Shef Aug 30 '11 at 12:48
-1 @Shef, you have added an XSS hole in that code, you need to escape the output of echo using `stripslashes` or alike function. – Johan Aug 30 '11 at 12:49
@nuubee, NP but it's hard to lurk in the [php] tag seeing all the XSS and SQL-injections flying over in such great numbers. – Johan Aug 30 '11 at 12:50
@Shef, it's not a math result, it's `$_POST['qtylitres']` anything can be in there, wait a minute.... I was too hasty. – Johan Aug 30 '11 at 12:53
@Johan: Show me a sample XSS which would bypass `echo ($_POST['qtylitres'] * $price['Price']);`. Don't talk nonsense.;) – Shef Aug 30 '11 at 12:54
@Shef let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2992/discussion-between-johan-and-shef) – Johan Aug 30 '11 at 12:55
Hello I am trying to calculate fuel price and add email address at the same time. Can I insert and select in same query?? – phpnoob Aug 30 '11 at 12:55
@nuubee: No, you can't do a `SELECT` and `INSERT` in the same query. Do you want to insert the email before the `echo 'The Price Today is...`? Where do you want the `INSERT` to occur? – Shef Aug 30 '11 at 12:57
Sorry for my lack of understanding. After the echo price if possible. User email addresses will be stored in a seperate table called subscribe under field Email?? – phpnoob Aug 30 '11 at 13:02
getting error Undefined variable: result. Any idea whats wrong? – phpnoob Aug 30 '11 at 13:11
@nuubee: Changed the code. I had forgotten an old variable in there. Copy it again and test it. – Shef Aug 30 '11 at 13:13
Shef price now working but doesn't seem to be adding Email. Getting echo 'Sorry, we could not subscribe you at this time. Please try again.'; Any Ideas?? – phpnoob Aug 30 '11 at 13:17
@nuubee: Are you passing the input `inputEmail` with the form submit, does the table `subscribe` exist? – Shef Aug 30 '11 at 13:22
yes and yes. Here is input and subscribe database has two cols one is ID and other Email. Not sure whats wrong?? Thanks again – phpnoob Aug 30 '11 at 13:25
@nuubee: Sorry, dumb me, didn't take a look at your query's syntax. I used it as it was. It must work with the updated code now. – Shef Aug 30 '11 at 13:28
Shef you are a life saver. How do I stop people from submitting form if email field empty? Its adding empty fields now at the moment. – phpnoob Aug 30 '11 at 13:34
Thanks. Its not adding empty records in subscribe database anymore bit it still lets user see price even email input empty?? Sorry to hassle you. Thanks – phpnoob Aug 30 '11 at 13:41
@nuubee: Okay, that must be the final touch. – Shef Aug 30 '11 at 13:44
@nuubee: Accept the answer by clicking the empty tick below the vote count on the top left side of my answer. – Shef Aug 30 '11 at 14:02

score 1 · Answer 2 · answered Aug 30 '11 at 12:20

$fueltype = mysql_real_escape_string($_POST['fueltype']);
$query = "SELECT price 
          FROM fuel_price 
          WHERE FuelType= '$fueltype'
          ORDER BY pricedate DESC
          LIMIT 1 ";

Explanation

Always use either PDO or mysql_real_escape_string()
Don't do SELECT *, only select the fields you need.
Put the injected $var in single quotes, or mysql_real_escape_string() will not work!
If you only need one price, select only 1. Use limit 1 to get only 1 and order by ... DESC to get the latest.

Filtering database query based on form input

2 Answers2