Page 1 of 1
Primary Key deleted...how can i re arrange things
Posted: Tue Feb 24, 2004 5:40 pm
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
Posted: Tue Feb 24, 2004 5:48 pm
by AVATAr
its suposed that de primary key of a recrod WILL NEVER CHANGE.
why do you want to do that?
Posted: Tue Feb 24, 2004 5:54 pm
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
Posted: Tue Feb 24, 2004 6:06 pm
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
Posted: Tue Feb 24, 2004 6:34 pm
by saeen
ummm okie ill change ma approach
can anyone tell me how to get the last record from a table??
Posted: Tue Feb 24, 2004 7:21 pm
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
Posted: Tue Feb 24, 2004 7:41 pm
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
Posted: Tue Feb 24, 2004 7:49 pm
by saeen
thanx man i think i got it:) i really appreciate ur help
