unable to add 2 rows of data to table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

unable to add 2 rows of data to table

Post by ashebrian »

hi, I have a problem in my php code:

Code: Select all

<?php
require_once 'config.php';
 
/*********************************************************
*                 CHECKOUT FUNCTIONS 
*********************************************************/
function saveOrder()
{
    $orderId       = 0;
    $shippingCost  = 10;
    $requiredField = array('hidShippingFirstName', 'hidShippingLastName', 'hidShippingAddress1', 'hidShippingCity', 'hidShippingPostalCode',
                           'hidPaymentFirstName', 'hidPaymentLastName', 'hidPaymentAddress1', 'hidPaymentCity', 'hidPaymentPostalCode');
                           
    if (checkRequiredPost($requiredField)) {
        extract($_POST);
        // make sure the first character in the 
        // customer and city name are properly upper cased
        $hidShippingFirstName = ucwords($hidShippingFirstName);
        $hidShippingLastName  = ucwords($hidShippingLastName);
        $hidPaymentFirstName  = ucwords($hidPaymentFirstName);
        $hidPaymentLastName   = ucwords($hidPaymentLastName);
        $hidShippingCity      = ucwords($hidShippingCity);
        $hidPaymentCity       = ucwords($hidPaymentCity);
                
        $cartContent = getCartContent();
        $numItem     = count($cartContent);
        // save order & get order id
        $sql = "INSERT INTO tbl_order(od_date, member_username, od_last_update, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, 
                                      od_shipping_address2, od_shipping_phone, od_shipping_state, od_shipping_city, od_shipping_postal_code, od_shipping_cost, od_vat, od_shipping_total, od_vat_total, 
                                      od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, 
                                      od_payment_phone, od_payment_state, od_payment_city, od_payment_postal_code)
                VALUES (NOW(), '$hidClient', NOW(), '$hidShippingFirstName', '$hidShippingLastName', '$hidShippingAddress1', 
                        '$hidShippingAddress2', '$hidShippingPhone', '$hidShippingState', '$hidShippingCity', '$hidShippingPostalCode', '$shippingCost', '$vat', '$totalShipping', '$totalVat', 
                        '$hidPaymentFirstName', '$hidPaymentLastName', '$hidPaymentAddress1', 
                        '$hidPaymentAddress2', '$hidPaymentPhone', '$hidPaymentState', '$hidPaymentCity', '$hidPaymentPostalCode')";
        $result = dbQuery($sql);
        // get the order id
        $orderId = dbInsertId();
        echo "vat= $totalVat";
        if ($orderId) {
            // save order items
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "INSERT INTO tbl_order_item(od_id, pd_id, od_qty)
                        VALUES ($orderId, {$cartContent[$i]['pd_id']}, {$cartContent[$i]['ct_qty']})";
                $result = dbQuery($sql);                    
            }
        
            
            // update product stock
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "UPDATE tbl_product 
                        SET pd_qty = pd_qty - {$cartContent[$i]['ct_qty']}
                        WHERE pd_id = {$cartContent[$i]['pd_id']}";
                $result = dbQuery($sql);                    
            }
            
            
            // then remove the ordered items from cart
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "DELETE FROM tbl_cart
                        WHERE ct_id = {$cartContent[$i]['ct_id']}";
                $result = dbQuery($sql);                    
            }                           
        }               
    }
    
    return $orderId;
}
/*
    Get order total amount ( total purchase + shipping cost + vat)
    Little niggle with the VAT. to calculate it is e.g ((21/100) * 13.72) = 2.8812 and for cents due to 3rd party is    
    2.8812 * 100 = 288.12. Need to get rid of the decimal points..........
*/
function getOrderAmount($orderId)
{
    
    //$orderTotal = str_replace(',', '', $_POST['totalTotal']);
 
    //echo "orderTotal= $orderTotal";
    //return $orderTotal; 
    $orderAmount = 0;
 
    $sql = "SELECT SUM(pd_price * od_qty)
            FROM tbl_order_item oi, tbl_product p
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT SUM(od_shipping_total) 
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat_total)
            FROM tbl_order
            WHERE od_id = $orderId";
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        
        $orderAmount = ($totalPurchase * 100) + ($totalShipping * 100) + ($totalVat * 100);
        //echo "orderTotal= $orderTotal";
    }   
    return $orderAmount;
    //echo "order= $orderTotal";
}
?>
What my problem is that my data is posted from the previous page into here and can add my data but only for od_shipping_total and od_vat_total i can't seem to add any data. i have been debugging them and they seemed to have the correct value. my table is as follows:

