Foreign key help

[phaga]

 

I'm quite new to PHP and databases but im trying to learn some at school. Only problem is that my teacher in that class is'nt really that good, to put it nicely. So we have to learn everything ourselves. My task is to create a order form using databases and PHP. To do this i set up this database(backup code from Sequel Pro):

 

# ************************************************************
# Sequel Pro SQL dump
# Version 3408
#
# http://www.sequelpro.com/
# http://code.google.com/p/sequel-pro/
#
# Host: localhost (MySQL 5.5.9)
# Database: Billettbestilling
# Generation Time: 2012-05-15 12:16:43 +0000
# ************************************************************




/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;




# Dump of table Bestilling
# ------------------------------------------------------------


DROP TABLE IF EXISTS `Bestilling`;


CREATE TABLE `Bestilling` (
  `BestillingID` int(3) NOT NULL AUTO_INCREMENT,
  `PersonID` int(4) DEFAULT NULL,
  `ForestillingID` int(2) DEFAULT NULL,
  `Billetter` int(2) NOT NULL,
  `Vilkar` int(3) DEFAULT NULL,
  PRIMARY KEY (`BestillingID`),
  KEY `fk_Bestilling_Person` (`PersonID`),
  KEY `fk_Bestilling_Forestilling1` (`ForestillingID`),
  CONSTRAINT `fk_Bestilling_Forestilling1` FOREIGN KEY (`ForestillingID`) REFERENCES `Forestilling` (`ForestillingID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Bestilling_Person` FOREIGN KEY (`PersonID`) REFERENCES `Person` (`PersonID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;






# Dump of table Forestilling
# ------------------------------------------------------------


DROP TABLE IF EXISTS `Forestilling`;


CREATE TABLE `Forestilling` (
  `ForestillingID` int(11) NOT NULL AUTO_INCREMENT,
  `Tidspunkt` datetime DEFAULT NULL,
  `Antallseter` int(3) DEFAULT NULL,
  PRIMARY KEY (`ForestillingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;






# Dump of table Person
# ------------------------------------------------------------


DROP TABLE IF EXISTS `Person`;


CREATE TABLE `Person` (
  `PersonID` int(4) NOT NULL AUTO_INCREMENT,
  `Fornavn` varchar(30) NOT NULL,
  `Etternavn` varchar(45) NOT NULL,
  PRIMARY KEY (`PersonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;








/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

This is my PHP:

 

<?php 

// Innloggins variabler!
define('DB_NAME', 'Billettbestilling');
define('DB_USER', 'root');
define('DB_PASSWORD', 'midlertidig');
define('DB_HOST', 'localhost');

    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    
    if (!$link) {
     die('Klarer ikke å koble til databasen!' . mysql_error());
    }
    
    $db_selected = mysql_select_db(DB_NAME, $link);
    
    if (!$db_selected) {
     die('Finner ikke databasen!' . DB_NAME . ': ' . mysql_error());
    }



$value = $_POST['fornavn'];
$value2 = $_POST['etternavn'];
$value3 = $_POST['forestilling'];
$value4 = $_POST['billetter'];
$value5 = $_POST['vilkar'];




$sql = "INSERT INTO Person (Fornavn, Etternavn) VALUES ('$value', '$value2')";
if (!mysql_query($sql)) {
echo('Ops! Noe gikk galt!(Person): ' . mysql_error());	
}


$personid = mysql_insert_id();


$dato = date("d/m/Y");


$sql2 = "INSERT INTO Forestilling (Tidspunkt) VALUES ($dato)";
if (!mysql_query($sql2)) {
echo('Ops! Noe gikk galt!(Forestilling): ' . mysql_error());	
}


$forestillingid = mysql_insert_id();


$sql3 = "INSERT INTO Bestilling (PersonID, ForestillingID, Billetter, Vilkar) VALUES ($personid, $forestillingid, '$value4', '$value5')";
    if (!mysql_query($sql3)) {
    	echo('Ops! Noe gikk galt!(Bestilling)' . mysql_error() . ' SQL : ' . $sql3);
    }


mysql_close();


?>

 

Adding test data gives me this error message:

 

Ops! Noe gikk galt!(Bestilling)Cannot add or update a child row: a foreign key constraint fails (`billettbestilling`.`bestilling`, CONSTRAINT `fk_Bestilling_Person` FOREIGN KEY (`PersonID`) REFERENCES `Person` (`PersonID`) ON DELETE NO ACTION ON UPDATE NO ACTION) SQL : INSERT INTO Bestilling (PersonID, ForestillingID, Billetter, Vilkar) VALUES (2, 2, '2', 'ja')

[/size]

 

[/size]I understand that this might be a "noob" question.. Please rewrite or point out my errors!Thanks in advance!

[ManiacDan]

The error message states that table Bestilling has a foreign key constraint.  You may not insert a PersonID into  Bestilling unless that PersonID already exists in the Person table. 

[phaga]

Thank you for quick response!

 

But I have set PersonID to autoincrement? What to do?

[ManiacDan]

Your code should be working, I don't understand why it's not.

 

Is there a personID 2 in the Person table?

[phaga]

No, there is only one PersonID in the table Person, which is also the primary key. How can I debug the code?

[Jessica]

I think he's asking is there a row with the PersonID of 2, not a second column called that.

[ManiacDan]

This error message is saying "you are trying to put PersonID 2 into bestilling, but PersonID 2 doesn't exist in the Person table."  So I'm asking: does personID 2 exist in the person table?

[phaga]

Oh, now I understand. Yes, there is a row with PersonID 2. Just tried again and I recieved this error:

 

Ops! Noe gikk galt!(Bestilling)Cannot add or update a child row: a foreign key constraint fails (`billettbestilling`.`bestilling`, CONSTRAINT `fk_Bestilling_Person` FOREIGN KEY (`PersonID`) REFERENCES `Person` (`PersonID`) ON DELETE NO ACTION ON UPDATE NO ACTION) SQL : INSERT INTO Bestilling (PersonID, ForestillingID, Billetter, Vilkar) VALUES (3, 3, '1', 'ja')

 

Checked Sequel Pro, and a row with PersonID 3 was created..

 

Any suggestions?

[ManiacDan]

Very weird.  DIE() right after inserting the person, don't let the other two queries run.  Does THAT work?

[phaga]

The page won't load when I use DIE()...

[phaga]

I took a look at my database and I believe I have done something wrong.

Let me shortly explain the meaning of the database.

 

It is a "Reserve tickets" system for a school show. The person is registered in the "Person"-table, you should be able to choose which show you want to see (Forestilling in Norwegian is Show in English) and Bestilling is the order.

 

My database puts data into the Forestilling-database, that is correct. How to I make a drop down menu for this?