PHP Check MySQL Variable

Question

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();

You mean switch or an if control structure, that is up to you. It largely depends on how many cases you have. Only a few if then is ok, for several comparing the same item then I'd use a switch. It really comes down to what is easier to read and make sense of. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jul 26, 2015 at 5:10
Traditionally Ive heard that if statements are more performant than switch case statements... ate least in in javascript Ive been told that. — user2276553
– user2276553, Commented Jul 26, 2015 at 5:12
Are you asking in SQL or in PHP? If PHP see this article stackoverflow.com/questions/7290889/… — chris85
– chris85, Commented Jul 26, 2015 at 5:12
@chris85 - wouldn't opcache make that answer obsolete. In anycase performance at this level is backseat to readability. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jul 26, 2015 at 5:13
I guess what Im asking is how do I use an if statement to switch 0 for permanent in the table? I did this a long time ago, and IM just having the hardest time remembering how I did it. It would only be for the length column every other one is fine as is. — user2041471
– user2041471, Commented Jul 26, 2015 at 5:14

MH2K9 · Accepted Answer · 2015-07-26 05:22:10Z

2

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>";

edited Jul 26, 2015 at 5:22

answered Jul 26, 2015 at 5:16

MH2K9

12.1k7 gold badges34 silver badges49 bronze badges

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

3 Comments

user2041471 Over a year ago

Awesome thank you so much, seeing as you answered my first question. Maybe you can answer one more? So the table inserts an admin id instead of the admins name, how would I go about converting id to admin name? I have the list of admin id's associated with the admins name. Would I just use a huge case list for this?

MH2K9 Over a year ago

In such case you can make relation between tables and JOIN query.

user2041471 Over a year ago

im having trouble getting it to work with an inner join, admin table with admin ids and usernames is from sb_admins and banlist with admin id is sb_bans, my query is just returning all zero's

ArtisticPhoenix · Accepted Answer · 2015-07-26 05:24:48Z

0

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>";

edited Jul 26, 2015 at 5:24

answered Jul 26, 2015 at 5:17

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

PHP Check MySQL Variable

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related