sorting question

[Odoggy5]

I am using a character field in one of my tables(MySQL) and using numbers in that field also how can make the #'s come out in numerical order?

ex.

cnfg1, cnfg2, cnfg3...cnfg10, cnfg11, cnfg12

my query sorts them like

cnfg1, cnfg10, cnfg11, cnfg12, cnfg2, cnfg20, cnfg21...

i want them like

cnfg1, cnfg2, cnfg3, cnfg4,...cnfg11, cnfg12, cnfg13

is there a way for me to do that or will i have to have seperate fields?

[sde]

i don't think mysql has a way to sort results like that, however you can make php do the work with natsort()

PHP Code:

<?
$array = array();

$result = mysql_query("select configname from configs");

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

  $array[] = $row['configname'];
}

natsort($array);

foreach($array as $each) {

  echo $each . "<br />\n";
}

?>

[Odoggy5]

dude,

sde, you are the man! thank you so much!

[Odoggy5]

one more question, how can i display the entire row?

[teknomage1]

If this were perl, it'd be a job for the Schwartzian Transform, but it's not so it depends on your data.

If the numbers you have are unique, you can build an array that has the field to sort on as the keys, and the rows of data as the values, sort the numbers and then iterate over the array of sorted numbers as keys to the array, pulling out the now sorted data.

There are a few implementations of this here . Otherwise you may have to add a decimal place or something to each number to ensure the keys are unique.

[sde]

PHP Code:

<?
$row_array = array();
$key_array = array();

$result = mysql_query("select configname from configs");

while($row = mysql_fetch_assoc($result)) {
    
    // create an array of cofig names so you can
    // use the natsort later
    $key_array[] = $row['configname'];
    
    // create an array of the rows using the
    // config name as the key
    $row_array[$row['configname']] = $row;
  
}

natsort($key_array);

foreach($key_array as $key) {

  // now you can access the row
  // by using the $key var
  
  $config = $row_array[$key];
}

?>

[Odoggy5]

i found a query that worked it out for me.

$query = 'SELECT * FROM memfiles WHERE `file` LIKE \'%psms_cnfg%\''
        . ' ORDER BY LPAD(field,7,"0")';

[technobard]

A different question is do you really need the character portion in the field? If this is a table storing multiple sets of type codes, for example, it makes sense. Anyway, the other way around the LPAD in the sort is to store the data with leading zeros out to the maximum length you'll ever see in your lifetime. You get the same result, but the ORDER by will do the sort without a function on the field.

Just my 2 cents.

[Odoggy5]

thats how i was originally going to do it, with the leading zeroes. however i have a page where all of my cnfgs are listed in a table. then i have another area where users can do a search on a particular cnfg. the user knows the cnfg as cnfg1 and not cnfg001. and, yes, i am using multiple sets of type codes besides for just cnfg(i don't know if that was what you were asking).

[teknomage1]

Well, if you still want to store items with leading zeroes but not show them to the user, you could transform the field before the user sees it.

PHP Code:

preg_replace( "/([A-Za-z]+)0*(\d+)/", "$1$2", $configname); 


[sde]

i'm wondering why there is a number in the name anyway. why don't you just use a field in the table for a config number?

[Odoggy5]

i could do that but was trying to avoid it. you see there is more than just "cnfg" in that table. i also have prcnfg, nwcnfg, etc...so there would prcnfg1, cnfg1, nwcnfg1 and so on, all in the same table. and i want it to be unique so that there cannot be two cnfg1 entries in the table. does it make any sense?

[sde]

i need schema to know what you're talking about. i don't know if you're storing just names of configs, or the text of the configs in ths table. you say prcnfg1, cnfg1, nwcfng1 in the same table.. do you mean in the same row of the table?

[Odoggy5]

my columns are as follows:

field name-stores the name of the config(ex. cnfg1)
definition-stores the defintion of the file(ex. .T. = credit cards are on .F. = credit cards are off)
file-store the name of the file where the config is held(ex. psms_cnfg.mem)

its a pretty basic little table, but to answer your question no two configs are ever in the same row.(obvisouly!)

[teknomage1]

I think sde is suggesting that you could rearrange your table to look like:
name - cnfg, prcnfg, etc
number - 1, 2, 3, ... 8000, etc
definition
file

Such that to generate the full name cnfg1 it's a combination of 2 fields. THat way you can let the database handle storing the numbers and sorting properly, and also easily get all of a specific type of cnfg file.