I'm trying to switch to prepared statements because of the SQL-injection protection that they offer, but I am currently stuck with this.
How do you make a prepared statement query, using data from another one as params?
What I mean is this: If i have a table with people, and another one with their devices, and I want to list all people with all their devices. How do I do that?
What I want to achive is something like this:
Alex: Pc, Laptop, Mobile
Annie: Laptop, Mobile
Danny: Mobile, Printer
What I have written so far is this:
$people_query = "SELECT id, name FROM people";
$device_query = "SELECT device_name FROM devices WHERE owner_id = ?";
if(!$pplstmt = $mysqli -> prepare($people_query)) die("Couldn't prepare people statement!");
if(!$devstmt = $mysqli -> prepare($device_query)) die("Couldn't prepare device statement!");
$pplstmt -> execute();
$pplstmt -> bind_result($person_id, $person_name);
while($pplstmt->fetch()){
echo $person_name . ':';
$devstmt -> bind_param("i", $person_id);
$devstmt -> execute();
$devstmt -> bind_result($device_name);
while($devstmt->fetch()){
echo $device_name . ',';
}
}
$pplstmt -> close();
$devstmt -> close();
I have moved the prepare statement of the second query out of the loop, because I read that this way it won't initialize again, and again, and again...
This however doesn't work. Any ideas, or advices?