PHP Prepared Statement I Am Curious To Know Why This Doesnt Work

Question

First and foremost I have to say I am loving PHP. This is too much fun. So here is my question, I am working with prepared statements and the following works

<?php
$qm = $con->prepare("SELECT id, receiving_user_id, send_date, message_title, message_body, senders_name FROM Messages WHERE receiving_user_id=? ORDER by id");
/* execute statement */
$qm ->bind_param('i',$userid);
$qm->execute();
/* bind result variables */
$qm->bind_result($messid, $uid, $sent, $title, $body, $dudesname);
/* fetch values */
while ($qm->fetch()) {
    echo ($dudesname. $title." ");
}
/* close statement */
$qm->close();
/* close connection */
$sql_e = $con-> prepare("SELECT id FROM Messages WHERE receiving_user_id=?");
$sql_e -> bind_param('i', $userid);
$sql_e -> execute();
$userCheck = $sql_e -> get_result();
$sql_e -> close();
if ($userCheck -> num_rows == 0) {
    echo "No Messages... :(";
}
?>

However, since the $sql_e statement and the $qm statement do the exact same thing I was thinking that I could put the following and it would work. But it does not.

$qm = $con->prepare("SELECT id, receiving_user_id, send_date, message_title, message_body, senders_name FROM Messages WHERE receiving_user_id=? ORDER by id");
    /* execute statement */
    $qm ->bind_param('i',$userid);
    $qm->execute();
    /* bind result variables */
    $qm->bind_result($messid, $uid, $sent, $title, $body, $dudesname);
    /* fetch values */
    while ($qm->fetch()) {
        echo ($dudesname. $title." ");
    }
    /* close statement */
    $qm->close();
    /* close connection */

    $qm -> execute();
    $userCheck = $qm-> get_result();
    $qm -> close();
    if ($userCheck -> num_rows == 0) {
        echo "No Messages... :(";
    }

Arent Prepared statements made to type in the same thing such as

$qm -> execute();
// Different Variables Here
$qm -> close();

$qm -> execute();
// Yet Another Set of Variables Here
$qm -> close();

Its weird. Any input on this would be greatly appreciated. I am new to PHP development and am trying to learn everything that I can.

EDIT TO CODE TO GET IT WORKING WITH $stmt reset();

$qm = $con->prepare("SELECT id, receiving_user_id, send_date, message_title, message_body, senders_name FROM Messages WHERE receiving_user_id=? ORDER by id");
/* execute statement */
$qm ->bind_param('i',$userid);
$qm->execute();
/* bind result variables */
$qm->bind_result($messid, $uid, $sent, $title, $body, $dudesname);
/* fetch values */
while ($qm->fetch()) {
    echo ($dudesname. $title." ");
}
/* reset statement */
$qm->reset();

$qm -> execute();
$userCheck = $qm -> get_result();
if ($userCheck -> num_rows == 0) {
    echo "No Messages... :(";
}

/* close statement */
$qm -> close();

user3783243 · Accepted Answer · 2018-06-01T12:15:30.540

0

You shouldn't need to execute the query twice. I think this is what you want:

$qm = $con->prepare("SELECT id, receiving_user_id, send_date, message_title, message_body, senders_name FROM Messages WHERE receiving_user_id=? ORDER by id");
/* execute statement */
$qm ->bind_param('i',$userid);
$qm->execute();
/* bind result variables */
$qm->bind_result($messid, $uid, $sent, $title, $body, $dudesname);
/* fetch values */
$userCheck = $qm->get_result();
if ($userCheck->num_rows > 0) {
     while ($userCheck->fetch()) {
         echo $dudesname. $title . " ";
     }
} else {
     echo "No Messages... :(";
}
/* close statement */
$qm -> close();

Your current code was closing the connection to early.

edited Jun 01 '18 at 12:15

answered Jun 01 '18 at 10:51

user3783243

4,418
5
14
34

Hey, so it is coming up as a white screen. I know this may sound rookie'ish but how do I check for errors? I know I use an 'or die' statement but am a bit confused on where to use it with prepared statements. BTW thank you for all the help provided so far and regardless of whether we get it or not using your above code I am still marking it as answered because you did help me answer my original question. – Jun 01 '18 at 14:22
For mysqli errors see https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments, for PHP errors in general see https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display. The white screen sounds like a syntax error. – user3783243 Jun 01 '18 at 14:55

PHP Prepared Statement I Am Curious To Know Why This Doesnt Work

1 Answers1