Selecting a row by number, NOT value

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
CGameProgrammer
Forum Newbie
Posts: 11
Joined: Sun Mar 09, 2003 11:26 pm

Selecting a row by number, NOT value

Post by CGameProgrammer »

Let's say I want to update row 36 of the "Name" column in "Users". Something like "UPDATE Users SET Name = 'Blah' WHERE row = 36", except that there's no column called "row", I just want to select a row by its number. This code should work with any database -- that's the point. So actually adding a column called "row" is no good for me.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

It would be better to give each record in the table a unique ID that you can reference in the update:

Code: Select all

UPDATE Users SET Name = 'Blah' WHERE ID = 36
I'm not sure how you'd know exactly what was in each row if you didn't have something unique about each record - you wouldn't know which row to update.

Mac
CGameProgrammer
Forum Newbie
Posts: 11
Joined: Sun Mar 09, 2003 11:26 pm

Post by CGameProgrammer »

I have no problem editing my databases, it's just that I wanted to create a script for editing any database at all through a simple interface where you click a cell, edit its contents, then click a Save Cell button. It was easy to do except that the save script can't tell which row you're editing.

I can simply describe the contents of every other field on the row, and say "where c1=a&c2=b&c3=c..." but I would have preferred something simpler.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

As Mac implied, in a relational database, each table should have a primary, unique key by which a particular row is identified. Many things could be used as that primary key, having an integer field that is defined as "autoincrement" is quite common - the actual value holds no real meaning as far as the data is concerned, it could or might as well be a timestamp, but is guaranteed to be unique.

To accomplish :
-------------------
it's just that I wanted to create a script for editing any database at all through a simple interface where you click a cell, edit its contents, then click a Save Cell button. It was easy to do except that the save script can't tell which row you're editing.

-----------------

You will need to find out first how to access the catalog (metabase) tables of whatever database you are using to know what tables exist, and what columns are in each table, etc.

You will also need to pull out the primary key and associate it with the data.

You are speaking in terms of a spreadsheet, which a database is not - you'll have to somehow carry along the primary key of each row of your proposed grid to know which row to update.

Phil J.
CGameProgrammer
Forum Newbie
Posts: 11
Joined: Sun Mar 09, 2003 11:26 pm

Post by CGameProgrammer »

You're right about the spreadsheet analogy -- I want to make editing databases as simple as using Excel. I'll just do it the way I figured I'd have to -- a whole bunch of WHEREs.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

But if you have a primary key - a unique identifier - for each record then there is no need for a bunch of where's instead you only need one.

Mac
Post Reply