|
Home > Archive > MySQL ODBC Connector > September 2005 > ORDER BY for ints
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Stephen A. Cochran Lists 2005-09-27, 3:23 am |
|
I'm getting a strange ordering when using ORDER BY on a int column.
The rows are being returned sorted as follows:
1
10
11
12
13
14
15
2
3
4
5
6
7
8
9
I'm sure this is a simple one, but I haven't found an answer in the
archives.
Thanks,
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jigal van Hemert 2005-09-27, 3:23 am |
| Stephen A. Cochran Lists wrote:
>
> I'm getting a strange ordering when using ORDER BY on a int column.
>
> The rows are being returned sorted as follows:
The list is typically the way to order a string.
You are most likely to get meaningful suggestions to solve the mystery
if you include the table definition (output of SHOW CREATE TABLE
<tablename> ) and the query.
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Stephen A. Cochran Lists 2005-09-27, 3:23 am |
|
On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote:
> You are most likely to get meaningful suggestions to solve the
> mystery if you include the table definition (output of SHOW CREATE
> TABLE <tablename> ) and the query.
mysql> SHOW CREATE TABLE Player|
+--------
+-----------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
| Table | Create
Table
|
+--------
+-----------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name` varchar(32) NOT NULL default '',
`year` varchar(16) NOT NULL default '',
`height` varchar(8) NOT NULL default '',
`season` int(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `season` (`season`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------
+-----------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
1 row in set (0.00 sec)
It now looks like mysql is returning the correct thing (at least on
the command line), but for some reason inside php it's all screwedup
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jasper Bryant-Greene 2005-09-27, 3:23 am |
| Stephen A. Cochran Lists wrote:
>
> On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote:
>
>
>
> mysql> SHOW CREATE TABLE Player|
> | Player | CREATE TABLE `Player` (
> `id` int(16) NOT NULL auto_increment,
> `first_name` varchar(32) NOT NULL default '',
> `last_name` varchar(32) NOT NULL default '',
> `year` varchar(16) NOT NULL default '',
> `height` varchar(8) NOT NULL default '',
> `season` int(4) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `season` (`season`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>
> It now looks like mysql is returning the correct thing (at least on the
> command line), but for some reason inside php it's all screwedup
What column are you ordering on?
--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Stephen A. Cochran Lists 2005-09-27, 9:23 am |
|
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
> mysql> SHOW CREATE TABLE Player|
> | Player | CREATE TABLE `Player` (
> `id` int(16) NOT NULL auto_increment,
> `first_name` varchar(32) NOT NULL default '',
> `last_name` varchar(32) NOT NULL default '',
> `year` varchar(16) NOT NULL default '',
> `height` varchar(8) NOT NULL default '',
> `season` int(4) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `season` (`season`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>
> What column are you ordering on?
The command in PHP is:
$query="SELECT id,first_name,last_n
ame FROM Player ORDER BY id";
$players=mysql_query
($query);
When issued from the mysql prompt, order is fine, but when called
from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Michael Stassen 2005-09-27, 9:23 am |
| Stephen A. Cochran Lists wrote:
>
> On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
>
>
> The command in PHP is:
>
> $query="SELECT id,first_name,last_n
ame FROM Player ORDER BY id";
> $players=mysql_query
($query);
>
> When issued from the mysql prompt, order is fine, but when called from
> php I'm getting that strange order:
>
> 1, 10, 11, 12, etc...
>
> Steve Cochran
Then the problem is in your php code. Mysql will certainly return the rows
ordered the same way to both the mysql client and to php. If php is showing
a different order, then it must be something your php code is doing. If you
post the code which displays the results, I'm sure someone could point out
the problem, though that really belongs on a php list.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Edward Vermillion 2005-09-27, 9:23 am |
| Michael Stassen wrote:
> Stephen A. Cochran Lists wrote:
>
>
>
> Then the problem is in your php code. Mysql will certainly return the
> rows ordered the same way to both the mysql client and to php. If php
> is showing a different order, then it must be something your php code is
> doing. If you post the code which displays the results, I'm sure
> someone could point out the problem, though that really belongs on a php
> list.
>
> Michael
>
>
I had this same problem a while back, and while I'm probably making the
same mistakes you are but have no idea what they are, I solved it by
using ZEROFILL on the field I was sorting. So that PHP was seeing 0001,
0002, 0003...
Worked for me, although from some of the replies I'm wondering if that
wasn't the best way to do it. :/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Stephen A. Cochran Lists 2005-09-27, 9:23 am |
|
On Sep 27, 2005, at 9:56 AM, Edward Vermillion wrote:
> Michael Stassen wrote:
>
>
> I had this same problem a while back, and while I'm probably making
> the same mistakes you are but have no idea what they are, I solved
> it by using ZEROFILL on the field I was sorting. So that PHP was
> seeing 0001, 0002, 0003...
>
> Worked for me, although from some of the replies I'm wondering if
> that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query="SELECT id,first_name,last_n
ame FROM Player ORDER BY id";
$players=mysql_query
($query);
$numPlayers=mysql_nu
mrows($players);
for ($i=0, $i < $numPlayers; $i++)
{
$label = mysql_result($player
s,$i,'id');
echo "$label<br>"
}
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
Thanks in advance.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Pooly 2005-09-27, 11:23 am |
| Hi,
>
> Well, since I wasn't the only person to have this problem, I'll post
> this here in case someone has the answer. My php code is:
>
> $query=3D"SELECT id,first_name,last_n
ame FROM Player ORDER BY id";
> $players=3Dmysql_que
ry($query);
> $numPlayers=3Dmysql_
numrows($players);
> for ($i=3D0, $i < $numPlayers; $i++)
> {
> $label =3D mysql_result($player
s,$i,'id');
> echo "$label<br>"
> }
Try with mysql_fetch_array
>
> And that generates an order like it was doing a string comparison.
> I'm just iterating over the rows in the result in order, so not sure
> what would be applying another sort.
or it's likely that mysql_result retrieve an array of rows
(well-ordered), but fetch it by using a string for the index.
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Stephen A. Cochran Lists 2005-09-27, 11:23 am |
|
On Sep 27, 2005, at 10:28 AM, Pooly wrote:
>
> Try with mysql_fetch_array
>
>
>
> or it's likely that mysql_result retrieve an array of rows
> (well-ordered), but fetch it by using a string for the index.
This seems unlikely since the mysql_result takes a row number (int)
to select which fetched row to get a cell from (zero based).
Using mysql_fetch_array woulnd't work since I need to select a
certain row based on the order by, but not necessarily accessed in
sequence as shown in the code above.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|