Marcel Marcel - 4 months ago 14
SQL Question

PHP & MySql: can't update database

I'm working on a project where I need to set the boolean value isHere to true(1)
when it finds the corresponding ID. However I can't seem to update the database. I can select from the database though.

I have two problems. First:

$handle = @fopen("../smartriders.txt", "r");

if ($handle)
{
while (($smartRider = fgets($handle, 255)) !== false)
{

if($update = $conn->query("UPDATE members SET isHere = 1 WHERE SmartRiderID = ?", $smartRider))
{
print("update successful");
}
}
if (!feof($handle)) {
echo "Error: unexpected fgets() fail\n";
}
fclose($handle);
}


This yields a warning:


Warning: mysqli::query() expects parameter 2 to be integer, string given in index.ph on line 14.


line 14:

if($update = $conn->query("UPDATE members SET isHere = 1 WHERE SmartRiderID = ?", $smartRider))


the second problem:
Even if I manually type the smartRider that is in the database, nothing gets updated.

Any help would really be appreciated.

Answer

If you refer to mysqli_query manual - you will see that second argument of this function is int $resultmode.

See - it is int. And it's purpose is to define a structure of returned result. So, passing your $smartRider to query() is useless.

query function doesn't work with prepared statements.

For prepared statements is prepare():

$stmt = $mysqli->prepare('UPDATE members SET isHere = 1 WHERE SmartRiderID = ?');
$stmt->bind_param('s', $smartRider);
$res = $stmt->execute();

For cheking result of execute() see http://php.net/manual/en/mysqli-stmt.execute.php.