[SOLVED] ORDER BY name ASC - but 10 is before 1 (please help)

[helpmeplease2]

How do I make it so it lists 1 before 10? If the names are

 

Djasa 1: jaskja

Djasa 10: 8jd2s

 

I have many results which relate to each other in series so I would like it to me in numerical order starting with 1, 2, 3,...,10,11,12.

 

How can I do this?

[bubblegum.anarchy]

extract the index from the column value and apply the appropriate sorting... like this:

 

SELECT substring_index(column_name, ' ', -1) AS column_name_index FROM tabl_name ORDER BY index DESC

 

[helpmeplease2]

This is confusing for me. Why doesn't this work?

 

<?php
$game=$_GET['game'];
$result = mysql_query("SELECT substring_index(VTitle,'',-1) AS ID FROM videos ORDER BY ID DESC")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {

echo "
  <tr>
      <td class=\"4\"><a href=\"http://www.gameanyone.com?p=video&game=$game&video=".$row['VTitle']."\">".$row['VTitle']."</a></td>
  </tr>
";
}
?>

[artacus]

It doesn't work because you are sorting by a string and not a number. You can do:

SELECT CAST(substring_index(VTitle,'',-1) AS UNSIGNED) AS ID FROM videos ORDER BY ID DESC

or even:
SELECT ROUND(substring_index(VTitle,'',-1)) AS ID FROM videos ORDER BY ID DESC

[helpmeplease2]

The only thing I can get to work is this, but if I were to add a new addition it would go to the end. I want it to be alphabetical and numerically sorted.

<?php
$game=$_GET['game'];
$result = mysql_query("SELECT * FROM videos WHERE GTitle = '$game' ORDER BY ID ASC")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {

echo "
  <tr>
      <td class=\"4\"><a href=\"http://www.gameanyone.com?p=video&game=$game&video=".$row['VTitle']."\">".$row['VTitle']."</a></td>
  </tr>
";
}
?>

[AndyB]

I want it to be alphabetical and numerically sorted.

 

I think we need to see a rational example of half a dozen or so of these 'names' and how they would appear if they were alphabetical and numerically sorted.

[helpmeplease2]

Here is an example

 

Walkthrough Chapter 1: Point Insertion - Part 1
Walkthrough Chapter 1: Point Insertion - Part 2
Walkthrough Chapter 1: Point Insertion - Part 3
Walkthrough Chapter 1: Point Insertion - Part 4
Walkthrough Chapter 2: A Red Letter Day - Part 1
Walkthrough Chapter 2: A Red Letter Day - Part 2
Walkthrough Chapter 2: A Red Letter Day - Part 3
Walkthrough Chapter 3: Route Kanal - Part 1
Walkthrough Chapter 3: Route Kanal - Part 2

 

Ordering them by ID works fine, however If I were to go back and add a part 5 for chapter 1 then it would get messed up. How can I make it so it always sorts them this way no matter when they were added?

[MadTechie]

humm

why not

<?php
$result = mysql_query("SELECT * FROM videos WHERE GTitle = '$game' ORDER BY VTitle ASC")
?>

[helpmeplease2]

I forgot to put in my last post that there are also results like

 

Walkthrough Chapter 30: Chaper Name - Part 21

[MadTechie]

Erm.. ok try

 

<?php
$sql = mysql_query("SELECT *, 
                        CASE WHEN ASCII(RIGHT(`VTitle`, 1)) > 47 AND ASCII(RIGHT(`VTitle`, 1)) < 58
                        THEN LPAD(CONCAT(`VTitle`,  '-'), 52, '0') 
                        ELSE LPAD(`VTitle`, 52, '0') END AS `vsort` 
                        FROM videos ORDER BY `vsort`;");
?>

[MadTechie]

Oh another option is to load them into an array and use natsort();

[bubblegum.anarchy]

Extract the chapter and part number to create decimal values

 

Walkthrough Chapter 2: A Red Letter Day - Part 3

Walkthrough Chapter 3: Route Kanal - Part 1

Walkthrough Chapter 3: Route Kanal - Part 2

Walkthrough Chapter 30: Route Kanal - Part 2

 

would be

2.3

3.1

3.2

30.2

 

cast over to a float and sort as normal.... that would work, right?

[helpmeplease2]

I'm now thinking it would be easier to use decimals in the ID field (if you can do that). Then I could sort the data by the ID field and have it then when I add a new entry I put in a decimal to get it where I want it. This just seems easier than all other suggestions, if it works.

[helpmeplease2]

I quickly found out you cant have a decimal in a key. I made another field named 'sort' and I will set these up (hopefully i can use decimals in these) and sort it by this. It fixed my problem for the moment at least.

 

Thanks for everyones help!