Page 1 of 1

DB INSERT issue (newb)

Posted: Sat Jan 23, 2010 7:25 am
by MiniMonty
Hi all,

having a little trouble inserting new entries to a table on my DB.
The error I get is:
"Cannot add or update a child row: a foreign key constraint fails (`shutterbug/students_to_courses`, CONSTRAINT `students_to_courses_ibfk_2` FOREIGN KEY (`course_fk`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE)"

The code I am using to insert (new student onto the course_to_students table) is

Code: Select all

 
////////////////   INSERT THE STUDENT INTO THE students_to_courses TABLE  
if (isset ($_POST['SignUp'])){
     $sql = mysql_query("INSERT INTO `students_to_courses` ( `id` , `student_fk` , `course_fk` , `course_completed` ) 
    VALUES ('', '$id', '$course_num', '0')")
     or die (mysql_error());
 



$id is declared in the session and $course_num is declared as a POST from the preceding page.
I'm pretty sure the issue is with "course_num" as if I change this to a simple integer it works fine.
I need to be able to use a variable for the third value as the receding page has options for a number
of courses to sign up to. Am I missing something fundamental and basic ?

All and any help very much appreciated (whole script posted below)

Best wishes
Monty

Code: Select all

 
<?php
session_start();
include_once "scripts/connect_to_mysql.php";
 
if (isset($_GET['id'])) {
   
     $id = $_GET['id'];
 
} else if (isset($_SESSION['id'])) {
   
     $id = $_SESSION['id'];
 
} else {
   
   include_once "index.php";
   exit();
}
 
$firstname= "";
$tutor_name="";
$coursenum =$_POST ["coursenum"];
$tutor_num = "";
$tutor_fk= "";
$tutorFirstname="";
$tutorSecondname= "";
$course_num ="";
 
 
////////////////// v   IDENTIFY THE USER 
$id = mysql_real_escape_string($id);
$id = eregi_replace("`", "", $id);
$sql = mysql_query("SELECT * FROM myMembers WHERE id='$id'");
while($row = mysql_fetch_array($sql)){ 
 
    $firstname = $row["firstname"];
}
 
///////////////    IDENTIFY THE COURSE 
 
$id = mysql_real_escape_string($id);
$id = eregi_replace("`", "", $id);
$sql = mysql_query("SELECT course_id FROM courses WHERE course_id='$coursenum'");
while($row = mysql_fetch_array($sql)){ 
 
    $course_num = $row["course_id"];
}
if ( $course_num == 1 ) {
    $title = "Day One Digital" AND $course_num ="1";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
} else if ($course_num == 2){
    $title = "Tune In To Programs" AND $course_num ="2";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
}  else if ($course_num == 3){
    $title = "Shutter Speeds For Shutter Bugs" AND $course_num ="3";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
}  else if ($course_num == 4){
    $title = "Compose Yourself" AND $course_num ="4";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
}  else if ($course_num == 5){
    $title = "The Modern portrait" AND $course_num ="5";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
}  else if ($course_num == 6){
    $title = "Outward Bound" AND $course_num ="6";
    print "CHECK IF course_num HAS A VALUE... VALUE IS  " . "$course_num";
}
 
///////////////    IDENTIFY THE COURSE TUTOR
 
$id = mysql_real_escape_string($id);
$id = eregi_replace("`", "", $id);
$sql = mysql_query("SELECT * FROM courses WHERE course_id='$course_num'");
while($row = mysql_fetch_array($sql)){ 
    $tutor_num = $row["tutor_fk"];
}
 
///////////////    GET TUTOR NAME
 
$sql = mysql_query("SELECT * FROM myMembers WHERE id= '$tutor_num'");
while($row = mysql_fetch_array($sql)){ 
    $tutorFirstname = $row["firstname"];
    $tutorSecondname = $row["lastname"];
    $tutor_name = "$tutorFirstname" . " " . "$tutorSecondname";
}
 
////////////////   INSERT THE STUDENT INTO THE students_to_courses TABLE  
if (isset ($_POST['SignUp'])){
 
     $sql = mysql_query("INSERT INTO `students_to_courses` ( `id` , `student_fk` , `course_fk` , `course_completed` ) 
    VALUES ('', '$id', '$course_num', '0')")
     or die (mysql_error());
     $id = mysql_insert_id();
      
     $msgToUser = "OK $firstname, you are signd up !";
     include_once 'msgToUser.php'; 
     
}
     //print_r($_REQUEST);
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Courses</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/main.css" rel="stylesheet" type="text/css" />
<link rel="icon" href="http://www.yourwebsite.com/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="http://www.yourwebsite.com/favicon.ico" type="image/x-icon" />
 
<style type="text/css">
<!--
a {
    font-family: Verdana;
    font-size: 11px;
}
a:visited {
    color: #FF6600;
    font-family: Verdana;
    font-size: 11px;
}
a:hover {
    color: #6699FF;
    font-family: Verdana;
    font-size: 11px;
}
-->
</style></head>
 
<body>
<?php include_once "header_template.php"; ?>
<br>
<br>
<table width="72%" align="center" cellpadding="2" cellspacing="2">
  <tr>
    <td width="480" height="326"><div align="center">
      </div>
      <div align="center"></div>
      <div align="left">
        <table width="87%" height="235"  border="0" align="center" cellpadding="0" cellspacing="0">
          <tr>
            <td><div align="justify">Hi&nbsp;<span class="orangeText"><?php print "$firstname"; ?><br>
              </span>Get ready to sign up for <span class="orangeText"><br>
              <span class="Big_Orange_Times"><?php print "$title"; ?></span> 
             
              <br>
              <br>
              </span>Just click the SIGN UP button and the illustrated course will download automatically as a PDF. Read the course and complete the assignments then, when you are ready, click on "Your Courses" above and you will see this course listed - then you can upload yourcourse assignments and your personal tutor will critique your work and give feedback.<br>
  The tutor for this course is: <?php print'<a href="profile.php?id=' . $tutor_num .  '"   class="orangeText">' . $tutor_name .  '  </a>'; ?>  </span></div></td>
  
          </tr>
          <tr>
            <td height="53"><form name="student_course_insert" method="post" action="mem_course_signup.php">
              <div align="left"><br>
                  I have read the Terms and Conditions 
                    <input name="terms_box" type="checkbox" id="terms_box" value="checkbox">
                  <br>
                  Sign Me Up ! &nbsp;&nbsp;
                  <input type="submit" name="SignUp" value="Submit">
                  <br>
                  </div>
            </form></td>
          </tr>
        </table>
      </div></td>
    <td width="475"><div align="center">
      <?php include_once  "showpics.php"; ?>
</div></td>
  </tr>
</table>
<table width="990" align="center" cellpadding="6" cellspacing="24">
  <tr>
    <td width="378"><div align="center"><span class="style21"><span class="blueText">DB INSERT TEST <br>
    </span></span></div></td>
<td width="378"><div align="justify" class="style21"></div></td>
  </tr>
</table>
<p><br>
  <?php include_once "footer_template.php"; ?>
</p>
</body>
</html>
 

Re: DB INSERT issue (newb)

Posted: Sat Jan 23, 2010 7:49 am
by requinix
The error is saying that there must be a record in the `courses` table with `course_id` = $course_num before you can run that INSERT query.

If you can't figure it out from there, what is the exact value of $course_num and have you looked in `courses` for that course ID?

Re: DB INSERT issue (newb)

Posted: Sat Jan 23, 2010 8:32 am
by MiniMonty
Aha... thanks for decoding the error - makes a bit more sense now.

In the courses table there is a column "course_id" and the values are 1 to 6 (see pic).
The value of "$course_num" in the script is an number from 1 to 6 to match the values in that column.

Have I screwed up the foreign keys somehow ?
Pretty new to this and building a many-to-many set of relationships is
seriously taxing my knowledge limits !

Best wishes
Monty
Image

Re: DB INSERT issue (newb)

Posted: Sat Jan 23, 2010 4:52 pm
by MiniMonty
Small bump...

Any news or views on this would be most helpful...