Primary Key deleted...how can i re arrange things

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
saeen
Forum Newbie
Posts: 24
Joined: Mon Sep 22, 2003 5:35 am
Contact:

Primary Key deleted...how can i re arrange things

Post by saeen »

hi thr
i have a lil Q.. i have this database with this field called id, its primary key and auto_increment so every time a new record is inserted it is set to id+1 ...jus incrementing 1...
lets say i have 10 records in my database so the value of this field is 1-10 and now im deleting record number 7...so i want the database to rearrange the id field so it can now b from 1-9 rather than 1-10 with 7 missing from inbetween
plz help
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

its suposed that de primary key of a recrod WILL NEVER CHANGE.
why do you want to do that?
saeen
Forum Newbie
Posts: 24
Joined: Mon Sep 22, 2003 5:35 am
Contact:

Post by saeen »

see i have this project whr users come in and signup for a conference...ok now i have used an auto generated primary key...the admin has the option to browse all records...admin clicks on View Records
this application gets the first record frm DB and shows it and provides next and previous buttons which are generated dynmically by using $id+1 and $id-1 logic (this value is assigned like
echo "<td><a align = left href =".$PHP_SELF."?mode=view&id=".($id-1)."><< Previous</a>";

echo "<td><a align = right href =".$PHP_SELF."?mode=view&id=".($id+1).">Next >></a>";

like this
so now if i have a record missing then it will jus show the empty table and it wont look nice ...thats y
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

mmm it isnt a good approach. YOU CANT CHANGE THE PRIMARY KEY OF A TABLE because of the navigation.

Try this approach:

frist grab the first record. Supose the id is 1.
In the next button put: "<a href="..."?action=next&id=($id)>"
In the prev button put: "<a href="..."?action=prev&id=($id)>"

when you grab the parameters with $_GET.. just do the select statement depending the $_GET['action']

if the action is next., do something like this
select * from table where id > $_GET['id'] order by id asc LIMIT 1

if the action is prev., do something like this
select * from table where id < $_GET['id'] order by id desc LIMIT 1
saeen
Forum Newbie
Posts: 24
Joined: Mon Sep 22, 2003 5:35 am
Contact:

Post by saeen »

ummm okie ill change ma approach
can anyone tell me how to get the last record from a table??
saeen
Forum Newbie
Posts: 24
Joined: Mon Sep 22, 2003 5:35 am
Contact:

Post by saeen »

i have used
$res = mysql_query("SELECT id FROM personal_info ORDER BY id DESC LIMIT 1 ");
and i think it shud return me the value of last id field
instead when i say
echo $res; it prints something like this Resource Id #3

help
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

Read this and you will know what happend...

when you run a select query.. you will hava a resource.. you need to access the data whith:

http://www.php.net/manual/en/function.m ... -assoc.php
http://www.php.net/mysql_fetch_array
saeen
Forum Newbie
Posts: 24
Joined: Mon Sep 22, 2003 5:35 am
Contact:

Post by saeen »

thanx man i think i got it:) i really appreciate ur help :D
Post Reply