Adamhumbug

Yes, that matches the where clause of course.

I have quite a long query that is supposed to select the highest id to get the row. SELECT MAX(dr.id), device_id, status_id, action_time, d.name as deviceName, d.type_id, d.notes, l.name, l.scanning_for, ds.name as deviceStatus from deployment_register dr inner join location l on dr.location_id = l.id inner join device d on dr.device_id = d.id inner join device_status ds on dr.status_id = ds.id where dr.status_id = 2 or dr.status_id = 5 and d.site_id = 20 group by dr.device_id The table structure is CREATE TABLE `deployment_register` ( `id` int(11) NOT NULL, `device_id` int(11) NOT NULL, `status_id` int(11) NOT NULL, `location_id` int(11) NOT NULL, `action_time` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; -- -- Dumping data for table `deployment_register` -- INSERT INTO `deployment_register` (`id`, `device_id`, `status_id`, `location_id`, `action_time`) VALUES (1, 3, 2, 1, '2023-10-06 21:06:45'), (7, 3, 5, 1, '2023-10-07 21:06:45'); ALTER TABLE `deployment_register` ADD PRIMARY KEY (`id`); The site table CREATE TABLE `event_site` ( `id` int(11) NOT NULL, `name` varchar(200) NOT NULL, `notes` varchar(2000) NOT NULL, `job_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; INSERT INTO `event_site` (`id`, `name`, `notes`, `job_id`) VALUES (1, 'qwef', 'efef', 19), (2, 'This', 'Note', 20); The query is selecting the first row when it should be selecting the second. Any obvious issues here?

I knew i was close: UPDATE quote_items qi inner join items i on qi.item_id = i.id set qi.discounted_price = qi.amount_charged_each * .5, qi.discount_percentage = .5 where qi.quote_id = 89 and i.discountable = 1

Hi All, I have a system where you create quotes. i have a table full of items and a table of quote items. I want to update all quote items (discount_percentage and discounted_price) with a value where the item in the items table has discountable set to 1. quote_items CREATE TABLE `quote_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `quote_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `quantity` int(11) NOT NULL, `start_date` varchar(30) DEFAULT NULL, `end_date` varchar(30) DEFAULT NULL, `notes` varchar(250) NOT NULL, `amount_charged_each` float(10,2) NOT NULL, `original_price_each` float(10,2) NOT NULL, `chargable_units` float(10,2) NOT NULL, `discounted_price` float(10,2) DEFAULT NULL, `discount_percentage` decimal(5,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci items CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `section_id` int(11) NOT NULL, `name` varchar(200) NOT NULL, `GBP` float NOT NULL DEFAULT 0, `USD` float NOT NULL DEFAULT 0, `CAD` float NOT NULL DEFAULT 0, `EUR` float NOT NULL DEFAULT 0, `charge_by_id` tinyint(20) NOT NULL, `discountable` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_c I am not really sure where to start with this one. I tried this but nothing is being updated - no errors reported though, which makes me think i am close. UPDATE quote_items qi inner join items i on qi.item_id = i.id set qi.discounted_price = qi.amount_charged_each * .5, qi.discount_percentage = .5 where qi.id = 89 and i.discountable = 1

Thank you, i will add that.

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

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

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

i tried setting it to null with no avail $cpId = ($_POST['cpId'][$i] == '') ? NULL : $_POST['cpId'][$i];

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

ahh ok - thanks

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(?) ...'

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

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) "" }

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

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.

on duplicate key seems to be better for what i am trying to do so will go with that.

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.

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?

Thanks for that - perfect

I have an array. Array ( [0] => Array ( [id] => 19 [section_id] => 4 [sectionName] => x [itemName] => Item 1 [GBP] => 0 [USD] => 0 [CAD] => 0 [EUR] => 0 [charge_by_id] => 1 [consumable] => 1 [cMIN] => 1 [cMAX] => 499 [cGBP] => 500 [cUSD] => 750 [cCAD] => 875 ) [1] => Array ( [id] => 19 [section_id] => 4 [sectionName] => x [itemName] => Item 1 [GBP] => 0 [USD] => 0 [CAD] => 0 [EUR] => 0 [charge_by_id] => 1 [consumable] => 2 [cMIN] => 500 [cMAX] => 999 [cGBP] => 1 [cUSD] => 1.5 [cCAD] => 1.75 ) [2] => Array ( [id] => 19 [section_id] => 4 [sectionName] => x [itemName] => Item 1 [GBP] => 0 [USD] => 0 [CAD] => 0 [EUR] => 0 [charge_by_id] => 1 [consumable] => 3 [cMIN] => 1000 [cMAX] => 1999 [cGBP] => 0.9 [cUSD] => 1.35 [cCAD] => 1.58 ) [3] => Array i will foreach through most of it to get the data such as cMIN and cMAX on their own lines. I need to get the item name out of this once, rather than once per line - the query means that the data will only be for one item so there is no chance that the item name will be different for any items in the array. I have been trying to access the array data with value='$rows[0][itemName]'> but i am getting array to string conversion issue. How do i get this out of the array without for eaching it?

I cant edit for some reason. The first code block should be $('#currency, #itemId, #startDate, #endDate, #quantity').change(updateFormNewQuoteItemChange)

Hi, I have a quote system where you can select and item and give a quantity. This will then go to the database and work out how much the item is per unit. I have the database structure and the code to get the info back. My issue is i need the javascript function that is handling the page being updated to wait for the response from Ajax before updating the page. It all starts here: $(document).ready(function() { updateFormNewQuoteItemChange() ..... } This is called function updateFormNewQuoteItemChange() { $isConsumable = $('#itemId').find(':selected ').data('isconsumable') $quantity = $('#quantity').val() $chargeBy = $('#itemId').find(':selected').data('chargeby') if($isConsumable == 1 && $quantity.length !== 0){ $iid = $('#itemId').find(':selected').val() getConsumablePrices($iid, $quantity); } if ($('#currency').length) { $ccode = $('#currency').find(':selected').data('currency-code').toLowerCase(); $price = $('#itemId').find(':selected').data($ccode) $('#pricePerItem').val($price) $('#pricePerItemDisplay').val($price) } .... } If the item is consumable and has break points this is called function getConsumablePrices($itemId, $qty) { $.ajax({ type: 'post', data: { "ajax": 'getConsumablePriceByQty', 'itemId': $itemId, 'quantity': $qty }, dataType: 'json', success: function(resp) { console.log(resp[0]['CAD']) } }) } This function returns pricing information which comes from a different table than other pricing. I need to feed the result of this AJAX back into the function that it was called from and i think it need it to run synchronously. Any advice, help on this would be really appreciated. Any further information needed, please do ask.

The data that i pasted came from the inspector window as the response.

console.log(resp[0]['CAD']) - gives me the data i was expecting console.log(resp.CAD) - gives me undefined