how to output different types of data in php?

Question

This is how my table is shown.

The code for the creation and returning of the table: i would like to test the value of the sport ID value. if it is 1, return football. if it is 2 return Tennis and if it is 3 return swimming. And if the away column is TRUE i want it to output Away, else output HOME.

$sql = "CREATE TABLE fixtureDetails
(
fixtureID INT(5) NOT NULL AUTO INCREMENT,
opponent VARCHARD(30) NOT NULL,
date DATE
away BOOLEAN,
sportID INT
refereeID INT,
PRIMARY KEY (fixtureID),
FOREIGN KEY (sportID) REFERENCES sport(sportID),
FOREIGN KEY (refereeID) REFERENCES referee(refereeID)
)";

$sql1 = "SELECT * FROM fixtureDetails";
if ($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result)>0){
        echo "<table>";
            echo "<tr>";
                echo "<th>fixtureID</th>";
                echo "<th>opponent</th>";
                echo "<th>date</th>";
                echo "<th>away</th>";
                echo "<th>sportID</th>";
                echo "<th>refereeID</th>";
            echo"</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td>" . $row['fixtureID'] . "</td>";
                echo "<td>" . $row['opponent'] . "</td>";
                echo "<td>" . $row['date'] . "</td>";
                echo "<td>" . $row['away'] . "</td>";
                echo "<td>" . $row['sportID'] . "</td>";
                echo "<td>" . $row['refereeID'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        mysqli_free_result($result);
    } else {
        echo "No records matching your query found.";
    }
} else {
    echo "ERROR: could not execute $sql1. " . mysqli_error($link);
}

You should be thinking about adding a table to contain the `1=football.2=Tennis and 3=swimming — RiggsFolly
– RiggsFolly, Commented Jan 9, 2017 at 15:16
I do have that table. it is called sport with the columns sportID and name — Faayo Bedrudin
– Faayo Bedrudin, Commented Jan 9, 2017 at 15:17
@RiggsFolly I believe he already does have another table for the Sport via: FOREIGN KEY (sportID) REFERENCES sport(sportID) in his table create script. — Siyual
– Siyual, Commented Jan 9, 2017 at 15:17

Siyual · Accepted Answer · 2017-01-09 15:26:16Z

1

You don't show the structure of the Sport table, so I'm assuming there is a column in that table named Name. You can change this part to reflect your actual table.

I wouldn't use PHP to retrieve those values if they're already in the database (as hinted by the following in your CREATE statement):

FOREIGN KEY (sportID) REFERENCES sport(sportID),

Instead of the SELECT * FROM fixtureDetails; query you're using, I would use the following query instead:

Select  F.FixtureId,
        F.Opponent,
        F.Date,
        Case When F.Away = 1 Then 'Away' Else 'Home' End As Away,
        S.Name As Sport,    -- Change S.Name to the actual column in Sport.
        F.RefereeId
From    FixtureDetails  F
Join    Sport           S   On  S.SportId = F.SportId

edited Jan 9, 2017 at 15:26

answered Jan 9, 2017 at 15:23

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RiggsFolly Over a year ago

That saves me writing it up

S M Iftakhairul · Accepted Answer · 2017-01-09 15:43:05Z

0

echo "<td>" . $row['fixtureID'] . "</td>";
echo "<td>" . $row['opponent'] . "</td>";
echo "<td>" . $row['date'] . "</td>";
if($row['away'])
  echo "<td>Away</td>";
else
  echo "<td>Home</td>";

switch($row['sportID']){
   case 1: echo "<td>Football</td>";
       break;
   case 2: echo "<td>Tennis</td>";
       break;
   case 3: echo "<td>Swimming</td>";
       break;
   default: echo "Error!";
 }
echo "<td>" . $row['refereeID'] . "</td>";

edited Jan 9, 2017 at 15:43

answered Jan 9, 2017 at 15:21

S M Iftakhairul

1,1502 gold badges20 silver badges45 bronze badges

Comments

Rafal Mazgaj · Accepted Answer · 2017-01-09 15:15:06Z

0

Please check it:

echo "<td>" . (isset($row['away']) && $row['away']) ? 'AWAY' : 'HOME' . "</td>";

answered Jan 9, 2017 at 15:15

Rafal Mazgaj

648 bronze badges

Collectives™ on Stack Overflow

how to output different types of data in php?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related