Jump to content

Replace Into blanking some fields

Adamhumbug

By Adamhumbug
in PHP Coding Help

 Share
Go to solution Solved by mac_gyver,

Recommended Posts

Adamhumbug Prolific Member

Adamhumbug

Posted
Posted

I have an insert function that is updating the records that i change fine - id is the key. 

if (isset($_POST['updateConsumablePrice'])) {
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
            $cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            $sql = "REPLACE INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                ':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

The issue that i have is that one of my columns which should not be being touched by this insert is being blanked to zero.

Is there something obvious that i am doing wrong or a better way of doing this?

Link to comment
https://forums.phpfreaks.com/topic/317282-replace-into-blanking-some-fields/
Share on other sites
  • Solution
mac_gyver Prolific Member

mac_gyver

Posted
  • Solution
Posted

From the documentation -

  Quote

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT.

Expand  

 

  Quote

updateConsumablePrice

Expand  

it seems like you should be using an UPDATE query for this operation?

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/13/2023 at 12:41 PM, mac_gyver said:

From the documentation -

 

it seems like you should be using an UPDATE query for this operation?

Expand  

Thank you for this.

I am updating but there is also the option on the page to add new data.

I was torn between ON DUPLICATE KEY or REPLACE INTO.

I dont know if one of these is better than the other.

Barand Prolific Member

Barand

Posted
Posted

My understanding is that REPLACE will always create a new record (and therefore setting default values). INSERT .. ON DUPLICATE KEY will insert only when the record does not exist and update specified fields when it does. I'd go for the latter in this case.

Experimentation is good. Try out a test.

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted (edited)

I am not sure why my code here is not working - but its not.

 

This is what happens when i echo $_POST 

array(11) {
  ["itemName"]=>
  string(23) "Large Format RFID Cards"
  ["itemCategory"]=>
  string(1) "4"
  ["chargeBy"]=>
  string(1) "1"
  ["minQty"]=>
  array(7) {
    [0]=>
    string(1) "5"
    [1]=>
    string(3) "500"
    [2]=>
    string(4) "1000"
    [3]=>
    string(4) "2000"
    [4]=>
    string(4) "3000"
    [5]=>
    string(4) "6000"
    [6]=>
    string(5) "10000"
  }
  ["maxQty"]=>
  array(7) {
    [0]=>
    string(3) "499"
    [1]=>
    string(3) "999"
    [2]=>
    string(4) "1999"
    [3]=>
    string(4) "2999"
    [4]=>
    string(4) "5999"
    [5]=>
    string(4) "9999"
    [6]=>
    string(8) "10000000"
  }
  ["GBP"]=>
  array(7) {
    [0]=>
    string(3) "500"
    [1]=>
    string(1) "1"
    [2]=>
    string(3) "0.9"
    [3]=>
    string(3) "0.8"
    [4]=>
    string(3) "0.7"
    [5]=>
    string(3) "0.6"
    [6]=>
    string(3) "0.5"
  }
  ["USD"]=>
  array(7) {
    [0]=>
    string(3) "750"
    [1]=>
    string(3) "1.5"
    [2]=>
    string(4) "1.35"
    [3]=>
    string(3) "1.2"
    [4]=>
    string(4) "1.05"
    [5]=>
    string(3) "0.9"
    [6]=>
    string(4) "0.75"
  }
  ["CAD"]=>
  array(7) {
    [0]=>
    string(3) "875"
    [1]=>
    string(4) "1.75"
    [2]=>
    string(4) "1.58"
    [3]=>
    string(3) "1.4"
    [4]=>
    string(4) "1.23"
    [5]=>
    string(4) "1.05"
    [6]=>
    string(3) "0.9"
  }
  ["EUR"]=>
  array(7) {
    [0]=>
    string(1) "0"
    [1]=>
    string(1) "0"
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    string(1) "0"
  }
  ["cpId"]=>
  array(7) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "3"
    [3]=>
    string(1) "4"
    [4]=>
    string(1) "5"
    [5]=>
    string(1) "6"
    [6]=>
    string(1) "7"
  }
  ["updateConsumablePrice"]=>
  string(0) ""
}

And this is the php 

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
                    ON DUPLICATE KEY 
                    UPDATE min_qty = :minQty, max_qty = :maxQty, GBP = :GBP, USD = :USD, CAD = :CAD, EUR = :EUR";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }
}

The post is happening but no new data is going in and changes are not being made.

There are no errors shown.

Edited by Adamhumbug
requinix Prolific Member

requinix

Posted
Posted

As the term would hopefully suggest, ON DUPLICATE KEY requires that you have a unique (or primary) key in your field list. That's how it knows there's a duplicate: you tried to insert something that exists. If you don't include any keys then you're simply going to insert new data.

I'm inclined to think that your min_qty, max_qty, and currency fields are not part of a unique key.

Put the ID back.

