phpapipdosqlsrv
Cannot insert data into SQL db using PDO SQLSRV
I'm trying to learn to create a simple CRUD API for sql server database. For now, I only manage to select table from the database and now I'm moving on to insert data into the database.
But I have problem inserting values into my local SQL Server DB using PDO SQLSRV. Please guide me to fix my code.
For your information, my sql table only use nvarchar, datetime, int and numeric data types.
Table structure
AcCustomerID | nvarchar(20)
AcCustomerName | nvarchar(100)
AcTermID | nvarchar(20)
AcAreaID | nvarchar(20)
AcCusGroupID | nvarchar(20)
AcCusUDGroupID | nvarchar(20)
BillingAddress1 | nvarchar(100)
BillingAddress2 | nvarchar(100)
BillingAddress3 | nvarchar(100)
BillingAddress4 | nvarchar(100)
BillingTel1 | nvarchar(100)
BillingTel2 | nvarchar(100)
BillingFax1 | nvarchar(100)
BillingFax2 | nvarchar(100)
BillingPostalCode | nvarchar(10)
BillingContact1 | nvarchar(100)
BillingContact2 | nvarchar(100)
BillingEmail | nvarchar(100)
BillingHomePage | nvarchar(100)
IsActive | nvarchar(1)
Notes | nvarchar(MAX)
CreateBy | nvarchar(20)
CreateDate | datetime
EditBy | nvarchar(20)
EditDate | datetime
Stamp | int
AcCustomerNickName | nvarchar(100)
DateOfBirth | datetime
IsMember | nvarchar(1)
Gender | nvarchar(1)
MemberNo | nvarchar(100)
MobileNo | nvarchar(100)
IdentityNo | nvarchar(100)
MemberJoinDate | datetime
MemberExpireDate | datetime
MemberMarriageDate | datetime
AcMemberEthnicID | nvarchar(20)
AcMemberIncomeID | nvarchar(20)
AcMemberOccupationID | nvarchar(20)
MemberPointBalance | numeric(18, 2)
MemberPointAccumulated | numeric(18, 4)
PriceCode | nvarchar(1)
AcSalesmanID | nvarchar(20)
IsGSTRegister | nvarchar(1)
GSTRegisterNumber | nvarchar(30)
BusinessRegisterNumber | nvarchar(30)
AcCurrencyID | nvarchar(5)
IsAllowPrintStatement | nvarchar(1)
IsAllowExceedCreditTerm | nvarchar(1)
IsAllowExceedCreditLimit| nvarchar(1)
LocalCreditLimit | numeric(18, 2)
LocalBalanceAmount | numeric(18, 2)
AcTaxID | nvarchar(20)
IsAllowCreditSalesForPOS| nvarchar(1)
MemberNotes | nvarchar(MAX)
AcINCardPackageID | nvarchar(20)
AcCustomerIDParentID | nvarchar(20)
TaxSLTRegistrationNo | nvarchar(40)
TaxSVTRegistrationNo | nvarchar(40)
AcTaxIDSST | nvarchar(20)
SSTExemptionCertNumber | nvarchar(30)
config/database.php
<?php
class Database
{
// specify your own database credentials
private $host = "localhost\QNEBSS";
private $db_name = "cafe99";
private $username = "sa";
private $password = "QnE123!@#";
public $conn;
// get the database connection
public function getConnection()
{
$this->conn = null;
try
{
$this->conn = new PDO("sqlsrv:Server=".$this->host.";Database=".$this->db_name, $this->username, $this->password);
$this->conn->exec("set names utf8");
}
catch(PDOException $exception)
{
echo json_encode(array("message" => "Connection error: " . $exception->getMessage()));
}
return $this->conn;
}
}
?>
objects/customer.php
<?php
class Customer
{
// database connection and table name
private $conn;
private $table_name = "AcCustomer";
// object properties
public $AcCustomerID, $AcCustomerName, $AcTermID, $AcAreaID, $AcCusGroupID, $AcCusUDGroupID,
$BillingAddress1, $BillingAddress2, $BillingAddress3, $BillingAddress4, $BillingTel1,
$BillingTel2, $BillingFax1, $BillingFax2, $BillingPostalCode, $BillingContact1,
$BillingContact2, $BillingEmail, $BillingHomePage, $IsActive, $Notes,
$CreateBy, $CreateDate, $EditBy, $EditDate, $Stamp, $AcCustomerNickName,
$DateOfBirth, $IsMember, $Gender, $MemberNo, $MobileNo, $IdentityNo,
$MemberJoinDate, $MemberExpireDate, $MemberMarriageDate, $AcMemberEthnicID,
$AcMemberIncomeID, $AcMemberOccupationID, $MemberPointBalance, $MemberPointAccumulated,
$PriceCode, $AcSalesmanID, $IsGSTRegister, $GSTRegisterNumber, $BusinessRegisterNumber,
$AcCurrencyID, $IsAllowPrintStatement, $IsAllowExceedCreditTerm, $IsAllowExceedCreditLimit,
$LocalCreditLimit, $LocalBalanceAmount, $AcTaxID, $IsAllowCreditSalesForPOS,
$MemberNotes, $AcINCardPackageID, $AcCustomerIDParentID, $TaxSLTRegistrationNo,
$TaxSVTRegistrationNo, $AcTaxIDSST, $SSTExemptionCertNumber;
// constructor with $db as database connection
public function __construct($db)
{
$this->conn = $db;
}
// read customers
function read()
{
// select all query
$query = "SELECT * FROM ".$this->table_name." ORDER BY AcCustomerName ASC";
// prepare query statement
$stmt = $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
// execute query
$stmt->execute();
return $stmt;
}
// create product
function create()
{
// query to insert record
$query = "INSERT INTO
" . $this->table_name . "
(
AcCustomerID, AcCustomerName, AcTermID,
AcAreaID, AcCusGroupID, AcCusUDGroupID,
BillingEmail, IsActive, CreateBy,
CreateDate, Stamp, DateOfBirth,
IsMember, Gender, MobileNo,
MemberJoinDate, MemberExpireDate, MemberMarriageDate,
AcMemberEthnicID, AcMemberIncomeID, AcMemberOccupationID,
MemberPointBalance, MemberPointAccumulated, PriceCode,
AcSalesmanID, IsGSTRegister, AcCurrencyID,
IsAllowPrintStatement, IsAllowExceedCreditTerm, IsAllowExceedCreditLimit,
LocalCreditLimit, LocalBalanceAmount, IsAllowCreditSalesForPOS,
AcCustomerIDParentID
)
VALUES
(
:AcCustomerID, :AcCustomerName, :AcTermID,
:AcAreaID, :AcCusGroupID, :AcCusUDGroupID,
:BillingEmail, :IsActive, :CreateBy,
:CreateDate, :Stamp, :DateOfBirth,
:IsMember, :Gender, :MobileNo,
:MemberJoinDate, :MemberExpireDate, :MemberMarriageDate,
:AcMemberEthnicID, :AcMemberIncomeID, :AcMemberOccupationID,
:MemberPointBalance, :MemberPointAccumulated, :PriceCode,
:AcSalesmanID, :IsGSTRegister, :AcCurrencyID,
:IsAllowPrintStatement, :IsAllowExceedCreditTerm, :IsAllowExceedCreditLimit,
:LocalCreditLimit, :LocalBalanceAmount, :IsAllowCreditSalesForPOS,
:AcCustomerIDParentID
)";
// prepare query
$stmt = $this->conn->prepare($query);
// bind values
$stmt->bindParam(":AcCustomerID", $this->AcCustomerID);
$stmt->bindParam(":AcCustomerName", $this->AcCustomerName);
$stmt->bindParam(":AcTermID", $this->AcTermID);
$stmt->bindParam(":AcAreaID", $this->AcAreaID);
$stmt->bindParam(":AcCusGroupID", $this->AcCusGroupID);
$stmt->bindParam(":AcCusUDGroupID", $this->AcCusUDGroupID);
$stmt->bindParam(":BillingEmail", $this->BillingEmail);
$stmt->bindParam(":IsActive",$this->IsActive);
$stmt->bindParam(":CreateBy",$this->CreateBy);
$stmt->bindParam(":CreateDate",$this->CreateDate);
$stmt->bindParam(":Stamp",$this->Stamp);
$stmt->bindParam(":DateOfBirth",$this->DateOfBirth);
$stmt->bindParam(":IsMember",$this->IsMember);
$stmt->bindParam(":Gender",$this->Gender);
$stmt->bindParam(":MobileNo",$this->MobileNo);
$stmt->bindParam(":MemberJoinDate",$this->MemberJoinDate);
$stmt->bindParam(":MemberExpireDate",$this->MemberExpireDate);
$stmt->bindParam(":MemberMarriageDate",$this->MemberMarriageDate);
$stmt->bindParam(":AcMemberEthnicID",$this->AcMemberEthnicID);
$stmt->bindParam(":AcMemberIncomeID",$this->AcMemberIncomeID);
$stmt->bindParam(":AcMemberOccupationID",$this->AcMemberOccupationID);
$stmt->bindParam(":MemberPointBalance",$this->MemberPointBalance);
$stmt->bindParam(":MemberPointAccumulated",$this->MemberPointAccumulated);
$stmt->bindParam(":PriceCode",$this->PriceCode);
$stmt->bindParam(":AcSalesmanID",$this->AcSalesmanID);
$stmt->bindParam(":IsGSTRegister",$this->IsGSTRegister);
$stmt->bindParam(":AcCurrencyID",$this->AcCurrencyID);
$stmt->bindParam(":IsAllowPrintStatement",$this->IsAllowPrintStatement);
$stmt->bindParam(":IsAllowExceedCreditTerm",$this->IsAllowExceedCreditTerm);
$stmt->bindParam(":IsAllowExceedCreditLimit",$this->IsAllowExceedCreditLimit);
$stmt->bindParam(":LocalCreditLimit",$this->LocalCreditLimit);
$stmt->bindParam(":LocalBalanceAmount",$this->LocalBalanceAmount);
$stmt->bindParam(":IsAllowCreditSalesForPOS",$this->IsAllowCreditSalesForPOS);
$stmt->bindParam(":AcCustomerIDParentID",$this->AcCustomerIDParentID);
// execute query
if($stmt->execute())
{
return true;
}
return false;
}
}
?>
customer/create.php
<?php
// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
// get database connection
include_once '../config/database.php';
// instantiate product object
include_once '../objects/customer.php';
$database = new Database();
$db = $database->getConnection();
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$customer = new Customer($db);
// get posted data
$data = json_decode(file_get_contents("php://input"));
// make sure data is not empty
if(!empty($data->AcCustomerID)
&& !empty($data->AcCustomerName)
&& !empty($data->BillingEmail)
&& !empty($data->DateOfBirth)
&& !empty($data->MobileNo)
)
{
// set product property values
$customer->AcCustomerID = $data->AcCustomerID;
$customer->AcCustomerName = $data->AcCustomerName;
$customer->AcTermID = "CASH";
$customer->AcAreaID = "NA";
$customer->AcCusGroupID = "NA";
$customer->AcCusUDGroupID = "NA";
$customer->BillingEmail = $data->BillingEmail;
$customer->IsActive = "Y";
$customer->CreateBy = "APP";
$customer->CreateDate = date('Y-m-d H:i:s.u');
$customer->Stamp = "0";
$customer->DateOfBirth = date('Y-m-d H:i:s.u', strtotime($data->DateOfBirth));
$customer->IsMember = "Y";
$customer->Gender = "NA";
$customer->MobileNo = $data->MobileNo;
$customer->MemberJoinDate = date('Y-m-d H:i:s.u');
$customer->MemberExpireDate = date('Y-m-d H:i:s.u', strtotime('+1 year'));
$customer->MemberMarriageDate = date('Y-m-d H:i:s.u');
$customer->AcMemberEthnicID = "NA" ;
$customer->AcMemberIncomeID = "NA";
$customer->AcMemberOccupationID = "NA";
$customer->MemberPointBalance = "0.00";
$customer->MemberPointAccumulated = "0.0000";
$customer->PriceCode = "1";
$customer->AcSalesmanID = "NA";
$customer->IsGSTRegister = "N";
$customer->AcCurrencyID = "MYR";
$customer->IsAllowPrintStatement = "N";
$customer->IsAllowExceedCreditTerm = "N";
$customer->IsAllowExceedCreditLimit = "N";
$customer->LocalCreditLimit = "0.00";
$customer->LocalBalanceAmount = "0.00";
$customer->IsAllowCreditSalesForPOS = "N";
$customer->AcCustomerIDParentID = $data->AcCustomerID;
// create the product
/*if($customer->create())
{
// set response code - 201 created
http_response_code(201);
// tell the user
//echo json_encode(array("message" => "New Member was created."));
}
// if unable to create the product, tell the user
else
{
// set response code - 503 service unavailable
http_response_code(503);
// tell the user
echo json_encode(array("message" => "Unable to create new member."));
}*/
try
{
if($customer->create())
{
// set response code - 201 created
http_response_code(201);
// tell the user
echo json_encode(array("message" => "New Member was created."));
}
}
catch (PDOException $e)
{
echo json_encode(array("message" => $e->getMessage()));
}
}
// tell the user data is incomplete
else
{
// set response code - 400 bad request
http_response_code(400);
// tell the user
echo json_encode(array("message" => "Unable to create new member. Data is incomplete."));
}
?>
Output
Solution
There are 2 issues
Your datetime format is incorrect, you have it as
date('Y-m-d H:i:s.u');, when it should bedate('Y-m-d H:i:s');Gender is being set to 'NA' (2 characters) when the table has Gender as nvarchar(1) meaning only one character only. Change the Gender to one character or increase the nvarchar limit.
- How to remove all non printable characters in a string?
- How to display WooCommerce product long description in the head?
- Is there a way to enforce a unique column using doctrine2?
- Authorize.net refund Transaction error: has invalid child element 'payment' in namespace
- cURL with SSL certificates fails: error 58 unable to set private key file
- PHPUnit: get arguments to a mock method call
- How to test Symfony standalone bundle
- Retrieve .htaccess login name from PHP
- Sort a 2d array by a column and obey a dynamic direction variable
- Sort a flat array ascending
- Calling sort() inside of a foreach() does not affect the original array
- Loop only stores the last row while trying to build a 2d array while looping a query result set
- xampp is not showing .php files saved in htdocs folder
- Populate a 2d array from a query result and use a column value as the first level keys
- how to declare punch of array variable in Excel using PHPExcel??/
- How can I detect all symbols on mathematical operators on PHP using regex?
- PHP response triggers HEX view in Edge debugger
- Populate a grouped multidimensional array from parsed HTML content
- Sort rows of a 2d array by its first column descending
- Duplicate and append each row of a 2d array using a flat aray
- PHP reading shell_exec live output
- PHP script not running with www-data cron
- laravel 8 , I have a relationship between 3 tables attribute and value and ad
- Store related items with many-to-many relationship Laravel
- Put function in Queue but not loaded relation data | Laravel Queue
- Magento AvS_fastsimpleimporter multiple addresses
- Parse a text file into an array of associative arrays by splitting on semicolons, then commas, then equals signs
- How to validate an Instagram username
- Parse video id and start time from differently formatted youtube URL strings
- How to create a multidimensional array with an identifying column value used as the first level key?