Table won't populate

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
summitweb
Forum Commoner
Posts: 27
Joined: Thu Jun 30, 2005 4:28 am
Location: Atlanta, Georgia

Table won't populate

Post by summitweb »

Hello:

I'm stumped. I am trying to populate and it is not working and for the life of me I don't know what's wrong.

I placed the code at the end of this post.

I am able to connect to the database with no trouble. The next thing I do is assing variable names to the POST variables. This is also working correctly.

The next step is to populate a customers table and that is working with no trouble.

The next step is to perform some calculations from data in another table. The calculations are working as well.

The problem is when I try to populate the ORDERMAIN table. The fields won't populate and I can't seem to figure out why. I've checked to make sure that all my table fields match the columns in the query - that is fine. I made sure that the columns in the query match the order of which the fields are in the table - that is fine. The only thing happening is no data is being placed into the table.

I'm hoping a second, third, or even fourth pair of eyes may see something that I am missing.

I hope someone can help me out. Thank you in advance for any and all help.

Here's the code.

Code: Select all

<?php
session_start();

//connect to the database 

$conn = mysql_connect("localhost", "sxx", "xx") or die (mysql_error());
mysql_select_db("xx");

//Let's make the variables easy to access in our queries
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$add1 = $_POST['add1'];
$add2 = $_POST['add2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$fax = $_POST['fax'];
$email = $_POST['email'];
$shipfirst = $_POST['shipfirst'];
$shiplast = $_POST['shiplast'];
$shipadd1 = $_POST['shipadd1'];
$shipadd2 = $_POST['shipadd2'];
$shipcity = $_POST['shipcity'];
$shipstate = $_POST['shipstate'];
$shipzip = $_POST['shipzip'];
$shipphone = $_POST['shipphone'];
$shipemail = $_POST['shipemail'];
$sessid = session_id();
$today = date("Y-m-d");

//1) Assign Customer Number to new Customer, or find existing customer number
$query = "SELECT * FROM customers WHERE
          (customers_firstname = '$firstname' AND
          customers_lastname = '$lastname' AND
          customers_add1 = '$add1' AND
          customers_add2 = '$add2' AND
          customers_city = '$city')";
$results = mysql_query($query)
  or (mysql_error());
$rows = mysql_num_rows($results);

if ($rows < 1) {
  //assign new custnum
  $query2 = "INSERT INTO customers (
             customers_firstname, customers_lastname, customers_add1,
             customers_add2, customers_city, customers_state, 
             customers_zip, customers_phone, customers_fax, 
             customers_email)
             VALUES (
            '$firstname',
            '$lastname',
            '$add1',
            '$add2',
            '$city',
            '$state',
            '$zip',
            '$phone',
            '$fax',
            '$email')";
  $insert = mysql_query($query2)
    or (mysql_error());
  $custid = mysql_insert_id();
}

//If custid exists, we want to make it equal to custnum
//Otherwise we will use the existing custnum
if ($custid) {
  $customers_custnum = $custid;
}
?>

<?php
$sessid = session_id();

$query = "SELECT * FROM carttemp WHERE carttemp_sess = '$sessid'";
$results = mysql_query($query) or die(mysql_error());
$rows = mysql_num_rows($results);
?>

<?php
$subtotal = 0.0;
$shipping = 0.0;
$total = 0.0;

while ($row = mysql_fetch_array($results)) {
extract($row);

$extprice = $carttemp_price * $carttemp_qty;

$subtotal = $extprice + $subtotal;
$shipping = $subtotal * 0.25;
$total = $subtotal + $shipping;
}

//2) Insert Info into ordermain

$query3 = "INSERT INTO ordermain (
           ordermain_orderdate, 
           ordermain_shipfirst, ordermain_shiplast,
           ordermain_shipadd1, ordermain_shipadd2,
           ordermain_shipcity, ordermain_shipstate,
           ordermain_shipzip, ordermain_shipphone,
           ordermain_shipemail)
           VALUES (
           '$today',
           '$shipfirst',
           '$shiplast',
           '$shipadd1',
           '$shipadd2',
           '$shipcity',
           '$shipstate',
           '$shipzip',
           '$shipphone',
           '$shipemail')";
$insert2 = mysql_query($query3) or (mysql_error());
$orderid = mysql_insert_id();
?>


This is the table structure:

Code: Select all

Field  Type  Null  Key  Default  Extra  
ordermain_ordernum int(6)   PRI 0   
ordermain_orderdate date     0000-00-00   
ordermain_shipfirst varchar(15)         
ordermain_shiplast varchar(50)         
ordermain_shipadd1 varchar(50)         
ordermian_shipadd2 varchar(50) YES   NULL   
ordermain_shipcity varchar(50)         
ordermain_shipstate char(2)         
ordermain_shipzip varchar(5)         
ordermain_shipphone varchar(12)         
ordermain_shipemail varchar(50)

feyd | Use

Code: Select all

and

Code: Select all

where appropriate please.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if you echoed out $query3 what does it look like? If you copy this query into phpMyAdmin and execute it, what happens?

It seems you are doing a lot of extra math in your total calculation.. The shipping and total price are pretty much independant of everything else in that loop.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

You may want to change

Code: Select all

$insert2 = mysql_query($query3) or (mysql_error());
to

Code: Select all

$insert2 = mysql_query($query3) or die(mysql_error());
This should display any sql error.
If not echo $query3 and try that in phpMyAdmin or whatever and see if that results in an error.
summitweb
Forum Commoner
Posts: 27
Joined: Thu Jun 30, 2005 4:28 am
Location: Atlanta, Georgia

Post by summitweb »

Hi,

Thanks for the reply. This is the result when I run the query in phpMyAdmin:

SQL-query :

$query3 = "INSERT INTO ordermain ( ordermain_orderdate, ordermain_shipfirst, ordermain_shiplast, ordermain_shipadd1, ordermain_shipadd2, ordermain_shipcity, ordermain_shipstate, ordermain_shipzip, ordermain_shipphone, ordermain_shipemail) VALUES ( '$today', '$shipfirst', '$shiplast', '$shipadd1', '$shipadd2', '$shipcity', '$shipstate', '$shipzip', '$shipphone', '$shipemail')"

MySQL said:


You have an error in your SQL syntax near '$query3 = "INSERT INTO ordermain (
ordermain_orderdate,
' at line 1


Also, I added the die into my query statement and this is the message I'm getting:

Unknown column 'ordermain_shipadd2' in 'field list'

I don't understand this message because I echoed the _POST['shipadd2'] is coming across. I looked at my table fields as well as my query and it appears that the column is fine.

Any ideas?
Last edited by summitweb on Mon Aug 08, 2005 7:44 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

whoa, whoa... I was talking about the echoed $query3... not the line from the code silly. :lol:
Post Reply