Code: Select all

CREATE TABLE `tbl_order` (
  `od_id` int(10) unsigned NOT NULL auto_increment,
  `member_username` varchar(30),
  `od_date` datetime default NULL,
  `od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  `od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
  `od_memo` varchar(255) NOT NULL default '',
  `od_shipping_first_name` varchar(50) NOT NULL default '',
  `od_shipping_last_name` varchar(50) NOT NULL default '',
  `od_shipping_address1` varchar(100) NOT NULL default '',
  `od_shipping_address2` varchar(100) NOT NULL default '',
  `od_shipping_phone` varchar(32) NOT NULL default '',
  `od_shipping_city` varchar(100) NOT NULL default '',
  `od_shipping_state` varchar(32) NOT NULL default '',
  `od_shipping_postal_code` varchar(10) NOT NULL default '',
  `od_shipping_cost` decimal(5,2) default '0.00',
  `od_vat` int(2) NOT NULL default '21',
  `od_shipping_total` decimal(5,2) default '0.00',
  `od_vat_total` decimal(5,2) default '0.00',
  `od_payment_first_name` varchar(50) NOT NULL default '',
  `od_payment_last_name` varchar(50) NOT NULL default '',
  `od_payment_address1` varchar(100) NOT NULL default '',
  `od_payment_address2` varchar(100) NOT NULL default '',
  `od_payment_phone` varchar(32) NOT NULL default '',
  `od_payment_city` varchar(100) NOT NULL default '',
  `od_payment_state` varchar(32) NOT NULL default '',
  `od_payment_postal_code` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`od_id`,`member_username`)
) TYPE=MyISAM AUTO_INCREMENT=1780 ;
Image
parka
Forum Commoner
Posts: 52
Joined: Mon Feb 26, 2007 6:48 am

Re: unable to add 2 rows of data to table

Post by parka »

What was the error message shown?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: unable to add 2 rows of data to table

Post by califdon »

I don't see anywhere in the code that you posted how you are getting the data into the variables for your INSERT statement. That's where you'd need to start, to analyze, but you haven't shown us any code that does that. If I understand you, these data are coming from $_POST variables, but you haven't shown us the code that gets that data and puts it into the variables. I'm at a complete loss to help you.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: unable to add 2 rows of data to table

Post by RobertGonzalez »

The variables are coming from extract(). What error messages are you getting from mysql_error(), if any?
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: unable to add 2 rows of data to table

Post by ashebrian »

It is coming from extract(). there was no error msg. Is there a way you can debug the Sql like php's 'echo'. If it will help i've added the script that came before this with the hidden inputs that posted the data to the code i showed first:

Code: Select all

<?php
include('include/session.php');
/*
Line 1 : Make sure this file is included instead of requested directly
Line 2 : Check if step is defined and the value is two
Line 3 : The POST request must come from this page but the value of step is one
*/
if (!defined('WEB_ROOT')
    || !isset($_GET['step']) || (int)$_GET['step'] != 2
    || $_SERVER['HTTP_REFERER'] != 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?step=1') {
    exit;
}
 
$errorMessage = '&nbsp;';
 
/*
 Make sure all the required field exist is $_POST and the value is not empty
 Note: txtShippingAddress2 and txtPaymentAddress2 are optional
*/
$requiredField = array('txtShippingFirstName', 'txtShippingLastName', 'txtShippingAddress1', 'txtShippingPhone', 'txtShippingState',  'txtShippingCity', 'txtShippingPostalCode',
                       'txtPaymentFirstName', 'txtPaymentLastName', 'txtPaymentAddress1', 'txtPaymentPhone', 'txtPaymentState', 'txtPaymentCity', 'txtPaymentPostalCode');
                       
