NULL value

I have a table category_options where the sub_category_id either has a value coming from the table product_details or is NULL. How do I declare that for a variable?

 $subCatId             = $product_details['sub_category_id'];
 $product_options = $this->shop->get_product_options($subCatId);

Thank you in advance

I tried the following:

$subCatId  = is_null($photo_details['sub_category_id']) ? NULL : $photo_details['sub_category_id'];

It indeed returns all options where the sub_category_id has an value, but it doesn’t return anything when the sub_category_id is NULL.

var_dump ($product_options); than gives me an empty array

that line of code is pointless, it’s like doing $x = $x.

if $product_options gives you an empty array and you expect something different, then there’s something wrong with that method.

I’m trying to understand what you need help with, exactly.

You seem to have successfully defined null for a variable if (your variable changes here - in your first block of code it’s $product_details, in the second it’s $photo_details…) it’s null. But… If it value is null, as Dormilich notes, you don’t need to re-define it as null. $subCatId = <variable>['sub_category_id'];

Hi @Dormilich and @StarLion. Thank you bot for the reply. I think I didn’t explain myself right.

This is the method to get the right category_options:

    public function get_product_options($catId,$subCatId)
    {
        $sql = "SELECT *
                  FROM category_options
                 WHERE category_id = ?
                   AND sub_category_id = ?";
                 
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($catId,$subCatId));
        
        return $stmt->fetchAll();
    }

This are the values in category_options:

INSERT INTO `category_options` (`option_id`, `category_id`, `sub_category_id`, `option_name`, `option_price`) VALUES
(1, 1, NULL, 'Digitaal bestand Hi-Res', '25.00'),
(2, 1, NULL, 'Afdruk canvas 30x40', '59.95'),
(3, 1, NULL, 'Afdruk canvas 50x70', '89.95'),
(4, 1, NULL, 'Afdruk canvas 70x100', '169.95'),
(5, 2, NULL, 'Digitaal bestand Hi-Res', '25.00'),
(6, 2, NULL, 'Afdruk 10x15', '2.50'),
(7, 2, NULL, 'Afdruk 15x20', '5.00'),
(8, 2, NULL, 'Afdruk 20x30', '10.00'),
(9, 2, NULL, 'Afdruk 30x40', '20.00'),
(10, 2, NULL, 'Afdruk canvas 30x40', '49.95'),
(11, 2, NULL, 'Afdruk canvas 50x70', '89.95'),
(12, 3, 6, 'Frame 20 x 30', '13.95'),
(13, 3, 6, 'Frame 30 x 40', '18.95'),
(14, 3, 6, 'Frame 40 x 50', '24.95'),
(15, 3, 7, 'Frame 20 x 30', '11.95'),
(16, 3, 7, 'Frame 30 x 30', '14.95'),
(17, 3, 7, 'Frame 30 x 40', '16.95'),
(18, 3, 7, 'Frame 40 x 50', '23.95'),
(19, 3, 8, 'Frame 20 x 30', '12.95'),
(20, 3, 8, 'Frame 30 x 30', '14.95'),
(21, 3, 8, 'Frame 30 x 40', '19.95'),
(22, 3, 8, 'Frame 40 x 50', '25.95'),
(23, 4, 9, 'Slip in 100', '4.95'),
(24, 4, 9, 'Slip in 200', '14.95'),
(25, 4, 10, '', '24.95'),
(26, 4, 11, '', '24.95'),
(27, 4, 12, '1000 stuks', '3.95'),
(28, 4, 12, '3 x 1000 stuks', '10.00');

The first 2 category_id’s dont have a sub_category_id as you can see. The last 2 do have a sub_category_id. I just would like to use the same query for both options. This is what I have in the controller now:

$product_details = $this->shop->get_product_photo_details($photoId);
$catId           = $product_details['category_id'];
$subCatId        = $product_details['sub_category_id'];
$product_options = $this->shop->get_product_options($catId,$subCatId);

As I said. category_options with category_id’s 3 and 4 are returning the right values, the first two on t6he other hand that have NULL as value for sub_category_id, are not returning values.

Does this makes any sense?

that won’t work. for checking a NULL value in SQL you have to use IS NULL.

field = NULL is per definition always NULL, even if the field value is NULL.

Hi @Dormilich thanks again for the reply. I know that I should use IS in mysql to check against a NULL value. So in other words you say I need to use two different queries just to get the results I am looking for? Or do I have any other options?

essentially yes. you could leverage the NULL test to PHP (by ignoring the sub categories in the SQL) and make PHP filter them out, but that also means that you would have to fetch unnecessary data. hence I deem it more efficient to just switch between the statements.

Presumably, NULL as a subcategory for an option means “all subcategories in this category”. Thus, the query can be restated as

                  SELECT *
                  FROM category_options
                 WHERE category_id = ?
                   AND (sub_category_id = ?
                   OR sub_category_id IS NULL);

Inserting another row into your dataset:

INSERT INTO `test`.`category_options` (`option_id`, `category_id`, `sub_category_id`, `option_name`, `option_price`) VALUES ('99', '3', NULL, 'wark', '3.50');

and then running the query for values 3 and 6 return 4 rows.

@Dormilich & @StarLion.

Thank you both again for the reply. This is something I can work with. I will start to test! Thanks again!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.