Solved

is it a problem to an primary key in mysql table that is int(100) and auto_increament

Posted on 2011-09-08
8
404 Views
Last Modified: 2012-05-12
I created a table that the primary_key is an id that the type is int(100) and AUTO_INCREMENT.
so I notice that even after deleting rows the "id" is still counting from a the former number and doesnt update it. can it cause problems? is there any way I can change that after the table is already have existing rows?

Thank you
0
Comment
Question by:Nura111
[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
8 Comments
 
LVL 17

Accepted Solution

by:
nanharbison earned 500 total points
ID: 36507036
Why would you want to mess with the auto-incremented id? You can always get a count of how many rows are in your database.
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36507050
Auto increment will always increment by +1 from the last inserted row. It wont cause any problems if you are deleting any rows from in between. If you want to reset the auto increment

ALTER TABLE "table_name" auto_increment=1

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
0
 

Author Comment

by:Nura111
ID: 36507056
I just don't have a lot of experience in mysql and I thught its might cause problems because I notices I dont have it any other tables in my application.
adn when I first update the table (from the php script) their was a problem there that it wasnt update unless I refreshed the page (every refresh made one update in the table even though it was suppose to update the all table in a foreach loop)  .
I thought that this might be the reason.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 17

Assisted Solution

by:nanharbison
nanharbison earned 500 total points
ID: 36507075
No, there must be an error in your code if it only makes one update instead of all the rows in the table. Why don;t you post the code in the for loop?
0
 

Author Comment

by:Nura111
ID: 36507414
PranjalShah: why would I do that Isn't it will cause that there will be the the same primary key more than once?
0
 

Author Comment

by:Nura111
ID: 36507456
Yes thank you
the next code is update into table and is inside of a while loop that go over 468 rows

the bug is 95% there after debugging the $_SESSION['displayMetro'] result are fine and the results in the table are fine.

but its doing that one update at a time something in there is throwing it out and will do it again only after refresh the page(im guessing that it getting information again from the server..)

//	Insert the table dispaly metro

				$countIt = 0;

		    	foreach ($_SESSION['displayMetro'] as $m) {

				$countIt++;

				if ($countIt == 1 ){$m['prime_metro'] = 1; $prime_city = $m['metro_name'];}

					if ($countIt == 4) { break; }	

					$insert_query  = "INSERT INTO displayMetro(domain_id, metro_name, population, distance, prime_metro)";

				   $insert_query .= "VALUES('{$m['domain_id']}', '{$m['metro_name']}', '{$m['population']}', '{$m['distance']}', '{$m['prime_metro']}')";

				

				if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)

				die(ShowError("Server Query Error"));

				}

Open in new window

0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 36509768
To answer your origianl question: your primary key field is fine.  No need to do anything with it.  It will do its job.

The SQL that you posted is not updating rows.  It is creating new rows.  The first thing you want to check is to make sure your session variable 'DisplayMetro' is a multi-dimensional array that contains all of the rows you are interested in.  

If that variable only has one row of data in it then the loop will only execute one time.

0
 

Author Comment

by:Nura111
ID: 36512083
yes sorry thats what I meant I want to Insert new rows.      the session varaibles are fine this all code is in a while loop (I didn't added it because its doing other things that are not relevant and just  comlicated the code) but the while loop is going on 460 rows and creating the SESSION for each row and do the foreach for each row.
when comment the Insert query the while is working just fine on the 468 rows at once. (and taking a long time because of a certain action that happen before this code) but when Im updating the db its somehow stop it after every one iterate of the while and will continue only when refresh the page.

again the Session is ok and the result in the db is ok exepect that it happen one at time.

any thought?
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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