MySQL if? statement

Question

In my MySQL table, there is a row labeled "state". There are 3 values possible for this. How could I utilise PHP to echo "Unbanned" as apposed to "0" in state? And "Banned" as apposed to "1" and "Temp-Banned" as apposed to "3"?

How would I use php to display "UnBanned" instead of 0 in the MySQL? I am using a table for this.

<?
          while($row = $result->fetch_assoc()){
          echo'<td>' . $row['player'] . '</td>';
          echo'<td>' . $row['admin'] . '</td>';
          echo'<td>' . $row['reason'] . '</td>';
          echo'<td>' . $row['created_at'] . '</td>';
          echo'<td>' . $row['expires_at'] . '</td>';
          echo'<td>' . $row['state'] . '</td>' . '</tr>';
          }
?>

All help is incredibly appreciated :)

@Mihai http://pastebin.com/1H1FyefC – Daryin Littlejohn Oct 08 '13 at 02:00 — Daryin Littlejohn, Oct 08 '13 at 02:00

score 3 · Answer 1 · answered Oct 07 '13 at 19:34

3

You can do it either via PHP or MySQL. There are a lot of possibilities.

SELECT
    CASE
        WHEN state = 0 THEN 'UnBanned'
        WHEN state = 1 THEN 'Banned'
        ELSE 'Temp-Banned'
    END AS state

In PHP:

while ($row = $result->fetch_assoc()) {
    switch ($row['state']) {
        case 0: $row['state'] = "UnBanned";
            break;
        case 1: $row['state'] = "Banned";
            break;
        default: $row['state'] = "Temp-Banned";
    }

answered Oct 07 '13 at 19:34

Explosion Pills

188,624
52
326
405

2

I think OP should create a table of possible status' – Kermit Oct 07 '13 at 19:42
@FreshPrinceOfSO That would work great since you could select the state's name by joining on the ID which would be the number – Explosion Pills Oct 07 '13 at 19:43

score 2 · Answer 2 · answered Oct 07 '13 at 19:38

<?
while($row = $result->fetch_assoc()){
    echo'<td>' . $row['player'] . '</td>';
    echo'<td>' . $row['admin'] . '</td>';
    echo'<td>' . $row['reason'] . '</td>';
    echo'<td>' . $row['created_at'] . '</td>';
    echo'<td>' . $row['expires_at'] . '</td>';

 switch ($row['state']){
    case 0: echo '<td>Unbanned</td>';
        break;
    case 1: echo '<td>Banned</td>';
        break;
    case 3: echo '<td>Temp-Banned</td>';
        break;
      } 
}
?>

Alternatively, you could have a look up table for state where the records are:

STATE
STATEID STATE
0       Unbanned
1       Banned
3       Temp-Banned

in your players table you have a foreign key for state where it just holds the StateId.

Change your query to be:

SELECT * FROM Players JOIN STATE on STATE.STATEID=Players.state WHERE...

I like breaking stuff like that out, it makes it easy to create selectable lists later and it's much more extensible in the long run if other possible states are created...like if you want to break Temp-Banned into several groups, 30-60 day ban, full season ban, etc, you just change the records in the table rather than having to search for all of your queries and switch statements.

You forgot the `case` in your `switch()` :-P – gen_Eric Oct 07 '13 at 19:38 — gen_Eric, Oct 07 '13 at 19:38

aaron · Answer 3 · 2013-10-07T19:47:11.830

You could use the CASE statement in MYSQL OR if you don't have many states use the following code:

<?
          $a = array('0'=>'Unbanned','1'=>'Banned','3'=>'Temp-Banned');
          while($row = $result->fetch_assoc()){
          echo'<td>' . $row['player'] . '</td>';
          echo'<td>' . $row['admin'] . '</td>';
          echo'<td>' . $row['reason'] . '</td>';
          echo'<td>' . $row['created_at'] . '</td>';
          echo'<td>' . $row['expires_at'] . '</td>';
          echo'<td>' . $a[$row['state']] . '</td>' . '</tr>';
          }
?>

MySQL if? statement

3 Answers3