auto_increment

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

Moderator: General Moderators

josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

auto_increment

Post 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
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

why do you want to do that? you are going to paginate?
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

update news set id=LAST_INSERT_ID(id+1);
or

Code: Select all

update news set id=1;
or set the id value to what ever you want it to be.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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..
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

The only solution is to renumber all that records.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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"); 
?>
?>
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

ok thanks
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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....
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Post Reply