auto_increment
Moderator: General Moderators
auto_increment
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
...
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
Code: Select all
update news set id=LAST_INSERT_ID(id+1);Code: Select all
update news set id=1;You can try to use something like:
This way you can delete and insert rows, it will be 'renumbered' automatically.
But it would require to issue 2 queries instead of 1.
Code: Select all
set @a=0+$startrow;
select @a:=@a+1 as id, id as real_id.....But it would require to issue 2 queries instead of 1.
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");
?>
?>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");
//...snipI 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
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