Convert Multiple Queries Into One Query Using Conditions

[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;

	}

}

 

Edited December 2, 2020 by mongoose00318

[Barand]

Any chance of some test data to work with?

[mongoose00318]

Hmm sure.. one sec

[mongoose00318]

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

[Barand]

Excellent - I'll get back to you.

[mongoose00318]

@Barand Awesome thanks man

[Barand]

Question: Your function specifies (order_id, kit_id, link) as parameters but your sample function call in last post has ( 7185, 119, $pdo ) .

According to your data, 119 is a product_id and not a kit_id.

Which is it?

[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;

	}

}

 

Edited December 2, 2020 by mongoose00318

[Barand]

Try "Plan A"

function determine_multi_price_kit( $order_id, $kit_id, $pdo )
{
    $res = $pdo->prepare("SELECT 
                               l.price
                             , l.enterprise
                        FROM production_data p
                             JOIN
                             leds_prices l ON l.enterprise IN (p.enterprise, 'Unbranded')
                                           AND kit_id = :kit_id
                        WHERE p.id = :order_id
                        ORDER BY l.enterprise = 'Unbranded'
                        ");
    $res->execute( [ 'kit_id' => $kit_id, 'order_id' => $order_id ] );
    $price = $res->fetchColumn();
    return $price ? $price : 'Not set';
}

$kit = 48;
$orders = [ 5017,
            7094,
            7179,
            7185,
            1234  ] ;
            
foreach ($orders as $oid) {
    echo "$oid : ". determine_multi_price_kit( $oid, $kit, $pdo ) . '<br>';
}

 

[mongoose00318]

@Barand Can you explain this part to me? 

return $price ? $price : 'Not set';

Isn't the first $price supposed to be the condition of the ternary operator? I'm confused as to how this statement operates.

Edited December 2, 2020 by mongoose00318

[mongoose00318]

Masterfully done by the way. I understand most of it. Just that one part is a little confusing to me.

[Barand]

24 minutes ago, mongoose00318 said:

Isn't the first $price supposed to be the condition of the ternary operator?

Correct - the ternary expression reads "If price is true, return price otherwise return 'Not set'.

In the line above

$price = $res->fetchColumn();
    

price will be "false" if no price was found.

[Barand]

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

[mongoose00318]

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.