None of this helps me to understand why your form, which is clearly designed around editing an existing ID, will ever have to deal with a situation where the data to be edited does not exist yet.

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 9:38 AM, requinix said:

As the term would hopefully suggest, ON DUPLICATE KEY requires that you have a unique (or primary) key in your field list. That's how it knows there's a duplicate: you tried to insert something that exists. If you don't include any keys then you're simply going to insert new data.

I'm inclined to think that your min_qty, max_qty, and currency fields are not part of a unique key.

Put the ID back.

None of this helps me to understand why your form, which is clearly designed around editing an existing ID, will ever have to deal with a situation where the data to be edited does not exist yet.

Expand  

So this 

if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
                    VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
                    ON DUPLICATE KEY 
                    UPDATE min_qty = :minQty, max_qty = :maxQty, GBP = :GBP, USD = :USD, CAD = :CAD, EUR = :EUR";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
			$cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
				':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

is now working for the updates.

But it is not adding a new item.

Again, the screen reloads but nothing happens in the database.

 

For reference of what i am doing i have attached a screen shot.

You can add a new line to add another breakpoint

 

Screenshot2023-09-14at13_50_17.thumb.png.ee03f94286c3bb3d42d79c127595fcd6.png

 

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted

This is the $_POST when i add a new line 

array(11) {
  ["itemName"]=>
  string(23) "Item 1"
  ["itemCategory"]=>
  string(1) "4"
  ["chargeBy"]=>
  string(1) "1"
  ["minQty"]=>
  array(8) {
    [0]=>
    string(1) "1"
    [1]=>
    string(3) "500"
    [2]=>
    string(4) "1000"
    [3]=>
    string(4) "2000"
    [4]=>
    string(4) "3000"
    [5]=>
    string(4) "6000"
    [6]=>
    string(5) "10000"
    [7]=>
    string(2) "11"
  }
  ["maxQty"]=>
  array(8) {
    [0]=>
    string(3) "499"
    [1]=>
    string(3) "999"
    [2]=>
    string(4) "1999"
    [3]=>
    string(4) "2999"
    [4]=>
    string(4) "5999"
    [5]=>
    string(4) "9999"
    [6]=>
    string(8) "10000000"
    [7]=>
    string(2) "11"
  }
  ["GBP"]=>
  array(8) {
    [0]=>
    string(3) "500"
    [1]=>
    string(1) "1"
    [2]=>
    string(3) "0.9"
    [3]=>
    string(3) "0.8"
    [4]=>
    string(3) "0.7"
    [5]=>
    string(3) "0.6"
    [6]=>
    string(3) "0.5"
    [7]=>
    string(2) "11"
  }
  ["USD"]=>
  array(8) {
    [0]=>
    string(3) "750"
    [1]=>
    string(3) "1.5"
    [2]=>
    string(4) "1.35"
    [3]=>
    string(3) "1.2"
    [4]=>
    string(4) "1.05"
    [5]=>
    string(3) "0.9"
    [6]=>
    string(4) "0.75"
    [7]=>
    string(2) "11"
  }
  ["CAD"]=>
  array(8) {
    [0]=>
    string(3) "875"
    [1]=>
    string(4) "1.75"
    [2]=>
    string(4) "1.58"
    [3]=>
    string(3) "1.4"
    [4]=>
    string(4) "1.23"
    [5]=>
    string(4) "1.05"
    [6]=>
    string(3) "0.9"
    [7]=>
    string(2) "11"
  }
  ["EUR"]=>
  array(8) {
    [0]=>
    string(1) "0"
    [1]=>
    string(1) "0"
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    string(1) "0"
    [7]=>
    string(2) "11"
  }
  ["cpId"]=>
  array(8) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "3"
    [3]=>
    string(1) "4"
    [4]=>
    string(1) "5"
    [5]=>
    string(1) "6"
    [6]=>
    string(1) "7"
    [7]=>
    string(0) ""
  }
  ["updateConsumablePrice"]=>
  string(0) ""
}

 

Barand Prolific Member

Barand

Posted
Posted

Try setting the cpId value to either 0 or NULL for new items.

It would appear that you are not setting the EMULATE_PREPARES option to "false" in your connection code on leaving it as "true" (default). Set it to false so prepared queries are truly prepared.

Note that with true prepares you will not be able to use execute array elements multiple times (eg ":minQty" appears twice in the query but only once in the array). Rewrite the query as 

INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
ON DUPLICATE KEY 
UPDATE min_qty = VALUES(minQty)
     , max_qty = VALUES(maxQty)
     , GBP = VALUES(GBP)
     , USD = VALUES(USD)
     , CAD = VALUES(CAD)
     , EUR = VALUES(EUR);

to re-use the values.

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted (edited)
  On 9/14/2023 at 10:28 AM, Barand said:

Try setting the cpId value to either 0 or NULL for new items.

It would appear that you are not setting the EMULATE_PREPARES option to "false" in your connection code on leaving it as "true" (default). Set it to false so prepared queries are truly prepared.

