adding fields to an existing database

gilgalbiblewhee · #1 (**permalink**) 07-09-09, 13:45

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),

mike_bike_kite · #2 (**permalink**) 07-09-09, 15:29

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

gilgalbiblewhee · #3 (**permalink**) 07-10-09, 01:26

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:

Quote:

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:

Quote:

ALTER TABLE findings ADD txtarea6 varchar(120) AFTER txtarea5

mike_bike_kite · #4 (**permalink**) 07-10-09, 04:19

Quote:

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.

mike_bike_kite · #5 (**permalink**) 07-14-09, 04:46

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.

gvee · #6 (**permalink**) 07-14-09, 08:26

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

Internet Services

Web Development

Digital Marketing

Consumer Tech