Weird Sorting Problem

[raouleduke]

I am sorting using ASC and it turns out like this, any ideas, it should sort by all the A's first then by number. The database is VARCHAR. Any ideas?

A665B 
G514A 
G569A 
G812A 
G813A 
G819A 
G821A 
A1096A 
A1119A 

[TheDefender]

I wonder if it has anything to do with the fact the first of them have 5 characters and the last 2 have 6... It appears as though the sorting actually sees it more like this:

A665B

G514A

G569A

G812A

G813A

G819A

_G821A

A1096A

A1119A

To compensate for them not being the same length...

[raouleduke]

Do you know how I can just sort by the first two characters. I did switch it to text and it did sort correctly, but still with the length issue.

[TheDefender]

Try something like this:

SELECT SUBSTRING(field,0,2) AS subfield, field FROM table ORDER BY subfield ASC

[raouleduke]

I cannot add a substr to a query, it is a PHP function not Mysql. All I get is an error

[TheDefender]

Yeah, I am sorry, it should be SUBSTRING not SUBSTR. My bad, I corrected it above for you.

[raouleduke]

I tried using TRIM to Remove the empty spaces, SUBSTRING works, for the letters, but after that the numbers are diferent. Shouldn't SUBSTRING sort by the first two or three, SUBSTRING(STOCK, 0,3). Any more ideas.

[raouleduke]

The more I look around it looks like natsort is what I need to use anyone know how I would add it to this, the manual is not very explicit on what to do. I am trying to sort by stock #, and the STock #'s look like this
A1111
A3219
G987
3234433

All different lengths, and letters and numbers.

$query = "SELECT * FROM inventory WHERE TYPE ='U' ORDER BY STOCK ASC";   

$result = mysql_query($query);

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


$stock = $myrow['STOCK']; 

print "$stock <br>";
} 

[raouleduke]

This does what I want but seems to leave out the ones with only numbers, 1234455 and only includes the A123P ones.

$query = "SELECT * FROM inventory WHERE TYPE ='U'";   

  if($sort == 'fresh'){
   $query.=" AND FRESH = '1' AND ACTIVE = '0'"; 
  }
   if($sort == 'nopictures'){
   $query.=" AND IMAGE_LOCATION = 'blank.jpg' AND ACTIVE = '1'"; 
  }
  if($sort == 'active'){
   $query.=" AND ACTIVE = '1' "; 
  }
  if($sort == 'nonactive'){
   $query.=" AND ACTIVE = '0' AND FRESH = '0'"; 
  }
$query.=" ORDER BY STOCK ASC";
$result = mysql_query($query);

$c=0; 
while ($rst = mysql_fetch_array($result)) { 
    $gen_array[$c][stock] = $rst[STOCK]; 
    $gen_array[$c][vin] = $rst[VIN]; 
	$gen_array[$c][make] = $rst[MAKE]; 
	$gen_array[$c][model] = $rst[MODEL]; 
	$gen_array[$c][year] = $rst[YEAR]; 
	$gen_array[$c][color] = $rst[EXTERIORCOLOR]; 
	$gen_array[$c][price] = $rst[PRICE]; 
    $c++; 

} 

$v=0; 
while ($v<$c) { 
    $gen2_array[] = $gen_array[$v][stock]; 
    $v++;    

} 

natsort($gen2_array); 

foreach($gen2_array as $key => $val) { 

	if ($bgcolor == "#FFFFFF"){ 
        $bgcolor = "#CCCCCC"; 
	}else{ 
        $bgcolor = "#FFFFFF";
	}  

echo "<table width='650' height='40' border='0' cellspacing='0' cellpadding='0' bgcolor=".$bgcolor." >
  <tr>
	<td width='50'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>$val</font><div></td>
    <td width='50'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][year] ."</font><div></td>
	<td width='100'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][make] ."</font><div></td>
    <td width='100'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][model] ."</font><div></td>
    <td width='150'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][vin] ."</font><div></td>
  	<td width='100'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][color] ."</font><div></td>
    <td width='100'><div align='center'><font size='2' color='000000' face='Verdana, Arial, Helvetica, sans-serif'>" .$gen_array[$key][price] ."</font><div></td>

  </tr>
</table>";

}

Any ideas why it would be doing that.