Why is my Prepared Statement not working?


#1

Hello community,

I have trouble to read my database and tried a lot of things the last hours.
When I use the following GET-Message in my browsers address bar:

http://127.0.0.1/SelectUserDetailsFacebookUser.php?UserID=8368845343374843

I get the following result:

83688453433748430 results

I don’t understand why it returns 0 results all the time.

Instead of
bind_param(‘s’, $userID);
I also tried
bind_param(“s”, $userID);

I also checked the database entry and tried to use the TRIM Function in the case there is a blank space in the user_id. Unfortunately, with no success.

Have you any ideas?

Thank you so much in advance!

Greetings, Christopher.

Here is my code:

38

<?php $mysqli = new mysqli("127.0.0.1", "root", "", "digitalcoursedb"); if (mysqli_connect_error()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } else { if (isset($_GET['UserID'])) { $userID = (string) $_GET['UserID']; echo $userID; $stmt = $mysqli->prepare("SELECT * FROM users WHERE user_id = ?"); $stmt->bind_param('s', $userID); $stmt->execute(); if ($stmt->num_rows > 0) { echo json_encode($results); while ($row = $stmt->fetch()) { print_r($row); } echo json_encode($userDetails); } else { echo "0 results"; } } } $mysqli->close(); ?>

#2

You need to switch to the much simpler php PDO extension.

The php mysqli extension is overly complicated and inconsistent.For example, you cannot use num_rows without first calling store_result() and you cannot fetch data without first calling either bind_result() or get_result() (which many not be available and therefore should not be used since it results in non-portable code.)


#3

Thank you so much!! It is working now :smiley:

Here is my code:

51


#4

If you aren’t adding any custom exception handling then you don’t really need the try/catch in your code. If you were doing anything special then you might want to have that include all your db code (including queries etc).

You don’t really have to set UserID to a new variable, so you can simplify your code by removing those lines and doing

$stmt->execute([$_GET['UserID']);

I assume you do not have to loop over rows from the db as selecting by id normally should only give one row (or none).

so you can just do this

$stmt->execute([$_GET['UserID']);
$user = $stmt->fetch();

if (!$user) {
  // do something when no user were found
}

If you were selecting multiple you could just do this

$stmt->execute();
$users = $stmt->fetchAll();

foreach ($users as $user) {
  // do something with each user
}

#5

You are absolutely right. :slight_smile:
I implemented the points you have mentioned.
PDO works much better than MySQLi and your infos helped me a lot.
I will recognize them in the future. Thank you! :blush: