Shea Lavington Shea Lavington - 6 months ago 26
SQL Question

Use less MySQLi queries

I'm trying to make a system to display information from a user database and it will then display it in a table, however, the issue I have is pageload, and I will be needing three of these and I think I've gone the longest possible route. the page will need to display recipient and type, now in my database I have 5 types of information and it currently counts them each and then displays them as good bad and flagged. can I use less MySQL

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$result = mysqli_query($conn,"SELECT *
FROM `user_flags`
ORDER BY user_flags.timestamp DESC
LIMIT 15");
?>
<tbody>
<?php

while( $row = mysqli_fetch_assoc( $result ) ) {

$GoodRepSql = mysqli_query($conn, "SELECT COUNT(*)
FROM user_flags
WHERE user_flags.recipient = {$row['recipient']}
AND type = 'Good Rep' ");

if ($GoodRepSql->num_rows > 0) {
while($goodrow = $GoodRepSql->fetch_assoc()) {
$GoodRep = $goodrow["COUNT(*)"] ;
}
} else {
$GoodRep = $goodrow["COUNT(*)"] ;
}

$BadRepSql = mysqli_query($conn, "SELECT COUNT(*)
FROM user_flags
WHERE user_flags.recipient = {$row['recipient']}
AND type = 'Bad Rep' ");

if ($BadRepSql->num_rows > 0) {
while($badrow = $BadRepSql->fetch_assoc()) {
$BadRep = $badrow["COUNT(*)"] ;
}
} else {
$BadRep = $badrow["COUNT(*)"] ;
}

$FlagSql = mysqli_query($conn, "SELECT COUNT(*)
FROM user_flags
WHERE user_flags.recipient = {$row['recipient']}
AND type IN ('Smurfing', 'Griefing', 'Suspicious')");
if ($FlagSql->num_rows > 0) {
while($flagrow = $FlagSql->fetch_assoc()) {
$Flags = $flagrow["COUNT(*)"] ;
}
} else {
$Flags = $flagrow["COUNT(*)"] ;
}

echo
"<tr onclick=\"document.location = '{$url}/{$row['recipient']}';\">
<td>{$Recipient->players->player->personaname}</td>
<td>{$BadRep}</td>
<td>{$GoodRep}</td>
<td>{$Flags}</td>
</tr>\n";
}

?>

Answer

If you need the count for each type you can use: GROUP BY

SELECT COUNT(*),type FROM user_flags WHERE user_flags.recipient = {$row['recipient']} GROUP BY type

Since you want some of the types to be added together you would need to add them using php.

$RepSql = mysqli_query($conn, "SELECT COUNT(*) as repcount,type FROM user_flags WHERE user_flags.recipient = {$row['recipient']} GROUP BY type ");
    $GoodRep=$BadRep=$Flags=0;   
if ($RepSql->num_rows > 0) {
            while($rep_row = $RepSql->fetch_assoc()) {
                if( $rep_row["type"]=='Smurfing'||$rep_row["type"]=='Griefing'||$rep_row["type"]=='Suspicious'){
                    $Flags+=$rep_row["repcount"];
                }else if($rep_row["type"]=="Good Rep"){
                    $GoodRep=$rep_row["repcount"];
                }else if($rep_row["type"]=="Bad Rep"){
                    $BadRep=$rep_row["repcount"];
                }
            }
        }