Get sum of row where username is the current user's name

Question

This code just displays a blank webpage. Is there anything wrong with it? It is supposed to show the total points the logged in user has.

<?php

session_start();

$servername = "localhost";
$username = "root";
$password = "randompassword";
$dbname = "transactions";

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

$query = "SELECT sum(points) AS points FROM transaction WHERE username =    '".mysqli_real_escape_string($conn,$_SESSION['username'])."'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

print($row)
?>

You have a table named `transaction`? If so, I'd highly recommend you change that. — Mike, Aug 21 '16 at 20:38
`transaction` is reserve word and so escape it using backtique. — Rahul, Aug 21 '16 at 20:39
As a general rule, when PHP shows you a blank page it is because the PHP error reporting (http://php.net/manual/en/function.error-reporting.php) is set to hide it. Look at your logs for the specific PHP error encountered. — Dan Bowling, Aug 21 '16 at 20:45
@Rahul `transaction` is a keyword, but it's not a reserved word. There's no `(R)` next to it on http://dev.mysql.com/doc/refman/5.7/en/keywords.html — Barmar, Aug 21 '16 at 21:04
`print($row)` won't work, because `$row` is an array. Use `var_dump($row)`. But it shouldn't produce a blank page, it should print the word `Array`. — Barmar, Aug 21 '16 at 21:07
@Barmar, Ahh! right but either way should be escaped or not used as normal literal. — Rahul, Aug 21 '16 at 21:08
What happens if you run the query by hand in the mysql command line or phpMyAdmin? — Barmar, Aug 21 '16 at 21:09
array(1) { ["points"]=> NULL } is what the page displays, please help — UntitledUsername, Aug 22 '16 at 01:27

score 0 · Answer 1 · edited May 23 '17 at 11:51

You should enable the error_reporting like this

error_reporting(E_ALL); ini_set("display_errors", 1);
transaction is a keyword in mysql. So use back tick ( ` ).
Instead of using direct substitution values, you could use below methods to avoid sql injection.

Using MySQLi (for MySQL):

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

Please refer How can I prevent SQL-injection in PHP?

Get sum of row where username is the current user's name

1 Answers1