how to update foreign key in many to many relationship

Question

i'm new in php,i have 2 table with many to many relation and another table for relations:

enter image description here

each time i want update foreign table i'm give the error:

Cannot add or update a child row: a foreign key constraint fails (`wikiseda`.`genre_singer`, CONSTRAINT `genre_singer_ibfk_1` FOREIGN KEY (`f_singer_id`) REFERENCES `singers` (`singerid`) ON DELETE CASCADE ON UPDATE CASCADE);

this is my code:

<?php
include('../db_inc.php');
define("UPLOAD_DIR",realpath(dirname(__FILE__)));

$singer_name =$_POST['singer_name'];
$singer_gender=$_POST['singer_gender'];
$singer_des=$_POST['singer_description'];
$singer_genre=$_POST['genre_list'];

    $path = UPLOAD_DIR .'/musics/'.$singer_name;
    if(!file_exists($path)){
        mkdir($path,0777,true);
        }

$sql ="INSERT INTO singers(singer_name,singer_gender,singer_description) VALUES ('$singer_name','$singer_gender','$singer_des')" ;
 $singer_id = mysql_insert_id();
$sql2 =("INSERT INTO genre_singer(f_singer_id,f_genre_id) VALUES ('$singer_id','$singer_genre')");

$result=mysql_query($sql)or die(mysql_error());
$result2=mysql_query($sql2)or die(mysql_error());
if('$result'){
    echo "insert successfully"; 

    };
?>

Please switch to [prepared statements](http://bobby-tables.com/php.html) to prevent [SQL injection](https://www.owasp.org/index.php/SQL_Injection). — Marcel Korpel, Nov 08 '13 at 15:31
You do have a different bug, yes, but it isn't okay. mysql_ functions are deprecated, and your code is vulnerable to sql injection. That is a more serious issue than your minor sql error. — Chris Baker, Nov 08 '13 at 15:33
To the bug itself, make sure you have a valid $singer_id and $singer_genre prior to executing the query. They must be numeric values that correspond to row IDs in the singer and genre table, respectively. — Chris Baker, Nov 08 '13 at 15:36
SQL injection bad, common but bad... but I don't think I've ever seen `mkdir()` with 0777 permissions embedded into PHP before, this is a new level of scary - hopefully the Apache/PHP user doesn't have permission to even do this... especially since it appears to be creating a directory within the web tree. — CD001, Nov 08 '13 at 15:36
@Chris also the code in my local and this is a test project,i don't want publish the code in web server, and i know about SQL Injection issue :) — Hamed mayahian, Nov 08 '13 at 15:41
@Radian the error message suggests otherwise. Do `die($sql2)` and paste the output here in the question, then go look at your singer table data. You will see that you are not executing the query, but you are trying to get the last insert id -- which does not correspond to the id generated from the insert query for the singer but some other random query previous in your code. — Chris Baker, Nov 08 '13 at 15:42
Best guess as to the actual problem, the genre doesn't exist... you'll get a foreign key restraint error if you're trying to create the link 'singer' > 'genre' in the `genre_singer` table if either the singer id or the genre id doesn't already exist ... and I can see you're creating the singer. — CD001, Nov 08 '13 at 15:44
He doesn't get the ID of the singer he creates, but some different ID. Slow down and consider the order of function calls :) — Chris Baker, Nov 08 '13 at 15:44
Also, `if('$result'){` will **always** evaluate to true because you're checking for an empty string. Remove the quotes to actually check the content of the variable. — Chris Baker, Nov 08 '13 at 15:46
lolyeah - the string to create the singer is defined `$sql` but it's not actually executed until AFTER the `mysql_insert_id()`, good point @Chris — CD001, Nov 08 '13 at 15:46
thanks friends, i'm also working on path and injection issue, — Hamed mayahian, Nov 08 '13 at 15:54

score 3 · Answer 1 · answered Nov 08 '13 at 15:42

SQL injection vulnerabilities and overly-loose directory permissions aside for a moment (though you really should pay heed to the comments about them). Try executing your first query before trying to get the id last inserted hence:

$sql ="INSERT INTO singers(singer_name,singer_gender,singer_description) VALUES ('$singer_name','$singer_gender','$singer_des')" ;    
$result=mysql_query($sql)or die(mysql_error());

$singer_id = mysql_insert_id();
$sql2 =("INSERT INTO genre_singer(f_singer_id,f_genre_id) VALUES ('$singer_id','$singer_genre')");
$result2=mysql_query($sql2)or die(mysql_error());

score 2 · Accepted Answer · answered Nov 08 '13 at 15:45

2

You are trying to get the id of the inserted record before actually inserting it.

You need to move your first query execution - $result=mysql_query($sql)or die(mysql_error()); before $singer_id = mysql_insert_id();.

Also:

Your code is vulnerable to SQL injection (as others noted).
Creating a directory on your server with an arbitrary path/name which comes from the user is probably a bad idea. If you follow that up with creating a file in a similar way you will allow any user to execute arbitrary code on your server.

answered Nov 08 '13 at 15:45

Vatev

7,493
1
32
39

can you give me an article for correct way to create path on sever? – Hamed mayahian Nov 08 '13 at 15:50
1

What I usually do in this case is to allow only a small set of safe characters for file/dir names (for example `a-zA-Z0-9 _-.`) and allow only specific extensions. [This question](http://stackoverflow.com/questions/2668854/sanitizing-strings-to-make-them-url-and-filename-safe) looks relevant to creating files with user supplied names. – Vatev Nov 08 '13 at 15:58

Chris Baker · Answer 3 · 2013-11-08T17:15:28.280

For the sake of being complete, here's the work done with PDO:

// note: untested code follows
$pdo = new PDO('mysql:host='.$host.';dbname='.$db_name, $username, $password);

$statement = $pdo->prepare('
    INSERT INTO `singers` (
        singer_name,
        singer_gender,
        singer_description
    ) VALUES (
        :singer_name,
        :singer_gender,
        :singer_des
    )
');

$statement->execute(array(
    'singer_name'=>$_POST['singer_name'],
    'singer_gender'=>$_POST['singer_gender'],
    'singer_des'=>$_POST['singer_description']
));
$singer_id = $pdo->lastInsertId();

if (!$singer_id) {
    // tip: do something nicer than die
    die('Error occurred:'.implode(":",$pdo->errorInfo()));
}


$statement = $pdo->prepare('
    INSERT INTO `genre_singer` (
        f_singer_id,
        f_genre_id
    ) VALUES (
        :singer_id,
        :singer_genre
    )
');
$result = $statement->execute(array(
    'singer_id'=>$singer_id,
    'singer_genre'=>$_POST['genre_list']
));

if (!result) {
    // tip: do something nicer than die
    die('Error occurred:'.implode(":",$pdo->errorInfo()));
}

Documentation

PDO - http://us1.php.net/manual/en/pdo.errorinfo.php
PDO::prepare - http://us1.php.net/manual/en/pdo.prepare.php
PDOStatement::execute - http://php.net/manual/en/pdostatement.execute.php
PDO::errorInfo - http://us1.php.net/manual/en/pdo.errorinfo.php
PDO::lastInsertId - http://php.net/manual/en/pdo.lastinsertid.php

Didn't you forget a `$` before `singer_id` (in the second `execute` statement)? — Marcel Korpel, Nov 08 '13 at 17:01
@MarcelKorpel Aye, fixed. You can edit answers to fix stuff like that :) — Chris Baker, Nov 08 '13 at 17:15

how to update foreign key in many to many relationship

3 Answers3