Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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
0
Pauliostro
Asked:
Pauliostro
2 Solutions
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now