Joining 2 tables with user input

Alright I have the foreign keys set up and I'm able to join the tables via the sql tab in phpmyadmin, but I can't figure out how to get things to work properly when I allow a user to input the values via a form. Right now I'm just playing around with a very basic form that allows first name, last name, and phone number. First and Last name get sent to a table named Customer and phone number gets passed to a table called Customer_Number. The problem is that now when I enter values into the input fields, First and Last name saved to the DB table, but phone number doesn't save and spits out this error message Cannot add or update a child row: a foreign key constraint fails (``.Customer_Number, CONSTRAINT Customer_Number_ibfk_1 FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID))

Is there a way I can just add these values and the foreign key for my Customer_Number table update according to the correct customer?

Here is my code:

 <?php

        $con=mysqli_connect("");
            if(mysqli_connect_errno()){
                echo "There was a mistake connecting". mysqli_connect_errno();
                }
        $First=mysqli_real_escape_string($con,$_POST["FirstName"]);
        $Last=mysqli_real_escape_string($con, $_POST["LastName"]);
            if(!empty($_POST["FirstName"]) && !empty($_POST["LastName"])){
                $sql="INSERT INTO Customer(First,Last)
                    VALUE('$First', '$Last')";

                        if(!mysqli_query($con,$sql)) {
                            die("ERROR". mysqli_error($con));
                            }else{
                                echo"record added";
                                }
                            }
                                    mysql_close($con);
                            ?>
     <?php

        $con=mysqli_connect("");
            if(mysqli_connect_errno()){
                echo "There was a mistake connecting". mysqli_connect_errno();
                }
        $Phone=mysqli_real_escape_string($con,$_POST["Number"]);

            if(!empty($_POST["Number"])){
                $sql="INSERT INTO Customer_Number(Number)
                    VALUE('$Phone')";

                        if(!mysqli_query($con,$sql)) {
                            die("ERROR". mysqli_error($con));
                            }else{
                                echo"record added";
                                }
                            }
                                    mysql_close($con);
                            ?>

And here is my table information:

CREATE TABLE `Customer` (
`Customer_ID` int(11) NOT NULL AUTO_INCREMENT,
`First` varchar(255) NOT NULL,
`Last` varchar(255) NOT NULL,
 PRIMARY KEY (`Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `Customer_Number` (
`Num_ID` int(11) NOT NULL AUTO_INCREMENT,
`Customer_ID` int(11) NOT NULL,
`Number` varchar(255) NOT NULL,
PRIMARY KEY (`Num_ID`),
KEY `Customer_ID` (`Customer_ID`),
CONSTRAINT `Customer_Number_ibfk_1` FOREIGN KEY (`Customer_ID`) REFERENCES `Customer`         (`Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

I know there has got to be a way to do this, but I'm new to this and my google searches has only really covered how to do this via phpMyAdmin and manually entering in the foreign key values.

Answers


Since Customer_Number.Customer_ID is a foreign key pointing to the corresponding Customer row, you need to set Customer_Number.Customer_ID to the correct value when inserting the number after inserting the Customer.

As long as you're using the same connection (ie skip closing/reopening it between the queries), you can use LAST_INSERT_ID() to get the Customer_ID of the just inserted customer, something like (the SQL part only);

INSERT INTO Customer(First,Last) 
  VALUES ('$First', '$Last')

INSERT INTO Customer_Number(Customer_ID, Number) 
  VALUES (LAST_INSERT_ID(), '$Phone')

Need Your Help

CSS3 transitions vs. jQuery animate if hardware acceleration is unavailable?

jquery html css3 css-transitions hardware-acceleration

I have an HTML5/JS app that runs in an in-dash display unit of a car. I was using jQuery for my animations, but after finally seeing it on the hardware, I realized it was performing like crap.

Delphi & Google drive - API info

delphi google-drive-sdk

You may crucify me for this, but the link for "Support - Drive API" on the Google drive SDK website itself took me here - on the SO forum to put up a new question, so thats exactly what I'm gonna d...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.