SQL WHERE CLAUSE syntax issue

[portabletelly]

So im trying to run a SQL query to insert data into a customer table. But I only want to insert date if the ID does not already exist.

This code here works

//************************************start of gizmola coe *************************
    $query = "INSERT INTO Customer (SyncroID, CompanyName) VALUES (?, ?)";
 
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare($query);
                foreach ($customers as $customer)
                    {
                        if (!empty($customer['business_name'])) {
                        $stmt->execute([$customer['id'], $customer['business_name']]);
                                                                }
                    }
        $pdo->commit();
            }
 
        catch (\Throwable $e){
            $pdo->rollback();
            throw $e;
                             }
//******************************end of gizmola code ***************************   

However when I try to add the where clause in it just  bails out on the below code, im pretty sure my where statement has bad syntax and then im not sure the execute is done right?

  //**************https://forums.phpfreaks.com/topic/317537-curl-rate-limits-and-time-outs-limit-rate-under-a-certain-amount-for-get-requests/ *************************
  //updated syncroid  
  $query = "INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) WHERE (SyncroID) != VALUES (?)";
 
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare($query);
                foreach ($customers as $customer)
                    {
                        if (!empty($customer['business_name'])) {
                            //udpated Customer ID for where clause
                            $stmt->execute([$customer['id'], $customer['business_name'], $customer['address'], $customer['city'], $customer['state'], $customer['zip'], [$customer['id']]);
                                                                }
                    }
        $pdo->commit();
            }
 
        catch (\Throwable $e){
            $pdo->rollback();
            throw $e;
                             }
//******************************end of gizmola code *************************** 

 

[Barand]

INSERT queries do not have a WHERE clause. You need to to use an "INSERT ... ON DUPLICATE KEY UPDATE ... "

EG
 

INSERT INTO table (id, col_a, col_b) VALUES (?, ?, ?)
ON DUPLCATE KEY UPDATE 
    col_a = VALUES(col_a),
    col_b = VALUES(col_b) ;

 

[Strider64]

There's no `WHERE` in `INSERT INTO`

Edited December 16, 2023 by Strider64

[Moorcam]

14 hours ago, portabletelly said:

So im trying to run a SQL query to insert data into a customer table. But I only want to insert date if the ID does not already exist.

This code here works

//************************************start of gizmola coe *************************
    $query = "INSERT INTO Customer (SyncroID, CompanyName) VALUES (?, ?)";
 
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare($query);
                foreach ($customers as $customer)
                    {
                        if (!empty($customer['business_name'])) {
                        $stmt->execute([$customer['id'], $customer['business_name']]);
                                                                }
                    }
        $pdo->commit();
            }
 
        catch (\Throwable $e){
            $pdo->rollback();
            throw $e;
                             }
//******************************end of gizmola code ***************************   

However when I try to add the where clause in it just  bails out on the below code, im pretty sure my where statement has bad syntax and then im not sure the execute is done right?

  //**************https://forums.phpfreaks.com/topic/317537-curl-rate-limits-and-time-outs-limit-rate-under-a-certain-amount-for-get-requests/ *************************
  //updated syncroid  
  $query = "INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) WHERE (SyncroID) != VALUES (?)";
 
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare($query);
                foreach ($customers as $customer)
                    {
                        if (!empty($customer['business_name'])) {
                            //udpated Customer ID for where clause
                            $stmt->execute([$customer['id'], $customer['business_name'], $customer['address'], $customer['city'], $customer['state'], $customer['zip'], [$customer['id']]);
                                                                }
                    }
        $pdo->commit();
            }
 
        catch (\Throwable $e){
            $pdo->rollback();
            throw $e;
                             }
//******************************end of gizmola code *************************** 

 

Only SELECT queries have WHERE clauses mate.

Edited December 17, 2023 by Moorcam

[Barand]

17 hours ago, Barand said:

INSERT queries do not have a WHERE clause

 

17 hours ago, Strider64 said:

There's no `WHERE` in `INSERT INTO`

 

2 hours ago, Moorcam said:

Only SELECT queries have WHERE clauses mate.

There is definitely an echo in here.

@Moorcam WRONG! What about UPDATE and DELETE queries?

[portabletelly]

