Jump to content

Convert Multiple Queries Into One Query Using Conditions


Recommended Posts

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 by mongoose00318
Link to post
Share on other sites
--
-- 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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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 by mongoose00318
Link to post
Share on other sites

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

 

Link to post
Share on other sites
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.

Link to post
Share on other sites

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

Link to post
Share on other sites
  • 2 weeks later...
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. 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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