PHP select MySQL column from query using users input

Question

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

Please, read up on bind_param because what you're doing here with string interpolation can cause severe problems. It's extremely important to properly escape any and all user data to avoid SQL injection bugs. mysqli supports parameterized queries which should be used whenever possible. — tadman
– tadman, Commented Aug 12, 2013 at 15:34

Gordon Linoff · Accepted Answer · 2013-08-12 14:52:25Z

1

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.

edited Aug 12, 2013 at 14:52

answered Aug 12, 2013 at 14:34

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

8 Comments

DevWithZachary Over a year ago

sorry I guess I wasnt clear, the right weight it selected but I then need to print out the right field from that row depending on a second value the user enters

Marc B Over a year ago

+1. @ZacPowell: It's a select * query. it's retrieving ALL of the fields in the row that has the closest weight. If you want another field, then just echo $row['the_other_field']

DevWithZachary Over a year ago

@MarcB yes I see that but my issue is that the field printed need to be one which is closest to the users value rounded up

Marc B Over a year ago

which is exactly what the second query is doing.

Marc B Over a year ago

then definitely put them into a sub table, e.g. weight_distances (weight, price) or something like that. then you can use the above answer by adding a simple join the query.

|

Rootvik · Accepted Answer · 2013-08-12 14:40:26Z

1

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

answered Aug 12, 2013 at 14:40

Rootvik

563 bronze badges

Comments

Sean · Accepted Answer · 2013-08-12 14:52:40Z

0

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

answered Aug 12, 2013 at 14:52

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

PHP select MySQL column from query using users input

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related