[SOLVED] arrange output by assending numbers

[runnerjp]

here is what my current form looks like

 

<?php
$result = mysql_query("SELECT * FROM pb where uid='".$getuser['ID']."' Order by pb") or die(mysql_error());
$num2 = mysql_num_rows($result);

echo "<table class=\"profilepbs\" width=195 align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
   <tr><td width=105 ><div align=\"center\">Distance</div></td><td width=125><div align=\"center\">Pb</div></td></tr>
   <tr>
     <td height=\"1\" colspan=\"2\" bgcolor=\"#000000\"></td>
     <td height=\"1\" colspan=\"2\" bgcolor=\"#000000\"></td>
   </tr>
   ";
while ( $row = mysql_fetch_array($result) ) {echo "<tr><td bgcolor=\"#FFFFFF\"><center>" . $row["distance"] . "</center></td><td bgcolor=\"#FFFFFF\"><center>" . $row["pb"] . "</center></td></tr>";
  }
  if($num2 < 1)
    {echo "<tr><td bgcolor=\"#FFFFFF\" ><center> N/A 
    </center></td><td bgcolor=\"#FFFFFF\"><center> N/A 
    </center></td></tr>";}
echo "</table>
<p>";
?> 

 

10k Road  32:10.1

100m  9.3

3000m  9:07.2

 

i would like it to go in time order so the  shorter the distances appear at the top like so

 

100m  9.3

3000m  9:07.2

10k Road  32:10.1

 

 

i just can figure out how to do this with the format thats used to enter the times

[Ayon]

"SELECT * FROM pb where uid='".$getuser['ID']."' ORDER BY pb ASC"

[annihilate]

If I understand the question correctly, you would need to change your order by to distance instead of pb. This is only going to work as you expect if all the distances are in the database in the same unit. eg 100 and 3000 mean 100 metres and 3000 metres. If you have entries like 100 metres and 3K, then its not going to work as expected.

[Mark Baker]

Don't know if this would work

ORDER BY (SUBSTRING(journeyTimeField,0,LOCATE(':',journeyTimeField)-1) * 60 +
         SUBSTRING(journeyTimeField,LOCATE(':',journeyTimeField)))

[runnerjp]

ok i came up with an idea... i have made it so the input into the db looks like this

 

<select name="distance" onchange="setDistance(this.value)">
				<option value="#|Distance">Select the distance</option>
				<option value='withoutMinutes|C:100m' >100m</option>
                    <option value='withoutMinutes|E:200m'>200m</option>
                    <option value='withoutMinutes|F:400m'>400m</option>
                    <option value='withoutHours|H:800m'>800m</option>
                    <option value='withoutHours|I:1500m'>1500m</option>
                    <option value='withoutHours|J:3000m'>3000m</option>
                    <option value='withoutHours|L:5000m'>5000m</option>
                    <option value='withoutHours|N:10,000m'>10,000m</option>
                    <option value='withoutHours|M:5k Road'>5k Road</option>
                    <option value='withoutHours|O:10k Road'>10k Road</option>
                    <option value='withHours|P:H Marathon'>H Marathon</option>
                     <option value='withHours|Q:Marathon'>Marathon</option>
                      <option value='withoutHours|K:3k S/Chase'>3k S/Chase</option>
                       <option value='withoutMinutes|D:110m Hurdles'>110m Hurdles</option>
                        <option value='withoutMinutes|G:400m Hurdles'>400m Hurdles</option>
                          <option value='withoutMinutes|A:60m (I)'>60m (I)</option>
                            <option value='withoutMinutes|2B:60m Hurdles'>60m Hurdles</option>
			</select>

 

so when i run an my script it should show reults in dec order..

 

a

b

c

d

e

ect

 

so i ran the script

 

<?php
$result = mysql_query("SELECT * FROM pb where uid='".$getuser['ID']."' Order by pb DESC") or die(mysql_error());
$num2 = mysql_num_rows($result);

echo "<table class=\"profilepbs\" width=195 align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
   <tr><td width=105 ><div align=\"center\">Distance</div></td><td width=125><div align=\"center\">Pb</div></td></tr>
   <tr>
     <td height=\"1\" colspan=\"2\" bgcolor=\"#000000\"></td>
     <td height=\"1\" colspan=\"2\" bgcolor=\"#000000\"></td>
   </tr>
   ";
while ( $row = mysql_fetch_array($result) ) {echo "<tr><td bgcolor=\"#FFFFFF\"><center>" . $row["distance"] . "</center></td><td bgcolor=\"#FFFFFF\"><center>" . $row["pb"] . "</center></td></tr>";
  }
  if($num2 < 1)
    {echo "<tr><td bgcolor=\"#FFFFFF\" ><center> N/A 
    </center></td><td bgcolor=\"#FFFFFF\"><center> N/A 
    </center></td></tr>";}
echo "</table>
<p>";
?>

 

the confusing thing is this is my output

 

why is J 1st :S

 

also i cant rember but whats the function so i can get rid of the 1st 2 thing so A:100m will become 100m

[runnerjp]

i really dont understand the j coming 1st :S

[Yesideez]

You'll be needing to run it through a regex to remove anything other than numbers. Not sure what regex as it isn't really my thing.

[Ayon]

edit:

i really don't know how i missed the solved part... *my bad*