Resyncing mysql table with PHP...any ideas?

Teach

Hi guys.

Bit of a strange query I'm sure, but I'm hoping this will be possible with PHP

Basically, to introduce my issue, I recently added a 'duration' option to a ban system I have in PHP. Part of the new system means that a row is added to my events table everytime a new ban is added.

For the past year or so of its existance, it hasn't done so - so I used the following code to inset one:

 $dateadded=date("Y-m-d H:i:s");

 $sql="SELECT * FROM bans";
 $result = mysql_query($sql);
 if ($myrow = mysql_fetch_array($result))
 {
   do
   {
      $update=mysql_query("Insert into events (dateadded,account,event,note,ip_address) values ('".mysql_real_escape_string($myrow['dateadded'])."','".mysql_real_escape_string($myrow['account'])."','Add new ban: ".mysql_real_escape_string($myrow['badword'])."','note here','".$getlastlogin['ip_address']."')");
   }
   while ($myrow = mysql_fetch_array($result));
 }

This worked absolutely fine, however, I am wanting to correct my events table a bit.

The table contains this structure:

CREATE TABLE `events` (
  `id_event` int(11) NOT NULL auto_increment,
  `dateadded` datetime NOT NULL default '0000-00-00 00:00:00',
  `account` varchar(50) NOT NULL default '',
  `event` varchar(100) NOT NULL default '',
  `note` text NOT NULL,
  `ip_address` varchar(50) NOT NULL default '',
  `edit` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id_event`)
) ENGINE=InnoDB AUTO_INCREMENT=1285 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1285 ;

Because that PHP code added all the entries in bulk, it has mucked up the order in which they're shown. I know I could use MySQL to "order by dateadded", but other pages require it's sorted by id_event

Is it possible to use PHP to echo out "INSERT" lines (like when re-inserting data into a table) and order it by the dateadded, so I can erase my events table, and reinsert them in the order of dateadded. This way, they will all be in the correct order of dateadded, and have the relevant id_event auto_incremement

Hope this makes sense!

Weedpacket

"Select * from bans order by dateadded" won't do it for you?

But look at using "insert into".

Teach

Hi there, thanks for your reply

Yeah, I know how to do that bit. My problem (or query) however is how I would get PHP to then display it in a ready to insert way, with like "INSERT INTO" queries...

Hope this makes sense