Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other Technologies > Database Help
Reload this Page

Multiple insert problem

Discuss Multiple insert problem in the Database Help forum on Codewalkers.
Multiple insert problem General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old October 7th, 2003, 05:59 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
Multiple insert problem
I'm trying to set up an insert query to add info into 2 tables at once, a "one-to-many" relation I beleive its called. Lets say its a song lyric database. Table 1 is called "artist" with the fields "id" and "artist". Table 2 is called "song" with the fields of "title", "lyrics", and a reference to the artist id, which i think is where this is messing up. Basically, I'll have a php page to show just a list of the artists from Table 1, which links to Table 2 showing song titles and lyrics.
Heres the insert query...
php Code:
Original - php Code 
  1.  
  2. Connect, yada yada yada
  3.  
  4. $query =  "INSERT INTO artist (id, name) VALUES ('', '$artist')";
  5. $query =  "INSERT INTO song (title, lyrics, id) VALUES ('$title','$lyrics','artist_id()')";

This is the only reference I could find to do this insert, and I'm sure its wrong. I don't get mysql error messages, but nothing is going into the tables. ???
Reply With Quote
  #2  
Old October 7th, 2003, 06:19 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: Multiple insert problem
I am assuming your id field in the artist database is an autoincrement field so here goes.
in a nutshell...
1. you need to insert the artist name into the first table.

2. you need to find out the autoincrement id of the record just inserted

3. you need to insert the data into the song table.

here is what you need to do

php Code:
Original - php Code 
  1.  
  2. <?
  3. $query =  "INSERT INTO artist (name) VALUES ('$artist')";
  4. mysql_query($query);
  5. //now to retreive the id of the newly inserted record...
  6. $artist_id= mysql_insert_id();
  7. //this gets the last inserted id
  8.  
  9. $query =  "INSERT INTO song (title, lyrics, id) VALUES ('$title','$lyrics','$artist_id()')";
  10. mysql_query($query);
  11.  
  12. ?>

that should solve your problem.
Reply With Quote
  #3  
Old October 7th, 2003, 06:39 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
The artist table still needs an id field to be auto-incremented, doesnt it? I'm not getting it to work either way.
Reply With Quote
  #4  
Old October 7th, 2003, 06:48 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: Multiple insert problem
yes, that is the only way it will work.
Reply With Quote
  #5  
Old October 7th, 2003, 06:58 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
php Code:
Original - php Code 
  1.  
  2. <?
  3. include("dbinfo.inc.php");
  4. $dbh=mysql_connect ("localhost", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
  5. mysql_select_db ("$database"); 
  6. $query =  "INSERT INTO artist (name) VALUES ('$artist')";
  7. mysql_query($query);
  8. //now to retreive the id of the newly inserted record...
  9. $artist_id= mysql_insert_id();
  10. //this gets the last inserted id
  11.  
  12. $query =  "INSERT INTO song (title, lyrics, id) VALUES ('$title','$lyrics','$artist_id()')";
  13. mysql_query($query);
  14.  
  16.  
  17. echo "<meta http-equiv="refresh" content="0; url=artist.php" />n";
  18. ?> 


Nothing is going in. I have an id field auto-incrementing on the artist table. Does the song table need an id field also? Indexing, primary?
Reply With Quote
  #6  
Old October 7th, 2003, 07:24 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: Multiple insert problem
I just ran the code here and it worked like a charm. I am not quite sure what is going on...I will keep looking
Reply With Quote
  #7  
Old October 7th, 2003, 07:26 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
OK, I'll start the database from scratch and try again.
Reply With Quote
  #8  
Old October 7th, 2003, 07:52 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
Found the problem. Hopefully the display part will be easier. Thnx for the help.
Reply With Quote
  #9  
Old October 7th, 2003, 07:57 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: Multiple insert problem
what was the problem?
Reply With Quote
  #10  
Old October 7th, 2003, 09:07 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
I took the (yada yada) stuff out of the insert querys and it went right in. Now my php artist list shows up with the artist names as links, which is fine, because I want them to link to the song table, but I'm not getting the song table to display the info when i click on them, even though the info is in the db. Not sure if the link info is getting passed to bring up the song table or what, probably need to redo the code for it.
Reply With Quote
  #11  
Old October 7th, 2003, 11:52 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Multiple insert problem
OK, I made some minor changes, maybe you can help me straighten this out.
I changed the original insert query to add the artist and title to the artist table. This way, the artist listing will show the artist and song title. However, the artist.php page shows only the artist as the link to the second table. I'd like to change this to include title, with the title as the link. I haven't been able to figure out how to change this.
php Code:
Original - php Code 
  1.  
  2. include("dbinfo.inc.php");
  3. $dbh=mysql_connect ("localhost", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
  4. mysql_select_db ("$database");  
  5. $query=("SELECT artist, title FROM artist ORDER BY title ASC");
  6.  $result = mysql_query($query);
  7. while($row = mysql_fetch_object($result))
  8.  { echo '<a href="lyrics.php?artist='.$row->id.'">'.$row->name.'</a><br />';
  9.  }


The second table (song.php) being linked to isn't displaying anything. I can usually alter a results page to display the way I want, but this one has me totally stumped.
php Code:
Original - php Code 
  1.  
  2. include("dbinfo.inc.php");
  3. $dbh=mysql_connect ("localhost", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
  4. mysql_select_db ("$database"); 
  5. $artist = $_GET['artist'];
  6. $result=mysql_query("SELECT * FROM song WHERE artist_id='.$artist.'");
  7.  $i = 0; 
  8. echo "<TABLE width=400 border=0 align=center cellpadding=1 cellspacing=0><tr>"; 
  9. while ($i < $num):
  10.  
  11.    $title=mysql_result($result,$i,"title");
  12.    $lyrics=mysql_result($result,$i,"lyrics");
  13.     echo "<td><font size=2 face=verdana, times><b>
  14.     $title<br>
  15.     $lyrics
  16.  </b></font>
  17.  <br><hr size=1 width=50%> </td>";
  18.     echo "</tr>"; 
  19.     $i++; 
  20.     endwhile;


Sorry if I'm dense on this, I usually don't have this much trouble.
Reply With Quote
  #12  
Old October 8th, 2003, 12:24 AM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: Multiple insert problem
the song query is incorrect.

$result=mysql_query("SELECT * FROM song WHERE artist_id='.$artist.'");

should be

$result=mysql_query("SELECT * FROM song WHERE artist_id='".$artist."'");

that should fix it.

and as for including the song title in the artist table you have broken the one to many relational rule and will have quite a bit of redundant data in the db. If you want to have the artist and title display together for each title, you would use a join query to pull the data from both tables.
Reply With Quote