PHP command line persistent connections

Question

I have a PHP script which works perfectly via Apache called through a browser, but the same code called on command line seems to drop the database connection after every call.

So for instance in an included file I have:

$pdo = new PDO('mysql:host=' . HOST . ';dbname=' . DB, USER, PASS, array(PDO::ATTR_PERSISTENT => true));

Then in my script I have:

$stmt = $pdo->prepare('SELECT intGroupID FROM tblquestiongroups WHERE dtDeleted IS NOT NULL ORDER BY RAND()');
$stmt->execute();
$something = $stmt->fetch(PDO::FETCH_ASSOC);

Which works fine, however directly afterwards I have:

$stmt = $pdo->prepare('SELECT intSurveyID FROM tblquestiongroups WHERE tblquestiongroups.intGroupID = :intQuestionId');
$stmt->bindValue(':intQuestionId', $intQuestionId); 
$stmt->execute();

Which doesn't and returns:

Call to member function bindValue() on a non-object

Now if I add a new connection, i.e. copy and paste the one in the include file above the second call it all works fine again, i.e.:

$pdo = new PDO('mysql:host=' . HOST . ';dbname=' . DB, USER, PASS, array(PDO::ATTR_PERSISTENT => true));
$stmt = $pdo->prepare('SELECT intSurveyID FROM tblquestiongroups WHERE tblquestiongroups.intGroupID = :intQuestionId');
$stmt->bindValue(':intQuestionId', $intQuestionId); 
$stmt->execute();

My first question is why won't PHP keep the connection open for the period of the script?

So onto my second question. As a test I went through and added the connection before all of the calls to database via PDO. Within this script I actually connect to two different servers and as such I have another connection defined which looks like this:

$pdoLocal = new PDO('mysql:host=' . HOST_LOCAL . ';dbname=' . DB_LOCAL, USER_LOCAL, PASS_LOCAL, array(PDO::ATTR_PERSISTENT => true));

So of course to try and get the thing working I have added this line above any calls to the local database. However with this code:

$pdoLocal = new PDO('mysql:host=' . HOST_LOCAL . ';dbname=' . DB_LOCAL, USER_LOCAL, PASS_LOCAL, array(PDO::ATTR_PERSISTENT => true));   
$pdoLocal->beginTransaction();
$stmtInsert = $pdoLocal->prepare('INSERT INTO tblresponses_string (strResponses, intSurveyID) VALUES (:strResponses, :intSurveyID)');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $stmtInsert->bindValue(':strResponses', $row['strResponses']);  
    $stmtInsert->bindValue(':intSurveyID', $surveyID);  
    $stmtInsert->execute();

}
$pdoLocal->commit();

I get the same error on the first bind.

I guess this is the same problem in that the first statement to get executed is beginTransaction and the PDO connection closes afterwards.

As mentioned this all works fine through Apache.

All help gratefully received.

Answer 1

Your speculations are wrong.

If PHP were indeed dropping a connection, you'd had an error not on bindValue call but on the line where the very PDO connection is used, so, the error would be

Call to member function prepare() on a non-object

So, the problem is not with connection but with query. Set PDO in error mode:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

then see the error message and then either fix it or ask another question regarding this particular error.