Example code can be confusing, so I wrote you some code myself. Create your form like this, obviously changing it to fit your site:
<form action="add_song.php" method="post">
<input type="text" name="song" value="Type song name" />
<input type="text" name="performer" value="Type performer name" />
<input type="submit" value="Submit!" />
</form>
Then put this into add_song.php, but make sure you change the variables at the top so they are accurate for your database and table names!
<?php
// assuming our form submits to this script
$song =
mysql_real_escape_string($_POST['song']);
$performer =
mysql_real_escape_string($_POST['performer']);
// setup database connection
$db_user = "me";
$db_pass = "mypassword";
$db_host = "localhost";
$db_name = "songsdb";
// choose our tables
$tbl_songs = "Table1";
$tbl_performers = "Table2";
$tbl_join = "Table3";
// connect
$connection = mysql_connect($db_host,
$db_user,
$db_pass);
mysql_select_db($db_name);
/* Since we know we are going to check
two different tables, $tbl_songs and
$tbl_performers, to see if our data
already exists, we should write a function
to avoid writing the same code twice. */
function row_exists($table, $where) {
// use sprintf to keep out code clean
$re_query = sprintf("SELECT COUNT(*)
AS TOTAL
FROM `%s`
WHERE %s",
$table, $where);
// execute the query
$re_result = mysql_query($re_query);
// $row = 1st row of the result
$row = mysql_fetch_assoc($re_result);
// make sure TOTAL is an integer
$total = intval($row['TOTAL']);
if($total > 0)
return true;
/* Since return statements always
end the execution of a function,
we did not need an "else" after
the if statement above. Why?
because if our $total is greater
than zero and the function returns
true, it can't also return false. */
return false;
}
// we'll use these strinsg for performers and songs
$insert_str = "INSERT INTO `%s` VALUES ('', '%s')";
$select_str = "SELECT `%1$s` FROM %2$s WHERE `%1$` = '%3$s' LIMIT 1";
// we'll use these vars to put our song and performer ids in
$song_id = NULL;
$performer_id = NULL;
// now we check our db with our form data
if(row_exists($tbl_songs, "`SongTitle` = '{$song}'")) {
// the song was already in the database
$message = "{$song} was already in the db!";
$query = sprintf($select_str,
"SID",
$tbl_songs,
$song);
$result = mysql_query($query);
$row = mysql_fetch_assoc($query);
$song_id = intval($row['SID']);
}
else {
// the song was not in the database
$query = sprintf($insert_str,
$tbl_songs,
$song);
mysql_query($query);
$song_id = mysql_insert_id();
$message .= "{$song} was added to the db!";
}
if(row_exists($tbl_performers, "`Performer` = '{$performer}'")) {
// the performer was already in the database
$message = "{$performer} was already in the db!";
$query = sprintf($select_str,
"PID",
$tbl_performers,
$performer);
$result = mysql_query($query);
$row = mysql_fetch_assoc($query);
$performer_id = intval($row['PID']);
}
else {
// the song was not in the database
$query = sprintf($insert_str,
$tbl_performers,
$performer);
mysql_query($query);
$performer_id = mysql_insert_id();
$message .= "{$performer} was added to the db!";
}
// finally, we can add our PID and SID to join table
$query = "INSERT INTO `{$tbl_join}`
VALUES ('{$song_id}', '{$performer_id}')";
mysql_query($query);
?>
