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
375 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now