?
Solved

want tables to start at zero rows using php

Posted on 2014-01-03
14
Medium Priority
?
345 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 84

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 111

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
Independent Software Vendors: 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 111

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 668 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 111

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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1332 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1332 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

762 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