Using same MySql Connection For Two Prepared Statements

Question

So I've got problem with my PHP code where I'm trying to run two prepared statements which have been 'prepare'd with the same $mysqli object.

        require 'database.php';
        $mysqli = new mysqli($database_host, $database_user, $database_pass, $database_name);

        /*First check if there is already a preference record for this user
          with this particular course.*/
        $query = "SELECT * FROM timetablePrefs WHERE username=? AND courseID=?";
        $stmt = $mysqli->prepare($query);
        foreach ($courseList as $key => $value)
        {
            $stmt->bind_param("ss", $username, $key);
            $stmt->execute();
            if (($stmt->num_rows) === 1)
                continue;
            else
            {
                $query = "INSERT INTO timetablePrefs (username, courseID, hexColour, hidden) ";
                $query .= "VALUES (?, ?, ?, 0)";
                $prepared = $mysqli->prepare($query);
                $prepared->bind_param("sss", $username, $key, "#FFFFFF");
                if (!($prepared->execute()))
                    print("Sorry couldnt change your colour preferences.");
                $prepared->close();
            }
        }
        $stmt->close();
        $mysqli->close();

I may be missing something here; can you even run two of these at the same time? Any help would be greatly appreciated!Any questions please ask ;)

The error I receive is with regards to the line; $prepared->bind_param("sss", $username, $key, "#FFFFFF"); Fatal error: Call to a member function bind_param() on a non-object

Ahh sorry that would help. The error I get is 'PHP Fatal error: Call to a member function bind_param() on a non-object' in regards to the $prepared->bind_param("sss", $username, $key, "#FFFFFF'); line — David S, Apr 27 '14 at 17:38

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

To get the actual error
To solve it. From the error you can tell that there are results from the previous query pending. Need to store them

edited May 23 '17 at 10:32

Community

1
1

answered Apr 27 '14 at 17:43

Your Common Sense

156,878
40
214
345

Thanks for getting back so quickly. Don't suppose you have a better explanation of the store_result function... The PHP documentation isn't too detailed as to the why. – David S Apr 27 '14 at 17:50

Waqas Muhammad · Answer 2 · 2021-03-26T13:41:25.383

-2

I was doing my thing the procedural way but also having same kind of issue.So I created two different DB connection variables (just a hack not proper solution) and instead of doing:

stmt_pnl = mysqli_stmt_init($conn);
stmt_bank = mysqli_stmt_init($conn);

I did:

stmt_pnl = mysqli_stmt_init($conn);
stmt_bank = mysqli_stmt_init($conn_bank);

And the error gone and it worked.

Edit: The above answer was just a hack to solve the problem not an actual solution. Proper way to solve this error is to follow below sequence:

Prepare Statement (initialize and bind parameters)
Execute Statement
Get results (mysqli_stmt_get_result)
Close Statement.

(special thanks to Your Common Sense)

edited Mar 26 '21 at 13:41

answered Mar 24 '21 at 11:05

Waqas Muhammad

11
3

Obviously you can and your answer is wrong – Your Common Sense Mar 24 '21 at 12:11
Thank you very much for your kind response. Do you mean when using prepared statements in procedural way, we can prepare two statements in the same script using the same connection variable? – Waqas Muhammad Mar 24 '21 at 13:43
1

Yes of course. Did you notice by chance my answer below? – Your Common Sense Mar 24 '21 at 13:44
This answer is very misleading. You can prepare as many statements as you want using a single connection. Opening a new connection each time is a terrible hack that prevents you from using transactions. – Dharman Mar 25 '21 at 10:46
@Dharman plz consider the edited portion. – Waqas Muhammad Mar 26 '21 at 10:04
I'm sorry but even that addition is not 100% correct. I just don't agree with the whole solution you are proposing. – Dharman Mar 26 '21 at 10:31
@Dharman plz visit this link that i used as source http://www.php.net/manual/en/mysqli-stmt.store-result.php – Waqas Muhammad Mar 26 '21 at 11:18

Using same MySql Connection For Two Prepared Statements

2 Answers2