PHP: Add field to a table.

Posted on 2006-04-09
Last Modified: 2012-06-27
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.

Question by:Pauliostro
    LVL 5

    Expert Comment

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

    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.

    Author Comment

    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.
    LVL 3

    Expert Comment

    so you get a quick answer
    use the query as suggested in tdterry's answer with

    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
    LVL 5

    Assisted Solution

    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

        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.
    LVL 6

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now