One form two tables MySQL not PHP Error

butchman · December 31, 2009

OK I admit it "I am a NOOB!"

I am sure I am just missing something very basic, but, after two days "literally two days" of googleing, trying every variation of INSERT INTO that I can think of and just staring at this code and I have decided I am ready for any flames that come my way.

Problem:

After submit, code exits at second insert with "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (idorder,orderamt,orderdate,customer_idcustomer) VALUES ('','123.45','Sept' at line 1" . This was supposed to be a simple exercise in learning that has turned into a nightmare.

Server Version:

mysql -V

mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

The Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <form name="test_data" action="insert.php" method="POST">
            <legend>Insert Data:</legend>
            <table border="1">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Field</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td><label for="custname">Customer Name</label></td>
                        <td><input type="text" name="Custname" id="custname" size="40" /></td>
                    </tr>
                    <tr>
                        <td><label for="custphone">Customer Phone Number</label></td>
                        <td><input type="text" name="Custphone" id="custphone" size="40" /></td>
                    </tr>
                    <tr>
                        <td><label for="orderamt">Order Amount</label></td>
                        <td><input type="text" name="Orderamt" id="orderamt" size="40" /></td>
                    </tr>
                    <tr>
                        <td><label for="orderdate">Order Date</label></td>
                        <td><input type="text" name="Orderdate" id="orderdate" size="40" /></td>
                    </tr>
                </tbody>
            </table>
            <input type="submit" value="Submit" name="submit" />
            <input type="reset" value="Reset">
        </form>


        <?php

        /* If form was submitted */
	/*  Setup connection */
	if (isset ($_POST['submit'])) {
            $link = mysql_connect('localhost','root','iatmo1vfs');
            mysql_select_db('mydb', $link);

            /* check to see if connection was established */
            if (mysql_error()) {
                echo mysql_errno($link) . ": " . mysql_error($link). "\n";
                exit();
            }

            /* Cleanup our POST data */
            $cust_name = mysql_real_escape_string($_POST['Custname']);
            $cust_phone = mysql_real_escape_string($_POST['Custphone']);
            $ord_amt = mysql_real_escape_string($_POST['Orderamt']);
            $ord_date = mysql_real_escape_string($_POST['Orderdate']);

            /* Insert into customer table */
            $result1 = mysql_query("INSERT INTO customer (idcustomer,custname,custphone) VALUES ('','".$cust_name."','".$cust_phone."')")OR die(mysql_error());

		/* Get id of first insert */
		$first_id = mysql_insert_id($link);

		/* ------------------------------------------------------------------------------------ */
		/* This is the offending Insert */
		/* I have taken out the php variables for this forum */
		/* Insert into order table */
            /*$result2 = mysql_query("INSERT INTO order (idorder,orderamt,orderdate,customer_idcustomer) VALUES ('','".$ord_amt."','".$ord_date."',".$first_id.")")OR die(mysql_error());*/
		$result2 = mysql_query("INSERT INTO order (idorder,orderamt,orderdate,customer_idcustomer) VALUES ('','123.45','Sept 22,1952',6)")OR die(mysql_error());
		/* ------------------------------------------------------------------------------------ */

            /* Cleanup and close */
            mysql_close($link);
            
        } else {
		/* Otherwise let us know form was not submitted */
            echo "<h3>Form not Submitted</h3>";
        }


        // Statments for debugging ---------- to be commented out later
        echo "<br /><br />";

        echo "File-insert.php<br />";

        echo "--------Check to see if page is pointing to itself-------<br />";
        echo $_SERVER['PHP_SELF'];
        echo "<br />";

        echo "----------- Elements of the \$_POST array ---------------<br />";
        echo '<pre>';
        var_dump($_POST);
        echo '</pre>';

        echo "----------- Elements of the \$_POST array validated---------------<br />";
        echo '<pre>';
        echo 'cust_name=';
        var_dump($cust_name);
        echo '<br />';
        echo 'cust_phone=';
        var_dump($cust_phone);
        echo'<br />';
        echo 'ord_amt=';
        var_dump($ord_amt);
        echo '<br />';
        echo 'ord_date=';
        var_dump($ord_date);
        echo '<br />';
        echo 'Id of first insert=';
        var_dump($first_id);
        echo '<br />';
        echo '</pre>';
        ?>



    </body>
</html>

DB Structure:

-- phpMyAdmin SQL Dump
-- version 2.11.8.1deb5+lenny3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 27, 2009 at 06:25 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6-1+lenny4

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: `mydb`
--
CREATE DATABASE `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `mydb`;

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

--
-- Table structure for table `customer`
--

CREATE TABLE IF NOT EXISTS `customer` (
  `idcustomer` int(11) NOT NULL auto_increment,
  `custname` varchar(45) NOT NULL,
  `custphone` varchar(45) default NULL,
  PRIMARY KEY  (`idcustomer`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`idcustomer`, `custname`, `custphone`) VALUES
(1, 'butchman', '123-456-7890');

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

--
-- Table structure for table `order`
--

CREATE TABLE IF NOT EXISTS `order` (
  `idorder` int(11) NOT NULL auto_increment,
  `orderamt` varchar(45) NOT NULL,
  `orderdate` varchar(45) NOT NULL,
  `customer_idcustomer` int(11) NOT NULL,
  PRIMARY KEY  (`idorder`,`customer_idcustomer`),
  KEY `fk_order_customer` (`customer_idcustomer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `order`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `order`
--
ALTER TABLE `order`
  ADD CONSTRAINT `fk_order_customer` FOREIGN KEY (`customer_idcustomer`) REFERENCES `customer` (`idcustomer`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Desired Outcome:

Insert the f---ing data.

Thanks in advance for any help in this matter. Again this was meant to be a learning exercise in using one form to populate multiple tables so complete code examples are not necessary just point me in the right direction. Also if I have stepped on any toes wrong forum, incomplete information, to much information, I AM A NOOB, I AM A NOOB , I AM A NOOB

rajivgonsalves · December 31, 2009

order is a mysql reserved keyword quote it in backticks

INSERT INTO `order` ...

butchman · December 31, 2009

As I prostrated myself before that grand and glorious being before me

Sign In

One form two tables MySQL not PHP Error

Recommended Posts

butchman

Link to comment

Share on other sites

rajivgonsalves

Link to comment

Share on other sites

butchman

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information