How to retrieve a single record in php/mySql?

Question

I'm trying to return a single record with the following statements:

$username = $_POST["username"];
$con=mysqli_connect("localhost","root","pass","Testproject");

// Check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$sql = mysqli_query($con,"SELECT * FROM registeredusers where Username=".$username);
  $row = mysqli_fetch_array($sql);

  if (!$row) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

and am getting the following errors:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in "Filepath goes here"/AddEvent.php Error: Unknown column 'Tom' in 'where clause'

"Tom" in this case is the user name. Any ideas how to fix this? I've looked at similar questions on stackoverflow, but majority of them are a little more complex or aren't trying to get a single record.

EDIT: I've change the query to the following:

  $sql = mysqli_query($con,"SELECT * FROM registeredusers") or die(mysqli_error($con));
  $row = mysqli_fetch_array($sql);

And added this as well"

$row_cnt = $result->num_rows;
 printf("Result set has %d rows.\n", $row_cnt);

Im getting the following:

"Result set has 0 rows".

I've typed the exact query into my DBMS and it returns many rows.

**DO NOT** use string interpolation or concatenation to add user data to your queries. You **must** [properly escape](http://bobby-tables.com/php) your values to avoid [SQL injection bugs](http://bobby-tables.com/). `mysqli` makes this easy with [`bind_param`](http://www.php.net/manual/en/mysqli-stmt.bind-param.php). — tadman, Dec 14 '13 at 20:10
try this `mysqli_query(...) or die(mysqli_error();` what error it throws or simply put check of `mysqi_num_rows() > 0` then `mysqli_fetch_array()` — M Khalid Junaid, Dec 14 '13 at 20:10
Change your query to use quotes: `where Username='$username')` -- but note that this code is vulnerable to SQL injection. You should use paramaterized queries instead. — Amal Murali, Dec 14 '13 at 20:10
http://stackoverflow.com/questions/11309187/php-mysql-single-record-show — Biswajit, Dec 14 '13 at 20:20
Ok, I added "or die(mysqli_error($con))" and get the following: Unknown column 'Tom' in 'where clause' — p0ny, Dec 14 '13 at 20:35
possible duplicate of [Reference - What does this error mean in PHP?](http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php) — user555, Dec 14 '13 at 20:36
I also tried swapping the statement to use"where Username='$username' " and got a syntax error. — p0ny, Dec 14 '13 at 20:36
`mysqli_query()` returns `FALSE` if the SQL query failed. You can't pass `FALSE` to `mysqli_fetch_array()` since it expects a `mysqli_result`. Simply put your SQL query has malformed syntax. — user555, Dec 14 '13 at 20:40
@user3101620 You tried `WHERE Username = '$username'` and got an error. What syntax error was it? We are not mind readers, you know. — user555, Dec 14 '13 at 20:55
Fixed the syntax error: the line of code now is: $sql = mysqli_query($con,"SELECT * FROM registeredusers where Username='$username'") or die(mysqli_error($con)); However, now when I try to display it, it doesn't print ( echo "
=". $row['username'] ."=
";) — p0ny, Dec 14 '13 at 21:02
@user3101620 Please update your question with the full code you're using. — user555, Dec 15 '13 at 12:46

score 1 · Answer 1 · answered Dec 14 '13 at 21:10

You are missing quotes for $username and is vulnerable for SQL injection. I suggest you to bind the variable, so MySQLi will do the quoting and protect you from SQL injection:

$sql = "SELECT * FROM registeredusers where Username = ?";
if ($stmt = $con->prepare($sql)) {
    $stmt->bind_param('s', $username);
    $stmt->execute();
    $results = $stmt->get_result();
    $row = mysqli_fetch_array($results);
    var_dump($row);
}

How to retrieve a single record in php/mySql?

=". $row['username'] ."=

1 Answers1