Foreignkey help? =)

[MathiasJor]

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. I have a task to create a contact from using databases and PHP. To do this i set up this database(backup code from phpmyadmin):

 

-- phpMyAdmin SQL Dump
-- version 3.4.10.1
-- http://www.phpmyadmin.net
--
-- Vert: localhost
-- Generert den: 11. Mar, 2012 08:16 AM
-- Tjenerversjon: 5.1.60
-- PHP-Versjon: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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 */;

--
-- Database: `mjornsen_kontakt`
--

-- --------------------------------------------------------

--
-- Tabellstruktur for tabell `firma`
--

DROP TABLE IF EXISTS `firma`;
CREATE TABLE IF NOT EXISTS `firma` (
  `firmaid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'FK fra person',
  `firmnavn` varchar(50) NOT NULL COMMENT 'firmanavn',
  PRIMARY KEY (`firmaid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellstruktur for tabell `henvendelser`
--

DROP TABLE IF EXISTS `henvendelser`;
CREATE TABLE IF NOT EXISTS `henvendelser` (
  `id` int(5) NOT NULL,
  `melding` text NOT NULL COMMENT 'melding',
  `date` date NOT NULL,
  `henvdelsesid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'PK for hendvendelser',
  PRIMARY KEY (`henvdelsesid`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Tabellstruktur for tabell `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'ID - Identifikator PK',
  `fornavn` varchar(35) NOT NULL COMMENT 'Fornavn til kunde',
  `etternavn` varchar(35) NOT NULL COMMENT 'Etternavn til kunde',
  `email` varchar(50) NOT NULL COMMENT 'Email til kunde',
  `firmaid` int(5) DEFAULT NULL COMMENT 'Firma til kunden',
  PRIMARY KEY (`id`),
  KEY `firmaid` (`firmaid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='person tabell' AUTO_INCREMENT=12 ;

--
-- Dataark for tabell `person`
--

--
-- Begrensninger for dumpede tabeller
--

--
-- Begrensninger for tabell `henvendelser`
--
ALTER TABLE `henvendelser`
  ADD CONSTRAINT `henvendelser_ibfk_2` FOREIGN KEY (`id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Begrensninger for tabell `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `person_ibfk_2` FOREIGN KEY (`firmaid`) REFERENCES `firma` (`firmaid`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!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

define('DB_NAME', 'mjornsen_kontakt');
define('DB_USER', 'mjornsen_*****');
define('DB_PASSWORD', '*******');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!$link) {
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}

$value = $_POST['name'];
$value2 = $_POST['ename'];
$value3 = $_POST['email'];
$value4 = $_POST['firma'];
$value5 = $_POST['melding'];

$sql = "INSERT INTO person (fornavn, etternavn, email) VALUES ('$value', '$value2', '$value3')";

if (!mysql_query($sql)) {
die('Error: ' . mysql_error());
}

$sql2 = "INSERT INTO henvendelser (melding) VALUES ('$value5')";


if (!mysql_query($sql2)) {
echo('Error: ' . mysql_error());
}

$sql3 = "INSERT INTO firma (firmanavn) VALUES ('$value4')";

if (!mysql_query($sql3)) {
echo('Error: ' . mysql_error());
}

mysql_close();
?>

 

When i try to add some test data to the form i get this error message: "Error: Cannot add or update a child row: a foreign key constraint fails (`mjornsen_kontakt`.`henvendelser`, CONSTRAINT `henvendelser_ibfk_2` FOREIGN KEY (`id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)"

 

Im sorry if this is a really "noobie" question but i cant seem to figure out the problem! Thanks!

[mikosiko]

the error means that you are trying to insert a record in your table henvendelser but you are not providing the person id to whom that record belong...

 

you must use mysql_insert_id() to get that id after you insert a person  (same logic apply to your table firma)

 

http://php.net/manual/en/function.mysql-insert-id.php  include usage examples

[MathiasJor]

Thanks for quick response!

 

hmm, i do see what this means in English, but im not really fluent enough in PHP to add this to my PHP script, any pointers? As i said im very new to this whole thing

 

[mikosiko]

quick and untested... enough for you to work on any error... pay attention to the comments, and also I will suggest to enable the ON DELETE constraints at least if you want to maintain automatic referential integrity, otherwise you must implement it manually yourself.

 

<?php

    define('DB_NAME', 'mjornsen_kontakt');
    define('DB_USER', 'mjornsen_*****');
    define('DB_PASSWORD', '*******');
    define('DB_HOST', 'localhost');
    
    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    
    if (!$link) {
    	die('Could not connect: ' . mysql_error());
    }
    
    $db_selected = mysql_select_db(DB_NAME, $link);
    
    if (!$db_selected) {
    	die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
    }
    
    // you MUST sanitize your $_POST variables before to use them in any query to prevent sqlinjection:
   // things to look at: trim(), type cast, validation (email the obvious one), mysql_real_escape_string().. full of examples in this forum
    $value = $_POST['name'];
    $value2 = $_POST['ename'];
    $value3 = $_POST['email'];
    $value4 = $_POST['firma'];
    $value5 = $_POST['melding'];

    // First insert the firma record because your person table has a FK to it
    $sql3 = "INSERT INTO firma (firmanavn) VALUES ('$value4')";
    
    if (!mysql_query($sql3)) {
    	echo('Error Inserting Firma Record: ' . mysql_error() . ' SQL : ' . $sql3);
    	exit(); // to prevent the script to execute any further
    }
    // Get the last_id from the previous successful insert on firma table
    $firmaid = mysql_insert_id();
    
    // and then use it to insert the person's record
    $sql = "INSERT INTO person (fornavn, etternavn, email, firmaid) VALUES ('$value', '$value2', '$value3', $firmaid)";
    
    if (!mysql_query($sql)) {
    	die('Error Inserting a Person Record: ' . mysql_error() . ' SQL : ' . $sql);
    	exit(); // to prevent the script to execute any further
    }
    // Get the last_id from the previous suscessfull insert on person table
    $personid = mysql_insert_id();
    
    // and then use it to insert the henvendelser record
    // Notice that here you are not including your field `date`, it is declared as NOT NULL in your table, therefore you should provide a value.    
    $sql2 = "INSERT INTO henvendelser (id, melding) VALUES ($personid,'$value5')";
    
    
    if (!mysql_query($sql2)) {
    	echo('Error Inserting a henvendelser Record: ' . mysql_error() . ' SQL : ' . $sql2);
    }

    
    mysql_close();
?>

[MathiasJor]

Thank you very much! You just learned me more about this stuff this evening than my teacher has done since christmas lol

 

Thanks for the advice on the sql injection & security. I will find something to read about it

 

 

[MathiasJor]

Ok, so now i need to get the data that is put into the tables and output them on a webpage. How would i go about doing that?

 

I tried to do this:

 

    

$sql = "SELECT person.fornavn, person.etternavn, person.email, henvendelser.melding FROM person";
    

$result = mysql_query($sql);

if (!$result) {
    echo "Klarte ikke å kjøre ($sql) fra DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "Ingen bestillinger funnet!";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus

while ($row = mysql_fetch_assoc($result)) {
    $fornavn = $row["fornavn"];
    $etternavn = $row["etternavn"];
    $email = $row["email"];
    $melding = $row["melding"];
    
    echo ($fornavn);
    echo ($etternavn);
    echo ($email);
    echo ($melding);
}

mysql_free_result($result);

 

And this would return the data, but it would duplicate like 4 times, depending on how many tables i select from??

Also how can i make sure that the right "melding"(which is norwegian for message/comment") is printed with the right person(sender)?

 

Thanks

[MathiasJor]

Got it workign

 

For thos interested this was what i did:

 

    $sql = "SELECT person.fornavn, person.etternavn, person.email, henvendelser.melding FROM person,henvendelser WHERE person.id=henvendelser.id";
    

$result = mysql_query($sql);

if (!$result) {
    echo "Klarte ikke å kjøre ($sql) fra DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "Ingen bestillinger funnet!";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus

while ($row = mysql_fetch_assoc($result)) {
    $fornavn = $row["fornavn"];
    $etternavn = $row["etternavn"];
    $email = $row["email"];
    $melding = $row["melding"];
    
    echo ("</br></br></br>");
    
    echo ("<h1>Bestilling:</h1>");
    echo ("<strong>Navn:</strong> $fornavn ");
    echo ("$etternavn </br>");
    echo ("<strong>E-mail:</strong> $email </br>");
    echo ("<strong>Melding:</strong> $melding");

}

mysql_free_result($result);

    
?>

[cpd]

@MathiasJor - For future reference you can add constraints directly in your CREATE TABLE section after defining each field. Have a read up.