Is there an "easy" way to update a database's tables? Like for instance, I have my own custom-coded member login system. Whenever I add a new feature that needs a database, I manually have to download, update and upload the database. Is there an easy way I can do this?

THANKS!!!

[N]F

Usually, it's rather easy to update a table in a database.
The syntax is something like "insert into MYTABLE values SOMETHING, SOMETHING_ELSE, ..."
Was this your exact question ?

What Yordan posted was inserting a record to the database and I don't think Nightfox is after that.

You are looking to change the tables structure right? Updating in database world usually means updating a single record which is done with SQL command beginning with UPDATE keyword or command or whatever.

You can do all possible operations to a database with queries. Sometimes certain features might be limited (for example dropping databases might not be allowed from PHP scripts and so on) but usually you should be able to change the structure. Well provided that the user has create rights.

Basically how you do it is use ALTER TABLE query. For example you can add column with query like this "ALTER TABLE tablename ADD column type". Naturally the syntax is exactly the same as for creating tables so you can everything here you can do there. You can drop columns with "ALTER TABLE tablename DROP COLUMN columnname".

It is also possible to rename and change datatypes for columns, but unless I'm completely mistaken the syntax varies from DBMS to DBMS. MySQL uses CHANGE "ALTER TABLE tablename CHANGE oldname newname type". Notice that you can change the type here. To just change the type keep the name same.

I have a vague memory that Oracle uses RENAME... I could be wrong though.



I hope this answers your question.

Nicely answered, Hercco.
I must confess that i was confused by the topic title, I understood the " Updating A Database's Tables" by updating some rows in a table, which is different from insert but thas nothing to do with the ALTER TABLE notion.
Sorry again
Yordan

This post has been edited by yordan: Jun 1 2006, 08:45 PM

Thanks!! I'll look into all of that. It does look like what I'm after. I use MySQL, not Oracle. But thanks again!!

[N]F

I'm pretty sure most if not all database software accepts pretty much the exact same syntax or close to it. I would say UPDATE is what you are looking for, it basically let's you change a field in a table to my knowledge to say update a int value incrementaly and such.

INSERT would allow you to insert a new field in the table or record either way, by id or if the table has an auto_incremental primary key then it would do it automatically.

I don't know of ALTER though? it sounds like it does the same thing as UPDATE? (Either way updating or altering your'e changing the original so what would be the difference between the two? there shouldn't be....)

Yep - the INSERT and UPDATE syntax is almost same in any SQL Compliant Database...

Chesso: UPDATE and ALTER have diametrically opposite functions - though both names suggest some sort of updating mechanism... UPDATE is used to modify the DATA stored inside a table, whereas ALTER is used to modify the STRUCTURE of the table itself.

Your usual UPDATE syntax (for MySQL) would be:


ALTER, as shown by Hercco allows you to modify the Column names as well as their data types in a particular table... Say in the above table, Field1 was VARCHAR and Field2 was DOUBLE... I want to swap the data types and change the name of Field2 to Field3

Ahhh so one changes the data stored and the other just changes the type of data stored.

Yes, in all SQL RDBMS the syntax is very similar.
CREATE TABLE to create a table
ALTER TABLE to change the table structure
INSERT INTO table to add rows
etc...
Very few differences between Oracle, Ingres, Informix, Mysql, PostGre.
Of course, things are easier if you use integrated scripts like the ones in cpanel, but sometimes you have to understand basics, or at least know the terminology.

This post has been edited by yordan: Jun 4 2006, 10:40 PM

Oracle's SQL is the same as mySQL with just some addons and optiomations..
on the other hand, for a small scale bussiness which need an Oracle functionality,
mySQL 5 is a nice thing to start with..

As long as it uses SQL and a compliant, the database controls are the same..