Solved

How would I write this INSERT statement in OOP?

Posted on 2010-11-22
8
487 Views
Last Modified: 2012-05-10
Here's my code for the shoppingCart function:

public function returnCartProducts(&$conn){
            
            $cart = $_SESSION['cart'];
            #if ($cart) {
                  $items = explode(',',$this->cart_items);
                  #echo $items;
                  $contents = array();
                  foreach ($items as $item) {
                        $contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
                  }
                  foreach ($contents as $id=>$qty) {
                        $sql = 'SELECT tblreplacementparts.*, tblreplacementproductparts.id, tblreplacementproductparts.replacementProductID, tblreplacementproductparts.price FROM tblreplacementproductparts LEFT JOIN tblreplacementparts ON tblreplacementparts.id = tblreplacementproductparts.partID WHERE partID = '.$id;
                        $results= $conn->query_first($sql);
                        #print_r($contents);
                         $cartProducts .= '<tr height="20"><td>'.$results['partNum'].'</td>
                                          <td width="100">$'.sprintf('%01.2f',$results['price']).'</td>
                                          <td width="100">'.$qty.'</td>
                                          <td align="center" width="100">$'.sprintf('%01.2f', $results['price'] * $qty).'</td></tr>';
                  }
                  
            #}
            
            return $cartProducts;

I need to insert every one of the products enumerated in the above query into an OrderDetails table and I have no idea how.

I'm thinking I've got to mirror the "for each" dynamic just so if the customer has ordered more than one item, it's being inserted into the table.

The OrderDetails table consists of price, quantity etc and there's also a spot where I'm going to have to write an additional select query within the "for each" dynamic so I can grab other manufacturing information.

How?
0
Comment
Question by:brucegust
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:dsmile
Comment Utility
I think it might be something like this
foreach ($contents as $id=>$qty) {

                        $sql = 'SELECT tblreplacementparts.*, tblreplacementproductparts.id, tblreplacementproductparts.replacementProductID, tblreplacementproductparts.price FROM tblreplacementproductparts LEFT JOIN tblreplacementparts ON tblreplacementparts.id = tblreplacementproductparts.partID WHERE partID = '.$id;

                        $results= $conn->query_first($sql);

                        #print_r($contents);

                         $cartProducts .= '<tr height="20"><td>'.$results['partNum'].'</td>

                                          <td width="100">$'.sprintf('%01.2f',$results['price']).'</td>

                                          <td width="100">'.$qty.'</td>

                                          <td align="center" width="100">$'.sprintf('%01.2f', $results['price'] * $qty).'</td></tr>';

			$sql = 'INSERT INTO OrderDetails (price, quantity) VALUES ('.$results['price'].', '.$qty.')'; //edit this query to fit your needs

                        $results= $conn->query($sql); //execute the query

                  }

Open in new window

0
 
LVL 13

Expert Comment

by:dsmile
Comment Utility
A more efficient way to do it
1. build a multiple insert query
2. execute the insert query when the loop ends
$sql_insert = 'INSERT INTO OrderDetails (price, quantity) VALUES'; //edit this to match OrderDetails' structure

foreach ($contents as $id=>$qty) {

                        $sql = 'SELECT tblreplacementparts.*, tblreplacementproductparts.id, tblreplacementproductparts.replacementProductID, tblreplacementproductparts.price FROM tblreplacementproductparts LEFT JOIN tblreplacementparts ON tblreplacementparts.id = tblreplacementproductparts.partID WHERE partID = '.$id;

                        $results= $conn->query_first($sql);

                        #print_r($contents);

                         $cartProducts .= '<tr height="20"><td>'.$results['partNum'].'</td>

                                          <td width="100">$'.sprintf('%01.2f',$results['price']).'</td>

                                          <td width="100">'.$qty.'</td>

                                          <td align="center" width="100">$'.sprintf('%01.2f', $results['price'] * $qty).'</td></tr>';

			$sql_insert .= ' ('.$results['price'].', '.$qty.'),'; //append data to query //edit to match number of values to be inserted                       

                  }



		  $sql_insert = substr($sql_insert, 0, -1); //remove comma at the end 

		  $results= $conn->query($sql_insert); //execute the query

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
I appreciate your time friend!

Below is the error message that I got. I'm thinking I need to make some changes, but not sure what those would be or where they would go.

I figured I would just fire your code to see what happened and go from there.

I've got the error message below and my code thus far is attached. Here's what came back:

B03032-PT $27.00 1 $27.00
Warning: Invalid argument supplied for foreach() in /var/www/vhosts/kolcrafttesting.com/httpdocs/cash_register.php on line 9
Database Error
Message:      MySQL Query fail: INSERT INTO OrderDetails (price, quantity) VALUE
MySQL Error:      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 '' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php
Database Error
Message:      MySQL Query fail: INSERT INTO customer-order-table {order_id, customer_id, part_number, price, quantity} VALUES {'', '', '', '', }
MySQL Error:      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-table {order_id, customer_id, part_number, price, quantity} VALUES {'',' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php
Database Error
Message:      Result ID: could not be freed.
MySQL Error:      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-table {order_id, customer_id, part_number, price, quantity} VALUES {'',' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php
Hallelujah!
<?php 

require_once('includes/classes/cart.class.php');

require_once("__config.php");

session_start();

$cartProducts = $_SESSION['cart']->returnCartProducts($conn);

echo $cartProducts; 



$sql_insert = 'INSERT INTO OrderDetails (price, quantity) VALUES'; //edit this to match OrderDetails' structure

foreach ($contents as $id=>$qty) {

                        $sql = 'SELECT tblreplacementparts.*, tblreplacementproductparts.id, tblreplacementproductparts.replacementProductID, tblreplacementproductparts.price FROM tblreplacementproductparts LEFT JOIN tblreplacementparts ON tblreplacementparts.id = tblreplacementproductparts.partID WHERE partID = '.$id;

                        $results= $conn->query_first($sql);

                        #print_r($contents);

                         $cartProducts .= '<tr height="20"><td>'.$results['partNum'].'</td>

                                          <td width="100">$'.sprintf('%01.2f',$results['price']).'</td>

                                          <td width="100">'.$qty.'</td>

                                          <td align="center" width="100">$'.sprintf('%01.2f', $results['price'] * $qty).'</td></tr>';

			$sql_insert .= ' ('.$results['price'].', '.$qty.'),'; //append data to query //edit to match number of values to be inserted                       

                  }



		  $sql_insert = substr($sql_insert, 0, -1); //remove comma at the end 

		  $results= $conn->query($sql_insert); //execute the query





// followed by...

$sqlinsert = "INSERT INTO customer-order-table {order_id, customer_id,   part_number, price, quantity} VALUES {'$order_id', '$customer_id', '".$results['partNum']."', '".$results['price']."', $qty}";

$result = $conn->query_first($sqlinsert); // ?? not sure of proper function

/*

//we need to consider two different tables where our customer data is concerned: tblorderaddresses and tblcustomers

//secondly we add the order to the tblorderaddresses. There may very well be duplicate customers, but this is where every order is documented, nothing is ever "updated"

//on this table



//we're going to start by inserting the customer data along with the order data

//first we need to see if the customer is already in the order addresses database. If so, we'll make any changes that we need to. If not, we'll insert a new record

$ship_phone = trim($_POST['ship_phone']);

$billing_phone = trim($_POST['billing_phone']);

$shipping_first_name = mysql_real_escape_string(trim($_POST['shipping_first_name']));

$billing_first_name = mysql_real_escape_string(trim($_POST['billing_first_name']));

$shipping_last_name = mysql_real_escape_string(trim($_POST['shipping_last_name']));

$billing_last_name = mysql_real_escape_string(trim($_POST['billing_last_name']));

$the_shipping_name = $shipping_first_name.' '.$shipping_last_name;

$the_billing_name = $billing_first_name.' '.$billing_last_name;

$shipping_address1 =  mysql_real_escape_string(trim($_POST['shipping_add_one']));

$shipping_address2 =  mysql_real_escape_string(trim($_POST['shipping_add_two']));

$billing_address1 =  mysql_real_escape_string(trim($_POST['billing_add_one']));

$billing_address2 =  mysql_real_escape_string(trim($_POST['billing_add_two']));

$ship_city =  mysql_real_escape_string(trim($_POST['ship_city']));

$ship_state =  mysql_real_escape_string(trim($_POST['ship_state']));

$ship_zip = trim($_POST['ship_zip']);

$ship_email = trim($_POST['ship_email']);

$billing_city =  mysql_real_escape_string(trim($_POST['billing_city']));

$billing_state =  mysql_real_escape_string(trim($_POST['billing_state']));

$billing_zip = trim($_POST['billing_zip']);

$billing_email = trim($_POST['billing_email']);

$date_created = date("Y-m-d H:i:s");



$get_customer = "SELECT * FROM tblorderaddresses WHERE bEmail = '$_POST[billing_email]'";

$result = $conn->query_first($get_customer);



if(!$result['bEmail']==trim($_POST['billing_email'])){

//customer is in database so we simply update whatever is in there based on the form customer just posted



//here's where we're adding a customer to the tblorderaddresses table

//first thing you need to do is find out how many rows are in your database and then add one more at the end with an accurate id number

$count_customer = new Database(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,'');

$count_customer->connect();

$count_customer_sql = "SELECT id  FROM tblorderaddresses ORDER by id DESC LIMIT 1";

$count_data = $count_customer->query_first($count_customer_sql);

$new_id = $count_data['id'] + 1;

$insert_customer = new Database(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,'');

$insert_customer->connect();

$insert_customer_sql = "INSERT INTO tblorderaddresses (id, bFullname, bFirstname, bLastname, bAddress1, bAddress2, bCity, bState, bZip, bEmail, sFullname, sFirstname, sLastname, sAddress1, sAddress2, sCity, sState, sZip, sEmail, sPhone, bPhone, dateCreated) VALUES ('$new_id', '$the_billing_name','$billing_first_name', '$billing_last_name', '$billing_address1','$billing_address2','$billing_city','$billing_state', 

'$billing_zip', '$billing_email', '$the_shipping_name', '$shipping_first_name', '$shipping_last_name', '$shipping_address1', '$shipping_address2', '$ship_city', '$ship_state', '$ship_zip', '$ship_email','$ship_phone', '$billing_phone', '$date_created')";

$insert_data = $insert_customer->query($insert_customer_sql);

}

else

{

//there's a record already in the database so we just update it based on what the customer just entered

$query_action = new Database(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,'');

$query_action->connect();

$sql2= "UPDATE tblorderaddresses set bFullname = '$the_billing_name', 

bFirstname='$billing_first_name', 

bLastname='$billing_lastname', 

bAddress1='$billing_address1', 

bAddress2 = '$billing_address2', 

bCity = '$billing_city', 

bState='$billing_state', 

bZip='$billing_zip', 

sFullname = '$the_shipping_name',

sFirstname = '$shipping_first_name',

sLastname='$shipping_last_name',

sAddress1 = '$shipping_address1',

sAddress2 = '$shipping_address2',

sCity = '$ship_city',

sState = '$ship_state',

sZip = '$ship_zip',

sPhone = '$ship_phone',

bPhone = '$billing_phone',

sEmail = '$ship_email'

where bEmail = '$billing_email'";

$query_data = $query_action->query($sql2);	

}		



*/

// now we're at that point where add the order details and then update both the order details table and the orderaddresses table once the credit card has been validated				

	



// By default, this sample code is designed to post to our test server for

// developer accounts: https://test.authorize.net/gateway/transact.dll

// for real accounts (even in test mode), please make sure that you are

// posting to: https://secure.authorize.net/gateway/transact.dll

$post_url = "https://secure.authorize.net/gateway/transact.dll";



$post_values = array(

	

	// the API Login ID and Transaction Key must be replaced with valid values

	"x_login"			=> "9Fmyc39Yy ",

	"x_tran_key"		=> "93YayQq39FC95kqx",



	"x_version"			=> "3.1",

	"x_delim_data"		=> "TRUE",

	"x_delim_char"		=> "|",

	"x_relay_response"	=> "FALSE",



	"x_type"			=> "AUTH_CAPTURE",

	"x_method"			=> "CC",

	"x_card_num"		=> $_POST['card_number'],

	"x_exp_date"		=> $_POST['authorize_year'],



	"x_amount"			=> $_POST['amount'],

	"x_description"		=> "Sample Transaction",



	"x_first_name"		=> $_POST['first_name'],

	"x_last_name"		=> $_POST['last_name'],

	"x_address"			=> $_POST['add_one'],

	"x_state"			=> $_POST['state'],

	"x_zip"				=> $_POST['zip']

	// Additional fields can be added here as outlined in the AIM integration

	// guide at: http://developer.authorize.net

);



// This section takes the input fields and converts them to the proper format

// for an http post.  For example: "x_login=username&x_tran_key="

$post_string = "";

foreach( $post_values as $key => $value )

	{ $post_string .= "$key=" . urlencode( $value ) . "&"; }

$post_string = rtrim( $post_string, "& " );



// The following section provides an example of how to add line item details to

// the post string.  Because line items may consist of multiple values with the

// same key/name, they cannot be simply added into the above array.

//

// This section is commented out by default.

/*

$line_items = array(

	"item1<|>golf balls<|><|>2<|>18.95<|>Y",

	"item2<|>golf bag<|>Wilson golf carry bag, red<|>1<|>39.99<|>Y",

	"item3<|>book<|>Golf for Dummies<|>1<|>21.99<|>Y");

	

foreach( $line_items as $value )

	{ $post_string .= "&x_line_item=" . urlencode( $value ); }

*/



// This sample code uses the CURL library for php to establish a connection,

// submit the post, and record the response.

// If you receive an error, you may want to ensure that you have the curl

// library enabled in your php configuration

$request = curl_init($post_url); // initiate curl object

	curl_setopt($request, CURLOPT_HEADER, 0); // set to 0 to eliminate header info from response

	curl_setopt($request, CURLOPT_RETURNTRANSFER, 1); // Returns response data instead of TRUE(1)

	curl_setopt($request, CURLOPT_POSTFIELDS, $post_string); // use HTTP POST to send form data

	curl_setopt($request, CURLOPT_SSL_VERIFYPEER, FALSE); // uncomment this line if you get no gateway response.

	$post_response = curl_exec($request); // execute curl post and store results in $post_response

	// additional options may be required depending upon your server configuration

	// you can find documentation on curl options at http://www.php.net/curl_setopt

curl_close ($request); // close curl object



// This line takes the response and breaks it into an array using the specified delimiting character

$response_array = explode($post_values["x_delim_char"],$post_response);



// The results are output to the screen in the form of an html numbered list.

/*echo "<OL>\n";

foreach ($response_array as $value)

{

	echo "<LI>" . $value . "&nbsp;</LI>\n";

	$i++;

}

echo "</OL>\n";

*/

if($response_array[0] == 1) {

echo "Hallelujah!";

$sql = "select email from tblcustomers where email = '$_POST[email]'";

$vivian=$conn->query_first($sql);

	

}

else

{

echo "no";

}



// individual elements of the array could be accessed to read certain response

// fields.  For example, response_array[0] would return the Response Code,

// response_array[2] would return the Response Reason Code.

// for a list of response fields, please review the AIM Implementation Guide



?>

Open in new window

0
 
LVL 13

Expert Comment

by:dsmile
Comment Utility
OK, let's see what we have here


Warning: Invalid argument supplied for foreach() in /var/www/vhosts/kolcrafttesting.com/httpdocs/cash_register.php on line 9


---> on line 9, you have
foreach ($contents as $id=>$qty) {

where does $contents  come from?
$contents  must be an array in order to be used in foreach()

-----


Database Error
Message:      MySQL Query fail: INSERT INTO OrderDetails (price, quantity) VALUE
MySQL Error:      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 '' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php

Since no contents were parsed, $sql_insert was incomplete which made MySQL throw an error


-----


Database Error
Message:      MySQL Query fail: INSERT INTO customer-order-table {order_id, customer_id, part_number, price, quantity} VALUES {'', '', '', '', }
MySQL Error:      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-table {order_id, customer_id, part_number, price, quantity} VALUES {'',' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php
Database Error
Message:      Result ID: could not be freed.
MySQL Error:      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-table {order_id, customer_id, part_number, price, quantity} VALUES {'',' at line 1
Date:      Wednesday, November 24, 2010 at 8:56:34 PM
Script:      /cash_register.php
Referer:      http://www.snowdogservertest.com/checkout.php

Since '-' is a confusing symbol in an sql, you have to wrap anything contains it with backsticks ` `

In this case, you have to wrap customer-order-table between ` ` so it looks like this `customer-order-table`

In addition to that mistake, your query here still has two other mistakes

{order_id, customer_id, part_number, price, quantity} VALUES {'', '', '', '', }

1. you have to use ( ), not { }
2. VALUES has only 4 params while it expects 5
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:brucegust
Comment Utility
OK, dsmile!

It looks like the first and biggest problem is the array ($contents) and better defining that, so here's the code that produces that arrray:

public function returnCartProducts(&$conn){
            
            $cart = $_SESSION['cart'];
            #if ($cart) {f
                  $items = explode(',',$this->cart_items);
                  #echo $items;
                  $contents = array();
                  foreach ($items as $item) {
                        $contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
                  }
                  foreach ($contents as $id=>$qty) {
                        $sql = 'SELECT tblreplacementparts.*, tblreplacementproductparts.id, tblreplacementproductparts.replacementProductID, tblreplacementproductparts.price FROM tblreplacementproductparts LEFT JOIN tblreplacementparts ON tblreplacementparts.id = tblreplacementproductparts.partID WHERE partID = '.$id;
                        $results= $conn->query_first($sql);
                        #print_r($contents);
                         $cartProducts .= '<tr height="20"><td>'.$results['partNum'].'</td>
                                          <td width="100">$'.sprintf('%01.2f',$results['price']).'</td>
                                          <td width="100">'.$qty.'</td>
                                          <td align="center" width="100">$'.sprintf('%01.2f', $results['price'] * $qty).'</td></tr>';
                  }
                  
            #}
            
            return $cartProducts;
            
      }

So, how do I break that up in a way where I can insert the relevant info into the Orders table?
0
 
LVL 13

Expert Comment

by:dsmile
Comment Utility
What determine the relevant between $contents and info you want to input into Orders table? (what info for which column)
0
 

Author Comment

by:brucegust
Comment Utility
Hey, dsmile!

I think the problem is that I'm trying to "straddle" a procedural approach and an OOP approach.

In my first post, I had that bit of code sitting on cash_register.php and I was grabbing from a previous function that's located on the cart.class.php page. Your question, "What determines the relevant between $contents is the $_SESSION[cart] variable, which is broken up into its various bits and pieces of data by the $cartProducts = $_SESSION['cart']->returnCartProducts($conn); function.

The $contents is coming from that function. I tried to solve things today by trying my hand at creating a function that would stay consistent with the conventions of OOP and I got all tangled up.

The first thing I have to do is establish the new id since the id field in the Order Details table is not an auto increment dynamic. But then I got all kinds of errors. I wound up putting this on the cash_register.php page:

$count_order = new Database(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,'');
$count_order->connect();
$count_order_sql = "SELECT id  FROM tblorderdetails ORDER by id DESC LIMIT 1";
$count_data = $count_order->query_first($count_order_sql);
$new_id = $count_data['id'] + 1;
echo $new_id;

This worked find, but how would I write that as a function as opposed to a procedure? How could I call that the same way the page is calling $cartProducts = $_SESSION['cart']->returnCartProducts($conn); I'm thinking the answer to that question will allow me to build a single function that will break up the $_SESSION[cart] variable and then insert everything into the appropriate tables.

Thoughts?
0
 
LVL 13

Accepted Solution

by:
dsmile earned 500 total points
Comment Utility
I don't quite understand what you're trying to achieve here, but if you want a function/method that gives you the next id, then you have at least two approaches as I'm about to write here

1. Procedural approach
function getNextOrderid(&$conn){
    $count_order_sql = "SELECT id  FROM tblorderdetails ORDER by id DESC LIMIT 1";
    $count_data = $conn->query_first($count_order_sql);
    return ($count_data['id'] + 1);
}

You call this function like this

$conn = new Database(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,'');
$conn->connect();
getNextOrderid($conn);

2. OOP approach: this code should be placed in some class named OrderDetails or same class of returnCartProducts()

public function getNextOrderid(&$conn){
    $count_order_sql = "SELECT id  FROM tblorderdetails ORDER by id DESC LIMIT 1";
    $count_data = $conn->query_first($count_order_sql);
    return ($count_data['id'] + 1);
}

I can point out what you need to write to insert $_SESSION['cart'] data into Orders table only if you can provide me these info:
1. what's the structure of Orders table? what data should be written to which column
2. what does $_SESSION['cart'] hold? which data should be extracted to be used for Orders table?
Just var_dump($_SESSION['cart'] ) or print_r($_SESSION['cart'] ); to have its structure
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now