PHP/SQL Order By

[programmer12]

Hi,

I am trying to organize the way my episode titles output. At the moment it prints like this. (I want to be able to upload the newest episodes and also go back and upload the older shows but i want the newer shows to stay at the bottom in numerical order)

Output: (At the moment it shows like this)

Episode 1
Episode 3
Episode 323
Episode 4

Output: (This is the way i want it)

Episode 1
Episode 3
Episode 4
Episode 323 //This number is way bigger than the others.

How can i get it to show like this option?

The SELECT Script i have rite now is

SELECT * FROM table.".$_GET['name']." ORDER BY episode, id ASC

but its not what i want. Please Help! thanks in advance!

[iamthwee]

The data Episode # is probably stored as a varchar.

One way to do this is to separate the number at the end and sort by this. This can be achieved in php.

[programmer12]

Thanks but i solved this issue... For others who may have the same problem take a look at this:

$query = "select project_name, project_id, id, domainname from projects ORDER BY project_name";

// I added some test projects here called
//      test10
//      test100
//      test30

$result = mysql_query($query) or die(mysql_error($linkid));

$c=0;
while ($rst = mysql_fetch_array($result)) {
    $gen_array[$c][pr_name] = $rst[project_name];
    $gen_array[$c][project_id] = $rst[project_id];
    $c++;
    // You can add other results, this was a test.
}

$v=0;
while ($v<$c) {
    // put in separate array to run natsort.
    $gen2_array[] = $gen_array[$v][pr_name];
    $v++;   
}

natsort($gen2_array);

foreach($gen2_array as $key => $val) {
    // here we can link by key.
    echo $val . " Project ID: " . $gen_array[$key][project_id] . "<br>";
}

Just change the tables to what points to your database!

Here is the URL as well: http://www.phpbuilder.com/board/showthread.php?t=10212406

[programmer12]

Also for my case I had to just take out the "..Project ID..." and just use

echo $val . "<br />";

B.c If i left the other things there it will just print the same thing but with the id number of the row... =D

Be Blessed!

[technical_amit]

Here is one specific solution. Try this if the number is always at the end and always has a space in front of it this will work:

SELECT episode FROM test ORDER BY SUBSTRING_INDEX(episode, ' ', 1), SUBSTRING_INDEX(episode, ' ', -1) + 0;