I want the highest and the lowest value of a table, why can't I save that value in PHP?

Question

$category = htmlspecialchars($_GET['category']);

$sql = "(SELECT number 
        FROM german 
        WHERE german.category_german LIKE ".$category." 
        ORDER BY number DESC 
        LIMIT 1) as 'high', 
        (SELECT number 
        FROM german 
        WHERE german.category_german LIKE ".$category." 
        ORDER BY number ASC 
        LIMIT 1) as 'low'";
    if ($result = $conn -> query($sql)) {
      while ($row = $result -> fetch_row()) {
        
          $high_value = $row[high];
          $low_value = $row[low];
          $r_n = rand($low_value,$high_value).PHP_EOL;
          echo $r_n;
          }

      }

What am I missing? I want the highest and the lowest value of a table, why can't I save that value in PHP? I just can't access the values. And I tried out MIN and MAX as well, but they didn't function neither:

$category = htmlspecialchars($_GET['category']);

$sql = "SELECT MIN('number') AS 'low', MAX('number') AS 'high' FROM german WHERE german.category_german LIKE ".$category."";
    if ($result = $conn -> query($sql)) {
      while ($row = $result -> fetch_row()) {
          $high_value = $row[high];
          $low_value = $row[low];
          $r_n = rand($low_value,$high_value).PHP_EOL;
          echo $r_n;
          }

      }

As a result of $r_n I only get 0. The database shouldn't be the problem. Beforehand (where I only used the highest value) everything functioned:

$category = htmlspecialchars($_GET['category']);

$sql = "SELECT number FROM german WHERE german.category_german LIKE ".$category." ORDER BY number DESC LIMIT 1";
if ($result = $conn -> query($sql)) {
  while ($row = $result -> fetch_row()) {
    
      $r_n = $row[0];
      $r_n = rand(1,$r_n).PHP_EOL;
      echo $r_n;
      }

  }

Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should always use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! This will also remove the unescaped character issue like a `'` in a text string. — RiggsFolly, Jan 05 '23 at 17:33
Because all your attempts have simple but catestrophic coding errors in them — RiggsFolly, Jan 05 '23 at 17:35

score 0 · Answer 1 · answered Jan 05 '23 at 17:37

0

You can't use multiple SELECT statements at top-level of a query. They would have to be subqueries:

SELECT (SELECT ...) AS high, (SELECT ...) AS low

Your second query would have worked, but you shouldn't have quotes around number. That makes it a literal string, not the column values. So MAX('number') should be MAX(number), MIN('number') should be MIN(number). See When to use single quotes, double quotes, and backticks in MySQL

And if category is a string, you need to put quotes around $category:

WHERE german.category_german LIKE '".$category."'"

But the better way to resolve that problem is to use a prepared statement with parameters, How can I prevent SQL injection in PHP? than substituting variables directly into the query. See

answered Jan 05 '23 at 17:37

Barmar

741,623
53
500
612

I changed the code to: $category = htmlspecialchars($_GET['category']); $sql = "SELECT MIN(number) AS 'low', MAX(number) AS 'high' FROM german WHERE german.category_german LIKE '".$category."'"; if ($result = $conn -> query($sql)) { while ($row = $result -> fetch_row()) { $high_value = $row[high]; $low_value = $row[low]; $r_n = rand($low_value,$high_value).PHP_EOL; echo "
".$r_n."
"; But it still doesn't work on the website (on phpmyadmin it works) – FreakyLord Jan 05 '23 at 17:48
There's no need for a `while` loop since it always returns exactly 1 row. – Barmar Jan 05 '23 at 17:52
If it's not returning anything you should display the error message. See https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments – Barmar Jan 05 '23 at 17:53
Please, again basic coding error! Arrays are addressed as `$row['low']` not `$row[low]` – RiggsFolly Jan 05 '23 at 17:54
@RiggsFolly True, although it will work with the warning that the undefined constant is being treated as a string literal. – Barmar Jan 05 '23 at 17:56
For reference, in PHP 8 that now becomes a `Fatal error: Uncaught Error: Undefined constant "low"` – RiggsFolly Jan 05 '23 at 18:02

I want the highest and the lowest value of a table, why can't I save that value in PHP?

1 Answers1