mat-tastic Posted February 9, 2009 Share Posted February 9, 2009 Hey Guys, I have devoloped a script for a virtual airline, basically it pulls data based on there ID. http://apg.flyafa.com/charter/ (in the drop down menu, select san francisco) As you can see, SFO-013 is at the top. When it should be at the bottom, as its ID is 121. If you select another option from the drop down menu, they will all be in order. I am totally confused as to why the order is getting muddled up, any suggestions? if you need further information just ask. I really need help with this as it has thrown me. Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/ Share on other sites More sharing options...
Maq Posted February 9, 2009 Share Posted February 9, 2009 Can we see the relevant code? Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758367 Share on other sites More sharing options...
printf Posted February 9, 2009 Share Posted February 9, 2009 Something like... query_start... ORDER BY (sfo_column_name+0<>0 OR sfo_column_name='0') DESC, sfo_column_name+0, sf_column_name; Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758371 Share on other sites More sharing options...
mat-tastic Posted February 9, 2009 Author Share Posted February 9, 2009 this is the section which takes the charters out and displays them: elseif ($hub) { print "<a href=\"http://apg.flyafa.com\">American Pacific Group</a> » <a href=\"index.php\">Charter Flight Center</a> » <a href=\"$file?air=$air&hub=$hub\">$hubname[$hub] Hub </a> » <br><br>To request a charter flight, please click <a href=\"request.php?air=$air&hub=$hub\">here</a>."; print "<table width=\"500\"><tr><td>"; print "<p align=\"justify\">"; mysql_select_db($database_conn, $conn_abrv) or die("cant selectdb"); $result = mysql_query("SELECT * FROM charter WHERE hub='$hub' ORDER BY 'id' ASC",$conn_abrv); $num_rows = mysql_num_rows($result); if ($myrow = mysql_fetch_array($result)) { if (!$id) { if ($num_rows == 1) { echo "<b>There is currently $num_rows charter flight in this hub.</b><br>"; } else { echo "<b>There are currently $num_rows charter flights in this hub.</b><br>"; } /* print "<a href=\"request.php?air=$air&hub=$hub\">Request a New Charter Flight</a><br>"; */ do { printf("<br><a href=\"?air=$air&hub=$hub&id=%s\">%s</a>", $myrow["id"], $myrow["chart_title"]); if ($myrow["chart_subtitle"] == null) { } else { print " - <i>$myrow[chart_subtitle]</i>"; } if ($myrow["reserved"] == 1 && $myrow["closed"] != 1) { print " <b>(RESERVED)</b>"; } elseif ($myrow["reserved"] == 1 && $myrow["closed"] == 1) { print " <b>(CLOSED)</b>"; } Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758378 Share on other sites More sharing options...
printf Posted February 9, 2009 Share Posted February 9, 2009 Try changing this... change this... $result = mysql_query("SELECT * FROM charter WHERE hub='$hub' ORDER BY 'id' ASC",$conn_abrv); to this... $result = mysql_query("SELECT * FROM charter WHERE hub='$hub' ORDER BY (chart_title+0<>0 OR chart_title='0') DESC, chart_title+0, chart_title;",$conn_abrv); Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758386 Share on other sites More sharing options...
mat-tastic Posted February 9, 2009 Author Share Posted February 9, 2009 No difference. You may of noticed its AJAX. Basically the script gives the response of this page: http://apg.flyafa.com/charter/charter.php?air=AFA&hub=KSFO (if you select san franciso) Worryingly, that page is not loading, and now I am wondering why its not :| Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758394 Share on other sites More sharing options...
mat-tastic Posted February 9, 2009 Author Share Posted February 9, 2009 Hmm this is confusing me: http://apg.flyafa.com/charter/index.php If you go there, it loads fine, if you select any other hub OTHER then San Francisco, they all load fine, only SFO seems to not load. Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758398 Share on other sites More sharing options...
printf Posted February 9, 2009 Share Posted February 9, 2009 It works for me... http://apg.flyafa.com/charter/charter.php?air=AFA&hub=KSFO SFO-001 - Aircraft Maintenance - Airbus SFO-002 - Aircraft Maintenance - Boeing SFO-003 - Aircraft Pickup - Airbus SFO-004 - Aircraft Pickup - Boeing SFO-005 - Aircraft Pickup - Bombardier CRJ-200 SFO-006 - Aircraft Pickup - Embraer 170 SFO-007 - Alpha Jet Multi-Leg 01 (KOAK-KABQ-KSTL-KTPA) (CLOSED) SFO-008 - Alpha Jet Multi-Leg 02 (KOAK-KSTL-KMDW-KBWI) (RESERVED) SFO-009 - Charter Request AFA4550 (RESERVED) SFO-011 - California Commuter Charter SFO-012 - Circle The Globe in 10 Legs (RESERVED) SFO-013 - Canada Tour Vacation SFO-014 - Alpha Jet Charter #01 - Texas Tour SFO-015 - Alpha Jet Charter #02 - Western US Charter SFO-016 - Multi-leg Cat 3 (KSAN, KABQ, KDAL, KMSY, KATL) SFO-017 - Multi-leg Cat 4 (KSLC, KMSP, KMEM, KIAD) (RESERVED) Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758401 Share on other sites More sharing options...
mat-tastic Posted February 9, 2009 Author Share Posted February 9, 2009 Probarly just my end! I will leave it a while and see what I get, thanks for your help though! Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758404 Share on other sites More sharing options...
mat-tastic Posted February 9, 2009 Author Share Posted February 9, 2009 I can now confirm this is solved. Can you please explain what was wrong with the original code? I don't understand what that new code does? And thank you, this has been bugging me for ages. Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758521 Share on other sites More sharing options...
printf Posted February 9, 2009 Share Posted February 9, 2009 Because your column * chart_title * has mixed character and numbers mysql doesn't know how to sort the column. So you have to tell it how to do the sorting, so... ... ORDER BY (chart_title+0<>0 OR chart_title='0') DESC, chart_title+0, chart_title; if the value of the column * chart_title * can be converted to a whole number (chart_title+0<>0) and the value does not equal (0), (that means its a real whole number), so put that value before any value that is not a real whole number. (OR) we use the (OR) because we are comparing a value to 0, so we need to include the (OR) in case a value is a literally (0), (chart_title='0'). After we do that, the values are sorted by their actual numerical order (chart_title+0). After that, the real string values are finally sorted (chart_title) Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758554 Share on other sites More sharing options...
mat-tastic Posted February 10, 2009 Author Share Posted February 10, 2009 Clever. Thank you so much. Would never of worked that out alone. Quote Link to comment https://forums.phpfreaks.com/topic/144521-solved-odd-sorting/#findComment-758574 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.