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 your values to avoid SQL injection bugs. mysqli makes this easy with bind_param. — tadman
– tadman, Commented Dec 14, 2013 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
– M Khalid Junaid, Commented Dec 14, 2013 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
– Amal, Commented Dec 14, 2013 at 20:10
Ok, I added "or die(mysqli_error($con))" and get the following: Unknown column 'Tom' in 'where clause' — p0ny
– p0ny, Commented Dec 14, 2013 at 20:35

user4035 · Accepted Answer · 2013-12-14 21:10:01Z

1

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);
}

answered Dec 14, 2013 at 21:10

user4035

24k11 gold badges69 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve a single record in php/mySql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related