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, Jul 26 '15 at 05:10
Traditionally Ive heard that if statements are more performant than switch case statements... ate least in in javascript Ive been told that. — , Jul 26 '15 at 05:12
Are you asking in SQL or in PHP? If PHP see this article http://stackoverflow.com/questions/7290889/what-is-the-difference-between-switch-case-and-if-else-in-php — chris85, Jul 26 '15 at 05:12
@chris85 - wouldn't opcache make that answer obsolete. In anycase performance at this level is backseat to readability. — ArtisticPhoenix, Jul 26 '15 at 05: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, Jul 26 '15 at 05:14
@ArtisiticPhoenix why is performance ever backseat? A case statement in SQL makes much more sense than having PHP process the data and evaluate it; if it can. As to `opcache` I've never heard of that before or used it so can't say... — chris85, Jul 26 '15 at 05:33
@chris85 - the difference between an `if then` statement with 2 conditions and a `swtich` statement with 2 cases, matters much less then the readability of the code. For example http://stackoverflow.com/questions/30754/performance-vs-readability , it's actually #3 on the answer's list. You will see this statement repeated in any respected article on the topic. Remember modern servers have Gigs of ram not kilobytes. `"Premature optimization is the root of all evil." - Donald Knuth` — ArtisticPhoenix, Jul 26 '15 at 05:38
@chris85 - why mix business/presentation logic with the sql ( data model ) , this can go on and on. As I said to start it's largely a matter of preference. — ArtisticPhoenix, Jul 26 '15 at 05:44
@chris85 - no need its Largely Opinion based, IMO .. pun intended. At some point you get to splitting hair and then it all reduces to preference of the person doing the work.. — ArtisticPhoenix, Jul 26 '15 at 05:46
@ArtisiticPhoenix I disagree with your thought that having SQL evaluate the data makes the code unreadable (there is only one condition here). End of topic. — chris85, Jul 26 '15 at 05:50
End of topic? lol, we wont go that rode, thanks, anyway FYI http://php.net/manual/en/book.opcache.php it's default in PHP 5.5 I believe. In any case never did I state using a case statement in SQL made it un-readable, where did that come from, in fact I never referenced SQL at all, until you mentioned it. I was merely talking about a `switch` vs a `if then` IN PHP. IS THAT CLEAR ENOUGH? And in fact I never said a switch an if or sql case -vs- php whatever was more readable, I said whatever make it more readable to the OP. — ArtisticPhoenix, Jul 26 '15 at 05:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84283/discussion-between-chris85-and-artisiticphoenix). — chris85, Jul 26 '15 at 05:56
@ArtisiticPhoenix Haha okay... we won't discuss the issue you brought up and felt was worthy of a discussion in the comments.... — chris85, Jul 26 '15 at 06:10

MH2K9 · Accepted Answer · 2015-07-26T05:22:10.040

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 '15 at 05:22

answered Jul 26 '15 at 05:16

MH2K9

11,951
7
32
49

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? – user2041471 Jul 26 '15 at 05:29
In such case you can make relation between tables and JOIN query. – MH2K9 Jul 26 '15 at 05:33
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 – user2041471 Jul 26 '15 at 05:44

ArtisticPhoenix · Answer 2 · 2015-07-26T05:24:48.560

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

PHP Check MySQL Variable

2 Answers2