How to Change the Order of Rows in a Table for MySql

MySQL databases are built on tables. Each table has columns (categories) and rows (entries). The default order of rows is an ascending order along the defined ID column (a special column is specified in each SQL table created to represent the ID of each row inserted). To change a specific row ordering in a table, decide on what column you would like to order it through, and whether the order will be ascending or descending. You can incorporate this in any code as a query.

Things You'll Need

• An editor for a programming language that works with SQL (preferably PHP)

Instructions

1. Open up your preferred editor and write an empty MySQL query function call (that is, “mysql_query();”). Be sure to connect to and select the database beforehand so that the interpreter knows what database to focus attention on--you do this by writing the code for connecting and selecting before the query. The following is an example in PHP:<br /><br />$c = mysql_connect(“localhost”, “root”, “password”)<br />GO<br /><br />mysql_select_db(“testdb”, $c)<br />GO<br /><br />In “mysql_select_db()”, you are selecting the database you would like to use, hence the first parameter. The second parameter is the connection on which to select such a database, in case you are connected to multiple databases. This should always be filled with the proper connection. In “mysql_connect” you pass the address as the first parameter, the account as the second and the password as the third.

2. Write the following in between the parentheses of your empty query function call:<br /><br />SELECT * FROM mytable ORDER BY category ASC<br /><br />Replace “mytable” with the table you would like to select rows from and “category” with the column you would like to use as the ordering column. “ASC” is for ascending order and “DESC” is for descending order.

3. Put the query into practice by assigning it to an array value. For example:<br /><br />$q = mysql_query(“SELECT * FROM Flights ORDER BY flight_number ASC”);

4. Display this value somewhere. Here is an example:<br /><br />while($r = mysql_fetch_array($q)) {<br /> echo $r[“arrival_time”] . “   ”<br />GO<br /> echo $r[“departure_time”] . “   ”<br />GO<br /> echo $r[“destination”] . “   ”<br />GO<br /> echo “<br />”<br />GO<br />}

5. Close the connection to the MySQL server when you are finished with all the queries you needed to do (that is, “mysql_close($c);”).

Tips & Warnings

• Try not to make too many connections simultaneously to the same server, because you might end up overloading the server if too many users try to connect to it. If you can, keep the connections brief and local. This is a good way to improve speed.<br /><br />You can also order by multiple columns. The second column value will be the ordering value if two first-column values are equal.

References

• W3Schools: PHP MySQL Order by Keyword