Sorting Numerically and then Alphabetically in Certain Format

[abney317]

Trying to find an answer for this question here: http://stackoverflow.com/questions/7547960/sorting-displaying-by-numeric-and-then-alphabetically

 

If any of you guys can't help that would be great

[WebStyles]

you can simply order by that field, and while retrieving, check if the first character equals the previous, so you know when to change lines of add a slash. Basically you need to keep track of the first character... since the field is a string, you can grab the first character with $varname[0].

[PFMaBiSmAd]

select *, dataID * 1 as ord FROM data order by dataID * 1, dataID

 

You can detect the change in the $row['ord'] value to start a new section.

[Psycho]

select *, dataID * 1 as ord FROM data order by dataID * 1, dataID

 

You can detect the change in the $row['ord'] value to start a new section.

 

I was going to suggest adding a new column for the numeric value of the dataID to use for sorting purposes. That is easier. However, if you are going to give the dynamic field a reference, might as well use it in the ORDER BY clause.

SELECT *, dataID * 1 AS ord FROM data ORDER BY ord, dataID

[abney317]

well that sorts them correctly (can someone explain how that SQL is actually working? haven't seen that method before)

 

but still I'm not sure how to get them showing correctly.... I can't just check the first number because 10 and 11 both start with 1... I guess I would need a regular expression or something to just get the number out to compare it with the previous number

 

Edit: lol I'm stupid... that's what "ord" is for I guess... still how does "dataID * 1" pull out the number?

[PFMaBiSmAd]

By multiplying the field contents by 1, it is converted to a number and only the leading numerical value is used.

 

<?php

$query = "select dataID, dataID * 1 as ord FROM data order by ord, dataID";
$result = mysql_query($query);

$heading = null; // remember last heading, initialize to a value that will never exist as data
while(list($dataID,$ord) = mysql_fetch_row($result)){
if($heading != $ord){
	// a new (or first) heading found
	if($heading != null){
		// not the first heading, close out the previous section
		echo implode (' / ',$data) . '<br />';
	}
	$heading = $ord; // remember new heading
	// start a new section
	$data = array();
}
// handle each piece of data
$data[] = $dataID;
}
// close out the last section
echo implode (' / ',$data) . '<br />';

[abney317]

By multiplying the field contents by 1, it is converted to a number and only the leading numerical value is used.

 

<?php

$query = "select dataID, dataID * 1 as ord FROM data order by ord, dataID";
$result = mysql_query($query);

$heading = null; // remember last heading, initialize to a value that will never exist as data
while(list($dataID,$ord) = mysql_fetch_row($result)){
if($heading != $ord){
	// a new (or first) heading found
	if($heading != null){
		// not the first heading, close out the previous section
		echo implode (' / ',$data) . '<br />';
	}
	$heading = $ord; // remember new heading
	// start a new section
	$data = array();
}
// handle each piece of data
$data[] = $dataID;
}
// close out the last section
echo implode (' / ',$data) . '<br />';

 

Awesome

Works perfectly and makes sense... really appreciate the help