Solved

want tables to start at zero rows using php

Posted on 2014-01-03
14
321 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
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:rgb192
Comment Utility
<?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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
gr8gonzo earned 167 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:rgb192
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
<?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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 333 total points
Comment Utility
$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
Comment Utility
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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 333 total points
Comment Utility
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
Comment Utility
Okay I will have another question on how to remove only the auto increment at the bottom of query.

Thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now