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
399 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
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.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

832 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