0

I have a code that looks for the smallest number in the average column. When in mysql, I put the column in "double", it works. But for some reasons and particular actions, my column must be in "text". I need a php / SQL code to show the smallest number.otherwise my code tells me that 13.06 is smaller than 8.56.here is my code:

 $sql1 = "SELECT MIN(moyenne)as  moyenneP  FROM  rangec where classe = '".$_SESSION['classroom']."' AND  position = '".$_SESSION['anscol']."'
 AND ecoles= '".$_SESSION['ecoles']."'  AND  trimestre= '".$_SESSION['trimestre']."'"; 

  foreach  ($bdd->query($sql1) as $classement) 
  {
    $moyenneP =$classement['moyenneP'];
    $moyenneP=''.round($moyenneP, 2).''; 
  }

I believe I have been explicit! Thank you in advance for your assistance.

ADyson
  • 57,178
  • 14
  • 51
  • 63
keunene richard david
  • 1
  • 2
  • 1
    You can still use MySQL query with the help of casting – nice_dev Jul 21 '21 at 07:33
  • 1
    Are there other values in this column which are not numeric? If not then it really makes no sense not to use the double type. You said there was some reason why you wanted to use the text type, but didn't explain what that reason was. I wonder if actually there's a better solution to whatever made you want to use text – ADyson Jul 21 '21 at 07:35
  • P.s. your code is vulnerable to SQL injection attacks and other unexpected syntax issues. Please urgently read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php to learn how to write queries safely and robustly in PHP – ADyson Jul 21 '21 at 07:42
  • This column is used to record text data. When a person is not rated, we mean in this column, that he is not rated. – keunene richard david Jul 21 '21 at 07:52
  • 2
    The only data you've shown is numeric data. If you're mixing two types of data in one column that usually a sign of a design mistake. Your explanation is still vague and doesn't really justify the use of the text type. If the user has no rating you can simply leave the field as `null`, you don't need to write separate text in there. – ADyson Jul 21 '21 at 07:53
  • this is a column in which we record averages.but when a student, due to illness and other reasons, has no averages, we record "ungraded" averages instead.I apologize if I speak bad English, I am a French speaker. – keunene richard david Jul 21 '21 at 08:00
  • 1
    `we record "ungraded" `...again as I said, just leave it as `null`. If you want to show the word "ungraded" in a web page, you can detect the null value in your PHP code and replace it. Don't pollute the database itself with mixed types or UI fluff – ADyson Jul 21 '21 at 08:05
  • when I leave the column as "null", it puts 0 for the one to whom I don't give notes. – keunene richard david Jul 21 '21 at 08:12
  • Maybe there is a default value set for the column. Check the table settings – ADyson Jul 21 '21 at 08:15
  • No default value was set, I just followed your advice. – keunene richard david Jul 21 '21 at 08:25
  • So you're saying if you leave the column as null in your insert, it actually puts 0 into the database table? That seems unlikely, unless either you did something weird in the code or there is another factor making it happen. We can't know the real cause of that, without more information from you. – ADyson Jul 21 '21 at 08:31
  • I will review my code and get back to you – keunene richard david Jul 21 '21 at 08:36
  • Unless you're saying that the output shows 0 in your browser, after you've run the code above? That's not the same as it showing 0 inside the database! Please always describe _specifically_ what you're seeing. "it shows 0" gives us no real idea what "it" is. I mention this because the `round()` function will return `0` if you pass `null` into it - demo: http://sandbox.onlinephpfunctions.com/code/e5bd7bfabe19a58771c95b157a2118110982a8b4 . So you should make your PHP check if the value is null before deciding to round it, or display "ungraded" instead. – ADyson Jul 21 '21 at 08:44
  • e.g. `$moyenneP = $classement['moyenneP']; if ($moyenneP != null) { $moyenneP=''.round($moyenneP, 2).''; } else { $moyenneP = "ungraded"; }` – ADyson Jul 21 '21 at 08:44
  • 1
    Thank you so much, your advice helped me to review my code. And miraculously everything works. You are a champion, a leader – keunene richard david Jul 21 '21 at 09:34

0 Answers0