NullHypothesis NullHypothesis - 2 months ago 7
MySQL Question

Multiple Queries and Closing Connections

I need to update a column in a few different tables with a very specific value (there are no parameters).

The code is something along the lines of:

public Function InitializeJob()
{
$stmt = $this->mysqli->prepare("Update `Table1` set `Job` = 'Init');
$stmt->execute();
$stmt->close();

$stmt = $this->mysqli->prepare("Update `Table2` set `Job` = 'Init');
$stmt->execute();
$stmt->close();

$stmt = $this->mysqli->prepare("Update `Table2` set `Job` = 'Init');
$stmt->execute();
$stmt->close();
}


I was wondering - should I close each connection like that, only to reopen it? Or should I just close it once at the end? $this->mysqli is simply a local variable in my repository that is a mysqli object.

What's the difference from a connection perspective from closing it 3 times, vs. closing it at the end? $this->mysqli will have a value so it's not like i'm reinitializing it (i'm only initializing it if it's null).

My guess is that it's better to only use one $stmt->close() at the very end?

Thanks, was just curious!

Answer

I think should be done this way

 public Function InitializeJob(){     

 $stmt = $this->mysqli->prepare("mysql statement 1");
 $stmt->execute();   
 $stmt->free_result();   
 $stmt->reset();
 $stmt = $this->mysqli->prepare("mysql statement 2");
 $stmt->execute();      
 $stmt->free_result();   
 $stmt->reset();

 $stmt = $this->mysqli->prepare("mysql statement 3");
 $stmt->execute(); 
 $stmt->free_result();   
 $stmt->reset();
 $stmt->close();
}

you do a free_result and reset before reusing the $stmt.