The default order of rows in mysql

When I open the table in mysql it usually displays the rows in an odd order. Here i an example:

id || name || pass
--------------------
1 name pass
502 name pass
3 name pass
4 name pass
77 name pass

PRIMARY KEY (`id`).

Why are they not in order? Also, when I change the id row, it stays in the same place... Why aren't they automatically arranged? What can I do to change it?

 

You can use ORDER by

"SELECT * FROM customer ORDER BY customer_id DESC ";

Code (markup):

 

You didn't understand my question. I want to know what determines the order they appear when I view the table in mysql. I know how to arrange the rows, that's not what I'm asking. I'm asking why aren't they arranged by the ID field by default. They are all messed up.

 

that happens on phpMyAdmin when you add more rows after deleting some. If you Optimize your table, it should be ok.

 

Just tried. Didn't work..

I found this:

But I don't know how to use it.

 

try this and check your database after. Put off and on your browser

// connect to your database first

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("ANALYZE TABLE `".$tablename."`")
       or die(mysql_error());
       mysql_query("OPTIMIZE TABLE `".$tablename."`")
       or die(mysql_error());
   }
}

Code (markup):

 

I don't think it worked. Didn't get any errors though...

 

i just run it on my localhost and it worked perfect. did you check your database table after wards?

 

I also tried on my local server. I might have used the script wrong. I just added $table = 'users';

I really don't understand how the code is supposed to work.

 

edit the database details and save it and run it

<?
$DBHost="localhost";
$DBUser="root";
$DBPass="";
$DBName="";
$DBCon=mysql_connect($DBHost,$DBUser,$DBPass) or die("Failed to Connect with Database.");
mysql_select_db($DBName,$DBCon);
// connect to your database first

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("ANALYZE TABLE `".$tablename."`")
       or die(mysql_error());
	   echo "$tablename analyzed <br>";
       mysql_query("OPTIMIZE TABLE `".$tablename."`")
       or die(mysql_error());
	   echo "$tablename optimized <br>";
   }
}
?>

PHP:

 

Oh, it's supposed to do it to all of the tables.

OK now the script itself did work (saw the log). But when I view the rows nothing has changed. They appear the same way in a program I use to view the db as well as myphpadmin. I've closed/refreshed both - nothing.

 

ALTER TABLE `users` ORDER BY `id`

Did it. Thanks for the help.