Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looping through and inserting DB records

Posted on 2010-08-15
6
Medium Priority
?
462 Views
Last Modified: 2013-12-13
I am creating a script that would read values posted by IPN script from ejunkie . com shopping cart and insert them into my database. My dilemma is how to handle the multiple cart items. In the script attached I hardcoded the possibilities of having 3 cart items, but in reality there could be any number of items. Below is the format from the $_POST where:

 [option_selection1_X] represents the option values from cart #X
so CART 1 option 1 selection is  [option_selection1_1]

CART 2 then would be  [option_selection1_2]

[num_cart_items] => 2 represents that there are 2 items total in this order.

How can I modify my attached PHP code so that it can accept infinite number of cart items instead of just 3?


//CART 1 ITEM    
[item_name1] => site.com ENHANCED listing
    [item_number1] => enhanced
    [mc_gross_1] => 0
    [option_name1_1] => featured
    [option_selection1_1] => yes
    [option_name2_1] => months
    [option_selection2_1] => 12
    [num_cart_items] => 2
    [option_name3_1] => options
    [option_selection3_1] => images: (12) vtours: (3) files: (3)
//CART 2 ITEM
  [item_name2] => site.com STANDARD listing
    [item_number2] => STANDARD
    [mc_gross_1] => 0
    [option_name1_2] => featured
    [option_selection1_2] => no
    [option_name2_2] => months
    [option_selection2_2] => 10
    [num_cart_items] => 2
    [option_name3_2] => options
    [option_selection3_2] => images: (12) vtours: (3) files: (3)
//CART 1
IF (isset($_POST['option_selection1_1'])) {
$uploads1 = $_POST['option_selection1_1'];
$listingdb_id1 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads1);
$images1 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads1);
$vtours1 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads1);
$files1 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads1);
}
//CART 2
IF (isset($_POST['option_selection1_2'])) {
$uploads2 = $_POST['option_selection1_2'];
$listingdb_id2 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads2);
$images2 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads2);
$vtours2 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads2);
$files2 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads2);
}
//CART 3
IF (isset($_POST['option_selection1_3'])) {
$uploads3 = $_POST['option_selection1_3'];
$listingdb_id3 = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads3);
$images3 = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads3);
$vtours3 = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads3);
$files3 = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads3);
}

