CODEWITHSUNDEEP
phpstringif-statement
mvictor
mvictor

Reputation: 3

How to write php if statement where mysql recordset result contain number

I have a mysql database called menus. I am able to query my database to obtain a recordset accordingly. I do NOT want to write a query for each season. My problem: I want to write a php if statement within body to echo a message if the season field CONTAINS 2.

table menu

id | item (varcar)  | season (set)
1  | fresh lemonade | 2,3
2  | smoothie       | 2
3  | cafe latte     | 4

My query works fine

mysql_select_db($database_rest, $rest); $query_menus = "SELECT * FROM menus";

$menus = mysql_query($query_menus, $rest) or die(mysql_error());

$row_menus = mysql_fetch_assoc($menus);

$totalRows_menus = mysql_num_rows($menus);

I can write a php if to work where recordset field = 2 that works.

<?php echo $row_menus['item']; ?>: <?php
      if (@$row_menus['season'] == 1)
        {
        echo "Winter";
        }
      else if (@$row_menus['season'] == 2)
        {
        echo "Spring";
        }
      else if (@$row_menus['season'] == 3)
        {
        echo "Summer";
        }
      else if (@$row_menus['season'] == 4)
        {
        echo "Fall";
        }
      ?>

Result Shows:

fresh lemonade: Spring
smoothie: Spring
cafe latte: Fall

I want to write php so that if season CONTAINS 2 (example: if (@$row_menus['season'] CONTAINS 1) echo Spring, etc for each. so that result would look like this:

fresh lemonade: Spring
fresh lemonade: Summer
smoothie: Spring
cafe latte: Fall

Upvotes: 0

Views: 949

Answers (2)

Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

First of all, it's time to upgrade those mysql_* functions! Second, there's better ways to set up the relationship between the item and its seasons. However, I'll let you look into that stuff.

$query_menus = "SELECT * FROM menus";
$menus = mysql_query($query_menus, $rest) or die(mysql_error());

$seasons_array = array('All Seasons', 'Winter', 'Spring', 'Summer', 'Fall');

while($row_menus = mysql_fetch_array($menus))
{
    $item = $row_menus['item'];
    $season = $row_menus['season'];

    if(strpos($season, ',') !== false)
    {
        $seasons = explode(',', $season);

        // To show the item and season on separate rows
        foreach($seasons as $s)
            echo $item . ': ' . trim($seasons_array[(int)$s]);

        // To show the item, and then a list of seasons
        echo $item . ': ';
        foreach($season as $k => $s)
            echo trim($seasons_array[(int)$s]) . (($k + 1) == count($season) ? '' : ', ');
    }
    else
    {
        echo $item . ': ' . $seasons_array[(int)$season];
    }
}

I didn't test this, but it should work for you. Try it out, and let us know.

EDIT: I updated it so that you can list the items on separate rows, or list the item followed by each season.

Upvotes: 1

DaveyBoy
DaveyBoy

Reputation: 2915

You have two choices:

  1. use PHP to split the field into an array and work through that
  2. use multiple rows where season only holds one value

Using PHP, you could use $season=explode(',',$row_menus['season']); and then use a foreach statement to iterate through $season

The problem you have is that PHP interprets 2,3 as a string. When it's converted into a number, it appears as 2

Try using multiple rows for the season field:

id | item (varcar)  | season
1  | fresh lemonade | 3
2  | smoothie       | 2
3  | cafe latte     | 4
4  | fresh lemonade | 2

This will also help where you want to select all rows where season=2

Finally, as Tobias said, don't use the mysql_ functions. Use newer functions which are supported

Upvotes: 0

Related Questions