Question in my add script

[bbmak]

I want the table to be like this. If the artist_name is existed, it just add the lyric in lyric table with the same artist_id.

artist table
artist_id / artist_name / gender
incremental id 11 / Britney Spears / Female
incremental id 12 / BSB / Male

lyric table
lyric_id / artist_id / song_title / lyric
incremental id 1 / 11 / baby hit me... / lyric here
incremental id 2 / 11 / I am a slave / lyric here
Incremental id 3 / 12 / I want it that way / lyric here
incremental id 4 / 12 / Show me the meaning / lyric here

I think the code should be added around here
if (mysql_num_rows($result) > 0) {

added here<<<
$artist_id = mysql_insert_id(); //assign same artist_id as artist table in lyric table , but it is not work for this line
$lyric_query = "INSERT INTO lyric (artist_id, song_title, lyric) VALUES ('{$artist_id}','{$input['song_title']}','{$input['lyric']}')";
$lyric_result = mysql_query($lyric_query) or die('Error in lyric query: ' . mysql_error());

}

here is the code

<?php 


if($_POST['submit']) 
{ 
    include ('connect.php'); 
    //Sanitize data 
    $input['artist_id'] = mysql_real_escape_string($_POST['artist_id']);
    $input['artist_name'] = mysql_real_escape_string($_POST['artist_name']); 
    $input['lyric'] = mysql_real_escape_string($_POST['lyric']); 
    $input['song_title'] = mysql_real_escape_string($_POST['song_title']); 
    $input['gender'] = mysql_real_escape_string($_POST['gender']); 

$result = mysql_query("SELECT * FROM artist WHERE artist_name = '" . $_POST['artist_name'] . "'"); 
if (mysql_num_rows($result) > 0) { 
//echo "artist name is existed."; 

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

//$query = "UPDATE Student SET artist_id = '" . $_POST['artist_id'] . "' WHERE artist_id = '" . $_POST['artist_id'] . "'";
//mysql_query($query);


    $artist_id = mysql_insert_id(); 
    $lyric_query = "INSERT INTO lyric (artist_id, song_title, lyric) VALUES ('{$artist_id}','{$input['song_title']}','{$input['lyric']}')"; 
    $lyric_result = mysql_query($lyric_query) or die('Error in lyric query: ' . mysql_error()); 

} 

else {

    $artist_query = ("INSERT INTO artist (artist_name, gender) VALUES ('{$input['artist_name']}','{$input['gender']}')"); 
    $artist_result = mysql_query($artist_query) or die('Error in artist query: ' . mysql_error()); 
    $artist_id = mysql_insert_id(); 
    $lyric_query = "INSERT INTO lyric (artist_id, song_title, lyric) VALUES ('{$artist_id}','{$input['song_title']}','{$input['lyric']}')"; 
    $lyric_result = mysql_query($lyric_query) or die('Error in lyric query: ' . mysql_error()); 
} 
} 

?>

[drew010]

the line
$artist_id = mysql_insert_id();

doesnt work because that function is used to get the new value from an auto_increment field AFTER an insert statement, and there is no insert.

my guess is you are going to want to fetch the data from the previous select and get the artist id from the existing field.

[bbmak]

so do I use mysql_fetch_array()???
Can you show me an example?

[drew010]

it works like this...

//.....
$q = "SELECT fieldA, fieldB, fieldC FROM mytable WHERE fieldD = 'xxxx'";
$r = mysql_query($q);

$data = mysql_fetch_array($r);

echo "fieldA = " . $data['fieldA'] . "<br />";
echo "fieldB = " . $data['fieldB'] . "<br />";
echo "fieldC = " . $data['fieldC'] . "<br />";

[man]mysql_fetch_array[/man]