IF (isset($_POST['option_selection3_1']) $featured1 = "yes"; //CART 1
IF (isset($_POST['option_selection3_2']) $featured2 = "yes"; //CART 2
IF (isset($_POST['option_selection3_3']) $featured3 = "yes"; //CART 3

IF (isset($_POST['option_selection2_1']) $term1 = $_POST['option_selection2_1'];
IF (isset($_POST['option_selection2_2']) $term2 = $_POST['option_selection2_2'];
IF (isset($_POST['option_selection2_3']) $term3 = $_POST['option_selection2_3'];

//$term = $term1 + $term2 + $term3;
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$plan = $_POST['item_name1']." + ".$_POST['item_name2']." + ".$_POST['item_name3'];
$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];

$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$plan."\r\n";
mail ("test@goswap.org","post from e-junkie", $message);

IF (isset($_POST['item_name1'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id1','$pp_txn_id','$payment_status','$payment_amount','$featured1','$term1',now(),'$discount_codes','$num_cart_items','images1','vtours1','files1','$plan')";
			$recordSet = $conn->Execute($sql);
}
IF (isset($_POST['item_name2'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id2','$pp_txn_id','$payment_status','$payment_amount','$featured2','$term2',now(),'$discount_codes','$num_cart_items','images2','vtours2','files2','$plan')";
			$recordSet = $conn->Execute($sql);
}

IF (isset($_POST['item_name3'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id3','$pp_txn_id','$payment_status','$payment_amount','$featured3','$term3',now(),'$discount_codes','$num_cart_items','images3','vtours3','files3','$plan')";
			$recordSet = $conn->Execute($sql);
}

Open in new window

0
Comment
Question by:greenerpastures
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Snarfles earned 2000 total points
ID: 33440801
Hi

What you will probably want to do is pass a variable to your script with the amount of carts and then use a for loop to go through it all.

Something along the lines of below. I havent tested it but the logic is there so it should be close.
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];

$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];


if ($num_cart_items != null){

for ( $i = 1; $i <= $num_cart_items; $i++) {


IF (isset($_POST['option_selection1_'.$i])) {
$uploads = $_POST['option_selection1_'.$i];
$listingdb_id = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads);
$images = preg_replace(".*/images: \((\d*)\).*/", "\\1", $uploads);
$vtours = preg_replace(".*/vtours: \((\d*)\).*/", "\\1", $uploads);
$files = preg_replace(".*/files: \((\d*)\).*/", "\\1", $uploads);
}

IF (isset($_POST['option_selection3_'.$i]) $featured = "yes"; 
IF (isset($_POST['option_selection2_'.$i]) $term = $_POST['option_selection2_'.$i];

IF (isset($_POST['item_name1'])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,images,vtours,files,plan) VALUES ('$listingdb_id','$pp_txn_id','$payment_status','$payment_amount','$featured','$term',now(),'$discount_codes','$num_cart_items','images','vtours','files','$plan')";
			$recordSet = $conn->Execute($sql);
}
if ($plan != null){
$plan = $_POST['item_name'.$i];
}
else {
$plan .= " + ".$_POST['item_name'.$i];
}
}
$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$plan."\r\n";
mail ("test@goswap.org","post from e-junkie", $message);


}
else {
	echo "No carts found. Epic Fail.";
}

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33440882
Wouldn't this be a lot easier if PayPal could present the information in XML?  Are you listening PayPal?

Sorry for the rant.  Have a look a this code snippet.  Your part starts at line 66.  It is old code and not very pretty, but it demonstrates what you need to know about the arcane PayPal IPN data.

HTH, ~Ray
<?php // RAY_paypal_ipn.php - CUSTOM PayPal IPN PROCESSING

// LOCAL FUNCTIONS AND VARIABLES, DB STUFF, ETC. - NO SESSION HERE - FUNCTION warning_RAY() SENDS AN EMAIL MESSAGE
require_once('_config.php');

// READ THE POST FROM PayPal AND ADD 'cmd'
$req      = 'cmd=_notify-validate';
$postdata = '';
foreach ($_POST as $key => $value)
{
    $postdata .= "\n $key = $value ";              // SAVE THE COLLECTION
    $$key     = trim(stripslashes($value));        // ASSIGN LOCAL VARIABLES
    $value    = urlencode(stripslashes($value));   // ENCODE FOR BOUNCE-BACK
    $req      .= "&$key=$value";                   // APPEND TO VERIFICATION STRING
}

// SET THE HEADERS FOR THE CONFIRMATION POST BACK TO PayPal
$header .= "POST /cgi-bin/webscr HTTP/1.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";

// OPEN THE HTTP PIPE FOR THE POST BACK TO PayPal
$fp = fsockopen ('www.paypal.com', 80, $errno, $errstr, 30);

// TEST FOR SUCCESSFUL OPENNING OF THE HTTP PIPE
if (!$fp) // HTTP ERROR
{
    warning_RAY("IPN HTTP ERROR", "FSOCKOPEN FAILED \n\n ERRNO=$errno \n\n ERRSTR=$errstr \n\n");
    die();
}

// WITH HTTP OPEN - WRITE HEADER AND REQUEST
fputs ($fp, $header . $req);

// WITH HTTP OPEN - READ PayPal RESPONSE
$paypal_reply   = '';
$paypal_headers = '';
while (!feof($fp))
{
    $paypal_reply    = fgets ($fp, 1024);
    $paypal_headers .= $paypal_reply;
}
fclose ($fp);

// IF THIS IS TRULY A POST FROM PAYPAL, PROCESS ORDER NOTIFICATION
if ($paypal_reply == "VERIFIED")
{
    $errormsg = "";

    // IF PAYMENT IS NOT COMPLETED (MAY BE E-CHECK?)
    if ($payment_status != "Completed") { $errormsg .= "\nE: payment_status"; }

    // IF PAYMENT WAS NOT SENT TO ME?
    $receiver_email = strtolower($receiver_email);
    if ($receiver_email == "me@my.org") { } else { $errormsg .= "\nE: receiver_email"; } // ??? SET EMAIL ADDRESS

    // I AM NOT CHECKING SOME THINGS BECAUSE WE ARE USING ENCRYPTED OR STORED BUY-NOW BUTTONS
    if ($mc_currency != 'USD') { $errormsg .= "\nE: mc_currency"; }

    // CHECK FOR TXN_ID ALREADY PROCESSED - NORMAL FOR E-CHECK
    $sql = "SELECT txn_id FROM PAYPAL_ORDER_LOG WHERE txn_id = \"$txn_id\" ";
    if (!$result = mysql_query($sql, $db_connection)) { fatal_query_error($sql); }
    $num_rows = mysql_num_rows($result);
    if ($num_rows  > 0) { $errormsg .= "\nE: Transaction id $txn_id already processed $num_rows time(s)"; }

    // GENERATE ARRAYS OF PURCHASE DATA FROM THE WEIRD PAYPAL VARIABLE NAMES
    // ACHTUNG: NO POSITION ZERO IN THESE ARRAYS
    $w_item_number = array();
    $w_quantity    = array();
    $w_mc_gross    = array();
    $w_item_name   = array();

    // IF TRANSACTION TYPE IS CART, MAY BE MULTIPLE ITEMS IN DIFFERENT QUANTITIES - LOAD ARRAYS
    // NOTE NOTE NOTE NO POSITION ZERO IN THESE ARRAYS
    if ($txn_type == "cart") {
        while ($num_cart_items > 0) {
            $proxy    = "item_number" . "$num_cart_items";
            $w_item_number[$num_cart_items] = $$proxy;

            $proxy    = "quantity" . "$num_cart_items";
            $w_quantity[$num_cart_items] = $$proxy;

            $proxy    = "mc_gross_" . "$num_cart_items";
            $w_mc_gross[$num_cart_items] = $$proxy;

            $proxy    = "item_name" . "$num_cart_items";
            $w_item_name[$num_cart_items] = $$proxy;

            $num_cart_items--;
        }
    } else {
        // NOT A CART - SINGLETON ITEM ONLY - NORMALIZE INTO ARRAY FOR USE WITH ITERATOR
        // NOTE NOTE NOTE NO POSITION ZERO IN THESE ARRAYS
        $w_item_number[1] = $item_number;
        $w_quantity[1]    = $quantity;
        $w_mc_gross[1]    = $mc_gross;
        $w_item_name[1]   = $item_name;
    }

// *****************************************************
// ACTIVATE THIS CODE BLOCK TO SEE WHAT IS IN THE ORDER ARRAYS
//
//    ob_start();
//    echo "<pre>\n";
//    echo "\nW_ITEM_NUMBER"; var_dump($w_item_number);
//    echo "\nW_QUANTITY";    var_dump($w_quantity);
//    echo "\nW_MC_GROSS";    var_dump($w_mc_gross);
//    echo "\nW_ITEM_NAME";   var_dump($w_item_name);
//    $foo    = ob_get_contents();
//    ob_end_clean();
//    warning_RAY("IPN VARDUMPS", "$foo \n\n");
// *****************************************************


    // ITERATE OVER THE ARRAYS
    $kount = 0; // NO POSITION ZERO IN THESE ARRAYS
    while ($kount < count($w_item_number))
    {
        $kount++; // BUMP BEFORE WORKING
        $my_item_number = $w_item_number[$kount];
        $my_quantity    = $w_quantity[$kount];
        $my_mc_gross    = $w_mc_gross[$kount];
        $my_item_name   = $w_item_name[$kount];

        //
        // ??? PROCESS THE ORDERS USING YOUR BUSINESS LOGIC
        //

    } // END ITERATION OVER THE ORDERS

    // END OF NORMAL PAYPAL IPN PROCESSING
    die();
}

// NOT NORMAL PROCESSING
// LOG INVALID POSTS FOR MANUAL INVESTIGATION AND INTERVENTION
if ($paypal_reply == "INVALID")
{
    warning_RAY("IPN INVALID", "IPN REPLY $paypal_headers \n\n$errormsg \n\nPOST DATA FOLLOWS: $postdata \n\n");
    die();
}

// OTHERWISE, PayPal RETURNED BAD DATA (OR INTERNET HTTP ERRORS OR TIMEOUT)
warning_RAY("IPN REPLY UNKNOWN", "IPN REPLY $paypal_headers \n\n$errormsg \n\nPOST DATA FOLLOWS: $postdata \n\n");
die();

Open in new window

0
 

Author Comment

by:greenerpastures
ID: 33442355
Snarfles,
1. Thanks that almost works, for some reason though it inserts the values 2 times in the table: ejunkie_orders, duplicating the entries, while 1 time in the ejunkie_uploads which is the way it should be.

2. I also has terrible time with this simple statement:
$sql = "SELECT * FROM 'ejunkie_uploads' WHERE listingdb_id = '$listingdb_id' ";
Why is it only accepting  it only these "   '  " quotes around the 'ejunkie_uploads' when copied from PhpMyadmin and not the normal single quotes "  '  " ? THe statement just would not work until I copied it over.
$pp_txn_id = $_POST['txn_id'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$discount_codes = $_POST['discount_codes'];
$num_cart_items = $_POST['num_cart_items'];

if ($num_cart_items != null){

for ( $i = 1; $i <= $num_cart_items; $i++) {

IF (isset($_POST['option_selection1_'.$i])) {
$uploads = $_POST['option_selection1_'.$i];
$listingdb_id = preg_replace("/ID: \((\d*)\).*/", "\\1", $uploads);
$images = preg_replace("/.*images: \((\d*)\).*/", "\\1", $uploads);
$vtours = preg_replace("/.*vtours: \((\d*)\).*/", "\\1", $uploads);
$files = preg_replace("/.*files: \((\d*)\).*/", "\\1", $uploads);
}

IF (isset($_POST['option_selection3_'.$i])) $featured = "yes"; //CART 1

IF (isset($_POST['option_selection2_'.$i])) $term = $_POST['option_selection2_'.$i];
$plan = $_POST['item_name'.$i];

$message = $listingdb_id."\r\n".$term."\r\n".$featured."\r\n".$images."\r\n".$vtours."\r\n".$files."\r\n".$plan."\r\n";
mail ("test@site.org","BEFORE processing", $message);

IF (isset($_POST['item_name'.$i])) {
$sql = "INSERT INTO ejunkie_orders (listingdb_id,pp_txn_id,confirmed,price,featured,term,date,discount_codes,cart,plan) VALUES ('$listingdb_id','$pp_txn_id','$payment_status','$payment_amount','$featured','$term',now(),'$discount_codes','$num_cart_items','$plan')";
			$recordSet = $conn->Execute($sql);
}

//UPLOADS TABLE
IF ($images != "" && $listingdb_id != "") {
$sql = "SELECT * FROM 'ejunkie_uploads' WHERE listingdb_id = '$listingdb_id' ";
$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$sql = "INSERT INTO `ejunkie_uploads` (listingdb_id,images,vtours,files) VALUES ('$listingdb_id','$images','$vtours','$files')";
$recordSet = $conn->Execute($sql);
} else {
mysql_query("UPDATE 'ejunkie_uploads' SET images = ".$recordSet->fields['images'] + $images." vtours = ".$recordSet->fields['vtours'] + $vtours.", files = ".$recordSet->fields['files'] + $files. "WHERE listingdb_id = ".$listingdb_id);
}


} // END OF UPLOADS

//$expiration = date("Y-m-d", mktime(0, 0, 0, date("m")+$term, date("d"),  date("Y")));

//IF(isnumeric($listingdb_id)) {

//mysql_query("UPDATE ".$config['table_prefix']."listingsdb SET listingsdb_expiration = '$expiration', listingsdb_featured = '$featured', listingsdb_active = 'yes' WHERE listingsdb_id = '$listingdb_id'");
//}


} //end of FOR 
} // END OF LOOP
else {
	echo "No carts found. Epic Fail.";
}

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:greenerpastures
ID: 33442499
I also cannot get this statement to work and update the table when I pass in the existing listingdb_id that already is in this table:

} else {
mysql_query("UPDATE 'ejunkie_uploads' SET images = ".$recordSet->fields['images'] + $images.", vtours = ".$recordSet->fields['vtours'] + $vtours.", files = ".$recordSet->fields['files'] + $files. "WHERE listingdb_id = ".$listingdb_id);
}

I suspect something is wrong with this statement: if ($recordSet2 === false)
0
 
LVL 9

Expert Comment

by:Snarfles
ID: 33442989
Hi

I dont think you need to have quotes around the table names in your queries.

I can't see why it would duplicate the entries in ejunkie_orders unless the $num_cart_items variable is being doubled somehow. Can you echo $num_cart_items for me and see that it only says 3?

I suspect something is wrong with this statement: if ($recordSet2 === false) <-- $recordSet2 isnt being set anywhere...

Hope that helps a little.

Cheers

Luke
0
 

Author Closing Comment

by:greenerpastures
ID: 33450212
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

715 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