#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Inserting data through the php form


    I have created a form in php. In mySqli database, I have-
    Customer Table: customerNo, name, phoneNo, email, address, city;
    TableOrder Table: orderNo, time, customerNo, orderType.

    This is my query in php form to insert into customer table:
    $query_str="INSERT INTO Customer (lName, fName, phoneNo, Email, address, city) VALUES ('$_POST[lName]','$_POST[fName]','$_POST[phoneNo]','$_POST[email]','$_POST[address]','$_POST[city]')";

    And query to insert into TableOrder Table ( *orderNo is auto increment*, *time is timestamp*, ***customerNo is foreign key of customer table**) :
    $query_str="INSERT INTO TableOrder (orderType) VALUES ('$_POST[orderType]')";

    But I don't have any idea about how to insert customerNo in TableOrder which is foreign key of Customer Table !!!!!!!

    Any Suggestion would be appreciated. Thank you!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,467
    Rep Power
    594
    This sounds like a MySQL question rather than a PHP question. Please clarify and if a MySQL question please move this to the MySQL forum (click the red triangle in the upper right). If a PHP question, show your code (using [ PHP ] tags, see the sticky at the top of this forum) and what error you are getting or what is not happening.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Here is the code for my form:
    Code:
    <form name="myForm" action="process.php" onsubmit="return validateForm()"  method="post">
    <div id="accordion">
    
    <h3>Customer Infomation</h3>
                    <div>
                            <p>
    				First Name: <input type="text" name="fName" size="10" />
    				Last Name: <input type="text" name="lName" size="10" />
    			</p>
                            <p>
    				Phone No: <input type="text" name="phoneNo" size="10" id="phone"/>
    			</p>
                            <p>
    				Email: <input type="text" name="email" size="10" id="email"/>
    			</p>
                            <p>
    				Address: <input type="text" name="address" size="25" id="address"/>
    			</p>
                            <p>
    				City: <input type="text" name="city" size="10" id="city"/>
    			</p>
                            <p>
                                Order Type:<select name="orderType">	
    					<option value="PU">Pick Up</option>
                                            <option value="DI">Dine In</option>
                                            <option value="DV">Delivery</option>
    					</select>
                            </p>
                    </div>
    <input type="submit" value="Submit Order" />
     
    </form>
    Problem is:
    I am getting Null in customerNo in TableOrder Table. (customerNo need to be like- 1, 2, 3... matching the customerNo in Customer Table which is auto increment.)

    I don't know where its fits in term of php or mySQL. I would consider 50/50.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50

    MySQL Insert ID


    The short answer to "how to insert customerNo in TableOrder which is foreign key of Customer Table?" is "Same way as any other data."

    E.g. something like, insert into TableOrder () values( NULL, now(), 1, '$_POST[orderType]' );

    I think the question you are really trying to ask is "I have a php file which gets data for two dependent tables. After I insert into the first, I need the key which is generated so I can use that as data for an insert into the second. But how do I get that key?"

    The answer is some sort of variation on mysqli_insert_id (). (Maybe you are using the PDO interface, or another interface, but this should get you looking at the right part of the manual.)
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Exactly, that was my problem.
    But, I tried like you described, it does not work. I could not find anything like I have in the link you have provided.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,467
    Rep Power
    594
    Again post your PHP code. We are not clairvoyant.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    #######This is php code to handle form:#########
    PHP Code:
       /*********************************
      *        //Insert into Customer Table:
    ****************************/
        
    $query_str="INSERT INTO Customer (customerNo, lName, fName, phoneNo, email, address, city) VALUES (1,'$_POST[lName]','$_POST[fName]','$_POST[phoneNo]','$_POST[email]','$_POST[address]','$_POST[city]')";
        
        echo 
    "<p><b>This will show all the customers in associative array:</b></p>";
        
    $query_str"SELECT lName, fName, phoneNo, email, address, city FROM Customer";
        if (
    $result $conn->query($query_str)) {
            
    //fetch associative array
            
    while ($row $result->fetch_assoc()) {
                
    printf ("%s %s %s %s %s %s \n"$row["lName"], $row["fName"], $row["phoneNo"], $row["email"], $row["address"], $row["city"]);
                
    printf ("<br/>");
            }
        }
        echo 
    "<br/>";
        echo 
    $query_str "<br/>";
        
        if ( 
    $conn->query($query_str)) {
            echo 
    "Your Information was sent to Customer Table. <br/>";
            
     
    /*********************************
      *        //Insert into OrderTable Table:
    ****************************/
            
    $query_str="INSERT INTO ATableOrder (orderNo, time, customerNo, orderType) VALUES (NULL, now(), 1, '$_POST[orderType]')";
            echo 
    "<br/>";
            echo 
    $query_str "<br/>";
            if ( 
    $conn->query($query_str)) {
                echo 
    "Your order was recorded in TableOrder Table.<br/>";
            }
            else {
                echo 
    "Table creation failed: (" $conn->errno ") " $conn->error;
            }
            
     
    /*********************************
      *       //Insert in to OrderLineItem Table;
    ****************************/
            
    $query_str="INSERT INTO OrderLineItem (pizzaNo, subNo, sandNo) VALUES ('$_POST[pizzaNo]','$_POST[subNo]','$_POST[sandNo]')";
            echo 
    "<br/>";
            echo 
    $query_str "<br/>";
            if ( 
    $conn->query($query_str)) {
                echo 
    "Your order was sent to OrderLineItem Table. <br/>";
            }
            else {
                echo 
    "Table creation failed: (" $conn->errno ") " $conn->error;
            }
             
    //$result = $conn->query($query_str);   
             //echo $result->      
        
    }
        else {
            echo 
    "Table creation failed: (" $conn->errno ") " $conn->error;
        } 
    #######AND this the query for my database:########

    CREATE TABLE Customer
    (customerNo INT (5) NOT NULL AUTO_INCREMENT,
    fName VARCHAR (10) NOT NULL,
    lName VARCHAR (10) NOT NULL,
    phoneNo CHAR (10) NOT NULL,
    email CHAR (30),
    address VARCHAR (20) NOT NULL,
    city VARCHAR (10) NOT NULL,
    PRIMARY KEY (customerNo));

    CREATE TABLE TableOrder
    (orderNo INT (5) NOT NULL,
    date DATE NOT NULL,
    time TIME NOT NULL,
    customerNo INT (5),
    orderType CHAR (2) NOT NULL
    CHECK (orderType IN ('PU', 'DI', 'DV')),
    PRIMARY KEY (orderNo),
    FOREIGN KEY (customerNo) REFERENCES Customer(customerNo) ON DELETE SET NULL ON UPDATE CASCADE);
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50
    It isn't clear to me if you are using mysqli or PDO or something else. I see you using ->errno which I think suggests you are using mysqli which means the link I provided ( http://php.net/manual/en/mysqli.insert-id.php ) is exactly what you need. It even has a fairly detailed example.

    Basically, after your
    PHP Code:
    $conn->query($query_str
    you need to add something like
    PHP Code:
    $myCustID $conn->insert_id 
    Then you can pass $myCustID to the insert for Orders.

    If it "doesn't work," start debugging. It is the PHP functionality you are looking for.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,467
    Rep Power
    594
    Also, again, enclose your code in [ PHP ] tags (edit the original post rather than re-posting it all). As I suggested earlier, read the sticky at the top of this forum.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo