How to create a dynamic PHP switch statement from MySQL query

Question

This is for adding images, categories and comments from an email to a photo gallery I'm working on.

I'm pulling the first two found category names out of an email message body and need to assign the category id to $cat variable for insertion into DB.

I have it working fine hardcoded, but would like to make it dynamic so changes to category names will cascade throughout.

The switch statement I'd like to change:

Hardcoded:

foreach($foundKeywords as $key => $value) {  
            if($key<=1){

switch($value)
        {
            case 'family':
            $cat='3';
            break;
            case 'friends';
            $cat='4';
            break;
            case 'fun';
            $cat='6';
            break;
            case 'places';
            $cat='5';
            break;
            case 'general';
            $cat='2';
            break;
            case 'henry';
            $cat='7';
            break;
            default;
            $cat='2';
        }

I'd like to pull the categories from a MySQL table and do it this way:

$res = mysql_query('SELECT * FROM gallery_category');
            $cat_switch_list = "";
            while($row = mysql_fetch_array($res)){
                $cat_switch_list .= "case '".$row[1]."':
                                    $cat = ".$row[0].";
                                    break;";
            }
            //////////////////////
            foreach($foundKeywords as $key => $value) {  
                if($key<=1){


                    switch($value)
                    {
                        echo $cat_switch_list;
                        default:
                        $cat='1';
                    }

In theory, I believe this should work, but something is not right.

Any suggestions?

You realize that your `$cat` variable is just going to end up as whatever the last "keyword" is, right? You cannot dynamically generate PHP this way (at least, not without eval or similar) and you shouldn't, and there is no need for it. — Brad, Jul 13 '11 at 21:57

score 2 · Accepted Answer · answered Jul 13 '11 at 21:59

You'd be better of to either use mysql directly for querying:

mysql_query('select id from gallery_category where name=\''.mysql_real_escape($name).'\'');

or by generating an array from your result:

$result = mysql_query('select * from gallery_category');
$categories = array();
while($row = mysql_fetch_num($result))
{
  $categories[$row[1]] = $row[0];
}

Then you can find the correct id simply be looking it up in your categories array: $id = $categories[$name].

If you want to generate code and it looks like it, you can use var_export() to save the $categories variable to real php code. Do you want to generate code?

Thanks very much for your help. Simply querying the DB was the obvious way to go! No wonder I didn't think of it. — Mark, Jul 13 '11 at 23:34

score 0 · Answer 2 · answered Jul 13 '11 at 21:57

0

There's no need for a switch statement at all (what you're asking for is not what switch statements are for).

Simply store the $cat value in the same table that describes the categories. Then you just select from that table, and in each row you have the $value and the $cat that you need.

answered Jul 13 '11 at 21:57

Jimmy Sawczuk

13,488
7
46
60

No, Jimmy. That's not what switches are for. I'm still learning. However, it did get the job done at the time so I was happy that at least it was working. – Mark Jul 13 '11 at 23:37
Right, (short) lists of static values are fine, but when you try to make it dynamic, that's when it gets hairy. Didn't mean to sound like a jerk, just wanted to express that your situation is now easier because you don't need the `switch`. :) – Jimmy Sawczuk Jul 13 '11 at 23:44

score 0 · Answer 3 · answered Jul 13 '11 at 21:58

Do your database query and load the results into an associative array:

$result = mysql_query('SELECT name, id FROM gallery_category');
$categories = array();
while ($row = mysql_fetch_assoc($result))
{
    $categories[$row['name']] = $row['id'];
}

foreach ($foundKeywords as $key => $val)
{
    if ($key <= 1)
    {
        $cat = $categories[$val];
        // Do whatever processing/output you need to do with this keyword
    }
}

N.B.: This code is untested.

How to create a dynamic PHP switch statement from MySQL query

3 Answers3

Linked