[FIXED] How to change mysql_num_rows etc functions to mysqli?

Issue

I am facing an error as I'm about to launch my PHP files to a free web hosting site. The error showing up is given below:

And below is the code for my project.

    $sql= "SELECT * FROM user WHERE staff_id='$staff_id' AND password='$password'";
    $query = mysql_query($sql) or die("Error: " . mysql_error());  //this is error on line 42
    $row = mysql_num_rows($query);

I'm not sure what the errors are as I am self-taught on PHP. hopefully you guys can point out what change i should make. Thanks in advance!

Solution

First, as you suggest in the title, use mysqli for security reasons, or even better, PDO.

With mysqli: (updated)

$stmt = $conn->prepare("SELECT COUNT(*) FROM user WHERE staff_id = :staff_id AND password = :password");
$res = $stmt->execute(["staff_id" => $staff_id, "password" => $password);
$row = mysqli_num_rows($res);

With PDO:

$stmt = $conn->prepare("SELECT COUNT(*) FROM user WHERE staff_id = :staff_id AND password = :password");
$res = $stmt->execute(["staff_id" => $staff_id, "password" => $password);
$row = $res->fetchColumn();

$conn being your database link. The PDO version assumes you don't need the rows but just the count. In case someone tells you to, don't use rowCount on SELECT query, that's not reliable.

Answered By - Jules R

Answer Checked By - Gilberto Lyons (PHPFixing Admin)