having 2 php function call

Question

<?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.

Out of curiosity, why are you mixing procedural and object-oriented mysqli_* calls in select2? — Chris Forrence, Apr 28 '17 at 17:35
we can't help you if we don't know the error you are getting. Please copy and paste the error. — Lekens, Apr 28 '17 at 17:56

score 1 · Accepted Answer · edited May 23 '17 at 12:02

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.

each id I get from table words I have for it multiple records in def table which also I want to retrieve — CS Student, Apr 28 '17 at 18:52
I am still a beginner can u show me an example please. incase that for each id i get from words table I retrieve all records that have the same id (as a foreign key) from def table. Thank you so much! — CS Student, Apr 28 '17 at 18:57

having 2 php function call

1 Answers1