Query with WHERE clause not working using mysqli bind_param()

Question

I am creating a webservice where I need to get some data from mysql and retrieve it in a mobile app.

I have been trying all day to do a query with a WHERE statement, but no success.

The normal query, without the WHERE is working perfectly though.

Also I went through all the similar questions on stack overflow and other forums, but none is the same problem as mine.

This is my current code:

<?php

$con=mysqli_connect("Hidden credentials");

if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if ($stmt = $con->prepare("SELECT 
team.id as teamId,
team.team_name as teamName, 
user.id as userId, 
user.username as username, 
team_members.role as role

FROM team_members 
inner join user on team_members.user_id = user.id 
inner join team on team_members.team_id = team.id 
where user.id = ?
")) { 
   $stmt->bind_param("i", $id); 
   $stmt->execute(); 
   $result = $stmt->get_result();

$resultArray = array();
 $tempArray = array();

 while($row = $result->fetch_object())
 {
    $tempArray = $row;
    array_push($resultArray, $tempArray);
 }

 echo json_encode($resultArray);

 $stmt->close();

}
?>

If I remove the line where user.id=? it gets the data correctly.

Running the query on phpMyAdmin is everything ok, so it's not any issue on the query.

What is the data type of `id` column? – Anurag Srivastava Mar 09 '20 at 07:48 — Anurag Srivastava, Mar 09 '20 at 07:48
How have you defined your `$id` – Omari Victor Omosa Mar 09 '20 at 07:51 — Omari Victor Omosa, Mar 09 '20 at 07:51

score 4 · Answer 1 · answered Mar 09 '20 at 08:22

4

The variable $id doesn't exist (in your code). If you want to set the variable dynamically you can use $id = $_GET['id'];. This will take the value from the url and put it in the variable!

answered Mar 09 '20 at 08:22

Marty1452 · Accepted Answer · 2020-03-09T09:00:49.743

1

Your QUERY doesn't work because your variable id doesn't exists (in code what you showing).

Fix: create variable id and put some data to this variable.

For example:

$id = 5;

Or dynamcially:

From URL with GET method:

$id = $_GET['id'];

this allows you to get parameter from URL. But you must set this parameter by link. For example: <a href="index.php?id=5">. By clicking on this a tag you will be redirected to page index.php with parameter id which equals to 5.

From POST method:

for example you have this FORM:

<form method="post">
    <input type="number" name="id">
    <input type="submit" name="submit">
</form>

after submiting this FORM values will be saved in $_POST. You can access them by $_POST["name"]. In this case $_POST["id"].

$id = $_POST["id"];

From SESSION:

$id = $_SESSION["id"];

but first you have define $_SESSION["id"]. You can access this variable ($_SESSION["id"]) in other pages of your domain.

edited Mar 09 '20 at 09:00

answered Mar 09 '20 at 08:07

Marty1452

430
5
19

Thanks for your reply Marty, I create the variable now, but it is working only hardcoded, how can I set it dynamically in the url? I am doing like this now: http://taskapp.000webhostapp.com/restapi/getusersteams.php?id=2 – Leonardo D'Amato Mar 09 '20 at 08:15
@LeonardoD'Amato if you don’t know about such basics, then you should go research them. “php get parameter from url” or similar typed into Google, leads to plenty of explanations in no time. https://stackoverflow.com/questions/5884807/get-url-parameter-in-php – CBroe Mar 09 '20 at 08:25
@LeonardoD'Amato I edited my answer. Take a look. – Marty1452 Mar 09 '20 at 09:01

Query with WHERE clause not working using mysqli bind_param()

2 Answers2