if (!checkRequiredPost($requiredField)) {
    $errorMessage = 'Input not complete';
}
                       
 
$cartContent = getCartContent();
 
?>
<table width="475" border="0" align="center" cellpadding="10" cellspacing="0">
    <tr>
        <td><span class="header-txt">Skinlogic Ltd Secure Payments System:</span>
        </td> 
    </tr>
    <tr>
        <td align="left"><img src="images/list-dot.gif" alt=""> Before you click on  the "Confirm Order" button, please check your information below.
        </td> 
    </tr>
    <tr>
        <td align="left"><img src="images/list-dot.gif" alt=""> If you find your information to be incorrect, please use the "Modify Shipping/Payment Info" button to go back and change your settings.
        </td> 
    </tr>
    <tr>
        <td>&nbsp;</td>
    </tr>
    <tr> 
        <td style="color:#cc0033; font-weight:bold">Step 2 of 2 : Confirm Order </td>
    </tr>
</table>
<p id="errorMessage"><?php echo $errorMessage; ?></p>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>?step=3" method="post" name="frmCheckout" id="frmCheckout">
<?php 
if ($_POST['optPayment'] == 'realex') {
?>
<?php
}
?>
    <table width="585" border="0" align="center" cellpadding="5" cellspacing="1" class="infoTable">
        <tr class="infoTableHeader"> 
            <td colspan="4">Ordered Item</td>
        </tr>
        <tr class="label2"> 
            <td>Account Number</td>
            <td>Item</td>
            <td>Unit Price</td>
            <td>Total</td>
        </tr>
        <?php
 $numItem  = count($cartContent);
 $subTotal = 0;
 $vat = 21;
 $shipTotalHigh = 300;
 for ($i = 0; $i < $numItem; $i++) {
     extract($cartContent[$i]);
     $subTotal += $pd_price * $ct_qty;
 
$vattax = floor(($subTotal) * 100 + .5);
$vattaxMe = $subTotal;
echo "vattax= $vattaxMe "; 
$vattaxing = ((21/100) * $vattax);
$vattaxOrder = floor(($vattaxing) * 100 + .5) * .01;
$vattaxOrdering = floor($vattaxOrder) / 100;
echo "vattaxOrdering= $vattaxOrdering ";
 
$ashtotalDone = $vattaxOrdering + $vattaxMe;
$ashtotal = floor ($ashtotal) / 100;
//echo "ashtotalDone= $ashtotalDone ";  
$ashtotalShipDone = $vattaxOrdering + $vattaxMe + 10.00;
$ashtotalShip = floor ($ashtotalShip);
//echo " ashtotalShipDone= $ashtotalShipDone";
 
 
?>
        <tr class="content"> 
            <td class="content"><?php if($session->logged_in){ echo "$session->member_username";} ?>
            <input name="hidClient" type="hidden" id="hidClient" value="<?php if($session->logged_in){ echo "$session->member_username";} ?>">
            </td>
            <td class="content"><?php echo "$ct_qty x $pd_name"; ?></td>
            <td align="right"><?php echo displayAmount($pd_price); ?></td>
            <td align="right"><?php echo displayAmount($ct_qty * $pd_price); ?></td>
        </tr>
        <?php
}
?>
        <tr class="content">
            <td colspan="3" align="right" class="label2">Sub-total</td>
            <td align="right"><?php echo displayAmount($subTotal); ?></td>
        </tr>
        <tr class="content"> 
            <td colspan="3" align="right" class="label2">Shipping</td>
            <td align="right"><input name="totalShipping" type="hidden" id="totalShipping" value="<?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount(0);
                        
                        } else {
                            echo displayAmount($shopConfig['shippingCost']);
                        }?>">
                        <?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount(0);
                        
                        } else {
                            echo displayAmount($shopConfig['shippingCost']);
                        }
                    ?></td>
        </tr>
        <tr class="content"> 
            <td colspan="3" align="right" class="label2">VAT @21%</td><input name="totalVat" type="hidden" id="totalVat" value="<?php echo displayAmount($vattaxOrdering); ?>">
            <td align="right"><?php echo displayAmount(($vat / 100) * $subTotal); ?></td>
        </tr>
        <tr class="content"> 
            <td colspan="3" align="right" class="label2">Total</td>
            <td align="right"><input name="totalTotal" type="hidden" id="totalTotal" value="<?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount($ashtotalDone); 
                            } else {
                            echo displayAmount($ashtotalShipDone);
                            }
                            ?>">
                            <?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount($subTotal + 0 + (($vat / 100) * $subTotal)); 
                            } else {
                            echo displayAmount($subTotal + $shopConfig['shippingCost'] + (($vat / 100) * $subTotal));
                            }
                            ?></td>
        </tr>
    </table>
    <p>&nbsp;</p>
    <table width="435" border="0" align="center" cellpadding="5" cellspacing="1" class="infoTable">
        <tr class="infoTableHeader"> 
            <td colspan="2">Shipping Information</td>
        </tr>
        <tr> 
            <td width="150" class="label2">First Name:</td>
            <td class="content"><?php echo $_POST['txtShippingFirstName']; ?>
                <input name="hidShippingFirstName" type="hidden" id="hidShippingFirstName" value="<?php echo $_POST['txtShippingFirstName']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Surname:</td>
            <td class="content"><?php echo $_POST['txtShippingLastName']; ?>
                <input name="hidShippingLastName" type="hidden" id="hidShippingLastName" value="<?php echo $_POST['txtShippingLastName']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Address:</td>
            <td class="content"><?php echo $_POST['txtShippingAddress1']; ?>
                <input name="hidShippingAddress1" type="hidden" id="hidShippingAddress1" value="<?php echo $_POST['txtShippingAddress1']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2"></td>
            <td class="content"><?php echo $_POST['txtShippingAddress2']; ?>
                <input name="hidShippingAddress2" type="hidden" id="hidShippingAddress2" value="<?php echo $_POST['txtShippingAddress2']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Town / City:</td>
            <td class="content"><?php echo $_POST['txtShippingState']; ?> <input name="hidShippingState" type="hidden" id="hidShippingState" value="<?php echo $_POST['txtShippingState']; ?>" ></td>
        </tr>
        <tr> 
            <td width="150" class="label2">County:</td>
            <td class="content"><?php echo $_POST['txtShippingCity']; ?>
                <input name="hidShippingCity" type="hidden" id="hidShippingCity" value="<?php echo $_POST['txtShippingCity']; ?>" ></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Postal Code:</td>
            <td class="content"><?php echo $_POST['txtShippingPostalCode']; ?>
                <input name="hidShippingPostalCode" type="hidden" id="hidShippingPostalCode" value="<?php echo $_POST['txtShippingPostalCode']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Phone Number:</td>
            <td class="content"><?php echo $_POST['txtShippingPhone'];  ?>
                <input name="hidShippingPhone" type="hidden" id="hidShippingPhone" value="<?php echo $_POST['txtShippingPhone']; ?>"></td>
        </tr>
    </table>
    <p>&nbsp;</p>
    <table width="435" border="0" align="center" cellpadding="5" cellspacing="1" class="infoTable">
        <tr class="infoTableHeader"> 
            <td colspan="2">Payment Information</td>
        </tr>
        <tr> 
            <td width="150" class="label2">First Name:</td>
            <td class="content"><?php echo $_POST['txtPaymentFirstName']; ?>
                <input name="hidPaymentFirstName" type="hidden" id="hidPaymentFirstName" value="<?php echo $_POST['txtPaymentFirstName']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Surname:</td>
            <td class="content"><?php echo $_POST['txtPaymentLastName']; ?>
                <input name="hidPaymentLastName" type="hidden" id="hidPaymentLastName" value="<?php echo $_POST['txtPaymentLastName']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Address:</td>
            <td class="content"><?php echo $_POST['txtPaymentAddress1']; ?>
                <input name="hidPaymentAddress1" type="hidden" id="hidPaymentAddress1" value="<?php echo $_POST['txtPaymentAddress1']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">&nbsp;</td>
            <td class="content"><?php echo $_POST['txtPaymentAddress2']; ?> <input name="hidPaymentAddress2" type="hidden" id="hidPaymentAddress2" value="<?php echo $_POST['txtPaymentAddress2']; ?>"> 
            </td>
        </tr>
        <tr> 
            <td width="150" class="label2">Town / City: </td>
            <td class="content"><?php echo $_POST['txtPaymentState']; ?> <input name="hidPaymentState" type="hidden" id="hidPaymentState" value="<?php echo $_POST['txtPaymentState']; ?>" ></td>
        </tr>
        <tr> 
            <td width="150" class="label2">County:</td>
            <td class="content"><?php echo $_POST['txtPaymentCity']; ?>
                <input name="hidPaymentCity" type="hidden" id="hidPaymentCity" value="<?php echo $_POST['txtPaymentCity']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Postal Code:</td>
            <td class="content"><?php echo $_POST['txtPaymentPostalCode']; ?>
                <input name="hidPaymentPostalCode" type="hidden" id="hidPaymentPostalCode" value="<?php echo $_POST['txtPaymentPostalCode']; ?>"></td>
        </tr>
        <tr> 
            <td width="150" class="label2">Phone Number:</td>
            <td class="content"><?php echo $_POST['txtPaymentPhone'];  ?> <input name="hidPaymentPhone" type="hidden" id="hidPaymentPhone" value="<?php echo $_POST['txtPaymentPhone']; ?>"></td>
        </tr>
    </table>
    <p>&nbsp;</p>
    <table width="435" border="0" align="center" cellpadding="5" cellspacing="1" class="infoTable">
      <tr>
        <td width="150" class="infoTableHeader">Payment Method:</td>
        <td class="content">
        <label for="optRealex" style="cursor:pointer; color:#cc0033; font-weight:bold">Credit Card</label>
          <input name="hidPaymentMethod" type="hidden" id="hidPaymentMethod" value="<?php echo $_POST['optPayment']; ?>">
        </tr>
    </table>
    <p>&nbsp;</p>
    <p align="center"> 
        <input name="btnBack" type="button" id="btnBack" value="<< Modify Shipping/Payment Info" onClick="window.location.href='checkout.php?step=1';" class="boxbutton">
        &nbsp;&nbsp; 
        <input name="btnConfirm" type="submit" id="btnConfirm" value="Confirm Order >>" class="boxbutton">
