Posted by dumbledad@gmail.com on 12/09/05 00:09

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);
if($result)
{
$row = mysqli_fetch_assoc($result);
$newSearchId = $row['lastID'];
mysqli_free_result($result);

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.

mysqli_close($link);
$link = mysqli_connect($hostname_catalogueSQLConnection,
$username_catalogueSQLConnection, $password_catalogueSQLConnection,
$database_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.

Cheers,

Tim.

[Back to original message]