CODEWITHSUNDEEP
phpmysql
Shaikat Chakraborty
Shaikat Chakraborty

Reputation: 29

Need help pulling up this Mysql Query in php

I have a simple scenario but not getting it to work. I need to pull up a value for column 'fee' specified in Mysql Database according to two values.

My Current table structure and values

I want to get the value for 'fee' if I specify an amount between columns 'from' and 'to' Example: if amount is 95, the value for row 'fee' should be 0.99.

I tried to use traditional method previously like below but now I would like MYSQL query method. Please help.

if ($amount < 100) {
$fee = '0.99';
}
else if ($amount > 100 && $amount < 500) {
$fee = '1.99';
}
else if ($amount < 500 && $amount < 1000) {
$fee = '2.99';
}
else if ($amount < 1000 && $amount < 10000) {
$fee = '4.99';
}
else if ($amount > 10000) {
$fee = '9.99';
}

A Sample PHP Code:

<?
include "dbconnect.php";

$post_amount = $_POST["amount"];

$sql = "SELECT fee FROM fees WHERE from < '$post_amount' AND to >     '$post_amount'     LIMIT 1";
$result = mysql_query($sql);
while($row = mysql_fetch_array( $result )) {
$fee = $row["fee"];
}
echo "Posted Amount is $post_amount and Fee is $fee";

?>

Upvotes: 1

Views: 72

Answers (3)

Deepak Kumar
Deepak Kumar

Reputation: 413

You just had problem in > and < all other things are fine in ur code there no need for other function to check it from database table.

I have checked it with this :

$amount = 250;

if ($amount < 100) {
    $fee = '0.99';
} else if ($amount > 100 && $amount < 500) {
    $fee = '1.99';
} else if ($amount > 500 && $amount < 1000) {
    $fee = '2.99';
} else if ($amount > 1000 && $amount < 10000) {
    $fee = '4.99';
} else if ($amount > 10000) {
    $fee = '9.99';
}

echo $fee;

Its works fine.

Upvotes: -1

Joshua Kissoon
Joshua Kissoon

Reputation: 3319

If I get your question, you need the SQL query.

You can use > and < signs:

"SELECT fee FROM tablename WHERE from < '$amount' AND to > '$amount' LIMIT 1"

Or you can use BETWEEN (How to search between columns in mysql):

"SELECT fee FROM tablename where '$amount' BETWEEN from AND to LIMIT 1"

Two more things:

  1. You'll need to sanitize $amount variable before using it in the query
  2. from and to are MySQL Reserve Words and should not be used as column names

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324650

MySQL has an awesome operator called BETWEEN:

"SELECT `fee` FROM `table` WHERE ".floatval($amount)." BETWEEN `from` AND `to`"

Upvotes: 1

Related Questions