0

I seem to be struggling to get a search bar to display the results of a PHP/SQL search and populate the table with them. The reason being I am not used to the amount of SQL I required to create the table in the first place. This is the PHP script.

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


if(isset($_GET['statsearch'])) {

$statSearch = $_GET['statsearch'];

}

if (!empty($statSearch)) {
    $sql = "SELECT player, zeus_stats_player.name, SUM(KILLS) AS TOTALKILLS, SUM(DEATHS) AS TOTALDEATHS,SUM(KILLS)/IF(SUM(DEATHS) > 0,SUM(DEATHS),1)  AS KDR
FROM (
SELECT player, 0 AS KILLS, sum(count) AS DEATHS FROM zeus_stats_player_death WHERE Cause <> 'SUICIDE' GROUP BY player
UNION ALL
SELECT killer as player, COUNT(1) AS KILLS, 0 AS DEATHS FROM zeus_stats_player_kill group by killer) AS TBL INNER JOIN zeus_stats_player ON zeus_stats_player.id = TBL.player
GROUP BY zeus_stats_player.name WHERE zeus_stats_player.name like '%$statSearch%'
ORDER BY SUM(KILLS)/IF(SUM(DEATHS) > 0,SUM(DEATHS),1) DESC";
}

$sql = "SELECT player, zeus_stats_player.name, SUM(KILLS) AS TOTALKILLS, SUM(DEATHS) AS TOTALDEATHS,SUM(KILLS)/IF(SUM(DEATHS) > 0,SUM(DEATHS),1)  AS KDR
FROM (
SELECT player, 0 AS KILLS, sum(count) AS DEATHS FROM zeus_stats_player_death WHERE Cause <> 'SUICIDE' GROUP BY player
UNION ALL
SELECT killer as player, COUNT(1) AS KILLS, 0 AS DEATHS FROM zeus_stats_player_kill group by killer) AS TBL INNER JOIN zeus_stats_player ON zeus_stats_player.id = TBL.player
GROUP BY zeus_stats_player.name
ORDER BY SUM(KILLS)/IF(SUM(DEATHS) > 0,SUM(DEATHS),1) DESC LIMIT 15";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    echo "<table class='table'><tr><th>Name</th><th>Kills</th><th>Deaths</th><th>K/D Ratio</th></tr>";
    while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["name"]."</td><td>".$row["TOTALKILLS"]."</td><td>".$row["TOTALDEATHS"]."</td><td>".$row["KDR"]."</td></tr>";
}
echo "</tbody>";
echo "</table>";
} else {
echo "0 results";
}
?>
</div>

<?php
$conn->close();
?>

I want to display the results normally if someone doesn't search, that's what the bottom SQL is, but I want the top one to display results WHERE playername = searchbox entry.

Here's a link to the table. Table Link

RachMcrae
  • 123
  • 2
  • 10
  • 2
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Jan 06 '17 at 21:57
  • Providing that you fix your sql inject problem, don't you want to use the script like this? `if (!empty($statSearch)) {...}else{ run the second $sql?! }`. Because you're just overwriting the initial `$sql` everytime the first is set. LE: You may want to look into XSS hacks as well at `$row['name']` – Mujnoi Gyula Tamas Jan 06 '17 at 22:11

0 Answers0