MySQL not arranging primary keys in numeric order.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

MySQL not arranging primary keys in numeric order.

Post by impulse() »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[url]http://stesbox.co.uk/php/db/log.php[/url] That's the output I'm getting.

Code: Select all

include_once("/var/www/html/php/db/logon.php");

mysql_connect($host, $user, $pass);
mysql_select_db($db);

$query = "SELECT * FROM counter";
$results = mysql_query($query);
$numrows = mysql_numrows($results);
$i = 0;

?>
<table border = "1" cellpadding = "1" cellspacing = "1" bgcolor="black">
  <tr>
    <th> <font color="white"> Count </th>
    <th> <font color="white"> IP </th>
  </tr>
<?php

while ($i < $numrows) {
  if (($i%2)==0) { $bgcolor = "#FFFFFF"; } else { $bgcolor = "#c0c0c0";}
    $count = mysql_result($results, $i, "counter");
    $ip = mysql_result($results, $i, "ip");


?>
  <tr>
    <td bgcolor="<? echo $bgcolor; ?><font color="black"><? echo $count; ?></td>
    <td bgcolor="<? echo $bgcolor; ?><font color="black"><? echo $ip; ?></td>
  </tr>
<?php $i++; } ?>
  </table>

The structure is setp so that counter is an int and ip is text. It was setup this way because only the first decimal place of the IP was recorded else.
From that. What's gone wrong here? Is this more related to the structure of the table rather than the coding itsself?

Regards,


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

It depends on the type of table you are using.

InnoDB tables automatically order by the primary key, whereas MyISAM tables keep the data in the order they are entered.

Have a look in the manual to see the various details of the different types of tables available.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

if you want your result set to be ordered in a particular way, use ORDER BY
Post Reply