Page 1 of 2
auto_increment
Posted: Tue Mar 09, 2004 7:53 pm
by josh
In mysql my id field is auto increment.... when i create a news item it is 1 then i do it again its 2
...
i delete 2 and then remake it so now in my database it goes 1, 3 etc...
how can i make it so my id's are correct because when i ask for page 2...
$pages=$page * 5;
$start=$pages - 5;
$sql->Query("Select id, topic, text from news where id > 10 order by id desc limit $pages");
it doesnt return the right stuff because my id's are messed up
Posted: Wed Mar 10, 2004 6:46 am
by AVATAr
why do you want to do that? you are going to paginate?
Posted: Wed Mar 10, 2004 7:28 am
by Wayne
Code: Select all
update news set id=LAST_INSERT_ID(id+1);
or
or set the id value to what ever you want it to be.
Posted: Wed Mar 10, 2004 2:06 pm
by josh
it already updates the id... say i have 10 items then i delete 7 and 8 .... now my list goes 1 2 3 4 5 6 9 10
i want 9 and 10 to be bumped down kind of.. so that i can assume that news page 1 contains news items 1-5 etc..
Posted: Wed Mar 10, 2004 2:30 pm
by Weirdan
The only solution is to renumber all that records.
Posted: Wed Mar 10, 2004 4:03 pm
by josh
so every time i delete a news item i have to go in and re-number them?? how come when you dl a cms it magically knows what posts to display?
Posted: Wed Mar 10, 2004 4:12 pm
by Weirdan
You can try to use something like:
Code: Select all
set @a=0+$startrow;
select @a:=@a+1 as id, id as real_id.....
This way you can delete and insert rows, it will be 'renumbered' automatically.
But it would require to issue 2 queries instead of 1.
Posted: Wed Mar 10, 2004 5:08 pm
by josh
where do i put that code exaclty? do i put that exact code or do i need to modify it?
Code: Select all
<?php
<?
// This page reads tha database and prints the code out
// according to the tables/other method in theme.php
// File referenced by news_data.php to be displayed on news.php
require("news/util.php");
$sql = new MySQL_class;
$sql->Create("onlychea_gnome");
echo('<table width=300 border=1 cellpadding=0 cellspacing=0 bordercolor="#CCCCCC">');
$pages=$page * 5;
$start=$pages - 5;
$sql->Query("Select id, topic, text from news where id > 10 order by id desc limit $pages");
for ($i = 0; $i < $sql->rows; $i++) {
$sql->Fetch($i);
$id = $sql->data[0];
$topic = $sql->data[1];
$text = $sql->data[2];
// Put code for table includeing the variables
echo(' <tr> ');
echo(" <td><strong><font size=+1><strong>$topic</strong></font></strong></td> ");
echo(' </tr> ');
echo(' <tr> ');
echo(" <td>$text<p><p></td> ");
echo(' </tr> ');
echo(' <tr> ');
echo(" <td>News # $id</td> ");
echo(' </tr> ');
echo('</table><br><table width=300 border=1 cellpadding=0 cellspacing=0 bordercolor="#CCCCCC">');
// render is done:
}
// after render do following:
echo('</table>');
//old code echo("</table>");
//include ("../footer.php");
?>
?>
Posted: Wed Mar 10, 2004 6:22 pm
by Weirdan
Code: Select all
//...snip
$pages=$page * 5;
$start=$pages - 5;
$sql->Query("set @a=0+$start");
$sql->Query("Select @a:=@a+1 as id,id as real_id, topic, text from news where id > 10 order by id desc limit $pages");
//...snip
It will work... perhaps.
Posted: Wed Mar 10, 2004 7:09 pm
by timvw
All you need is another table that contains the order of the items.....
order(id, item_id);
Everytime you insert an item, you have to insert into order too.
(MAX(id)+1 for order.id)
And everytime you delete an item, you have to delete from order;
AND UPDATE id=id-1 WHERE id>deleted_id;
Posted: Wed Mar 10, 2004 7:32 pm
by josh
ok thanks
Posted: Wed Mar 10, 2004 7:58 pm
by josh
Error: Unable to perform update: update news, set id = id-1 where ID > 2 :You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'set id = id-1 where ID > 2' at line 1
Posted: Wed Mar 10, 2004 9:23 pm
by Weirdan
remove the comma. And if you're using the update after delete you could use it directly on your initial table. No need for extra table.
Posted: Thu Mar 11, 2004 4:53 am
by timvw
Forgot to mention, make sure you having transactions enabled (in mysql INNODB or LOCK functions) because each time you delete something the (DELETE and UPDATE) have to be considered as one....
Posted: Thu Mar 11, 2004 8:37 am
by volka
I strongly advise against tempering with auto_increment fields - esp. if it's only to have continuous values. There are good reasons why they behave this way. Speed: your renumbering takes a considerable amount of time, ever more with an increasing amount of records. Uniqueness: an auto_increment field is supposed to identify a record, but it doesn't if you assign new ids. Yesterday id=2 identified record a, today it's record b - hmmmm.
There's another property assigned to the records which is always continuous. The position within the result set. You may use it within the database, e.g.
SELECT a,b, from tablename WHERE <condition> LIMIT 4,2
(get records 5 to 6 that fulfill the condition) and/or within your script/program