Jump to content

Adamhumbug

Members
 View Profile  See their activity

  • Posts

    583

  • Joined

  • Last visited

 Content Type 

Posts posted by Adamhumbug

    Union Select

    in MySQL Help

    Posted

    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

    Screenshot2023-10-20at21_38_40.png.1b636c5b372c9cf024b9d5e911e85d38.png

    Get selected option with jquery (not the option with the selected sudo)

    in Javascript Help

    Posted

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

                                    }
                                },

     

    Union Select

    in MySQL Help

    Posted

    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

    Get selected option with jquery (not the option with the selected sudo)

    in Javascript Help

    Posted

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

     

    Select statement selecting wrong data

    in MySQL Help

    Posted · Edited by Adamhumbug

    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.

    Select statement selecting wrong data

    in MySQL Help

    Posted

    1 minute ago, Barand said:

    You need to join to the other tables to add that.

    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

     

    Select statement selecting wrong data

    in MySQL Help

    Posted · Edited by Adamhumbug

    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?

    UPDATE - JOIN on WHERE clause

    in MySQL Help

    Posted · Edited by Adamhumbug

    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

     

    Replace Into blanking some fields

    in PHP Coding Help

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

     

    Replace Into blanking some fields

    in PHP Coding Help

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

     

    Replace Into blanking some fields

    in PHP Coding Help

    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

    Replace Into blanking some fields

    in PHP Coding Help

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

    Replace Into blanking some fields

    in PHP Coding Help

    Posted · Edited by Adamhumbug

    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

    Replace Into blanking some fields

    in PHP Coding Help

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

     

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