rgb192
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
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;
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.
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.
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);
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>";
}
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsq
HeidiSQL might solve another mysql based question, but this is a php zone question.
Dave I am looking to have php remove this line.
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
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
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\readingsq l4.php on line 12
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsq
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I added:
ini_set('display_errors', TRUE);
but got no additional information from that line.
only output is a warning:
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsq l5.php on line 16
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>";
}
only output is a warning:
Warning: Invalid argument supplied for foreach() in C:\wamp\www\test\readingsq
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().
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>';
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;"
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;"
}
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;
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;
Your code deletes the first
auto_increment,
in every create query
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay I will have another question on how to remove only the auto increment at the bottom of query.
Thanks
Thanks