Php does not execute sql query and time out

Question

I'm trying to delete some rows of 'occupation_data' (table) but there are a foreign constraint, so I did a small php script to delete the data linked in the others tables and after delete it in occupation_data.

When I run the script I see a loading in browser but nothing appear, what tools should I use to debug this?

Thank you

Goldiman

Here my code:

<?php
error_reporting(E_ALL);
set_time_limit(60000); // There are more than 30 tables and 380 primary key to delete, may take time
ini_set("display_errors", 1);


$tupleasup = array(
    '13-1199.05',
    '13-1023.00',
    '13-1022.00',
    '53-6031.00'
); //Contain the primary key of the row


$table = array(
    'abilities',
    'education_training_experience',
    'green_occupations',
    'occupation_data'
);

try {
    $VALEUR_hote         = '**********';
    $VALEUR_port         = '******';
    $VALEUR_nom_bd       = '********';
    $VALEUR_user         = '*******';
    $VALEUR_mot_de_passe = '*******'; //Working connection setting

    $connexion = new PDO('mysql:host=' . $VALEUR_hote . ';port=' . $VALEUR_port . ';dbname=' . $VALEUR_nom_bd, $VALEUR_user, $VALEUR_mot_de_passe);
    $connexion->exec("SET NAMES 'UTF8'");
    $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "toto"; // This message is not displayed

    foreach ($tupleasup as $codeOnet) {

        foreach ($table as $nomTable) {
            $query     = "DELETE FROM " . $nomTable . " WHERE onetsoc_code =" . $codeOnet;
            $resultats = $connexion->query($query);
        }
        echo "Supprimé" . $codeOnet; // This message is not displayed too.

    }
}
catch (PDOException $e) {
    echo 'Échec lors de la connexion : ' . $e->getMessage();
}



?>

Answer 1

This code should work :

<?php
error_reporting(E_ALL);
set_time_limit(60000); // There are more than 30 tables and 380 primary key to delete, may take time
ini_set("display_errors", 1);


$tupleasup = array(
    '13-1199.05',
    '13-1023.00',
    '13-1022.00',
    '53-6031.00'
); //Contain the primary key of the row


$table = array(
    'abilities',
    'education_training_experience',
    'green_occupations',
    'occupation_data'
);

try {
    $VALEUR_hote         = '**********';
    $VALEUR_port         = '******';
    $VALEUR_nom_bd       = '********';
    $VALEUR_user         = '*******';
    $VALEUR_mot_de_passe = '*******'; //Working connection setting

    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $pdo_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8';
    $connexion = new PDO('mysql:host=' . $VALEUR_hote . ';port=' . $VALEUR_port . ';dbname=' . $VALEUR_nom_bd, $VALEUR_user, $VALEUR_mot_de_passe, $pdo_options);

    echo "toto"; // This message should be displayed

    $codes = "(";
    $i = 0;
    foreach($tupleasup as $code){
        if($i > 0)
            $codes .= ", ";
        $codes .= "'".$code."'";
        $i++;
    }
    $codes .= ")";

    foreach ($table as $nomTable) {
        $req = $connexion->prepare("DELETE FROM ".$t." WHERE onetsoc_code IN (:datas)")
        $req->bindParam("datas", $codes, PDO::PARAM_INT);
        $req->execute();
    }
}
catch (PDOException $e) {
    echo 'Échec lors de la connexion : ' . $e->getMessage();
}

If you don't have output log, check your PDO connection and your credentials. If you don't have any logs, change your default php.ini log errors variable.

You seems to really want to use a double foreach for delete the rows. It's a bad practice and you should avoid it, by the way you could do something like :

    foreach($tupleasup as $code){
        foreach ($table as $nomTable) {
            $req = $connexion->prepare("DELETE FROM ".$nomTable." WHERE onetsoc_code = :code");
            $req->bindParam("code", $code, PDO::PARAM_STR);
            $req->execute();
            echo "Onet: ".$code." > Table: ".$nomTable." DELETED\n";
        }
    }

Answer 2

I Hope I can help you out, besides the PDOException the PDO class posses the errorcode and errorinfo wich you should log or print to debug when using PDO

ERRORCODE AND ERRORINFO MANUALS

they should be called after each Query method call to debug, I usually do it by making a log class name LOG that writes to a file trough a static method call "logIt", this way I can make calls like LOG::LogIt(serialize($conexion->errorInfo()));

My logging class:

Class LOG 
{
    public static function LogIt($mensaje, $tipo_usuario=false)
    {
        $fichero    = '../application.log';
        $maxFichero = 4096000; 
        $logMensaje = '';
        $size       = filesize($fichero);
        $usuario    = isset($_SESSION['USUARIO'])?$_SESSION['USUARIO']:'';

        # de momento se registrará el ID de RED del usuario.
        if($tipo_usuario==false){
            $logMensaje = '['.date('Y-m-d H:i:s').']['.$usuario.'] '.$mensaje.PHP_EOL;
        }

        if(($size != false) && ($size>$maxFichero))
        {
            # Borramos el Fichero Actual
            unlink($fichero);
            # En el futuro Muevo el fichero actual a otro nombre
        }

        $escritor = fopen($fichero, 'a');
        fwrite($escritor, $logMensaje);
        fclose($escritor);
    }

Answer 3

I think you need to rewrite your PDO connection. You can whether use this:

$connexion = new PDO(sprintf('mysql:host=%s;port=%s;dbname=%s', $VALEUR_hote, $VALEUR_port, $VALEUR_nom_bd), $VALEUR_user, $VALEUR_mot_de_passe);

or this (notice: with double quotes):

$connexion = new PDO("mysql:host=$VALEUR_hote;port=$VALEUR_port;dbname=$VALEUR_nom_bd", $VALEUR_user, $VALEUR_mot_de_passe);

Answer 4

Just to debug and check if connection is ok transform your code to:

try {
    $VALEUR_hote         = '**********';
    $VALEUR_port         = '******';
    $VALEUR_nom_bd       = '********';
    $VALEUR_user         = '*******';
    $VALEUR_mot_de_passe = '*******'; //Working connection setting

    $connexion = new PDO('mysql:host=' . $VALEUR_hote . ';port=' . $VALEUR_port . ';dbname=' . $VALEUR_nom_bd, $VALEUR_user, $VALEUR_mot_de_passe);
}
catch (PDOException $e) {
    echo 'Échec lors de la connexion : ' . $e->getMessage();
    $connexion = false;
}

if ($connexion != false) {
  $connexion->exec("SET NAMES 'UTF8'");
  echo "toto"; // This message is not displayed
  foreach ($tupleasup as $codeOnet) {

    foreach ($table as $nomTable) {
        $query     = "DELETE FROM `$nomTable` WHERE onetsoc_code = :code";
        $sth = $connexion->prepare($query);
        $sth->bindParam(':code',$codeOnet);
        if (! $sth->execute() ) {
          $arr = $sth->errorInfo();
          print_r($arr);
        }
    }
    echo "Supprimé" . $codeOnet; // This message is not displayed too.

}