PHP: Add field to a table.

Hi all EE people,

Is it possible to script in PHP the ability to add, and delete for that matter, a field to an already existing table in MySQL?

Hope someone knows this answer.

Thanks
PauliostroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tdterryCommented:
Absolutely, although this question is more suited to MySQL than PHP.

http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

To add a field:
  ALTER TABLE mytable ADD COLUMN column_name data_type [not null, default 'blah', etc]

To remove a field
  ALTER TABLE mytable DROP COLUMN column_name

Check out the link for more options on ALTER TABLE.
0
PauliostroAuthor Commented:
Thanks, but is it possible to do this with PHP?  There is a certain feature in a CMS i am building and this would be a huge benefit and so much more easier.

Hope you can help.
0
spotxCommented:
so you get a quick answer
use the query as suggested in tdterry's answer with
mysql_query(....);

YOu will have to allow the user that your php script connects as to be able to alter tables though
this is more than likely where you are getting tripped up (my guess) ie its a permission issue raher than a coding issue
0
tdterryCommented:
Sorry, I assumed you would put it in a mysql_query(...) call like all your other queries.  :)

If that isn't working, then as spotx said, you probably have a permissions problem.  Use

    mysql_query("ALTER TABLE ...") or die(mysql_error());

to see the error message.  The user/password in your mysql_connect() call will need GRANT ALTER permission on the database.

From a design perspective, if you need to regularly change the structure of your database at run time, then you have a design flaw.  Users should only be adding and removing rows to the tables, not changing the table structure.  If you are allowing them to add meta data or extra descriptions, or something similar, it would be better to create a new table (at design time) for "generic" data, and then allow them to insert generic data and join it to the main table(s) at run time.

Instead of
    tbl_item:  user_id  |  item_id  |  description  |  user_data1  |  user_data2  |  user_data3  |  ...

    select * from tbl_item where user_id = xxx

Try:
    tbl_item:  user_id  |  item_id  |  description
    tbl_userdata:  data_id  |  item_id  |  user_data  (this is where users add/remove data rows)

    select * from tbl_item, tbl_userdata where tbl_item item_id = tbl_userdata.item_id where user_id = xxx


Save the structure manipulation (ALTER TABLE, etc) for your upgrade scripts.
0
soapergemCommented:
Make sure to use the PHP functions mysql_connect() and mysql_select_db() to have PHP automatically connect to your database. It's understood that most people already know automatically to do this, but I'm just checking to be sure. Then, you can use the PHP command mysql_query() to make any changes to your MySQL database. Use the queries that the other guys outlined above. If you want to be completely literal, there is no way (technically) for PHP to change the tables directly. It just doesn't work that way (in an absolutely literal sense). Instead (again, in the most literal sense), PHP automatically connects to your MySQL database using those functions I just mentioned and basically says "you do it for me" to the MySQL database. This is for very good reason. But yes, you can make PHP change things in MySQL automatically using these functions. But as some people have pointed out, what you asked is really a MySQL question, not a PHP question. So use those functions, and use the MySQL query that was given above.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.