mysqli, stored procedures, and connection requiring close and reopen?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Hi All,

I'm having trouble moving from a query that calls a MySQL 5 stored
procedure to a query that calls a simple SQL INSERT. Here's the
relevant part of the code that calls the stored procedure:

$query = sprintf( "CALL AddSearchTerm('%s', %d)",
$massaged_search_term, count($_SERVER['postId_array']) );
$result = mysqli_query($link, $query);
   $row = mysqli_fetch_assoc($result);
   $newSearchId = $row['lastID'];

Next I need to call a sequence of INSERT statements:

   foreach ($_SERVER['categoriesSelected'] as $current)
      $query = sprintf("INSERT INTO categories_browsed (search_id,
category_id) VALUES (%d, %d)", $newSearchId, $current);
      $result = mysqli_real_query($link, $query);

But I get the error: "Fatal error: Lost connection to MySQL server
during query" and a line number pointing to that last line, the
mysqli_real_query (it's the same with mysqli_query).

However, if I insert this code after the first fragment I've given,
then it works fine.

   $link = mysqli_connect($hostname_catalogueSQLConnection,
$username_catalogueSQLConnection, $password_catalogueSQLConnection,

Why should the link need closing between a call to a stored procedure
and a call to INSERT? Surely there is some less time consuming clean-up
I can do rather closing and reopening the connection to the MySQL
server. Why isn't the call to mysqli_free_result() enough?

Thanks in advance for any insights and help.



Site Timeline