Unable to get Appropriate Sorted Results from MySQL

[Dev Sherpa]

In my php app, I have stored a column 'plotno' in mysql table 'plots' of format varchar(10)

Plot Numbers are in Following Format

2-D
1-A
2-C
2-A
1-B
15-R

When I give following query:

SELECT plotno FROM plots ORDER BY plotno

I get following results:

1-A
1-B
15-R
2-A
2-C
2-D

But I am looking for result like this:

1-A
1-B
2-A
2-C
2-D
15-R

What should I change in my query to get this result?

[Ovidiu Alexa]

you should make two table heads:

table plots:
id, section;

query:

SELECT CONCAT(id, "-", section)
FROM plots
ORDER BY id, section

Cheers!

> --
> This group is managed and maintained by the development staff at 360
> PSG. An enterprise application development company utilizing
> open-source technologies for todays small-to-medium size businesses.
>
> For information or project assistance please visit :
> http://www.360psg.com
>
> You received this message because you are subscribed to the Google
> Groups "Professional PHP Developers" group.
> To post to this group, send email to Professi...@googlegroups.com
> To unsubscribe from this group, send email to
> Professional-P...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Professional-PHP

[Ovidiu Alexa]

OH yeah, the table:

CREATE TABLE `besttour_bt2`.`plots` (

`ref` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id` INT( 10 ) NOT NULL ,
`section` VARCHAR( 255 ) NOT NULL ,
INDEX ( `id` )

) ENGINE = MYISAM ;

SELECT CONCAT(id, "-", section)
FROM plots
ORDER BY id, section

Cheers!


On 8/30/2010 15:45, Dev Sherpa wrote:

[Jeremy Wauters]

Or use sort(), msort() or asort() on your array depending on situation.

Jeremy

[Ovidiu Alexa]

Oh, yeah, AS plotno

On 8/30/2010 16:02, Ovidiu Alexa wrote:

OH yeah, the table:

CREATE TABLE `plots` (

`ref` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id` INT( 10 ) NOT NULL ,
`section` VARCHAR( 255 ) NOT NULL ,
INDEX ( `id` )

) ENGINE = MYISAM ;

SELECT CONCAT(id, "-", section) AS pltono

[Dev Sherpa]

Hi Jeremy, That is after fetching the results from mysql to php. Instead I am trying to do this in query itself.

Hi Ovidiu, Yes, I can do that If I am developing an application which is from scratch but at this moment there are millions of record in table as of now and I do not want to alter table structure. Is there any alternative way?

Regards
Dev

[Dev Sherpa]

Thanks for quick reply.

If I need to do this in PHP, I could use follwoing:

// loop to fetch msyql_array untill all records come into $ROWS array
$ROWS = mysql_fetch_array($rs);

// and then using following function:
sort($ROWS, 'SORT_NUMERIC');

I am trying to get some sorting function to sort varchar field numerically in mysql which I can add into query itself with some parameter like 'SORT_NUMERIC' in php.

Please help me.

Regards
Dev

[Ovidiu Alexa]

Hi, have you tried this?

SELECT plotno FROM plots ORDER BY plotno+0

[Dev Sherpa]

Thanks Ovidiu Alexa,

It worked.

Regards
Dev

[Ovidiu Alexa]

JUST FOR SEARCH INDEXING: (I may need this in the future ;P)

tag line: order string as integer
tag line: order string as number
tag line: order text as integer
tag line: order text as number

[Dev Sherpa]

There is a different situation also i.e. plot_no is varchar and it contains in following format:

blockname-plotnoinblock e.g. S-301 where S is block name and 301 is a plot no in block.

Also some plot no have same number seperated by a,b,c etc
e.g. S-259C, S-259A

Here are some of the plots number mentioned as below:

S-301
S-312B
S-307
S-259C
S-259A
S-236
S-236A
S-236B
Q-424
F-126
F-7
F-106A

