Jump to content

mongoose00318

Members
 View Profile  See their activity

  • Posts

    253

  • Joined

  • Last visited

 Content Type 

Posts posted by mongoose00318

    Convert Multiple Queries Into One Query Using Conditions

    in MySQL Help

    Posted

    On 12/2/2020 at 2:57 PM, Barand said:

    As an alterntive for 

    
    $price = $res->fetchColumn();
    return $price ? $price : 'Not set';

    I could have used 

    

    return $res->fetch()['price'] ?? 'Not set';

    which might be better (if you have freebies and 0.00 is a valid price).

    Awesome man. Sorry for the delayed response and as always thanks for the thorough explanation. 

    Convert Multiple Queries Into One Query Using Conditions

    in MySQL Help

    Posted · Edited by mongoose00318

    It could be a kit or a product. A kit is a collection of products which has it's own pricing. If it is a product* I will call this function...sorry about that. 

    function determine_multi_price_product( $order_id, $product_id, $pdo ) {

	//get the enterprise associated with the order
	$query = 'SELECT enterprise FROM production_data WHERE id = :order_id';
	$statement = $pdo->prepare($query);
	$statement->execute(['order_id' => $order_id]);
	
	if ( $statement->rowCount() > 0 ) {

		$enterprise = $statement->fetch()['enterprise'];

		//query database for a branded price for a specifc enterprise
		$query = '
					SELECT
						price
					FROM
						leds_prices
					WHERE
						enterprise = :enterprise AND
						product_id = :product_id
		';
		$statement = $pdo->prepare($query);
		$statement->execute([
			'enterprise' => $enterprise,
			'product_id' => $product_id,
		]);

		//check if the query returned a result and if not default to an unbranded price
		if ( $statement->rowCount() > 0 ) {
		
			$price = $statement->fetch()['price'];

		}

		//default to an unbranded price
		else {

			//query database for a branded price for a specifc enterprise
			$query = '
						SELECT
							price
						FROM
							leds_prices
						WHERE
							enterprise = "Unbranded" AND
							product_id = :product_id
			';
			$statement = $pdo->prepare($query);
			$statement->execute([
				'product_id' => $product_id
			]);

			if ( $statement->rowCount() > 0 ) {

				$price = $statement->fetch()['price'];

			}

			//price isn't set
			else {

				$price = 'Not Set';

			}

		}

		return $price;

	}

}

     

    Convert Multiple Queries Into One Query Using Conditions

    in MySQL Help

    Posted 

    --
-- Table structure for table `leds_prices`
--

