RE: [PHP] Resetting ID in mySQL DB

Jack, Thanks a lot for you help. I did actually work out the logistics of the code and it's doing fine. It's probably a solution to my problem, there's only a couple of things though : a) I got about 800 plus records in the table. And there's a lot of empty or duplicate records in there which need to be deleted. So if i individually delete every record then i will have to run the code for each ID individually. That would be a lot to do. That's why i was wondering if there's a way, by which after having deleted those empty/duplicate records, i could just clear the entire ID field and run a code that would generate IDs in order again... b) How can i check when the record is sent to the DB using PHP3 or mySQL if there's any value in the field or not? I have put up a JavScript validation to check for the same in the form, and yet, something's defeating that and empty records are entering into the DB. I don't know why really!! Thanks a bunch! Cheers & Glory, Thomas Edison jr. --- Jack Dempsey <dempsejn@georgetown.edu> wrote: > Thomas, > > The code i gave can't be copied directly in...you > need to use it as a guide, > but use your own variables... > So, basically, whatever field you have as your id > field, that is what you > will put in place of what i called id_num...and the > number 36 was just an > example...see if this makes sense: > > you want to delete a record with the column ID being > X > 'delete from table where ID=X' that would be your > sql > then you send another query: > $sql = "update table set ID=ID-1 where ID>X"; > what this does is say "ok, the numbers before the > item you deleted will stay > the same, but from the place you deleted on higher > you need to decrement the > id values by one. > > try this from the mysql command line on a test table > with some fake records, > and you should see what i mean... > > good luck > jack > > -----Original Message----- > From: Thomas Edison Jr. > [mailto:thomasedisonjr@yahoo.com] > Sent: Monday, November 12, 2001 2:09 AM > To: Jack Dempsey > Subject: RE: [PHP] Resetting ID in mySQL DB > > > Jack, > > Ok i get it! Lemme just go through the code once > more... to be exact.. the code will be : > > <?php > $db = mysql_connect("localhost","dbname","pass"); > mysql_select_db("dbname",$db); > $sql = "update table set id_num=id_num-1 where id > > 36",$db; > $result = mysql_query($sql); > ?> > > Or i made a mistake? > Is there anyway i can re-write all the IDs from 1 > onwards? > > Thanks a Ton! > Cheers & Glory! > > > > > > --- Jack Dempsey <dempsejn@georgetown.edu> wrote: > > say you have 100 records. > > you delete number 36. > > your db will then be > > ... > > 34 > > 35 > > 37 > > 38 > > ... > > correct? > > after a delete, you could then say: > > update table set id_num=id_num-1 where id > 36 > > and this would update all relevant records > > > > -----Original Message----- > > From: Thomas Edison Jr. > > [mailto:thomasedisonjr@yahoo.com] > > Sent: Monday, November 12, 2001 1:52 AM > > To: php-general@lists.php.net > > Subject: [PHP] Resetting ID in mySQL DB > > > > > > Hi, > > > > I'm facing a problem. I made a simple DB with > simple > > adding records in DB operation. I've even put up a > > JavaScript validation of the Form from where you > add > > info. And yet, in the display table, there are > many > > many blank fields. I don't know how they are > > generated. > > > > The Adding of info form is at : > > > http://www.mjimm.com/index.php3?page=fanlist_add.php3 > > > > The display of records is at : > > http://www.mjimm.com/fanlist_view.php3 > > > > Now the Display table also Displays the ID of the > > Record. If i go in my mySQL Admnin, and delete the > > rows with blank or duplicate records.. the ID also > > is > > deleted but then is alway missing from the list. > > That > > is, if i delete Row with ID 78, the next record > > feeded > > will start from 79 and not 78. So there's gonne be > > missing ID's from Records. Which i don't want. But > > which has already happened. How can i correct it?? > > I want to reset the entire IDs to reflect the true > > Number of records after i delete the Empty/Blank > > Rows > > & Duplicate Record Rows!! How can i do that! > Please > > help!!!!! > > > > Cheers & Glory, > > Thomas edison jr. > > > > > > > > ===== > > Rahul S. Johari (Director) > > ****************************************** > > Abraxas Technologies Inc. > > Homepage : http://www.abraxastech.com > > Email : abraxastech@yahoo.com > > Tel : 91-4546512/4522124 > > ******************************************* > > > > __________________________________________________ > > Do You Yahoo!? > > Find a job, post your resume. > > http://careers.yahoo.com > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: > > php-general-unsubscribe@lists.php.net > > For additional commands, e-mail: > > php-general-help@lists.php.net > > To contact the list administrators, e-mail: > > php-list-admin@lists.php.net > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: > > php-general-unsubscribe@lists.php.net > > For additional commands, e-mail: > > php-general-help@lists.php.net > > To contact the list administrators, e-mail: > > php-list-admin@lists.php.net > > > > > ===== > Rahul S. Johari (Director) > ****************************************** > Abraxas Technologies Inc. > Homepage : http://www.abraxastech.com > Email : abraxastech@yahoo.com > Tel : 91-4546512/4522124 > ******************************************* > > __________________________________________________ > Do You Yahoo!? > Find a job, post your resume. > http://careers.yahoo.com > ===== Rahul S. Johari (Director) ****************************************** Abraxas Technologies Inc. Homepage : http://www.abraxastech.com Email : abraxastech@yahoo.com Tel : 91-4546512/4522124 ******************************************* __________________________________________________ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com