What is the best way to have a SQL table contain a column of conditions for the row?

[Technetium]

I definitely need to explain what I mean by the thread title.

Suppose I have a database table “household_objects” with… a large list of household objects… Sofa, rug, table, frying pan, etc… The purpose of this table is for randomly selecting one of these objects. However, I only want each one to be an option if certain other conditions are met. For example, the row for frying pan is only eligible in the random selection if a variable “$I_like_fried_food == true”. Sofa is only an option if “$livingroom_w * $livingroom_l >= 100”. The conditions do not use the same variables for each row (and may not even use the same number of variables), so it’s not like I can just have a column with a value to test.

So the way I am currently approaching this is the brute-force way. I bring in the whole table (“SELECT * FROM household_objects”, followed up with a fetchall), and then go through an if-statement for each resulting element in the array, and based on the result of the if-statement I either leave the element intact or I unset it. Then at the end of this process I use array_rand to get the random option.

I am aware of another option. I could have a column in the household_objects table that contains the if-statement condition to test, and then use eval() on that to determine if the array element gets unset. This would allow me to have the if-statement embedded in the foreach loop, and thus only have to type it once. But I’ve been told that using eval() almost always means you’re doing something wrong. There is no user input involved anywhere close to this code.

So I just wanted to see how anyone else would go about this problem.

[phdr]

There is no user input involved anywhere close to this code.

Where are the variables - $I_like_fried_food, $livingroom_w, … getting their values from?

For your first case, you can use a data driven design, where you have an array containing the conditions, then use array_filter() with a call-back function, rather than actually writing out all the conditional tests -

<?php

// logic that gets/sets the various variables
$I_like_fried_food = true;
$livingroom_w = 9;
$livingroom_l = 9;

// define an array of filters. the index name is the name of the item, matching what's stored in the database table. the value is the logic expression
$filters['frying pan'] = $I_like_fried_food == true;
$filters['sofa'] = $livingroom_w * $livingroom_l >= 100;
 
// data fetched from the query 
$data[] = ['id'=>1,'name'=>'frying pan']; // an associative row of data. only id and name elements are shown in this example...
$data[] = ['id'=>2,'name'=>'sofa'];
$data[] = ['id'=>3,'name'=>'oven']; // a value w/o a filter
 

$data = array_filter($data, function($arr) use ($filters) {
		if(!isset($filters[$arr['name']]))
		{
			// i'm assuming you want to keep data that doesn't have a filter entry
			return true; 
		}
		else
		{
			// keep data if the filter expression is true
			return $filters[$arr['name']];
		}
	}
);

// examine the results
echo '<pre>'; print_r($data);

[Technetium]

The variables are set earlier in various places in the previous 4500 lines of code, at random as well. The best way I can describe this is… suppose you click a button and it sets of a huge chain of functions that are responsible for generating a random home, and the items within the home are just one part of that process (the part I’m working on right now).

Very interesting. I have seen array_filters used in other contexts but have yet to use it myself due to not really understanding what it does. I will definitely be looking into it more now.

This method would still require me to have a line of code for every conditional, whereas I was hoping there would be some way to bury it in the MySQL table. I guess it doesn’t really matter whether it’s here or there. The way you have it set up certainly looks a lot cleaner than that many if-statements.

Thanks!

[phdr]

the previous 4500 lines of code

Perhaps that could be reduced by using data driven design methods, where you have a data structure (array) that contains the rules for each step in the process, that you then loop over using general-purpose code to produce the result.

[skawid]

Have you considered an object oriented design? If the variables for each object are so different that they have to be described in code, that would give you a better chance at keeping everything organised. For your two examples, you have something like the following:

<?php
interface Item
{
    public function label(): string;

    public function isSuitable(array $house_params): bool;
}

class FryingPan implements Item
{
    public function label(): string
    {
        return 'Frying Pan';
    }

    public function isSuitable(array $house_params): bool
    {
        return $house_params['personal']['i_like_fried_food'] == true;
    }
}

class Sofa implements Item
{
    public function label(): string
    {
        return 'Sofa';
    }

    public function isSuitable(array $house_params): bool
    {
        return $house_params['living_room']['width'] * $house_params['living_room']['length'] > 100;
    }
}

$available_items = array(new FryingPan, new Sofa);

// These would be set by your user
$my_house_params = array(
    'personal' => array('i_like_fried_food' => true),
    'living_room' => array('width' => 10, 'length' => 9)
);

$number_of_items_to_choose = 10;

$suitable_items = array_filter(
    $available_items,
    fn ($item) => $item->isSuitable($my_house_params)
);

/**
 * We want a certain number of items for our house (10 in our example)
 * If there are less than 10 items in our list of suitable items, array_rand will fail
 * so make sure our number of items to get is no bigger than suitable_items
 */
if (count($suitable_items) < $number_of_items_to_choose) {
    $chosen_items = $suitable_items;
} else {
    $chosen_items = array_rand($suitable_items, $number_of_items_to_choose);
}

foreach ($chosen_items as $item) {
    echo $item->label(), PHP_EOL;
}

You write a class for each item your house may contain. These classes all use the interface Item - this isn’t strictly necessary, but it makes it easier for you to remember how your code hangs together and make it obvious if you’ve missed something from one of your items. You might decide your items need other methods besides; I’ve included a label() method for illustration purposes.

The important part for your question is isSuitable(); this method takes the properties of your house and returns true if the item is a suitable match or false if it isn’t.

[MacDknife]

I may be a bit late here, but may I give a bit of advice on structure. Very often one would rather have a complicated IF script, than a better database structure. (been there done that).
It would be much simpler and need less random script if you, instead of one large table, first create tables for each room, like table Livingroom, table Kitchen and so on. In each of those tables you post an Item that belongs in that room, each with it’s own ID. You can of course even create more tables, dividing up thing that e.g. need to be on the wall, in the cupboards, the floor and so on. This could of course be scripted as well, but it is so much easier just to add items to tables.
This database structure would need less script, make it quicker to execute a search for random items, as well as manage later - in my opinion.

That was my two bits worth!
: P
MacD