Gossamer Forum: General: Databases and SQL: MySQL Query ORDER BY giving weird results

Jun 4, 2008, 7:48 AM

EZFrag

User (141 posts)

Jun 4, 2008, 7:48 AM

Post #1 of 13

Shortcut

MySQL Query ORDER BY giving weird results

Hi there

,

I have the following table

Code:

 ID |    Name
 | 
 | 
 | 
 | 
 |  
 | book 
 | testing 
 | PERL 
 | CSS Mastery 
| Cellphones

When I do a SELECT ID,Name FROM table ORDER BY Name; using a CGI Script, I get the following result:

Code:
 ID |    Name 
 | 
 | book 
 | 
 | testing 
 | 
 | PERL 
 | 
 | CSS Mastery 
 |  
| Cellphones

Now if I use SQL Monitor, The expected results are returned:

Code:

 ID |    Name
 | 
 | 
 | 
 | 
 |  
 | book 
| Cellphones 
 | CSS Mastery 
 | PERL 
 | testing

Is there something I am missing here?

Thanks Unsure

Sacrifice is not about what you lose,
it is about what you gain in the process.

Jun 4, 2008, 8:07 AM

Andy

Veteran / Moderator (18436 posts)

Jun 4, 2008, 8:07 AM

Post #2 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

Hik,

Is this being done with GT::SQL ($DB), or some other method? Not sure why the results would sort like that (especially if they are showing up fine in SQLMonrtor)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 4, 2008, 11:10 AM

Wychwood

User (182 posts)

Jun 4, 2008, 11:10 AM

Post #3 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

It might be to do with the different cases, i.e. mixture of upper and lower. Perhaps MySQLMan does something internally to correct it.

Jun 4, 2008, 11:37 PM

EZFrag

User (141 posts)

Jun 4, 2008, 11:37 PM

Post #4 of 13

Shortcut

Re: [Andy] MySQL Query ORDER BY giving weird results In reply to

Yes, I use GT::SQL,
$DB->table('Links')->do_query();

Quite weird though, maybe there is something I missed or my query might need some tuning.

Thanks Andy

Sacrifice is not about what you lose,
it is about what you gain in the process.

Jun 4, 2008, 11:57 PM

Andy

Veteran / Moderator (18436 posts)

Jun 4, 2008, 11:57 PM

Post #5 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

What version of mySQL are you on? 4 or 5?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 5, 2008, 12:23 AM

EZFrag

User (141 posts)

Jun 5, 2008, 12:23 AM

Post #6 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

Ah, I got it. The query's results are fine. My bad Frown

.
The problem here was, later in the script I moved the rows to variables for a template.
in the process I used:

Code:
   foreach my $key (keys (%locs)) { 
                        push (@searchRes,  
                                        {  
                                            'LocID'         => $locs{$key }->{'triplink_id'}, 
                                            'LocBMID'       => $locs{$key }->{'triplink_bm_id'}, 
                                            'LocUser'       => $locs{$key }->{'triplink_username'}, 
                                            'LocComment'    => $locs{$key }->{'triplink_comment'}, 
                                            'LocTitle'      => $locs{$key }->{'triplink_title'}, 
                                            'LocType'       => $locs{$key }->{'triplink_type'}, 
                                            'LocUrl'        => $locs{$key }->{'triplink_url'}, 
                                        }, 
                            );     
  }

Apparently it doesn't get the data in sequence (the Dump for $key gave me 4,8,3,7,2,6,1,5,0)
I fixed it by doing the following:

Code:

 $d = 0; 
                        foreach my $key (keys (%locs)) { 

                            push (@searchRes,  
                                        {  
                                            'LocID'         => $locs{$d}->{'triplink_id'}, 
                                            'LocBMID'       => $locs{$d}->{'triplink_bm_id'}, 
                                            'LocUser'       => $locs{$d}->{'triplink_username'}, 
                                            'LocComment'    => $locs{$d}->{'triplink_comment'}, 
                                            'LocTitle'      => $locs{$d}->{'triplink_title'}, 
                                            'LocType'       => $locs{$d}->{'triplink_type'}, 
                                            'LocUrl'        => $locs{$d}->{'triplink_url'}, 
                                        }, 
                            ); 
$d++;
                        }

The Dump for $d gives me 0,1,2,3,4,5,6,7,8 and $key is still 4,8,3,7,2,6,1,5,0.

Is there an explanation on why the sequence is changed?
Thanks Smile

Sacrifice is not about what you lose,
it is about what you gain in the process.

Jun 5, 2008, 1:13 AM

Andy

Veteran / Moderator (18436 posts)

Jun 5, 2008, 1:13 AM

Post #7 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

Hi,

I expect it may be down to needing a "sort" on the hash (loads of stuff on Google about it - just search on "perl sort hash").

Either way though, if its working as you have it now - you may as well leave it like that =)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 5, 2008, 2:34 AM

EZFrag

User (141 posts)

Jun 5, 2008, 2:34 AM

Post #8 of 13

Shortcut

Re: [Andy] MySQL Query ORDER BY giving weird results In reply to

Sort on hash? Ok, I'll google that.
Thanks Andy Wink

Sacrifice is not about what you lose,
it is about what you gain in the process.

Jun 5, 2008, 2:39 AM

Andy

Veteran / Moderator (18436 posts)

Jun 5, 2008, 2:39 AM

Post #9 of 13

Shortcut

Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to

Yeah, can't remember (been a long day ;)) the exact syntax - but I think its something like:

foreach my $key (sort keys (%locs)) {

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Last edited by:

Andy: Jun 6, 2008, 3:43 AM

Jun 5, 2008, 8:39 PM

dan

Enthusiast (760 posts)

Jun 5, 2008, 8:39 PM

Post #10 of 13

Shortcut

Re: [Wychwood] MySQL Query ORDER BY giving weird results In reply to

Let's play nice =)

Perl hashes have no internal ordering. By design, the results are returned in a random order. One solution may be Tie::IxHash. Another solution is to retrieve and map the original hash keys to an array, and use that array to iterate the hash.

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
GoodPassRobot
Magelln

Jun 6, 2008, 2:27 AM

Wychwood

User (182 posts)

Jun 6, 2008, 2:27 AM

Post #11 of 13

Shortcut

Re: [dan] MySQL Query ORDER BY giving weird results In reply to

I think I made a very fair point and then "someone" deleted my post about Andy calling himself a Perl programmer in his profile and yet not knowing how to sort a hash...one of the fundamentals on being a Perl programmer.

Interesting.

To me that's like an electrician not knowing how to wire a plug.

Very bizarre.

Last edited by:

Wychwood: Jun 6, 2008, 2:28 AM

Jun 9, 2008, 11:13 AM

SandraR

User (424 posts)

Jun 9, 2008, 11:13 AM

Post #12 of 13

Shortcut

Re: [Wychwood] MySQL Query ORDER BY giving weird results In reply to

Quote:

Very bizarre.

The only thing Bizarre is your bad manners on this forum where every one gets along.

Jun 10, 2008, 2:26 AM

Wychwood

User (182 posts)

Jun 10, 2008, 2:26 AM

Post #13 of 13

Shortcut

Re: [SandraR] MySQL Query ORDER BY giving weird results In reply to

There's a distinct difference between bad manners and stating the truth.

It's understandable that you jump to his defence - you rely on him as the level of support from Gossamer-Threads on this forum is appalling.

Last edited by:

Wychwood: Jun 10, 2008, 2:27 AM