altering mysql column definition

Have set my database and it was the perfect database I have ever designed(perhaps) but how about trying to edit the columns?

Here is the code:

alter table account modify `id` int(11) NOT NULL AUTO_INCREMENT;

Note: the id column is bigint(9) before the transformation.

resetting the auto_increment field in mysql

Oftentimes one needs to test web application to see if it works. But during those times one doesn’t have to retain all of the test data and after you delete the test data the auto_increment field(mostly id’s) don’t reset itself.

Here is a way to reset the auto_increment column in your mysql database:

alter table account auto_increment=4;

Guessing that I have 3 accounts I wanted to retain.

find the next auto_increment number in mysql

Finding the next auto_increment is easy on phpmyadmin. Just select your database from the left side dropdown box. Then select the table on the lower part of it. Then click structure.

But what if it’s in mysql? or in PHP?
In PHP:

<?
$tablename         = “tablename”;
$next_increment     = 0;
$qShowStatus         = “SHOW TABLE STATUS LIKE ‘$tablename’”;
$qShowStatusResult     = mysql_query($qShowStatus) or die ( “Query failed: ” . mysql_error() . “<br/>” . $qShowStatus );

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

echo “next increment number: [$next_increment]“;
?>

source: http://blog.jamiedoris.com/geek/560/

In MySQL prompt:

SHOW TABLE STATUS LIKE ‘tablename’;

rearrange columns in mysql

Suppose I have a table with:

id int(11) auto_increment,
date datetime,
created_at datetime

and want’s to have this arrangement:

id int(11) auto_increment,
created_at datetim,
date datetime

This will be the command to do just that.

ALTER table test.date MODIFY COLUMN created_at date AFTER id