insert multiple foreignkeys into the same table

[paulus4605]

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]

Can anyone help me out here your help is much appreciated

 

thanks

[chintansshah]

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]

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]

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

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

 

[chintansshah]

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]

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]

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

[paulus4605]

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

[paulus4605]

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]

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

[paulus4605]

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]

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]

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