CODEWITHSUNDEEP
phpmysql
user2041471
user2041471

Reputation: 33

PHP Check MySQL Variable

Im inserting mysql data into a table using php to echo out a nice looking table.

I'm basically pulling ban data for a gaming community and when the time shows a 0 in the table I would like it to show "Permanent" instead. Would I be using CASE for this or using an if then?

   // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT name, authid, length/3600, reason, aid FROM sb_bans ORDER BY `sb_bans`.`bid` DESC, `sb_bans`.`created` ASC, `sb_bans`.`ends` ASC LIMIT 100";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table class='tftable' border='1'><tr><th>Username</th><th>Steam ID</th><th>Ban Time</th><th>Ban Reason</th><th>Admin</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["authid"]."</td><td>".$row["length/3600"]." Hours</td><td>".$row["reason"]."</td><td>".$row["aid"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();

Upvotes: 3

Views: 55

Answers (2)

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21671

Ok, now we are getting some where,

First off fix this if this is the field you are using 

 length/3600

Change it to 

 length/3600 as ban_time

Using an alias to rename this field makes it more readable, however you'll want to update code that used the $row['length/3600'], or you can just stick with that.

then you go ( inside your result while loop )

echo "<tr>
    <td>".$row["name"]."</td>
    <td>".$row["authid"]."</td>
    ";
if( $row['ban_time'] == 0 ) {
   echo "<td>Permanent</td>";
}else{
   echo "<td>".$row["ban_time"]." Hours</td>";
}
echo "<td>".$row["reason"]."</td>
    <td>".$row["aid"]."</td>
</tr>";

Upvotes: 0

MH2K9
MH2K9

Reputation: 12039

You can try using CASE like this

SELECT
    NAME,
    authid,
CASE
    WHEN (length / 3600) > 0 THEN
    (length / 3600)
ELSE
    'Permanent'
END AS time,
    reason,
    aid
FROM
    sb_bans
ORDER BY
    `sb_bans`.`bid` DESC,
    `sb_bans`.`created` ASC,
    `sb_bans`.`ends` ASC
LIMIT 100

And for table data

echo "<tr>
    <td>".$row["name"]."</td>
    <td>".$row["authid"]."</td>
    <td>".$row["time"]." Hours</td> //Changed this line
    <td>".$row["reason"]."</td>
    <td>".$row["aid"]."</td>
</tr>";

2nd way

Also can fix it by PHP condition without changing SQL

echo "<tr>
    <td>".$row["name"]."</td>
    <td>".$row["authid"]."</td>
    <td>".($row["length/3600"] > 0 ? $row["length/3600"] : 'Permanent')." Hours</td>
    <td>".$row["reason"]."</td>
    <td>".$row["aid"]."</td>
</tr>";

Upvotes: 2

Related Questions