<?php
select1($conn);
function select2 ($conn,$id ,$name)
{
$stmt = $conn->prepare("SELECT def FROM define WHERE id = ?");
mysqli_stmt_bind_param($stmt, 'i', $id);
$stmt->execute();
$stmt->bind_result($def);
while($stmt->fetch()) {
echo $def . “<br>”
}
$stmt->close();
}
function select1 ($conn){
$stmt2 = $conn->prepare("SELECT id , name FROM words");
$stmt2->execute();
$stmt2->bind_result($id, $name);
while($stmt2->fetch()) {
select2 ($conn,$id ,$name);
}
}
?>
I have 2 php functions to select data from database. each record in words has multiple records in define table which I need to select. The first function is working correctly. the problem is that the second function is not working.
As @u_mulder mentions, you have improper double quotes in use.
Correct:
$stmt = $conn->prepare("SELECT def FROM define WHERE id = ?");
Incorrect:
$stmt2 = $conn->prepare("SELECT id , name FROM words”);
The ” character is not the same as " character. This will cause an issue in your Query. See more: https://www.cl.cam.ac.uk/~mgk25/ucs/quotes.html
Cleaned up:
<?php
select1($conn);
function select2($conn, $id, $name){
$stmt = $conn->prepare("SELECT def FROM define WHERE id = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($def);
while($stmt->fetch()) {
echo $def . "<br>"
}
$stmt->close();
}
function select1 ($conn){
$stmt2 = $conn->prepare("SELECT id, name FROM words");
$stmt2->execute();
$stmt2->bind_result($id, $name);
while($stmt2->fetch()) {
select2($conn, $id, $name);
}
}
?>
Upon further inspection, I do not see where you use $id or $name, so I am not sure why you're performing 2 queries. I would advise a Join query.
<?php
function select1 ($conn){
$stmt = $conn->prepare("SELECT w.id, w.name, d.define FROM words AS w INNER JOIN define AS d ON w.id = d.id");
$stmt->execute();
$stmt->bind_result($id, $name, $def);
while($stmt->fetch()) {
echo "($id) $name $def<br />";
}
}
?>
Update
If you have table words:
+----+--------+
| id | name |
+----+--------+
| 1 | 'John' |
| 2 | 'Mary' |
| 3 | 'Bob' |
+----+--------+
And table define:
+----+--------------+
| id | def |
+----+--------------+
| 1 | 'Some stuff' |
| 1 | 'Other stuff'|
| 1 | 'More stuff' |
| 2 | 'Weird stuff'|
| 2 | 'Kind stuff' |
| 3 | 'Just stuff' |
+----+--------------+
We refer to this: What is the difference between "INNER JOIN" and "OUTER JOIN"?
The query could be a Regular Join:
SELECT a.id, a.name, b.def FROM word AS a JOIN define AS b ON a.id = b.id;
The result set should be:
+----+--------+--------------+
| id | name | def |
|----|--------|--------------|
| 1 | 'John' | 'Some stuff' |
| 1 | 'John' | 'Other stuff'|
| 1 | 'John' | 'More stuff' |
| 2 | 'Mary' | 'Weird stuff'|
| 2 | 'Mary' | 'Kind stuff' |
| 3 | 'Bob' | 'Just stuff' |
+----+--------+--------------+
Hope this helps explain your options.