Hmm, am I doing something wrong here. It appears that the following code just add new duplicate entries into the database. Clearing out the customer table, 9 pages of records are created on first run then an additional 9 pages total 18 pages on second run.

<?php

function Get_Customer()
{
  set_time_limit(300); 
  $customers = [];
  $totalPages = getTotalPages();

  $page = 1;
  while ($page <= $totalPages) {
    $returnedCustomers = Customers($page);
    $customers = array_merge($customers, $returnedCustomers);
    $page++;
  }
  include('db_con.php');

  //**************https://forums.phpfreaks.com/topic/317537-curl-rate-limits-and-time-outs-limit-rate-under-a-certain-amount-for-get-requests/ *************************
  //updated syncroid  
//updated SQL query with duplicate key update
  $query = "INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
      SyncroID = VALUES(SyncroID), 
      CompanyName = VALUES(CompanyName),
      Address = VALUES(Address),
      City = VALUES(City),
      State = VALUES(State),
      Postcode = VALUES(Postcode)";
      
echo $query;
      
      
 
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare($query);
                foreach ($customers as $customer)
                    {
                        if (!empty($customer['business_name'])) {
                            //udpated Customer ID for where clause
                            $stmt->execute([$customer['id'], $customer['business_name'], $customer['address'], $customer['city'], $customer['state'], $customer['zip']]);
                                                                }
                    }
        $pdo->commit();
            }
 
        catch (\Throwable $e){
            $pdo->rollback();
            throw $e;
                             }
//******************************end of gizmola code ***************************                    
     

  return $customers;
}

function Customers($page = 1)
{
  $url =
    "https://xxxxxxxxxxxxxxxxxxxxxmsp.com/api/v1/customers?sort=business_name&page=" .
    $page;

  $cURL = curl_init();

  curl_setopt($cURL, CURLOPT_URL, $url);
  curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($cURL, CURLOPT_HTTPGET, true);
  curl_setopt($cURL, CURLOPT_HTTPHEADER, [
    "Content-Type: application/json",
    "Accept: application/json",
    "Authorization: xxxxxxxxxxxxxxxxxxxxxxxx"
  ]);

  $result = curl_exec($cURL);
  $data = json_decode($result, true);
  $customers = $data["customers"];

  curl_close($cURL);
  return $customers;
}

function getTotalPages()
{
  $url = "https://xxxxxxxxxxxxxxxxmsp.com/api/v1/customers";

  $cURL = curl_init();

  curl_setopt($cURL, CURLOPT_URL, $url);
  curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($cURL, CURLOPT_HTTPGET, true);
  curl_setopt($cURL, CURLOPT_HTTPHEADER, [
    "Content-Type: application/json",
    "Accept: application/json",
    "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
  ]);

  $result = curl_exec($cURL);
  $data = json_decode($result, true);
  $totalPages = $data["meta"]["total_pages"];
  curl_close($cURL);
  return $totalPages;
}

// Get_Customer();
// test
/*
 echo "<br>";
 echo $custname;
 echo " ";
 echo $customer["address"];
 echo " ";
 echo $customer["city"];
 echo ",";
 echo $customer["state"];
 echo " ";
 echo  $customer["zip"];
 echo "<b>Customer ID:</b> ";
 echo " ";
 echo $customer["id"];
 echo "<br>";
 echo $query;
 echo "<br>";
 */
//INSERT INTO `Customer` (`SyncroID`, `PrimaryContactID`, `CompanyName`, `Address`, `City`, `State`, `Postcode`, `Country`, `AccountStatus`, `UserName`, `Password_Hash`, `Password_Salt`, `Notes`, `BillingContactID`)
//VALUES ('12321', NULL, 'test', NULL, NULL, 'QLD', NULL, 'Australia', 4, NULL, NULL, NULL, NULL, NULL);

The $query variable returns 

INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE SyncroID = VALUES(SyncroID), CompanyName = VALUES(CompanyName), Address = VALUES(Address), City = VALUES(City), State = VALUES(State), Postcode = VALUES(Postcode)

my primary key on the customer table is Customer_ID but I don't want duplicate entries based on Syncro_ID either.

[portabletelly]

Please ignore above post about additional pages I jumped the gun  I had to change the collum syncroID to be  unique in mysql in order for this to work. Seems to work well.