</form>
<p>&nbsp;</p>
 
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: unable to add 2 rows of data to table

Post by RobertGonzalez »

var_dump() will be your friend in situations like this. var_dump() your $_POST array, var_dump() the $sql query string your are passing to the database server, var_dump() everything to see what is coming out.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: unable to add 2 rows of data to table

Post by califdon »

The variables are coming from extract().
Dohh! Sorry! I never use that, in that context, and it totally escaped my attention! :oops:
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: unable to add 2 rows of data to table

Post by ashebrian »

Thank you for that debugging code......when i

Code: Select all

 
var_dump($totalShipping);
var_dump($totalVat);
below this:

Code: Select all

function saveOrder()
{
    $orderId       = 0;
    $shippingCost  = 10;
It comes out as NULL. However, when i put in under the Insert statement, anywhere after this code:

Code: Select all

$result = dbQuery($sql);
        // get the order id
        $orderId = dbInsertId();
The correct amount is displayed.

But when i put it anywhere in the

Code: Select all

function getOrderAmount($orderId)
Its blank because its an include file (i think this is why it can't be displayed).

However, for this reason it still does not explain why its not added to the db. Its displayed correctly after the Insert statement but not added to db. I think it has to be something wrong with my table not being assigned correctly to accept this value(s). Maybe the variable type. I've tried decimal(5,2), bigint(10), without any of them workings.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: unable to add 2 rows of data to table

Post by RobertGonzalez »

If the field types were wrong you would certainly get an error returned.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: unable to add 2 rows of data to table

Post by ashebrian »

the reason i didn't get an error was coz of this:

Code: Select all

<?php
ini_set('display_errors', 'Off');
//ob_start("ob_gzhandler");
error_reporting(E_ALL);
however, the variable type was wrong and needed to be varchar. This allows me to add to the db and complete my work. I only have one small problem to delet a record now.
Post Reply