asked on

want tables to start at zero rows using php

https://www.experts-exchange.com/questions/28328838/create-statements-from-a-mysql-dump-file.html

I like the solution but if I create many empty tables in my new database, the auto_increment primary key will start at a high number (254 in this example).
I want to start at 0 or 1 whichever is the normal start of table

so my question is how to remove
AUTO_INCREMENT=254
(will this change the table)
or
AUTO_INCREMENT=0 or 1

CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL auto_increment, `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;

Open in new window

gr8gonzo

Try out HeidiSQL. Its a good Gui for mysql that will also show you the commands being run. So you can set the auto incrementing ID and see the commands.

Dave Baldwin

AUTO_INCREMENT will find the highest number in that column and add 1 to it. You can set it to whatever you want but that's what it's going to do. AUTO_INCREMENT=254 in a CREATE statement is basically telling you what the next number is going to be. You can remove that entirely and it will work the way I described.

Ray Paseur

Building on the earlier question, this shows how to remove the AUTO_INCREMENT argument.

<?php // RAY_temp_rgb192.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28330417.html
// AND http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28328838.html

// TEST DATA
$sql = <<<EOD
CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;


insert into table
...

...


CREATE TABLE `a_images2` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;


insert
...
...
...

CREATE TABLE `a_images3` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
EOD;

// ISOLATE THE CREATE TABLE STATEMENTS
preg_match_all("/CREATE TABLE [^;]+;/", $sql, $mat);
$new = implode(PHP_EOL, $mat[0]);

// REMOVE THE AUTO_INCREMENT
$new = preg_replace('/AUTO_INCREMENT.*?\s/i', NULL, $new);
var_dump($new);

Open in new window

rgb192

ASKER

<?php
$sql = file_get_contents("C:/Users/Acer/Documents/dumps/storage-creates.sql");

// ISOLATE THE CREATE TABLE STATEMENTS
preg_match_all("/CREATE TABLE [^;]+;/", $sql, $mat);
$new = implode(PHP_EOL, $mat[0]);

// REMOVE THE AUTO_INCREMENT
$new = preg_replace('/AUTO_INCREMENT.*?\s/i', NULL, $new);
//var_dump($new);

foreach($new[0] as $query)
{
  echo $query . "<br><br>";
}

Open in new window

Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsql4.php on line 12

HeidiSQL might solve another mysql based question, but this is a php zone question.
Dave I am looking to have php remove this line.

Ray Paseur

Please use var_dump($new) to see what that variable contains. I see you commented it out, but you really need to get used to using var_dump(). It will save you a lot of time!

Here is the man page: foreach() works only on arrays and objects, and will issue an error when you try to use it on a variable with a different data type or an uninitialized variable.

Going forward, you might want to post your questions in more than one Zone. You can have up to three Zones. When the question deals with PHP and MySQL, posting in both Zones makes a lot of sense.

Wishing you a good New Year 2014, ~Ray

rgb192

ASKER

string(778) "CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsql4.php on line 12

ASKER CERTIFIED SOLUTION

gr8gonzo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

I added:
ini_set('display_errors', TRUE);
but got no additional information from that line.

<?php
ini_set('display_errors', TRUE);
$sql = file_get_contents("C:/Users/Acer/Documents/dumps/storage-creates.sql");

// ISOLATE THE CREATE TABLE STATEMENTS
preg_match_all("/CREATE TABLE [^;]+;/", $sql, $mat);
$new = $mat[0];
// $new = implode(PHP_EOL, $mat[0]);

// REMOVE THE AUTO_INCREMENT
foreach($new as $idx => $sql)
{
  $new[$idx] = preg_replace('/AUTO_INCREMENT.*?\s/i', NULL, $sql);
}

foreach($new[0] as $query)
{
  echo $query . "<br><br>";
}

Open in new window

only output is a warning:
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsql5.php on line 16

Ray Paseur

I think I would start the debugging process by looking at the contents of the file that gets read into $sql. Does it make sense? Next I would look at the contents of $mat. If those variables appear correct, I would then look at $new. You can inspect these variables with var_dump().

rgb192

ASKER

<?php
ini_set('display_errors', TRUE);
$sql = file_get_contents("C:/Users/Acer/Documents/dumps/storage-creates.sql");

// ISOLATE THE CREATE TABLE STATEMENTS
preg_match_all("/CREATE TABLE [^;]+;/", $sql, $mat);
$new = $mat[0];
// $new = implode(PHP_EOL, $mat[0]);

// REMOVE THE AUTO_INCREMENT
foreach($new as $idx => $sql)
{
  $new[$idx] = preg_replace('/AUTO_INCREMENT.*?\s/i', NULL, $sql);
}

foreach($new[0] as $query)
{
  echo $query . "<br><br>";
}
echo '<pre>';
echo '<br>sql<br>-------------------<br><br>';
var_dump($sql);
echo '<br>mat<br>-------------------<br><br>';
var_dump($mat);
echo '<br>new<br>-------------------<br><br>';
var_dump($new);
echo '</pre>';

Open in new window

output

Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsql6.php on line 16

sql-------------------string(554) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;"
mat-------------------array(1) {
  [0]=>
  array(2) {
    [0]=>
    string(291) "CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;"
    [1]=>
    string(554) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;"
  }
}
new-------------------array(2) {
  [0]=>
  string(257) "CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL 
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
  [1]=>
  string(519) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL 
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
}

Open in new window

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


sql-------------------string(554) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;"
mat-------------------array(1) {
  [0]=>
  array(2) {
    [0]=>
    string(291) "CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;"
    [1]=>
    string(554) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;"
  }
}
new-------------------array(2) {
  [0]=>
  string(257) "CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL 
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
  [1]=>
  string(519) "CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL 
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` datetime default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
}

Open in new window

output

CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL `conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

desired output

CREATE TABLE `a_images` ( `a_images_id` int(11) NOT NULL  auto_increment, `profile_id` varchar(20) default NULL, `image_name` varchar(100) default NULL, `image_url` varchar(200) default NULL, PRIMARY KEY (`a_images_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `a_messages` ( `a_messages_id` int(11) NOT NULL  auto_increment,`conversation_id` bigint(20) default NULL, `profile_id` varchar(20) default NULL, `sender` varchar(20) default NULL, `message_id` bigint(20) default NULL, `message_text` varchar(1000) default NULL, `dateAgo` datetime default NULL, `message_read` tinyint(4) default NULL, `this_user` varchar(20) default NULL, PRIMARY KEY (`a_messages_id`), UNIQUE KEY `unique_message_id` (`message_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

Your code deletes the first
auto_increment,
in every create query

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

Okay I will have another question on how to remove only the auto increment at the bottom of query.

Thanks