insert multiple foreignkeys into the same table

paulus4605 · August 15, 2011

Hi,

I created the following tables

-- phpMyAdmin SQL Dump
-- version 3.3.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 15, 2011 at 10:39 AM
-- Server version: 5.1.54
-- PHP Version: 5.3.5-1ubuntu7.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!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: `test`
--

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

--
-- Table structure for table `adressen`
--

CREATE TABLE IF NOT EXISTS `adressen` (
  `adres_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gebruiker_id` int(10) unsigned NOT NULL,
  `plaats` varchar(40) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`adres_id`),
  KEY `gebruiker_id` (`gebruiker_id`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `adressen`
--

INSERT INTO `adressen` (`adres_id`, `gebruiker_id`, `plaats`, `country_id`) VALUES
(1, 1, 'Amsterdam', 5);

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

--
-- Table structure for table `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(80) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`country_id`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`country_id`, `country`) VALUES
(1, 'Belgium'),
(2, 'France'),
(3, 'Germany'),
(4, 'Austria'),
(5, 'Netherlands');

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

--
-- Table structure for table `gebruikers`
--

CREATE TABLE IF NOT EXISTS `gebruikers` (
  `gebruiker_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `naam` varchar(20) NOT NULL,
  PRIMARY KEY (`gebruiker_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `gebruikers`
--

INSERT INTO `gebruikers` (`gebruiker_id`, `naam`) VALUES
(1, 'Jan');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `adressen`
--
ALTER TABLE `adressen`
  ADD CONSTRAINT `adressen_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `adressen_fk` FOREIGN KEY (`gebruiker_id`) REFERENCES `gebruikers` (`gebruiker_id`) ON DELETE CASCADE ON UPDATE CASCADE;

I used this script in order to update in order to get the gebruiker_id in my table adressen

however I can't figure out how to get the country_id updated in my table adresses,

in my form I use a dropdownlist to display the countries where the customer can choose from

can you help me out here

<?php

// verbind met de database
$objMysqli = new mysqli( 'host', 'user', 'pass', 'db' );

// start transactie
$objMysqli->query( "START TRANSACTION" );

// voeg de gebruiker toe
if ( $objMysqli->query( "INSERT INTO gebruikers (naam) VALUES ('Jan')" ) )
{
    // de query is gelukt, voeg het adres toe
    $strQuery = sprintf( 
        "INSERT INTO adressen (gebruiker_id, plaats) VALUES (%d, 'Amsterdam')",
        $objMysqli->insert_id
    );
    
    if ( $objMysqli->query( $strQuery ) )
    {
        // beide queries zijn gelukt, voltooi de transactie
        $objMysqli->query( "COMMIT" );
        echo 'De gebruiker is toegevoegd';
    }
    else 
    {
        // de adresquery is mislukt, maak de transactie ongedaan
        $objMysqli->query( "ROLLBACK" );
        echo 'De gebruiker is niet toegevoegd, het adres kon niet worden opgeslagen';
    }
}
else 
{
    // de adresquery is mislukt, beëindig de transactie
    echo 'De gebruiker is niet toegevoegd aan de tabel gebruikers';
    $objMysqli->query( "ROLLBACK" );
}

// sluit de verbinding
$objMysqli->close();
?>

thanks

paulus4605 · August 15, 2011

Can anyone help me out here your help is much appreciated

thanks

chintansshah · August 15, 2011

Can you paste your HTML code here. In your country drop down, use country_id is option value. like <option value="<?php echo $row['country_id']; ?>"><?php echo $row['country_name']; ?></option>

paulus4605 · August 15, 2011

this is my form

<?php 
session_start();

mysql_connect("localhost", "root", "pv22021968") or die("Connectie met Database mislukt");
mysql_select_db("test") or die("Fout bij het selecteren van de database");

if ( $_SERVER['REQUEST_METHOD'] == 'POST' ) {

  //  Er zijn gegevens verstuurd naar deze pagina! 



  //  We gaan de errors in een array bijhouden

  $aErrors = array();
if ( !isset($_POST['gebruiker']) or !preg_match( '~^[\w ]{2,}$~', $_POST['gebruiker'] ) ) {

    $aErrors['gebruiker'] = 'Uw naam moet ingevuld zijn';
}

if ( !isset($_POST['password']) or !preg_match( '~^[\w ]{2,}$~', $_POST['password'] ) ) {

    $aErrors['password'] = 'uw password moet ingevuld zijn';
}
if ( !isset($_POST['country']) or !preg_match( '~^[\w ]{2,}$~', $_POST['country'] ) ) {

    $aErrors['country'] = 'uw country moet ingevuld zijn';
}
//alles in een sessie opslaan
$_SESSION['gebruiker'] = $_POST['gebruiker'];

$_SESSION['email'] = $_POST['email'];

$_SESSION['password'] = $_POST['password'];

$_SESSION['country'] = $_POST['country'];

//redirect als alle fouten zijn opgelost

if ( count($aErrors) == 0) {

header('Location: gebruiker.php');

   exit();

    }

   }

?>
<?php include_once('headerform.php');?>



<form action ="formulier.php" method="post" class="cmxform">

<?php

      if ( isset($aErrors) and count($aErrors) > 0 ) {

        print '<ul class="errorlist">';

        foreach ( $aErrors as $error ) {

          print '<li>' . $error . '</li>';

        }

        print '</ul>';

      }

  ?>

<fieldset>
<legend>uw gegevens</legend>
  <ol>

  <li>

  <label for="gebruiker">Naam<em>*</em></label>

  <input id="gebruiker" name="gebruiker" value=''>

  </li>

  <li>

  <label for="email">Email<em>*</em></label>

  <input id="email" name="email" value=''>

  </li>

  <li>

  <label for="password">password<em>*</em></label>

  <input id="password" name="password" value=''>

  </li>

  <li>
<?php
$query="SELECT * FROM country";

/* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */

$result = mysql_query ($query);
echo "<select country=country value=''>country Name</option>";
// printing the list box select command

while($nt=mysql_fetch_array($result)){//Array or records stored in $nt
echo "<option value=$nt[id]>$nt[country]</option>";
/* Option values are added by looping through the array */
}
echo "</select>";// Closing of list box
?>
</td>

  </li>
  <li>

  <label for="zipcode">zipcode<em>*</em></label>

  <input id="zipcode" name="zipcode" value=''>

  </li>
  <li>

  <label for="town">town<em>*</em></label>

  <input id="town" name="town" value=''>

  </li>
</fieldset>
<input type="submit" value="Volgende Pagina">
</form>

paulus4605 · August 16, 2011

how do I get the $nt[id] in $_POST[]?

"<option value=$nt[id]>$nt[country]</option>";

chintansshah · August 16, 2011

how do I get the $nt[id] in $_POST[]?
"<option value=$nt[id]>$nt[country]</option>";

Write below line instead of your current line

"<option value=".$nt['id'].">".$nt['country']."</option>";

String concatenation required! please confirm your table field name also.

paulus4605 · August 16, 2011

thanks for your help, how do I instruct the client that selecting a country is mandatory? and by not selecting this he can't continue with the subscription?

chintansshah · August 16, 2011

do you wish to apply javascript validation or php validation? Javascript is client side validation and php is server side validations.

paulus4605 · August 16, 2011

I prefere php validation since I don't want to depend on the client they may have java disabled

paulus4605 · August 16, 2011

and if possible also an empty field on top of the list;

so you have an empty box and when you click on the dropdown you get all the possible countries

at the moment I have by default the first country selected

chintansshah · August 16, 2011

dude, do some googling, you will find ample of reference links to validate forms in php

paulus4605 · August 16, 2011

true,

however when I complete the form correctly it still gives me the notification that countries is not filled in

that's why I am asking.

how can I let the customer select multiple fields from the dropdownlist?

thanks again for your patience and help

chintansshah · August 16, 2011

About country validation, you need to check country field name and your validation. can you paste validation code here?

use multiple phrase and change size =5 in select tag like <select multiple size="5" name="country">

You can select multiple values using control key

paulus4605 · August 16, 2011

if ( !isset($_POST['country']) or !preg_match( '~^[\w ]{2,}$~', $_POST['country'] ) ) { $aErrors['country'] = 'uw country moet ingevuld zijn';}

Sign In

insert multiple foreignkeys into the same table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information