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
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.

 

Quote

updateConsumablePrice

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

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
2 hours ago, mac_gyver said:

From the documentation -

 

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

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.

Link to comment
Share on other sites
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.

Link to comment
Share on other sites
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
Link to comment
Share on other sites
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.

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
10 minutes ago, 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.

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

 

Link to comment
Share on other sites
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) ""
}

 

Link to comment
Share on other sites
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.

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted (edited)
17 minutes ago, 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.

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
Link to comment
Share on other sites
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(?) ...'

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
4 minutes ago, Barand said:

Can you show us the output from this query? 

show create table consumable_price;

 

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

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
6 minutes ago, Barand said:

P.S.

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

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

 

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();
  }

 

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
42 minutes ago, Barand said:

You have omitted item_id from your query.

I created your table and ran a test...

 

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  
            ]);
        }
    }

 

Link to comment
Share on other sites

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.