Note that with true prepares you will not be able to use execute array elements multiple times (eg ":minQty" appears twice in the query but only once in the array). Rewrite the query as 

INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
ON DUPLICATE KEY 
UPDATE min_qty = VALUES(minQty)
     , max_qty = VALUES(maxQty)
     , GBP = VALUES(GBP)
     , USD = VALUES(USD)
     , CAD = VALUES(CAD)
     , EUR = VALUES(EUR);

to re-use the values.

Expand  

i have set the emulate in the connection file to 

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

and have used the query 

if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (id, min_qty, max_qty, GBP, USD, CAD, EUR) 
		VALUES (:cpId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
		ON DUPLICATE KEY 
		UPDATE min_qty = VALUES(minQty)
			 , max_qty = VALUES(maxQty)
			 , GBP = VALUES(GBP)
			 , USD = VALUES(USD)
			 , CAD = VALUES(CAD)
			 , EUR = VALUES(EUR)";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
			$cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];

            
            $stmt->execute([
				':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

I am now getting the error

Unknown column 'minQty' in 'field list' in...

 

Screenshot2023-09-14at14_45_48.thumb.png.141b98c5e736c8b5295f7f2b94fcef40.png

Edited by Adamhumbug
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted

I thought they might need the ":" infront of them but that gives me

 

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) , max_qty = VALUES(?) , GBP = VALUES(?) , USD = VALUES(?) ...'

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 10:49 AM, Barand said:

Sorry.

minQty and maxQty should be min_qty and max_qty (to match your column names)

Expand  

So this worked for the update but still nothing happening in regard to the inserting of a new row.

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 11:11 AM, Barand said:

Can you show us the output from this query? 

show create table consumable_price;

 

Expand  

consumable_priceCREATE TABLE `consumable_price` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `min_qty` int(11) NOT NULL,
 `max_qty` int(11) NOT NULL,
 `GBP` float(5,2) NOT NULL,
 `USD` float(5,2) NOT NULL,
 `CAD` float(5,2) NOT NULL,
 `EUR` float(5,2) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Barand Prolific Member

Barand

Posted
Posted

You have omitted item_id from your query.

I created your table and ran a test...

  Quote

 Uncaught PDOException: SQLSTATE[HY000]: General error: 1364 Field 'item_id' doesn't have a default value

Expand  

 

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 12:06 PM, Barand said:

P.S.

Looks like you need to set another PDO option on connecting... 

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 

Expand  

I have that in already - hopefully correctly. 

try {
    $pdo = new PDO("mysql:host={$host_name}; dbname={$database}", $user_name, $password);
    $pdo-> exec("SET time_zone='+01:00';");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    // echo "Connected successfully";
  } catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
  }

 

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 11:35 AM, Barand said:

You have omitted item_id from your query.

I created your table and ran a test...

 

Expand  

of course you are right.  Thanks as always. 

if (isset($_POST['updateConsumablePrice'])) {
		$sql = "INSERT INTO consumable_price (id, item_id, min_qty, max_qty, GBP, USD, CAD, EUR) 
		VALUES (:cpId, :itemId, :minQty, :maxQty, :GBP, :USD, :CAD, :EUR)
		ON DUPLICATE KEY 
		UPDATE min_qty = VALUES(min_qty)
			 , max_qty = VALUES(max_qty)
			 , GBP = VALUES(GBP)
			 , USD = VALUES(USD)
			 , CAD = VALUES(CAD)
			 , EUR = VALUES(EUR)";
            $stmt = $pdo->prepare($sql);
        for ($i = 0; $i < count($_POST['minQty']); $i++) {
			
			$cpId = $_POST['cpId'][$i];
            $minQty = $_POST['minQty'][$i];
            $maxQty = $_POST['maxQty'][$i];
            $GBP = $_POST['GBP'][$i];
            $USD = $_POST['USD'][$i];
            $CAD = $_POST['CAD'][$i];
            $EUR = $_POST['EUR'][$i];
			$itemId = $_GET['itemId'];
            
            $stmt->execute([
				':itemId' => $itemId,
				':cpId' => $cpId,
                ':minQty' => $minQty,
                ':maxQty' => $maxQty,
                ':GBP' => $GBP,
                ':USD' => $USD,
                ':CAD' => $CAD,
                ':EUR' => $EUR  
            ]);
        }
    }

 

Barand Prolific Member

Barand

Posted
Posted

Looks OK, but you are missing the charset setting. eg 

$pdo = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
                                                   ^^^^^^^^^^^^

but you should've got the same error I did when I ran your code

Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
  On 9/14/2023 at 12:19 PM, Barand said:

Looks OK, but you are missing the charset setting. eg 

$pdo = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
                                                   ^^^^^^^^^^^^

but you should''ve got the same error I did when I ran your code

Expand  

Thank you, i will add that.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.