1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

The default order of rows in mysql

Discussion in 'PHP' started by x0x, May 17, 2011.

  1. #1
    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?
     
    x0x, May 17, 2011 IP
  2. baris22

    baris22 Active Member

    Messages:
    543
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #2
    You can use ORDER by

    
    
    "SELECT * FROM customer ORDER BY customer_id DESC ";
    
    
    Code (markup):
     
    baris22, May 17, 2011 IP
  3. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #3
    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.
     
    x0x, May 17, 2011 IP
  4. baris22

    baris22 Active Member

    Messages:
    543
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #4
    that happens on phpMyAdmin when you add more rows after deleting some. If you Optimize your table, it should be ok.
     
    baris22, May 17, 2011 IP
  5. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #5
    Just tried. Didn't work..

    I found this:

    But I don't know how to use it.
     
    x0x, May 17, 2011 IP
  6. baris22

    baris22 Active Member

    Messages:
    543
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #6
    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):
     
    baris22, May 17, 2011 IP
  7. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #7
    I don't think it worked. Didn't get any errors though...
     
    x0x, May 17, 2011 IP
  8. baris22

    baris22 Active Member

    Messages:
    543
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #8
    i just run it on my localhost and it worked perfect. did you check your database table after wards?
     
    baris22, May 17, 2011 IP
  9. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #9
    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.
     
    x0x, May 17, 2011 IP
  10. baris22

    baris22 Active Member

    Messages:
    543
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #10
    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:
     
    baris22, May 17, 2011 IP
  11. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #11
    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.
     
    x0x, May 17, 2011 IP
  12. x0x

    x0x Well-Known Member

    Messages:
    510
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    110
    #12
    ALTER TABLE `users` ORDER BY `id`

    Did it. Thanks for the help.
     
    x0x, May 17, 2011 IP