Page 1 of 1

SQLite rowid problem

Posted: Sun Feb 13, 2005 1:58 pm
by rohmanovich
1. I can't access rowid values in any of my tables using php. So when I want to delete a record from a webpage, I delete wrong one based on how I am choosing the rowid value.

2. When I delete a record it doesn't change the rowid of all the other records, what is up with that? Is it suppose to do that?

Thanks for youe help

Posted: Sun Feb 13, 2005 3:09 pm
by timvw
If you delete a row, this does not and should not change the other rows.

Re: SQLite rowid problem

Posted: Sun Feb 13, 2005 3:14 pm
by d3ad1ysp0rk
rohmanovich wrote:1. I can't access rowid values in any of my tables using php. So when I want to delete a record from a webpage, I delete wrong one based on how I am choosing the rowid value.

2. When I delete a record it doesn't change the rowid of all the other records, what is up with that? Is it suppose to do that?

Thanks for youe help
Imagine if you had a game, with all users having a specific id.
To get to their stats, they'd type in stats.php?id=theirID

Bob wakes up, checks his stats at stats.php?id=47, and then goes to work.
During the day, steve decides your game sucks, and quits (deleting his row).
Bob comes home, goes to check his stats at stats.php?id=47 and finds out it's not HIS stats anymore, it's sally's. Sally was 48 until steve(30) quit, now sally is 47 and bob is 46, but bob doesn't know this, so he emails tech support to see why his account got deleted, and so does every other user with an id greater than 30.

*realizes that was a long example*

Anywho, thats how it's supposed to work.

Posted: Sun Feb 13, 2005 3:32 pm
by rohmanovich
no. What I am doing is deplaying all contacts(records and their info in table rows. WHen an admin clicks on delete button at the end of every row, that contact and all it's info is deleted. But I can't retrieve the rowid for any of the contacts. Thats the problem. and when I delete a contact it's place is not taken.

Posted: Sun Feb 13, 2005 3:35 pm
by feyd
and it won't. It's not supposed to. Do not rely on rowid's as a numbering mechanism. You need to number them by yourself via counting in php.

Posted: Mon Feb 14, 2005 7:09 am
by n00b Saibot
what's wrong with some primary key based deletion :?

Posted: Wed Feb 16, 2005 9:05 am
by rohmanovich
primary key/ rowid is exactly what you need to use to delete or modify records. I figured out that if I make my own comlumn and set it to be primary key, I can access it just fine. Something weird with SQLite.

Posted: Wed Feb 16, 2005 10:40 am
by jonemo
you are perfectly right by using the sqlite-internal rowid. in each query you must ask the DBMS for the rowid seperately:

Code: Select all

SELECT rowid, * FROM table
. then you have it and can use it for whatever you want.