MySQL+PHP - Problem with Query not showing all results
-
$request2 = mysql_query("SELECT * FROM tablename WHERE status IN('In Progress','Pending') ORDER BY FIND_IN_SET('urgent', priority) DESC, FIND_IN_SET('high', priority) DESC, priority DESC, status DESC") or die(mysql_error());
Above is my query. I want the query to show me all the cases where the status is "In Progress" or "Pending" then order them by the priority (urgent, high, mid, low) in that order, then order them DESC by status. Example: Case 1, urgent, Pending Case 2, urgent, In Progress Case 3, high, Pending Case 4, high, In Progress ... For some reason though, random cases aren't displayed in my table. Everything works fine if I leave out the ORDER BY section, but for some reason when ordering is added it stops working. Also, the mid and low sections always seem to show up, but random high and urgent cases do not. I don't understand what is wrong. Can someone please help? I'll gladly provide more information if needed.
-
$request2 = mysql_query("SELECT * FROM tablename WHERE status IN('In Progress','Pending') ORDER BY FIND_IN_SET('urgent', priority) DESC, FIND_IN_SET('high', priority) DESC, priority DESC, status DESC") or die(mysql_error());
Above is my query. I want the query to show me all the cases where the status is "In Progress" or "Pending" then order them by the priority (urgent, high, mid, low) in that order, then order them DESC by status. Example: Case 1, urgent, Pending Case 2, urgent, In Progress Case 3, high, Pending Case 4, high, In Progress ... For some reason though, random cases aren't displayed in my table. Everything works fine if I leave out the ORDER BY section, but for some reason when ordering is added it stops working. Also, the mid and low sections always seem to show up, but random high and urgent cases do not. I don't understand what is wrong. Can someone please help? I'll gladly provide more information if needed.
You could show us an example of some of the records that are not getting displayed. Check to see if nulls are getting in the way. Another thing you can do is take the order by clause and turn it into part of your select statement output to see what the computer is seeing for those seemingly random records. Something like this, although you might have to clean up the syntax to get it to run. $request2 = mysql_query("SELECT tablename.*, FIND_IN_SET('urgent', priority), FIND_IN_SET('high', priority), priority, status FROM tablename WHERE status IN('In Progress','Pending') ") or die(mysql_error()); This way you might have a clearer view of why a given record gets dropped.
_____________________________ Give a man a mug, he drinks for a day Teach a man to mug... -Scott M.
-
You could show us an example of some of the records that are not getting displayed. Check to see if nulls are getting in the way. Another thing you can do is take the order by clause and turn it into part of your select statement output to see what the computer is seeing for those seemingly random records. Something like this, although you might have to clean up the syntax to get it to run. $request2 = mysql_query("SELECT tablename.*, FIND_IN_SET('urgent', priority), FIND_IN_SET('high', priority), priority, status FROM tablename WHERE status IN('In Progress','Pending') ") or die(mysql_error()); This way you might have a clearer view of why a given record gets dropped.
_____________________________ Give a man a mug, he drinks for a day Teach a man to mug... -Scott M.
Thanks for the reply, I'll try that today if I have time. None of my fields allow nulls so I don't think that is the problem. Now here's something interesting. I put the same values in my HTML form on the previous page (the one that enters them into the database) and submitted, then it shows up in the list, where the first one with the same values does not. I went into MySQL itself and can't find anything wrong with the data...it seems exactly the same in both places. Now one was made before the sorting query was setup and one was made after...would that somehow make a difference? I wouldn't think so since it's just a query...but might as well cover all possibilities.