SQLite rowid problem

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

Moderator: General Moderators

Post Reply
rohmanovich
Forum Newbie
Posts: 3
Joined: Sun Feb 13, 2005 1:55 pm

SQLite rowid problem

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

Post by timvw »

If you delete a row, this does not and should not change the other rows.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Re: SQLite rowid problem

Post 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.
rohmanovich
Forum Newbie
Posts: 3
Joined: Sun Feb 13, 2005 1:55 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

what's wrong with some primary key based deletion :?
rohmanovich
Forum Newbie
Posts: 3
Joined: Sun Feb 13, 2005 1:55 pm

Post 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.
jonemo
Forum Commoner
Posts: 28
Joined: Wed Feb 09, 2005 1:32 pm
Location: london, uk

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