[RESOLVED] PHP query sorting by ID

meverhart913

I'm writing a program that queries a database and returns all rows that have a value in any of the 19 columns into a table. The issue I am having is that the values are being sorted by which column has text in it instead of the ID number that I would like to sort by. I'm pretty new to PHP so it may be something that easy or it may require a complete re-write of my code. Any help that you can give me will be greatly appreciated.

<?php

$con= mysqli_connect('localhost','root','spyder11','audits');
 echo "<table border = '1'>
    <tr>
    <th>ID</th>
    <th>Auditor</th>
    <th>Area</th>
    <th>ESP</th>
    <th>Date</th>
    <th>Safety Finding</th>
   </tr>";

 for ($x=1; $x<20;$x++){
 $y="SOT".$x;
 $f=1;
  mysqli_query($con,"UPDATE safety SET fixed=0
WHERE fixed!=$f and $y='No'");
}
for ($x=1; $x<20; $x++){
$y="SOT".$x;
 mysqli_query($con,"UPDATE safety SET $y = Null WHERE $y ='Yes'");


$result=mysqli_query($con, "SELECT idsafety, auditor, area, esp, date,".$y." FROM safety WHERE SOT".$x." is not null Order BY idsafety");


while ($row = mysqli_fetch_assoc($result)){

  echo "<tr>";
  echo "<td>". $row["idsafety"]."</td>";
  echo "<td>". $row["auditor"]. "</td>";
  echo "<td>". $row["area"]. "</td>";
  echo "<td>". $row["esp"]. "</td>" ;
  echo "<td>". $row["date"]. "</td>";
  echo "<td>". $row[$y]."</td>";

  echo "</tr>";
  }

}

?>

bradgrafelman

Can you show us an example result set that's getting returned and explain how the results should have been ordered instead?

One issue I see is that you have numbered columns like that in the first place. Nine times out of ten, a structure like that is just begging to be normalized into a more useful schema.

EDIT: Disregard the example result set request; it makes sense why you're getting those results now. You're executing 19 different SQL queries and displaying the results after each one. If you had a more normalized schema, you could likely do this in one SQL query (which, of course, would allow SQL to order the results correctly).

johanafm

Seconded. Really! If your table is not normalized, you will run into more trouble than you know later on. It will be painful and it will take a lot of time. Also do note that if you want your rows sorted in order of creation or something similar, you should have a date column called created to sort by. You should not assume the id to be in any particular order.

Apart from that, have you tried separating your fields with a comma: idsafety