Two MySQL queries in one PHP page - error "Trying to get property of non-object"

Question

This is probably very, very easy, but how do I execute two queries in the same PHP page?

I'm trying to do this...

<?PHP
error_reporting(E_ALL);
ini_set('display_errors', '1');
include 'db.php';

// FIRST QUERY;

$intCompanyID = 2612;
$sql =  "CALL sp_get_company ($intCompanyID)";
$result = $connect->query($sql);
if ($result->num_rows > 0) :
    $row = mysqli_fetch_assoc($result);
    $company_name = $row["company_name"];
    $contact_TEL = $row["contact_TEL"];
    echo "company_name=".$company_name."<br />";
    echo "contact_TEL=".$contact_TEL."<br />";
endif;

//SECOND QUERY;

$sql =  "CALL sp_list_by_region (1)";
$result = $connect->query($sql);
if ($result->num_rows > 0):
    while($row = $result->fetch_assoc()) {
    echo "company_name = ".$row["company_name"]."<br />";
}
endif;
$connect->close();
?>

The first query works on its own. The second query works on its own. But when I run both I get the error "Trying to get property of non-object". Obviously I'm not reusing the connection properly. Can someone explain it to me?

Many thanks in advance.

@aynber has to be `$result->num_rows > 0`, right? Oh, but there are two of those, I see. — Don't Panic, Jan 27 '20 at 19:08
@Jon is your mysqli connection set up to convert mysql errors to php exceptions? (Like this: https://stackoverflow.com/a/22662582/2734189) — Don't Panic, Jan 27 '20 at 19:15
Looks as issue: https://www.php.net/manual/en/mysqli.query.php#102904 See answer here: https://stackoverflow.com/questions/4997601/php-commands-out-of-sync-if-i-mysqliquery-again-after-a-call-to-a-results — Slava Rozhnev, Jan 27 '20 at 21:16
@aynber Yes, the error is line 23 if ($result->num_rows > 0): — Jon Ewing, Jan 28 '20 at 08:41
@Don't Panic You were correct about the line that was causing the error. On your recommendation I added the line of code to transfer MySQL errors into PHP exceptions and now get the error "Commands out of sync; you can't run this command now". — Jon Ewing, Jan 28 '20 at 08:46
@Slava Rozhev I think you may well be right. I'll investigate this further. — Jon Ewing, Jan 28 '20 at 08:47
@SlavaRozhnev That was exactly what I needed. Thanks. To be clear I just needed to add $result->close(); $connect->next_result(); between the first and second query. I should have guessed this would be something to do with using stored procedures. — Jon Ewing, Jan 28 '20 at 08:55

score 0 · Answer 1 · answered Jan 28 '20 at 08:58

The problem here was due to using two stored procedures in one page rather than just any two MySQL queries. The solution was to add these two lines between the first and second query:

$result->close();
$connect->next_result();

Thanks to @SlavaRozhev and apologies if I'm not following the correct etiquette here - I want to make sure to give credit where it's due.

Two MySQL queries in one PHP page - error "Trying to get property of non-object"

1 Answers1