title: Saving to database on payment successful (paypal)
body: <p>I am trying to figure out the best way to save data (which was submitted previously in a form) to a database, after the client has paid for the item, using paypal.  Something along the lines of this process:</p>
  
  <p>1)Fill in form on actual website --> 2)Login Paypal --> 3) Pay Now (PayPal) --> 4) Data is inserted in database --> 5) Return to starting point?</p>
  
  <p>I have figured out how to do steps 1 to 3 and 5, however would need some help in doing step 4. As far as I can tell, I need to somehow store the data, and then save or discard the stored data as required.  What would the best way to do so be?</p>
  
  <p><strong>The Form</strong> </p>
  
  <pre><code> &lt;form action="" method="post" target="" id="bookstay"&gt;
        &lt;input type="hidden" name="cmd" value="_xclick" /&gt;
        &lt;input type="hidden" name="unitprice" value="40" /&gt;
        &lt;input type="hidden" name="apt_name" value="Apartment1" /&gt;
        &lt;input type="hidden" name="no_note" value=""/&gt;
        &lt;input type="hidden" name="lc" value="MT" /&gt;
        &lt;input type="hidden" name="currency_code" value="EUR" /&gt;
        &lt;input type="hidden" name="bn" value="BuyNowBF:btn_buynow_LG.gif:NonHostedGuest" /&gt;
        &lt;input type="hidden" name="apartment" value="1"/&gt;
        &lt;input name='first_name' class="short-input" id='name' type="text" value="Name" onFocus="this.value = ''" /&gt;
        &lt;input  name= 'last_name' class="short-input" id='name' type="text" value="surname" onFocus="this.value = ''" /&gt;
        &lt;input  name='payer_email' class="long-input" type="text" value="Email" onFocus="this.value = ''"  /&gt;
        &lt;input name='address' class="long-input" type="text" value="Address" onFocus="this.value = ''" /&gt;
        &lt;input name='mobile'  class="short-input" type="text" value="mobile" onFocus="this.value = ''"  /&gt;
        &lt;div class='select' id='peopletostay'&gt;
             &lt;select name='pax' class='short-input'&gt;
                  &lt;option value='0'&gt;people to stay&lt;/option&gt;
                  &lt;option value='1'&gt;1&lt;/option&gt;
                  &lt;option value='2'&gt;2&lt;/option&gt;
                  &lt;option value='3'&gt;3&lt;/option&gt;
                  &lt;option value='4'&gt;4&lt;/option&gt;
             &lt;/select&gt;
        &lt;/div&gt;                         
        &lt;div id="dateofarrival"&gt;
             date of arrival&lt;br&gt;                 
             &lt;div class='select' id='date'&gt;
                  &lt;select class="short-input day-from" name="day_from"&gt;   
                         &lt;option value= "01" &gt;01&lt;/option&gt; 
                            ...
                          &lt;option value= "31" &gt;31&lt;/option&gt;
                  &lt;/select&gt;
             &lt;/div&gt;
             &lt;div class='select' id='month'&gt;
              &lt;select class="short-input month-from" name="month_from" size="1"&gt;
                    &lt;option value="01" &gt;January&lt;/option&gt;
                    ....                    
                    &lt;option value="12" &gt;December&lt;/option&gt;
             &lt;/select&gt;
             &lt;/div&gt;
             &lt;div class='select' id='year'&gt;
                  &lt;select class="short-input year-from" name='year_from'&gt;
                        &lt;option value= 2015 &gt; 2015&lt;/option&gt;
                         ....
                         &lt;option value= 2025 &gt; 2025&lt;/option&gt;
                   &lt;/select&gt;
             &lt;/div&gt;
        &lt;/div&gt;
        &lt;div id="dateodeparture"&gt;
             date of arrival&lt;br&gt;                 
             &lt;div class='select' id='date'&gt;
                  &lt;select class="short-input day-from" name="day_to"&gt;   
                         &lt;option value= "01" &gt;01&lt;/option&gt; 
                            ...
                          &lt;option value= "31" &gt;31&lt;/option&gt;
                  &lt;/select&gt;
             &lt;/div&gt;
             &lt;div class='select' id='month'&gt;
              &lt;select class="short-input month-from" name="month_to" size="1"&gt;
                    &lt;option value="01" &gt;January&lt;/option&gt;
                    ....                    
                    &lt;option value="12" &gt;December&lt;/option&gt;
             &lt;/select&gt;
             &lt;/div&gt;
             &lt;div class='select' id='year'&gt;
                  &lt;select class="short-input year-from" name='year_to'&gt;
                        &lt;option value= 2015 &gt; 2015&lt;/option&gt;
                         ....
                         &lt;option value= 2025 &gt; 2025&lt;/option&gt;
                   &lt;/select&gt;
             &lt;/div&gt;
        &lt;/div&gt;
        &lt;textarea name='remarks'&gt;Extra Remarks&lt;/textarea&gt;
        &lt;button type="submit" name="proceedtopaypal" id="proceedtopaypal"&gt;make booking (proceed to paypal)&lt;/button&gt;
  
        &lt;/form&gt;
  </code></pre>
  
  <p><strong>Payment Code</strong></p>
  
  <pre><code>&lt;?php
  if ($_POST) {
  if (isset($_POST['proceedtopaypal'])){
  
  include 'connect.php';
  
      $apartment = mysqli_real_escape_string($conn, $_POST['apartment']);
      $unitprice = mysqli_real_escape_string($conn, $_POST['unitprice']);
      $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
      $last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
      $payer_email = mysqli_real_escape_string($conn, $_POST['payer_email']);
      $address = mysqli_real_escape_string($conn, $_POST['address']);
      $apt_name = mysqli_real_escape_string($conn, $_POST['apt_name']);
      $mobile = mysqli_real_escape_string($conn, $_POST['mobile']);
      $pax = mysqli_real_escape_string($conn, $_POST['pax']);
      $remarks = mysqli_real_escape_string($conn, $_POST['remarks']);
      $day_from = mysqli_real_escape_string($conn, $_POST['day_from']);
      $month_from = mysqli_real_escape_string($conn, $_POST['month_from']);
      $year_from = mysqli_real_escape_string($conn, $_POST['year_from']);
      $booking_from = $year_from."-".$month_from."-".$day_from;
      $day_to = mysqli_real_escape_string($conn, $_POST['day_to']);
      $month_to = mysqli_real_escape_string($conn, $_POST['month_to']);
      $year_to = mysqli_real_escape_string($conn, $_POST['year_to']);
      $booking_to = $year_to."-".$month_to."-".$day_to;
      $no_of_nights = abs(strtotime($booking_to) - strtotime($booking_from)); 
      $quantity = floor($no_of_nights / (60*60*24));
  
      // paypal settings 
      $paypal_email = 'christabelbusuttil@gmail.com';
      $return_url = 'http://localhost/Webdevelopment/V18/apartments.php';
      $cancel_url = 'http://localhost/Webdevelopment/V18/apartments.php';
      $notify_url = 'http://localhost/Webdevelopment/V18/paypal/payments.php';
  
      $item_amount = $unitprice * $quantity;
      $item_name = "Booking at ".$apt_name." from " .$booking_from ." to " .$booking_to;
      $validdate = false;
      $buttonpressed = false;
      $checkin='&lt;p&gt;Check in date is invalid.&lt;/p&gt;';
      $checkout='&lt;p&gt;Check out date is invalid&lt;/p&gt;';
      $larger = '&lt;p&gt;Check in date is after check out date&lt;/p&gt;';
      $noinfo='&lt;p&gt;please fill in the missing information.&lt;/p&gt;';
      $booked='&lt;p&gt;The dates selected are already booked for this apartment&lt;/p&gt;';
      $equal = '&lt;p&gt;You need to spend a minimum of 1 night in these apartment&lt;/p&gt;';
      $thankyou = '&lt;h5&gt;Thank you&lt;/h5&gt;&lt;p&gt;thank you for booking an apartment with V18-apartments.&lt;/p&gt;';
      $window = '';
  
          function IsInjected($str) {
            $injections = array('(\n+)',
                        '(\r+)',
                        '(\t+)',
                        '(%0A+)',
                        '(%0D+)',
                        '(%08+)',
                        '(%09+)'
                        );
            $inject = join('|', $injections);
            $inject = "/$inject/i";
            if(preg_match($inject,$str))
              {
              return true;
            }
            else
              {
              return false;
              }
          }
  
          if (!checkdate($month_from, $day_from, $year_from)) {
              $window = $checkin;
              echo $window;
              $validate = true;
          }
          else if (!checkdate($month_to, $day_to, $year_to)) {
              $window = $checkout;
              $validate = true;
              echo $window;
              //echo "Check out date is invalid";
          }
          else if ($booking_from &gt; $booking_to) {
                  $window = $larger;
                  $validate = true;
                  echo $window;
                  // echo "Check in date is after check out date";
          }
          else if ($booking_from == $booking_to) {
              $window = $equal;
              $validate = true;
              echo $window;
          }   
      // check if all info is filled in 
          else if (($first_name == "Name") || ($last_name == "surname") || ($payer_email == "Email") || ($mobile == "mobile") || ($address == "Address")) {
              $window = $noinfo;
              echo $window;
              $validate = true;
              // echo "Please fill in the missing information";
          }
          else if (IsInjected($payer_email)) {
              echo "Not an email";
          }
          else if ($validdate == false) {
              $final = true;
              $sql = "SELECT COUNT(*)  FROM room_nights WHERE apartmentID= '$apartment' AND dates &gt;= '$booking_from' AND dates &lt;= '$booking_to'";
              $result = mysqli_query($conn, $sql);
              $result = mysqli_query($conn, $sql);
              $row=mysqli_fetch_row($result);
  
              if ($row[0] &gt; 0) {
                  $window = $booked;
                  echo $window;
              }
  
          else if ($final == true)  {
              // save to database 
                  include 'insertdata.php';   // code below
  
                  echo $item_name;
                  // include functions
                  include ("pay_functions.php");
                  // Check if paypal request or response
                  if (!isset($_POST["txn_id"]) &amp;&amp; !isset($_POST["txn_type"])){
                  // Firstly Append paypal account to querystring
                      $querystring .= "?business=".urlencode($paypal_email)."&amp;";  
                      // Append amount&amp; currency (£) to quersytring so it cannot be edited in html
                      //The item name and amount can be brought in dynamically by querying the $_POST['item_number'] variable.
                      $querystring .= "item_name=".urlencode($item_name)."&amp;";
                      $querystring .= "amount=".urlencode($item_amount)."&amp;";
                          //loop for posted values and append to querystring
                          foreach($_POST as $key =&gt; $value){
                              $value = urlencode(stripslashes($value));
                              $querystring .= "$key=$value&amp;";
                          }
                      // Append paypal return addresses
                      $querystring .= "return=".urlencode(stripslashes($return_url))."&amp;";
                      $querystring .= "cancel_return=".urlencode(stripslashes($cancel_url))."&amp;";
                      $querystring .= "notify_url=".urlencode($notify_url);
                      // Append querystring with custom field
                      //$querystring .= "&amp;custom=".USERID;
                      // Redirect to paypal IPN
                      header('location:https://www.sandbox.paypal.com/cgi-bin/webscr'.$querystring);
                      exit();
  
                  } 
              else {
                      // Response from paypal
                      $req = 'cmd=_notify-validate';
                      foreach ($_POST as $key =&gt; $value) {
                          $value = urlencode(stripslashes($value));
                          $value = preg_replace('/(.*[^%^0^D])(%0A)(.*)/i','${1}%0D%0A${3}',$value);// IPN fix
                          $req .= "&amp;$key=$value";
                      }
  
                      // assign posted variables to locate variables
                      $data['item_name'] = $_POST['item_name'];
                      $data['item_number'] = $_POST['item_number'];
                      $data['payment_status'] = $_POST['payment_statis'];
                      $data['payment_amount'] = $_POST['mc_gross'];
                      $data['payment_currency'] = $_POST['mc_currency'];
                      $data['txn_id'] = $_POST['txn_id'];
                      $data['receiver_email'] = $_POST['receiver_email'];
                      $data['payer_email'] = $_POST['payer_email'];
                      $data['custom'] = $_POST['custom'];
  
                      // post back to paypal system and validate
  
                      $header = "POST /cgi-bin/webscr HTTP/1.0\r\n";
                      $header .= "Content-Type : application/x-www-form-urlencoded\r\n";
                      $header .= "Content-Lenght: " .strlen($req) . "\r\n\r\n";
  
                      $fp = fsockopen ('ssl://www.sandbox.paypal.com', 443, $errno, $errstr, 30);
  
                  if (!$fp) {
                  // HTTP error
                  } else {
                      mail('christabelbusuttil@gmail.com', '0', '0');
                      fputs ($fp, $header . $req);
                      while (!feof($fp)) {
                          $res = fgets($fp, 1024);
                          if (strcmp ($res, "VERIFIED") == 0) {
  
                               // validate payment (check unique txnid &amp; correct price) 
                               $valid_txnid = check_txnid($data['txn_id']);
                               $valid_price = check_price($data['payment_amount'], $data['item_number']);
                               // Payment validated and verified
                              if ($valid_price &amp;&amp; $valid_price) {
                                   $orderid = updatePayments($data);
                                  if ($orderid){
                                       // payment has been made and inserted into db
                                  } else {
                                       echo "Error";
                                  }
                              } 
                              else if (strcmp($res, "INVALID") == 0) {
                                      echo "Payment invalid";
                              }
                          }
                               fclose($fp);
                      }
                  }
              }    
          } 
      }
  }
  
   }
  
  ?&gt;
  </code></pre>
  
  <p><strong>INSERTDATA.PHP</strong></p>
  
  <pre><code> &lt;?php
  
      $apartment = mysqli_real_escape_string($conn, $_POST['apartment']);
      $unitprice = mysqli_real_escape_string($conn, $_POST['unitprice']);
      $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
      $last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
      $payer_email = mysqli_real_escape_string($conn, $_POST['payer_email']);
      $address = mysqli_real_escape_string($conn, $_POST['address']);
      $apt_name = mysqli_real_escape_string($conn, $_POST['apt_name']);
      $mobile = mysqli_real_escape_string($conn, $_POST['mobile']);
      $pax = mysqli_real_escape_string($conn, $_POST['pax']);
      $remarks = mysqli_real_escape_string($conn, $_POST['remarks']);
      $day_from = mysqli_real_escape_string($conn, $_POST['day_from']);
      $month_from = mysqli_real_escape_string($conn, $_POST['month_from']);
      $year_from = mysqli_real_escape_string($conn, $_POST['year_from']);
      $booking_from = $year_from."-".$month_from."-".$day_from;
      $day_to = mysqli_real_escape_string($conn, $_POST['day_to']);
      $month_to = mysqli_real_escape_string($conn, $_POST['month_to']);
      $year_to = mysqli_real_escape_string($conn, $_POST['year_to']);
      $booking_to = $year_to."-".$month_to."-".$day_to;
      $no_of_nights = abs(strtotime($booking_to) - strtotime($booking_from)); 
      $quantity = floor($no_of_nights / (60*60*24));
      $reason = "Booked by ".$first_name." ".$last_name." for ".$pax ." people";
  
  function daterange($booking_from, $booking_to, $step = '+1 day', $output_format = 'Y-m-d') {
    $dates = array();
    $first = new DateTime($booking_from);
    $last = new DateTime($booking_to);
    $last = $last-&gt;modify('+ 1 day');
    $interval = DateInterval::createFromDateString($step);
    $period = new DatePeriod($first, $interval, $last);
  
  
    foreach ($period as $date) {
        $dates[] = $date-&gt;format($output_format);
    } 
  
    return $dates;
  }
  
  $dates = daterange($booking_from, $booking_to);
  include 'connect.php';
  
   if (!$conn-&gt;autocommit(FALSE)) {
      printf("Errormessage: %s\n", $conn-&gt;error);
   }
  
   if (!$conn-&gt;query("INSERT INTO client_details (clientID, name, email, address, mobile) VALUES ('', '$first_name $last_name', '$payer_email', '$address', '$mobile')")) {
       printf("Errormessage: %s\n", $conn-&gt;error);
   }
  
  
   if (!$conn-&gt;query("INSERT INTO bookings (bookingID, apartmentID, clientID, date_from, date_to, nights, pax, remarks) VALUES ('', '$apartment', LAST_INSERT_ID(), '$booking_from', '$booking_to', '$days', '$pax', '$remarks')")) {
       printf("Errormessage: %s\n", $conn-&gt;error);
   }
  
   foreach ($dates as $date) {
   if (!$conn-&gt;query("INSERT INTO room_nights (bookingID, apartmentID, dates, reason) VALUES (LAST_INSERT_ID(), '$apartment', '$date', '$reason')")) {
        printf("Errormessage: %s\n", $conn-&gt;error);
   }
   }
  
   if (!$conn-&gt;commit()) {
       printf("Errormessage: %s\n", $conn-&gt;error);
   }
   $conn-&gt;close();
  
  ?&gt;
  </code></pre>