Adamhumbug

i do care about what is in the consumable table, it has items that should be in the list - it just doesnt have the same format as the normal items table. The consumables table has several rows per item as it has price breaks. Sorry if that wasnt too clear.

So all i am trying to do here is populate a dropdown with all of the items that appear in the items table. I also include items that are in the consumable_price table but as there are several rows per item, i have used the WITH. I am now letting the user add custom items, they are stored in a different table as they have different properties such as only being available in that "job". The code that i posted above shows how i select them in the same layout as the initial select that works. So the layout of both of the queries is the same and they return the same columns. CREATE TABLE `custom_item` ( `id` int(11) NOT NULL, `job_id` int(11) NOT NULL, `name` varchar(200) NOT NULL, `section_id` int(11) NOT NULL, `price` float NOT NULL, `charge_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; INSERT INTO `custom_item` (`id`, `job_id`, `name`, `section_id`, `price`, `charge_by`) VALUES (1, 106, 'name', 3, 100, 0), (2, 22, 'NAME', 2, 9999, 2); ALTER TABLE `custom_item` ADD PRIMARY KEY (`id`); ALTER TABLE `custom_item` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; COMMIT; In a nut shell i just need to also show the items from the custom_items table in the select box that i populate with the first piece of code that has the WITH. This select doesnt care about the different price breaks, it just wants to grab the items and group them by the display_order. The select looks like this currently.

If i do a normal join on this table i end up with multiple rows for the items that i select from the consumable price table. I use this table for price breakpoints so each item is in there several times. It looks like this

It is actually part of a custom left click with a custom confirm: "Change Location": { name: "Change Location Of " + $name, icon: "fa-solid fa-percent", accesskey: "r", callback: function(itemKey, opt, e) { $.confirm({ title: 'Change Location Of ' + $name, content: "<form action='' class='formName'>" + "<label class='mb-2'>Currently: " + $locationId + "</label>" + "<?php echo getAllLocationsBySite($_GET['event'], 'Select'); ?>" + "</form>", type: 'blue', typeAnimated: true, escapeKey: true, backgroundDismiss: true, buttons: { formSubmit: { text: 'Change Location', btnClass: 'btn-blue', action: function() { $loc = $('#locationId option:selected').val() console.log($loc, $deviceId) // updateDeviceLocation($deviceId, $loc) } }, close: { text: 'Close', action: function() {} } }, onContentReady: function() { // bind to events var jc = this; this.$content.find('form').on('submit', function(e) { // if the user submits the form by pressing enter in the field. e.preventDefault(); jc.$$formSubmit.trigger('click'); // reference the button and click it }); } }); } },

The modal is built and hidden on page load.

Hi All, I have a select statement which works fine. WITH consumable as ( Select * from consumable_price group by item_id ) SELECT qs.name as sectionName, qi.id as itemId, qi.name as itemName, qi.section_id, qi.GBP, qi.USD, qi.CAD, cb.charge_by, qs.display_order as displayorder, cp.id as isConsumable from items qi inner join quote_sections qs on qi.section_id = qs.id inner join charge_by cb on qi.charge_by_id = cb.id left join consumable cp on qi.id = cp.item_id order by qs.display_order, qi.name I have added a new table where custom items are held and have a query that selects them just fine SELECT qs.name , ci.id , ci.name , ci.section_id , ci.price as GBP , ci.price as USD , ci.price as CAD , ci.charge_by , qs.display_order , null as isConsumable from custom_item ci inner join quote_sections qs on ci.section_id = qs.id I am trying to union select the second set of data with the first set but having a real hard time as i keep getting the error on the WITH at the start. Can anyone point me in the right direction - i can provide table structures if required. Thanks in advance

i get null. I am getting this from a modal - the modal is populated dynamically when the modal is launched if that makes a difference.

I have a select box with options. The first options looks like the following: <option selected disabled value='0'>Something</option> I am creting the rest of the options dynamically using php. When the user changes the dropdown box from the preselected value, it cannot be changed back due to the disabled tag. When i try and get the value that has been selected, it is returning the value of 0 - which is the option shown above rather than the one that has actually been selected. Is there a way around this? I have been using $('#locationId').find('option:selected').val()

❤️

HI All, Sorry, i have been workign with this query and its not actually doing what i need i dont think. After correcting my wrong site faux pas - when using it, it seems to be showing me rows that it shouldnt. SELECT 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 JOIN ( SELECT device_id , MAX(action_time) as action_time FROM deployment_register WHERE status_id IN (2, 5) GROUP BY device_id ) latest USING (device_id, action_time) where d.site_id = :site"; the register table 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; 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'), (8, 1, 5, 1, '2023-10-07 21:06:45'), (9, 2, 2, 1, '2023-10-09 20:08:18'), (10, 6, 2, 2, '2023-10-09 20:08:26'), (11, 5, 2, 2, '2023-10-09 20:08:31'), (12, 5, 2, 2, '2023-10-09 20:08:44'), (17, 3, 3, 1, '2023-10-10 19:39:58'), (18, 3, 3, 1, '2023-10-10 19:51:59'); ALTER TABLE `deployment_register` ADD PRIMARY KEY (`id`); ALTER TABLE `deployment_register` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20; COMMIT; as you can see id 18 has a status id of 3. I only want to see the row that has the highest action_date where the status is 2,5. It is not showing row 18 in the results of the query but insead row 7. I have clearly got the query wrong and would really appreciate a pointer with it. I hope i have explained this well enough, if not please shout and i will try and do a better job of explaining what i am trying to achieve. Thanks as always.

WRONG SITE ID. My error - thank you as always.

I tried this - but it yields no results at all - doesnt error erither SELECT 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 JOIN ( SELECT device_id , MAX(action_time) as action_time FROM deployment_register WHERE status_id IN (2, 5) GROUP BY device_id ) latest USING (device_id, action_time) where d.site_id = :site

Thanks very much for this. Where in here would the second part of the where clause go? (The site_id = 20)

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