Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: adding fields to an existing database

    How do I add fields to an existing database?
    Code:
    	$sql = "CREATE TABLE IF NOT EXISTS findings
    	(
    	ID MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	book1 int,
    	chapter1 int,
    	verse1 VARCHAR(4),
    	book2 int,
    	chapter2 int,
    	verse2 VARCHAR(4),	
    	txtarea0 VARCHAR(120),
    	txtarea1 VARCHAR(120),
    	txtarea2 VARCHAR(120),
    	txtarea3 VARCHAR(120),
    	txtarea4 VARCHAR(120),
    	txtarea5 VARCHAR(120),
    	comments VARCHAR(120)
    	)";
    I want to continue:
    Code:
    txtarea6 VARCHAR(120),
    .
    .
    .
    .
    txtarea39 VARCHAR(120),
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by gilgalbiblewhee
    How do I add fields to an existing database?
    I'm not sure how to break this to you so I'll just tell you straight. Your table design is very poor:
    • Why have 2 sets of book, chapter and verse?
    • What does the auto increment field actually represent?
    • The syntax here looks wrong by the way.
    • How do the txtarea fields relate to the 2 sets of book, chapter and verse?
    • Why limit yourself to 39 txtarea fields?
    • Shouldn't these be separate rows rather than extra fields - the SQL to access this data is going to get real ugly if you don't alter your design???
    • I have just noticed you have 450 posts - perhaps it's time to splash out and buy a book on SQL and database design.


    If you just want to go ahead and make the design worse then you could add the fields using the following syntax. Then repeat it for each of the new fields.

    Code:
    ALTER TABLE findings ADD txtarea6 varchar(120) AFTER txtarea5

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    What's wrong with this code:
    PHP Code:
    $sql "ALTER TABLE ".$table." ADD ";
        for(
    $theField=6$theField<40;$theField++){
        
        
    $sql .= "txtarea".$theField." varchar(120) AFTER txtarea".$theField-1;
        } 
    I get:
    ALTER TABLE findings ADD -1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
    I was hoping to get:
    ALTER TABLE findings ADD txtarea6 varchar(120) AFTER txtarea5

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    What's wrong with this code:
    See my previous post - I think I listed most things.

    I'm guessing it's PHP so here's another attempt:
    Code:
    for($theField=6; $theField<40;$theField++) {
        $sql = "ALTER TABLE $table ADD txtarea$theField " .
                      "varchar(120) AFTER txtarea" . ($theField-1);
        # run the sql !
    }
    PS you can just enter SQL directly into the database rather than writing a program.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Funny - I was looking up a biblical quote with an example of saying thank you - I was then going to simply give you the book, chapter and verse. Oddly I couldn't find one. Perhaps that's what's missing.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Working on a product/design that is sure to fail is a thankless task Mike, don't expect them to share that thanks with you
    George
    Home | Blog

Posting Permissions

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