php mysqli multiple queries using first query result

Question

I am trying to select results from DB.

I am tried multiple query and prepared also.

but, both of them are not working to me :(

$query = "SELECT u.id FROM user u;";
$stmt = $mysqli->prepare($query);
$stmt->execute();
$stmt->bind_result($id);

while($stmt->fetch()){
$query = "SELECT position FROM company WHERE id = $id";
$stmt2 = $mysqli->prepare($query);
$stmt2->execute(); // error HERE (line 16)
$stmt_send->bind_result($position);
while($stmt->fetch()){
    print $position;
}

Here is Error Code,

PHP Fatal error:  Call to a member function execute() on a non-object in /var/www/test.php on line 16

May the same problem is asked here, but I cannot find any solver with this problem.

Please excuse my searching skill and thank you in advance.

sorry about late updating.

as I learn from this problem.

STMT is must closed before open new one even they have different name.

I solved this saved the first result into array and close it.

then, open new one with the array.

I think it makes redundance loop to make array at the first.

As I learn this problem, Prepared MySqli is so good if you need the same query changing parameters. O/W, just use simple MySqli.

Let me know, I understood wrong way.

Thanks.

Prix · Accepted Answer · 2013-06-17 02:50:56Z

3

How about a single query ?

SELECT c.position FROM user u LEFT JOIN company c ON u.id = c.id

And here is sample:

$db = new mysqli($host,$user,$pass,$dbselect);
if($db->connect_error)
        die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

$result = $db->query("SELECT c.position FROM user u LEFT JOIN company c ON u.id = c.id");
if (!$result)
        die "Error: " . $db->error;

while ($row = $result->fetch_object())
{
    echo $row->position;
}
$result->close();
$db->close();

And in case you need to be able to point out the ID, replace this:

$result = $db->query("SELECT c.position FROM user u LEFT JOIN company c ON u.id = c.id");
if (!$result)
        die "Error: " . $db->error;

while ($row = $result->fetch_object())
{
    echo $row->position;
}

With this:

$stmt = $db->prepare("SELECT c.position FROM user u LEFT JOIN company c ON u.id = c.id WHERE u.id = ?");
$stmt->bind_param('i',$_POST['id']);

if (!$stmt->execute())
       die('Insert Error ' . $db->error);

$result = $stmt->get_result();
while ($row = $result->fetch_assoc())
{
    echo $row['position'] ;
}

Note the interrogation sign on the query and the bind_param, s means string and i means integer, you can read more here.

So i means we will 1 integer entry.

edited Jun 17, 2013 at 2:50

answered Jun 17, 2013 at 2:38

Prix

19.6k16 gold badges79 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joseph Over a year ago

Thanks you Prix, However, I simplified my question. In real problem, I have to connect DB several times with previous results.

Prix Over a year ago

@joseph Have you tried my first example ? from your question it should provide you with the same result.

joseph Over a year ago

I simplified my problem to make easy to post a question. sorry confused. as I tested, stmt is must closed before open new one. so I sore first result in array and close first one and open new stmt. it works. Thanks.

Prix Over a year ago

@joseph what you are doing is, you're using the id from the first query to find the results of the 2nd query which is what SELECT c.position FROM user u LEFT JOIN company c ON u.id = c.id does with a single query, hence the first example above will work just fine for you or so I believe.

Collectives™ on Stack Overflow

php mysqli multiple queries using first query result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related