[SOLVED] adding data to a table from another table using forms

[dub_stylee]

Hello, I am building a database to manage my music collection, and I have a form that looks something like this:

 

Album name: <text box>

Release date: <text box>

Genre: <selection list generated from mysql database>

Album artist: <selection list generated from mysql database>

<SUBMIT>

 

My question is this:  when I want to INSERT the new album information into my table, how can I convert the 'album artist' into the corresponding number from a different table?  I.e. the selection list is generated using names "Metallica", etc... but in the album table, the artist is stored as an integer, 'band_id' that is the primary key of the bands table.  I am thinking I will have to query the database for the band_id whose band_name matches the one chosen from the selection list?  But I am unsure how to accomplish this.  Any help is much appreciated.  Thanks!

 

----

Here is what I am trying to do:

$query = "SELECT band_id FROM bands WHERE band_name LIKE '".$band_name."'";

$result = mysql_query($query)

                or die("Invalid Query.");

 

$album_name = $_POST['album_name'];

$release_date = $_POST['release_date'];

$album_genre = $_POST['album_genre'];

 

... but I don't know how to access the band_id that is returned from my query?

 

Brian

[Yesideez]

I've done something similar.

 

Table "artists" lists (surprise, surprise!) the artists.

 

The form on my page looks something like this:

Artist: ############### or _____________________

 

The #'s are a drop-down selection box populated with the contents of the table "artists". The _'s is a text inbox box.

 

1. If the selection box has something selected, use that.

2. If there is text in the text box use that - add it to the "artists" table and use the ID it generated

[Yesideez]

A picture paints a thousand words...

 

[dub_stylee]

Yesideez, I really like the idea of having a selection list as well as a text box, that would allow me to add new information to the database if what I am looking for does not appear in the selection list.  I am new to PHP, could you give me an idea of how to accomplish this?  I know how to make the HTML-side populate the list and have the text box, but not how to handle it on the PHP side.  Also, any tips on how to access the band_id field that is returned in the query from my original post?  Thanks!

 

Brian

[Yesideez]

I'm here - typing what I think can be a long reply...

[Yesideez]

First your script runs and you grab the data from the form

$artistID=intval($_POST['artistid']);
$artistName=$_POST['artistname'];

 

2. Then we read the entire table of artists

$optArtists='';
$query=mysql_query("SELECT * FROM `artists` ORDER BY `name` ASC");
while ($row=mysql_fetch_assoc($query)) {
  $optArtists.='<option value="'.$row['id'].'"'.($row['id']==$artistID ? ' selected="selected"' : '').'>'.$row['name'].'</option>';
}

 

3. We check if the submit button was pressed

if ($_POST['subadd']) {
  if ($artistID>0) {
    //insert $artistName into the "artists" table and get the ID of it
  } else {
    //we're using the ID from the selection box
  }
}

Something like that - off the top of my head - up to you to piece together. Anything above you don't understand just ask but I don't want to go writing too much for you

[dub_stylee]

Thanks for your help Yesideez, I think that you gave me a great idea for a way to improve my database   here is what I finally ended up with to get the result I was looking for (for now)...

 

$band_name = $_POST['band_name'];
$query = "SELECT band_id FROM bands WHERE band_name LIKE '".$band_name."'";
$result = mysql_query($query)
                or die("Invalid Query.");

$album_name = $_POST['album_name'];
$release_date = $_POST['release_date'];
$album_genre = $_POST['album_genre'];

$row = mysql_fetch_array($result);
extract($row);

$query = "INSERT INTO albums (album_name, release_date, album_genre, band_id) VALUES ('".$album_name."', '".$release_date."', '".$album_genre."', '".$band_id."')";

 

Thank you!

 

Brian

[Yesideez]

Here's the reason for code chunk 2:

 

Say your "artists" table has hundreds of artists in it. Let's say you've had to select an artist somewhere in the middle and submit your form only to find out that your data hasn't been added because you forgot something. Having to select the artist again from that huge list can be a pain. That code not only builds the list for you but automatically selects the one chosen and displays it in the selection box.

 

($row['id']==$artistID ? ' selected="selected"' : '')

Reads: if row ID in database equals the one chosen by the user the selected text is used otherwise do nothing - it's called a ternary condition.

 

http://www.addedbytes.com/php/ternary-conditionals/

 

[.josh]

I'd recommend going for the dropdown option, but just to answer your question, you can do something like this:

 

insert into table1 (column) values ((select column1 from table2 where column2 = value))

[dub_stylee]

Thanks Crayon, it makes it much cleaner to do it all in one query, rather than doing another separate query to pull up the band_id!

[.josh]

well it makes more sense to use a dropdown so you don't have to worry about case sensitivity or mispelling when trying to 'convert' a name to an id later on.