DROP TABLE IF EXISTS `leds_prices`;
CREATE TABLE IF NOT EXISTS `leds_prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `kit_id` int(11) DEFAULT NULL,
  `enterprise` text NOT NULL,
  `price` decimal(7,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=95 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `leds_prices`
--

INSERT INTO `leds_prices` (`id`, `product_id`, `kit_id`, `enterprise`, `price`) VALUES
(94, 119, NULL, 'SHRVIE', '20000.00'),
(86, NULL, 48, 'SHRVIE', '78.00'),
(85, NULL, 48, 'SHELL', '23.00'),
(31, 43, NULL, 'Unbranded', '432.00'),
(32, 43, NULL, 'EXXON', '432.00'),
(33, 43, NULL, 'GLOBAL', '654.00'),
(34, 45, NULL, 'Unbranded', '567.00'),
(35, 45, NULL, 'GLOBAL', '567.00'),
(84, NULL, 48, 'EXXON', '34.00'),
(83, NULL, 48, 'Unbranded', '12.00'),
(93, 119, NULL, 'SHELL', '2000.00'),
(92, 119, NULL, 'EXXON', '200.00'),
(91, 119, NULL, 'Unbranded', '10.00');
COMMIT;

--
-- Table structure for table `production_data`
--

DROP TABLE IF EXISTS `production_data`;
CREATE TABLE IF NOT EXISTS `production_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_number` int(8) NOT NULL,
  `enterprise` tinytext NOT NULL,
  `part_number` text NOT NULL,
  `description` text NOT NULL,
  `psm` tinytext NOT NULL,
  `qty` int(11) NOT NULL,
  `line_item` varchar(11) NOT NULL,
  `as400_ship_date` date DEFAULT NULL,
  `hold_reason` text NOT NULL DEFAULT '0',
  `hold_date` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
  PRIMARY KEY (`id`),
  KEY `job_line` (`job_number`,`line_item`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
COMMIT;

INSERT INTO `production_data` (`id`, `job_number`, `enterprise`, `part_number`, `description`, `psm`, `qty`, `line_item`, `as400_ship_date`, `hold_reason`, `hold_date`, `insert_time`) VALUES
(5017, 22971651, 'EXXON', 'XM2054FM.2LDCHPR', 'EXXON 4\'8x4\'2 2P DF FM SGN (C45 CHEV)||(12\" R/G ABLE 7-SEG LEDS)||PRINCIPAL PINN ILLUM||DWG XM2054FM_2LDRGABCHPR / EX204780||*REGULAR/DIESEL EFFICIENT||BLUE/GREEN BACKGROUND||DWG 204780||', 'M', 1, 'D', '2020-09-08', 'WO', '0000-00-00', '2020-09-04 13:35:22'),
(7094, 22006972, 'SHRVIE', 'SG2066TP.2LDVSHSB', 'SHELL GLOBAL 6x6 2-PROD VC TP SGN(SHELL)||24\"/12\" ABLE LEDS 1-RED/1-GRN||RTE G66SH2LV||SIGNBOX 2 ILLUMINATION||2X REGULAR, 1X DIESEL, 1X V-POWER LOGO||ENG:TP075073_6948_LLV_SBLED||GRA:114270, 144876, 144496||', 'M', 1, 'B', '2020-09-18', '', '0000-00-00', '2020-09-04 13:35:23'),
(7179, 23620991, 'NAPAHQ', 'NA0058SF.0V', 'NAPA 5x8 ID VC SF SGN||PRINCIPAL ILLUMINATION||ENG 181597 NA0058SF GRPH 204767||3X8 NAPA AUTOCARE CENTER||2X8 RELIABLE WRENCHERS||', 'M', 1, 'A', '2020-09-14', 'WO', '0000-00-00', '2020-09-04 13:35:23'),
(7185, 22050692, 'SHELL', 'LD0080GR.1AB', 'LED 8\" GREEN 1-PROD MOD ABLE (2 MODULES)||SET OF 2 MODULES W/PWR PACKS||2 GRN W/ REMOTE CONTROL||', 'S', 2, 'C', '2020-09-16', '', '0000-00-00', '2020-09-04 13:35:23');
COMMIT;

    I think that should be the data needed...each of the four order ids should run through all the possibilities, price isn't set, product has a branded price, and product has an unbranded price, or no order was found. 

    determine_multi_price_product( 7185, 119, $pdo );

    Sample call to the function using a specific product that has prices for each of those enterprises except NAPAHQ.

    Convert Multiple Queries Into One Query Using Conditions

    in MySQL Help

    Posted · Edited by mongoose00318

    I am very unfamiliar with using using variables and conditional statements with an SQL statement.

    Is there a way to streamline this code to make it perform all the logic I am currently doing in PHP within the query itself in the interest of streamlined code? 

    function determine_multi_price_kit( $order_id, $kit_id, $pdo ) {

	//get the enterprise associated with the order
	$query = 'SELECT enterprise FROM production_data WHERE id = :order_id';
	$statement = $pdo->prepare($query);
	$statement->execute(['order_id' => $order_id]);
	
	if ( $statement->rowCount() > 0 ) {

		$enterprise = $statement->fetch()['enterprise'];

		//query database for a branded price for a specifc enterprise
		$query = '
					SELECT
						price
					FROM
						leds_prices
					WHERE
						enterprise = :enterprise AND
						kit_id = :kit_id
		';
		$statement = $pdo->prepare($query);
		$statement->execute([
			'enterprise' => $enterprise,
			'kit_id' => $kit_id,
		]);

		//check if the query returned a result and if not default to an unbranded price
		if ( $statement->rowCount() > 0 ) {
		
			$price = $statement->fetch()['price'];

		}

		//default to an unbranded price
		else {

			//query database for a branded price for a specifc enterprise
			$query = '
						SELECT
							price
						FROM
							leds_prices
						WHERE
							enterprise = "Unbranded" AND
							kit_id = :kit_id
			';
			$statement = $pdo->prepare($query);
			$statement->execute([
				'enterprise' => $enterprise,
				'kit_id' => $kit_id,
			]);

			if ( $statement->rowCount() > 0 ) {

				$price = $statement->fetch()['price'];

			}

			//price isn't set
			else {

				$price = 'Not Set';

			}

		}

		return $price;

	}

}

     

    Repeating Elements Even After Using .remove();

    in Javascript Help

    Posted

    I am populating a modal window with some divs based on data which is retrieved via AJAX anytime the user clicks a button which opens the modal window.

    Here is the HTML: 

    <div class="row">

	<div class="float-left unbranded m-2 p-2 border border-dark rounded bg-primary text-white">

		<p class="border-bottom pb-0 mb-1">Unbranded</p>
		<input type="text" name="price_unbranded" size="5" placeholder="$">

	</div>

	<div class="float-left price m-2 p-2 border border-dark rounded d-none bg-secondary text-white">

		<p class="border-bottom pb-0 mb-1">Price 1</p>
		<select name="price_ent[]">
			<option>Select Enterprise</option>						
		</select><br>
		<input type="text" name="price_price[]" size="5" placeholder="$">

	</div>

</div>

    Here is the jQuery: 

    //user clicks on edit button for a product
$('body').on('click', '.btn-edit', function() {

	$('div.price:not(:hidden)').remove(); //remove any existing elements other than the hidden template

	$.ajax({
		type: 'GET',
		url: 'scripts/leds_product_manager.php',
		data: {
			action: 'get_led',
			id: $(this).attr('data-product-id'),
		},
		success: function(data) {
			
			//parse response
			data = $.parseJSON(data);

			var consignment = ( data.consignment == 0 ) ? 'No' : 'Yes';

			//populate selects
			$('#add-edit-product select#make').find('option').attr('selected', false);
			$('#add-edit-product select#make option[value=' + data.make + ']').attr('selected', 'selected');
			$('#add-edit-product select#qty-per-box').find('option').attr('selected', false);
			$('#add-edit-product select#qty-per-box option[value=' + data.qty_per_box + ']').attr('selected', 'selected');
			$('#add-edit-product select#consignment').find('option').attr('selected', false);
			$('#add-edit-product select#consignment option[value=' + consignment + ']').attr('selected', 'selected');

			//populate inputs
			$('#add-edit-product input#part_number').val(data.part_number);
			$('#add-edit-product input#description').val(data.description);

			var count = 1;
			$.each(data.price, function(i,d) {
				
				// handle "Unbranded" price
				if ( d.enterprise == 'Unbranded' ) {

					$('div.unbranded input').val(d.price);

				} else {

					//handle all the others
					var div = $('div.price:last');
					div.clone().removeClass('d-none').insertAfter(div);
					$('div.price:last p:first').text('Price ' + count);

					//setup selects
					cur_ent = d.enterprise;
					$.each(data.enterprises, function(i,d) {

						if ( cur_ent == d.enterprise )
							$('div.price:last select').append('<option value=' + d.enterprise + ' selected="selected">' + d.enterprise + '</option>');
						else
							$('div.price:last select').append('<option value=' + d.enterprise + '>' + d.enterprise + '</option>');
					});

					//populate price
					$('div.price:last input').val(d.price);

				}

				count++;

			});

			//change button view
			$('.add-new-product').hide();
			$('.edit-new-product').attr('data-product-id', data.id).show();
			
			// change the title of the dialog box and open it
			$('#add-edit-product').dialog('option', 'title', 'Edit Record').dialog('open');

		}

	});

});

    At the top I have: $('div.price:not(:hidden)').remove(); to remove any preexisting elements and if I enter that into the console it erases all the correct elements. But anytime I open the modal window and then reopen it it is still duplicating the elements. Here are some screenshots (also I checked the json response coming back each time and all of that seems correct; it has to be something I am doing wrong with the jQuery):

    First open:

    image.png.6c7caa01c098bfe5d4cce20735785644.png

    Second open of the modal window:

    image.thumb.png.0ea19d679f4e4232bb9f511d1a22e8cf.png

    I've tried alerting how many elements of div.price which are visible, etc and can't figure out where I am going wrong...

    Using PDO to Connect to An MS Access DB

    in Microsoft SQL - MSSQL

    Posted

    Weird...okay this did it... 

    SELECT
	[PROD SCHD].[WORK ORDER],
	[PROD SCHD].[ENTERPRISE],
	[PROD SCHD].[PART NUMBER],
	[PPBOMFCB].[ITEM_NUMBER_BOM],
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
	[PPBOMFCB].[DESCRIPTION_BOM]
FROM
	[PROD SCHD]
LEFT JOIN
	[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
WHERE
	[PROD SCHD].[WORK ORDER] IS NOT NULL AND
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA%'

     

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