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: stackoverflow.com/questions/332365/… — Johan
– Johan, Commented Aug 30, 2011 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
– galchen, Commented Aug 30, 2011 at 12:14
possible duplicate of php mysql multiply database value with form selection — Your Common Sense
– Your Common Sense, Commented Aug 30, 2011 at 12:18

Shef · Accepted Answer · 2011-08-30 13:44:49Z

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, 2011 at 13:44

answered Aug 30, 2011 at 12:19

Shef

45.6k15 gold badges82 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

24 Comments

Johan Over a year ago

you forgot the order by without that you will probably select the first fuel price from 1928.

Shef Over a year ago

@Johan: Huh? How do you know the table schema? :)

phpnoob Over a year ago

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

Johan Over a year ago

@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 Over a year ago

@Shef, limit 1 without an order by is pointless, it just selects a "random" row.

|

Johan · Accepted Answer · 2011-08-30 12:20:23Z

1

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

answered Aug 30, 2011 at 12:20

Johan

77.4k28 gold badges205 silver badges346 bronze badges

Collectives™ on Stack Overflow

Filtering database query based on form input

2 Answers 2

24 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

24 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related