0

I am failry new to PHP. I have written a few REST services in the past using basic JSON methods within the PHP framework but am struggling to get the correct Syntax for a PDO insert working. I have read through a number of posts on here but the syntax seems slightly different each time so after struggling, I thought it best to try & reach out for advice.

The code I have for querying the API via a Cuurl is as per below:

//Set Access Token
$access = base64_encode($str);

//Set Header Request Data 
$header = array();
$header[] = 'Content-length: 0';
$header[] = 'Content-type: application/json';
$header[] = 'Authorization: Basic'." ".$access;

//Initiate GET Request via curl
$ch=curl_init();    
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_HTTPGET, TRUE);
curl_setopt($ch, CURLOPT_URL, $apiurl );
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 2);
curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$result = curl_exec($ch);

//Close the curl list
curl_close($ch);

//Output the Response
$data = json_decode($result, true);

I am then using the results of this JSON response as per below to try & insert the rows from the Array that haven't been processed thus far for the day.

$sth = $con->prepare('INSERT INTO epos_transactions (TransactionId,CustomerId,TableId,DeviceId,DateTime,PaymentStatus,DiscountValue,Total,ChangeTendered,Barcode,EatOut,NonDiscountable,NonVAT) 
                        VALUES 
                        (?,?,?,?,?,?,?,?,?,?,?,?,?) 
                        WHERE 
                        TransactionID not in (select TransactionId from epos_transactions where DateTime >= date("Y-m-d")."T00:00:00.000")');

$sth->bindParam (":TransactionId", $TransactionId, PDO::PARAM_STR);
$sth->bindParam (":CustomerId", $CustomerId, PDO::PARAM_STR);
$sth->bindParam (":TableId", $TableId, PDO::PARAM_STR);
$sth->bindParam (":DeviceId", $DeviceId, PDO::PARAM_STR);
$sth->bindParam (":DateTime", $DateTime, PDO::PARAM_STR);
$sth->bindParam (":PaymentStatus", $PaymentStatus, PDO::PARAM_STR);
$sth->bindParam (":DiscountValue", $DiscountValue, PDO::PARAM_STR);
$sth->bindParam (":Total", $Total, PDO::PARAM_STR);
$sth->bindParam (":ChangeTendered", $ChangeTendered, PDO::PARAM_STR);
$sth->bindParam (":Barcode", $Barcode, PDO::PARAM_STR);
$sth->bindParam (":EatOut", $EatOut, PDO::PARAM_STR);
$sth->bindParam (":NonDiscountable)", $NonDiscountable, PDO::PARAM_STR);
$sth->bindParam (":NonVAT", $NonVAT, PDO::PARAM_STR);

  $sth->execute(array($data));

$sth = null;
$con = null;

The error states that I have an issue with array to string conversion when executing the PDO statement & also that the number of bound variables does not match the number of tokens.

This is where my knowledge seems to run out & I have gone round in circles reading a number of posts on here but I think they are all regarding slightly different scenarios. e.g.

I think I have the exact number of tokens matching the variables.

I'm really just looking for advise here so I can further my knowledge on this & set myself up on the right path for moving forwards on this.

Thanks in advance.

AshBash
  • 27
  • 6
  • Where do your variables you bind to come from? I can't see any definition of `$TransactionId` .. `$CustomerId` and all these... – Twinfriends Oct 09 '17 at 10:15
  • Oh btw: You don't have to pass the `$data` array to the `execute()` method when you're binding the variables with the `bindParam()` method. Use either the method, or pass the array, but not both. Not sure if this may even cause the number of bound variables error. – Twinfriends Oct 09 '17 at 10:22
  • You're using a named place holder so that should be in your 'SQL` as well if you're using positional place holder then it should go with 1, 2 , 3 .... and ? in the SQL. Reffer [this](http://php.net/manual/en/pdo.prepared-statements.php) example 1 and 2. And there's a typo at `":NonDiscountable)"` correct that as well. As @Twinfriends said just use `$sth->execute();` no need to pass an array. – S4NDM4N Oct 09 '17 at 10:22

0 Answers0