Gossamer Forum
Home : General : Databases and SQL :

MySQL Query ORDER BY giving weird results

Quote Reply
MySQL Query ORDER BY giving weird results
Hi there Smile,

I have the following table
Code:

ID | Name
1 |
2 |
3 |
4 |
5 |
6 | book
7 | testing
8 | PERL
9 | CSS Mastery
10 | 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
1 |
6 | book
2 |
7 | testing
3 |
8 | PERL
4 |
9 | CSS Mastery
5 |
10 | Cellphones

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

Code:

ID | Name
1 |
2 |
3 |
4 |
5 |
6 | book
10 | Cellphones
9 | CSS Mastery
8 | PERL
7 | 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.
Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
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.
Quote Reply
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.
Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [EZFrag] MySQL Query ORDER BY giving weird results In reply to
Ah, I got it. The query's results are fine. My badFrown .
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?
ThanksSmile


Sacrifice is not about what you lose,
it is about what you gain in the process.
Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] MySQL Query ORDER BY giving weird results In reply to
Sort on hash? Ok, I'll google that.
Thanks AndyWink


Sacrifice is not about what you lose,
it is about what you gain in the process.
Quote Reply
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Last edited by:

Andy: Jun 6, 2008, 3:43 AM
Quote Reply
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
Quote Reply
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
Quote Reply
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.
Quote Reply
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