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();
}



?>

Alex · Accepted Answer · 2015-04-29 15:16:34Z

1

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.

}

answered Apr 29, 2015 at 15:16

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goldiman Over a year ago

Work like a charm ! Thank you , but can you explain me what did I do wrong ? :)

DiegoCoderPlus · Accepted Answer · 2015-04-30 07:14:47Z

1

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);
    }

edited Apr 30, 2015 at 7:14

answered Apr 29, 2015 at 15:18

DiegoCoderPlus

7706 silver badges15 bronze badges

3 Comments

goldiman Over a year ago

Hello, thank you I tried your method which look interesting but I couldn't get any message in notepad file, except this: [2015-04-30 08:27:41][] a:3:{i:0;s:5:"00000";i:1;N;i:2;N;} [2015-04-30 08:27:41][] a:3:{i:0;s:5:"00000";i:1;N;i:2;N;} [2015-04-30 08:27:41][] a:3:{i:0;s:5:"00000";i:1;N;i:2;N;}

DiegoCoderPlus Over a year ago

Hey @goldiman, I made a couple of arrangements which you may already have done, the result your are having should be the serialized form of the errorInfo Array, try it with a none working query, and tell me what you get.

DiegoCoderPlus Over a year ago

@goldiman you could also use json_encode instead of serialize to translate into string the errorInfo object/array

Spoke44 · Accepted Answer · 2015-04-30 08:48:42Z

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";
        }
    }

edited Apr 30, 2015 at 8:48

answered Apr 29, 2015 at 15:15

Spoke44

98810 silver badges25 bronze badges

2 Comments

goldiman Over a year ago

Hi, thank you, , I did some changes in your script : hastebin.com/vubayosufi.sm and i'm able to display the rows that i'm going to delete but they don't delete themselves in the database :(

Spoke44 Over a year ago

En même temps tu as modifié toute la logique de mon code concernant le IN dans la requête. Ton code n'a plus aucun sens. You should build the $codes variable before using it in the query. If you "really" want to use a double foreach (bad performances for query), see my edit.

hatef · Accepted Answer · 2015-04-29 15:22:25Z

0

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);

answered Apr 29, 2015 at 15:22

hatef

6,29930 gold badges47 silver badges48 bronze badges

1 Comment

goldiman Over a year ago

Even if you connection is better than my mine :) , that's not my problem right now, but thank you anyway !

Collectives™ on Stack Overflow

Php does not execute sql query and time out

4 Answers 4

1 Comment

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related