[SOLVED] Odd sorting?

[mat-tastic]

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.

 

[Maq]

Can we see the relevant code?

[printf]

Something like...

 

query_start... ORDER BY (sfo_column_name+0<>0 OR sfo_column_name='0') DESC, sfo_column_name+0, sf_column_name;

[mat-tastic]

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>";
}

[printf]

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);

 

[mat-tastic]

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 :|

 

[mat-tastic]

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.

[printf]

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)

[mat-tastic]

Probarly just my end! I will leave it a while and see what I get, thanks for your help though!

[mat-tastic]

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.

[printf]

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)

 

 

[mat-tastic]

Clever.

 

Thank you so much. Would never of worked that out alone.