PHP Class uses Static DB Table/Column Names. How to make Dynamic? (BEST PRACTICES)

Question

I found a PHP-based recipe and meal plan script, that I'd like to edit.

There's a MealPlan Class that allows all users to create their own daily menus/mealplans, by selecting from a list of all recipes found in the database.

I want to switch the table/column names used in the insert statement, based on the level of the user.

The original insert method is:

// Insert Meal Plan
function insert($date, $meal, $recipe, $servings, $owner) {
        global $DB_LINK, $db_table_mealplans, $LangUI;

        $date = $DB_LINK->addq($date, get_magic_quotes_gpc());
        $meal = $DB_LINK->addq($meal, get_magic_quotes_gpc());
        $recipe = $DB_LINK->addq($recipe, get_magic_quotes_gpc());
        $servings = $DB_LINK->addq($servings, get_magic_quotes_gpc());
        $owner = $DB_LINK->addq($owner, get_magic_quotes_gpc());

        $sql = "INSERT INTO $db_table_mealplans (
          mplan_date,
          mplan_meal,
          mplan_recipe,
          mplan_servings,
          mplan_owner)
          VALUES (
          '$date',
          $meal,
          $recipe,
          $servings,
          '$owner')";
        $rc = $DB_LINK->Execute($sql);
        DBUtils::checkResult($rc, NULL, $LangUI->_('Recipe already added on:'.$date), $sql);
}

My idea was to change:

mplan_date,
mplan_meal,
mplan_recipe,
mplan_servings,
mplan_owner

to:

$mplan_date,
$mplan_meal,
$mplan_recipe,
$mplan_servings,
$mplan_owner

and use a switch statement to change the table/column names. But I've been told that this sort of thing is frowned upon in OOP. What's the best way to go about this?

(NOTE: I can post the whole class if need be, but figured this would be enough info)

What other columns are in `db_table_mealplans` that you would switch to? It doesn't sound like you have a programming problem, it sounds more like an application design/data design problem. If you have to try to pull something like this off, you're probably going about it all wrong. — Jeremy Harris, Nov 13 '12 at 20:38

score 1 · Accepted Answer · answered Nov 13 '12 at 20:56

I don't have any code for you, but I've done something similar to this recently. Basically I dynamically made the SQL string by concating the string depending on what the user input. Bear with me as I try to type something on the fly.

$columnsArray = array();
$sqlCmd = "INSERT INTO";

If (someCondition) {
    array_push($columnsArray, "mplan_date");
}//Could try turning this into a loop and just push all 
 //applicable columns into the array

$counter = 0;

Foreach ($columnsArray as $columnName) {
    $sqlCmd .= " $columnName";
    $counter++
    If ($counter < count($columnsArray)){
        $sqlCmd .= ",";
    }//This will put a comma after each column with the 
     //exception of the last one.
}

So I did something like this only with a user defined select statement to retrieve data. Basiclly keep going with the logic until you’ve built a custom string that will be the user defined SQL statement, and then execute it. This includes doing something similar to the above script to concat the values into the string.

Hope this gives you some ideas.

Also a lot of the testing came from a `$_POST` form in html and checked is it was set or not to preload other variables for use in building the SQL statements as well. — Iron3eagle, Nov 13 '12 at 20:57

PHP Class uses Static DB Table/Column Names. How to make Dynamic? (BEST PRACTICES)

1 Answers1