MySQL Forums
Forum List  »  PHP

Re: Inserting multiple lines into table
Posted by: Hartmut Holzgraefe
Date: June 26, 2008 03:47AM

Why 5 forms? Really independent <form> blocks or just 5 <input type="file"...> fields within one form?

If it is just one form inserting should be as easy as

INSERT INTO table SET artist="$artist", album="$album", track="$track1" ...

repeated 5 times or a multi insert:

INSERT INTO table (artist, album, track, length)
VALUES ("$artist", "$album", "$track1", "$length1")
, ("$artist", "$album", "$track2", "$length2")
, ("$artist", "$album", "$track3", "$length3")
, ("$artist", "$album", "$track4", "$length4")
, ("$artist", "$album", "$track5", "$length5");

But i think you already see the problem: you have to repeat $artist and $album all the time as your table is not normalized ...

You'd be better of with three separate tables for artist, album and track:

CREATE TABLE artist (
id int primary key auto_increment,
name varchar(100)
);

CREATE TABLE album (
id int primary key auto_increment,
artist_id int,
name varchar(100)
);

CREATE TABLE track (
id int primary key auto_increment,
album_id int,
name varchar(100),
length time
);

Now to add a completely new artist, album and track you would

INSERT INTO artist (name) VALUES ("$artist");
$artist_id = mysql_insert_id();
INSERT INTO album (artist_id, name) VALUES ($artist_id, "$album");
$labum_id = mysql_insert_id();
INSERT INTO track (album_id, name, length) VALUES ($album_id, "$track1", "$length1");
INSERT INTO track (album_id, name, length) VALUES ($album_id, "$track2", "$length2");
[...]

and for existing artists and albums you'd look them up by name or present select boxes to the user right away that return the appropriate artist and album ids to you and only have users add new artists and albums if these are not yet in your system ...

--
Hartmut Holzgraefe, MySQL Regional Support Manager EMEA

Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten
Amtsgericht Muenchen: HRB161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering

Options: ReplyQuote


Subject
Written By
Posted
Re: Inserting multiple lines into table
June 26, 2008 03:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.