Jump to content

SQL Insertion error

sugarplum_19

By sugarplum_19
in MySQL Help

 Share

Recommended Posts

sugarplum_19 Newbie

sugarplum_19

Posted
Posted

I am trying to insert data via a separate web form, but when I want to insert the data i get this error.

 

SQL Insertion error: Cannot add or update a child row: a foreign key constraint fails (`ang_tania`.`studentcourses`, CONSTRAINT `studentcourses_ibfk_1` FOREIGN KEY (`SID`) REFERENCES `students` (`SID`))

 

Below shows the sql table that I have created

 

create table if not exists Studentcourses(

SID int(4) not null,

CID int(4) not null,

Grade float(4) not null,

comments varchar(200) not null,

key SID(SID),

key CID(CID),

PRIMARY KEY (SID, CID),

foreign key(SID) references students(SID),

foreign key (CID) references courses(CID)

)ENGINE=InnoDB;

 

this is the php bit

 

 

<?php

 

require("db_config.php");

 

class db_studentcourse {

 

    private $conn;

 

    function getResult($sql)

    {

        $result = mysql_query($sql, $this->conn);

        if($result)

        {

            return $result;

        }

        else

        {

            die("SQL Retrieve Error: ".mysql_error());

        }

 

    }

 

    function insert_studentcourse($sid, $cid, $grade, $comments) {

        $esc_sid = mysql_real_escape_string($sid, $this->conn);

        $esc_cid = mysql_real_escape_string($cid, $this->conn);

        $esc_grade = mysql_real_escape_string($grade, $this->conn);

        $esc_comments = mysql_real_escape_string($comments, $this->conn);

        $sql = "INSERT INTO studentcourses(sid, cid, grade, comments)

        VALUES ('{$sid}', '{$cid}', '{$grade}', '{$comments}')";

        $result = mysql_query($sql, $this->conn);

        $sql = "SELECT SID FROM STUDENTS";

        if (!$result) {

            die("SQL Insertion error: " . mysql_error());

        } else {

            $numofrows = mysql_affected_rows($this->conn);

            return $numofrows;

        }

    }

 

    function openDB() {

        $this->conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); {

            if (!$this->conn)

                die("SQL Connection error: " . mysql_error());

        } $db_selected = mysql_select_db(DB_NAME, $this->conn);

        if (!$db_selected) {

            die("SQL Selection error: " . mysql_error());

        }

    }

 

    function delete_studentcourse($sid,$cid) {

        $esc_sid= mysql_real_escape_string($sid, $this->conn);

        $esc_cid= mysql_real_escape_string($cid, $this->conn);

        $sql = "DELETE FROM studentcourses where SID='{$sid}' && where CID='{$cid}'";

        $result = mysql_query($sql, $this->conn);

 

        if (!$result)

            die("SQL Error: " . mysql_error());

        else {

            $numofrows = mysql_affected_rows($this->conn);

            return $numofrows;

        }

    }

 

 

    function closeDB() {

 

        mysql_close($this->conn);

    }

 

}

 

 

?>

 

 

 

<?php include 'db_studentcourse.php'; //import the class in this web page    ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

        <title>Student-Course Insert new Record</title>

    </head>

    <body>

        <?php

        $db1 = new db_studentcourse();

        $db1->openDB();

 

        $sql = "SELECT SID FROM STUDENTS";

        $result = $db1->getResult($sql);

 

        $sql = "SELECT CID FROM COURSES";

        $result1 = $db1->getResult($sql);

 

 

 

 

        if (!$_POST) {

        ?>

            <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">

                Select student ID: <select name = "SID">

<?php

            while ($row = mysql_fetch_assoc($result))

                echo "<option value='{$row['SID']}'>{$row['SID']}</option>";

?> 

            </select><br/>

            Select course ID: <select name = "CID">

<?php

            while ($row = mysql_fetch_assoc($result1))

                echo "<option value ='{$row['CID']}'>{$row['CID']} </option>";

?>

            </select><br/>

 

            Enter grade:<input type="text" name="grade" /><br />

            Enter comment:<input type="text" name="comments" /><br />

 

            <input type="submit" value="Save" />

            <input type="reset" value="Reset" name="reset"/>

            <input type="button" value="Cancel"/>

 

        </form>

<?php

} else {

$sid = $_POST['sid'];

$cid = $_POST['cid'];

$grade = $_POST['grade'];

$comments = $_POST['comments'];

$db1 = new db_studentcourse();

$db1->openDB();

$numofrows = $db1->insert_studentcourse($sid, $cid, $grade, $comments);

echo "Success. Number of rows affected: <strong>{$numofrows}<strong>";

$db1->closeDB();

}

?>

 

</body>

</html>

 

 

Any help to solve this problem will be greatly appreciative

 

Link to comment
https://forums.phpfreaks.com/topic/223374-sql-insertion-error/
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.