Solved

want tables to start at zero rows using php

Posted on 2014-01-03
14
341 Views
Last Modified: 2014-01-15
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28328838.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

0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39755523
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.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39755527
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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39756022
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rgb192
ID: 39756064
<?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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39756120
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
0
 

Author Comment

by:rgb192
ID: 39758362
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
0
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 167 total points
ID: 39759318
It's because $new isn't an array, it's a string because you used implode() to convert the array to a string.

Try this if you want to keep the data in an array:

<?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 = $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


Let me note something - it doesn't matter how many tables you create. Each table has its own auto-incrementing value (if you use an auto-incrementing primary key). If you saw auto-incrementing values above 0, then it's usually because you did a mysqldump of a table that already had some records in it.

I mentioned HeidiSQL because you had already created the tables and it sounded like you wanted to reset the auto-increment values for each table, but if you're going to drop and recreate the tables, then this approach will work, too.
0
 

Author Comment

by:rgb192
ID: 39773427
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
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39773454
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().
0
 

Author Comment

by:rgb192
ID: 39780853
<?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

0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 333 total points
ID: 39780967
$new is an array, and $new[0] is a string, so the foreach() iterator does not make sense as written on line 16.  Try this for line 16:

foreach($new as $query)
0
 

Author Comment

by:rgb192
ID: 39781317
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
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 333 total points
ID: 39781406
so my question is how to remove
AUTO_INCREMENT=254
   (will this change the table)
or
AUTO_INCREMENT=0 or 1
And that is why we showed you how to remove the AUTO_INCREMENT
0
 

Author Closing Comment

by:rgb192
ID: 39782270
Okay I will have another question on how to remove only the auto increment at the bottom of query.

Thanks
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question