CODEWITHSUNDEEP
phpmysql
DevWithZachary
DevWithZachary

Reputation: 3675

PHP select MySQL column from query using users input

I have a MySQL table which is something like this:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Weight  | int         | YES  |     | NULL    |       |
| 100     | double      | YES  |     | NULL    |       |
| 120     | double      | YES  |     | NULL    |       |
| 130     | double      | YES  |     | NULL    |       |
| 150     | double      | YES  |     | NULL    |       |
| 160     | double      | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

I select a Row using 

$row = mysqli_query($con, "SELECT * FROM Rates WHERE Weight<='$weight' AND Weight>='$weight'");

My question is using the returned query result how do I print the value of one of the Fields (100, 120, 130, 150, 160) which is closest to what the user enters.

For example is the user types in 110 the value in the field '120' would be printed. Or if 131 was entered the value of '150' would be printed (so always rounded up).

As it stands I have:

while($row = mysqli_fetch_array($rate))
    {
        echo "Using weight your cost is: " . $row['100'];
        echo "<br>";
    }

But this only prints the value of the field '100' How would I replace this to take the users input and work out which of the rows are closest and print that

So something like:

IF ($value =< 100) echo $row['100']
IF ($value =< 120 && $value > 100) echo $row['120']
IF ($value =< 130 && $value > 120) echo $row['130']
..etc

Upvotes: 0

Views: 938

Answers (3)

Sean
Sean

Reputation: 12433

It sounds like you need an if/else block to set your field -

if($your_value <= 100) {$field = 100;}
else if($your_value <= 120) {$field = 120;}
else if($your_value <= 130) {$field = 130;}
else if($your_value <= 150) {$field = 150;}
else {$field = 160;}
while($row = mysqli_fetch_array($rate))
{
    echo "Using weight your cost is: " . $row[$field];
    echo "<br>";
}

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Try this version of the query:

SELECT *
FROM Rates
order by abs(Weight - $weight)
limit 1;

This returns one row, where the weight is closest to the user value.

The following version always rounds up:

SELECT *
FROM Rates
WHERE Weight >= $weight
order by Weight
limit 1;

EDIT:

Based on your edit, I think this may be what you are looking for:

SELECT (case when "100" >= $value then "100"
             when "120" >= $value then "120"
             when "130" >= $value then "130"
             when "150" >= $value then "150"
             when "160" >= $value then "160"
        end)
FROM Rates
WHERE Weight<='$weight' AND Weight>='$weight';

The case chooses the first matching value.

Upvotes: 1

Rootvik
Rootvik

Reputation: 56

You can try this version.

SELECT *
FROM Rates
WHERE Weight>='$weight'"
limit 1;

It will return only one row. you can fetch value as

$row = mysqli_fetch_array($rate);
echo "Using weight your cost is: " . $row['Field'];
echo "<br/>";

Upvotes: 1

Related Questions