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
383 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
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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

911 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

21 Experts available now in Live!

Get 1:1 Help Now