dcsimg
www.webdeveloper.com
Results 1 to 2 of 2

Thread: Can I insert into a database and have it create a column if needed?

  1. #1
    Join Date
    May 2009
    Location
    Rochester, NY
    Posts
    396

    Can I insert into a database and have it create a column if needed?

    I have data going in to a database from a bunch of different sources. I'm writing this all in PHP.

    I'm wondering if it's possible to write a bit of code which creates a column in the database if it doesn't exist when I input the data? If it is, can someone help me do it?

    Let's say I have a database with the following:
    ID . NAME . EMAIL

    And I input the following:
    ID - 4
    NAME - Anon
    EMAIL - anon@email.com
    PHONE - 555-111-1111

    What would be the script I need in order to input all this into my database and create the "PHONE" column since that's a new piece of information?

    This is part of my actual code:

    Code:
    $sql = "INSERT INTO sbe_payments (id, invoice, created, amount, firstname, middlename, lastname, email, phone, address, city, state, zip, county, description)
    VALUES ('', 
    '".$_POST['x_invoice_num']."', '".$myTimestamp."', '".$_POST['x_amount']."', '".$_POST['my_firstname']."', '".$_POST['my_middlename']."', '".$_POST['my_lastname']."', '".$_POST['my_email']."', '".$_POST['my_phone']."', '".$_POST['my_address']."', '".$_POST['my_city']."', '".$_POST['my_state']."', '".$_POST['my_zip']."', '".$_POST['my_county']."', '".$_POST['x_description']."')";
    I'd like to create a FOR loop which takes all my postdata I provide and feed it in to my database (in my case, sbe_payments), but create any row which doesn't exist.

    Thanks,
    M

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,237
    Generally speaking, adding a new column on the fly has a bad "smell" to it. To my mind, if that's a real possibility, then you probably want a separate relational table to put "ad hoc columns" into.
    Code:
    payments_additional_data
    ================
    id (auto-inc primary key)
    sbe_payments_id (int, foreign key)
    name (varchar)
    value (varchar)
    Then you'd insert new payment records with the "standard" columns, get the insert_id, then use that as the sbe_payments_id to insert any needed additional keys/values into the payments_additional_data table.

    When pulling data out of the payments table, you could then add an additional query to get all rows from that additional table based on the sbe_payments_id.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center