How to sort these numerically so that we can results like this:

F-7
F-106A
F-126
Q-424
S-236
S-236A
S-236B
S-259A
S-259C
S-301
S-307
S-312B

Regards
Dev

[Robert Gonzalez]

You might have to do this with regular expressions in your query. And I have to say that the table structure is terrible if that is the way that data is stored. You really should look at ways to move away from that structure into a more normalized structure for your data.

[Ovidiu Alexa]

you could use temporary tables to separate the values just for sorting, then assign an id for each one so later you can compile them in the right order:

id=''

s-301
s-312B
q-424
f-126

$sql="SELECT  id FROM the_table";

$val=get_data_here (I use a personal function to put all the results in an array);

so $val=askDB($sql);
$t_v=count($val);

for($x=0; $x<$t_v; $x++)
    {
    $tmp=explode('-', $val[$x]);
    $val_1=$tmp[0];
    $val_2=intval($tmp[1]);
    $val_3=substr($string, strlen($val_2), (strlen($tmp[1])-intval($tmp[1])));
    $sql="INSERT INTO tmp_table SET a='".$val_1."', b='".$val_2."',c='".$val_3."' ";
    mysql_query($sql);
    }
   
    $sql="SELECT CONCAT(a, '-', b, c) as final_id FROM tmp_table ORDER BY a, b, c";
    $result=askDB($sql);


THIS CODE IS NOT TESTED! DON'T REPLY TO ME IF IT DOES NOT WORK. It's merely an approach to your problem.

Let us know if it works and what did you manage to do.

Cheers!

[Ovidiu Alexa]

CORRECTION $val_3=substr($string, strlen($val_2), (strlen($tmp[1])-intval($tmp[1]))); WRONG

$val_3=substr($tmp[1], strlen($val_2), (strlen($tmp[1])-intval($tmp[1]))); CORRECT :-D

[Robert Gonzalez]

I just ran this query in my MySQL database and got what you are after (`plot` is the column name that contains your crazy string that you need to blow up and sort by its parts):

select 

  SUBSTR(`plot`, 1, LOCATE('-', `plot`)-1) AS leadchar, 

  SUBSTR(`plot`, LOCATE('-', `plot`)+1) AS endchar, 

  plot 

from 

  test /* The name of the table to select from */

ORDER BY leadchar, endchar

[Robert Gonzalez]

My data return was:

F 106A F-106A

F 126 F-126

F 7 F-7

Q 424 Q-424

S 236 S-236

S 236A S-236A

S 236B S-236B

[schmar04]

Hi,

you can use SQL-Order-Statement like this:

SELECT ...
....
ORDER BY (CASE WHEN CAST(SUBSTR(plotno ,3) AS SIGNED) > 0 THEN
CAST(SUBSTR(plotno ,3) AS SIGNED) ELSE CAST (plotno AS SIGNED) END);


But check the performance ... could be quite slow ;-)

> > On Mon, Aug 30, 2010 at 6:42 PM, Ovidiu Alexa <ovidiu.al...@gmail.com>wrote:
>
> >>  Hi, have you tried this?
>
> >> SELECT plotno FROM plots ORDER BY plotno+0
>
> >> On 8/30/2010 16:07, Dev Sherpa wrote:
>
> >> Hi Jeremy, That is after fetching the results from mysql to php. Instead I
> >> am trying to do this in query itself.
>
> >> Hi Ovidiu, Yes, I can do that If I am developing an application which is
> >> from scratch but at this moment there are millions of record in table as of
> >> now and I do not want to alter table structure. Is there any alternative
> >> way?
>
> >> Regards
> >> Dev
>

> >> On Mon, Aug 30, 2010 at 6:34 PM, Jeremy Wauters <docbr...@gmail.com>wrote:
>
> >>> Or use sort(), msort() or asort() on your array depending on situation.
>
> >>> Jeremy
>