MySQL query returns no results with PDO

Question

I have an issue with PDO and MySQL. When I send that query to MySQL through phpMyAdmin, it returns 490 rows :

SELECT art.AR_Ref AS AR_Ref, art.AR_Design AS AR_Design, art.AR_PrixVen AS AR_PrixVen, art.AR_CodeBarre AS AR_CodeBarre, af.CT_Num AS CT_Num, af.AF_RefFourniss AS AF_RefFourniss, ct.CT_Intitule AS CT_Intitule, art.CL_No1, art.CL_No2, art.CL_No3, art.CL_No4, MATCH (art.indexation) AGAINST ("+:iso*") AS cpt, art.AR_Design REGEXP "^:iso*" AS commencepar, art.AR_Design REGEXP ".+:iso.*" AS contient
FROM f_article art
    LEFT JOIN f_artfourniss af ON (art.AR_Ref = af.AR_Ref AND af.AF_Principal = 1)
    LEFT JOIN f_comptet ct ON (af.CT_Num = ct.CT_Num AND ct.CT_Type = 1)
WHERE art.AR_Sommeil = '0' AND MATCH (art.indexation) AGAINST (("+:iso*") IN BOOLEAN MODE)
ORDER BY commencepar DESC, contient DESC, cpt DESC, art.AR_Ref

But with that code, there are no results at all :

<?php
$dbCon = new PDO('mysql:host=SERVER_IP; port=3306; dbname=DATABASE',
    'root',
    'PASSWORD',
    array(PDO::ATTR_PERSISTENT => true,
          PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING,
          PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

$sql = 'SELECT art.AR_Ref AS AR_Ref, art.AR_Design AS AR_Design, art.AR_PrixVen AS AR_PrixVen, art.AR_CodeBarre AS AR_CodeBarre, af.CT_Num AS CT_Num, af.AF_RefFourniss AS AF_RefFourniss, ct.CT_Intitule AS CT_Intitule, art.CL_No1, art.CL_No2, art.CL_No3, art.CL_No4, MATCH (art.indexation) AGAINST ("+:saisie1*") AS cpt, art.AR_Design REGEXP "^:saisie2*" AS commencepar, art.AR_Design REGEXP ".+:saisie3.*" AS contient FROM f_article art LEFT JOIN f_artfourniss af ON (art.AR_Ref = af.AR_Ref AND af.AF_Principal = 1) LEFT JOIN f_comptet ct ON (af.CT_Num = ct.CT_Num AND ct.CT_Type = 1) WHERE MATCH (art.indexation) AGAINST (("+:saisie4*") IN BOOLEAN MODE) ORDER BY commencepar DESC, contient DESC, cpt DESC, art.AR_Ref';
$stmt = $dbCon->prepare($sql);
$saisie = 'iso';
$stmt->bindParam(':saisie1', $saisie, PDO::PARAM_STR);
$stmt->bindParam(':saisie2', $saisie, PDO::PARAM_STR);
$stmt->bindParam(':saisie3', $saisie, PDO::PARAM_STR);
$stmt->bindParam(':saisie4', $saisie, PDO::PARAM_STR);
$stmt->execute();
if (($stmt->errorCode() != '00000')){
    var_dump($stmt->errorInfo());
} else {
    while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        var_dump($row);
    }
    $stmt->closeCursor();
}
$dbCon = null;
?>

What could be the problem?

You should try formatting your SQL queries so people can actually read them instead of 500 character long single lines. — Mike, Jan 03 '17 at 20:28
There is no error or exception. I even activate the MySQL log. It simply does not return any results. — T'lash, Jan 03 '17 at 21:56
@Jay This is not the same problem as the other since it was a typo problem in the table name. Please do not mark questions as duplicated too quickly. — T'lash, Jan 03 '17 at 22:43
Mark it too quickly? Until your comment here we could not have possibly known you had a typo in the table name. — Jay Blanchard, Jan 04 '17 at 12:58
@JayBlanchard Not me, but the question you link to that one. I still don't know where is my problem. — T'lash, Jan 04 '17 at 18:31
It is about how you're formatting your variables in the SQL statement. You should do the formatting in the `bindParam()` for each variable where the placeholder replaces the whole variable and any conditions surrounding it. — Jay Blanchard, Jan 04 '17 at 18:40

score 0 · Accepted Answer · answered Jan 04 '17 at 21:47

Thanks to Jay Blanchard,

It is about how you're formatting your variables in the SQL statement. You should do the formatting in the bindParam() for each variable where the placeholder replaces the whole variable and any conditions surrounding it.

I found the solution :

<?php
$dbCon = new PDO('mysql:host=SERVER_IP; port=3306; dbname=DATABASE',
    'root',
    'PASSWORD',
    array(PDO::ATTR_PERSISTENT => true,
          PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING,
          PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

$sql = 'SELECT art.AR_Ref AS AR_Ref, '
            . 'art.AR_Design AS AR_Design, '
            . 'art.AR_PrixVen AS AR_PrixVen, '
            . 'art.AR_CodeBarre AS AR_CodeBarre, '
            . 'af.CT_Num AS CT_Num, '
            . 'af.AF_RefFourniss AS AF_RefFourniss, '
            . 'ct.CT_Intitule AS CT_Intitule, '
            . 'art.CL_No1, '
            . 'art.CL_No2, '
            . 'art.CL_No3, '
            . 'art.CL_No4, '
            . 'MATCH (art.indexation) AGAINST (:saisie1) AS cpt, '
            . 'art.AR_Design REGEXP :saisie2 AS commencepar, '
            . 'art.AR_Design REGEXP :saisie3 AS contient '
     . 'FROM f_article art '
            . 'LEFT JOIN f_artfourniss af ON (art.AR_Ref = af.AR_Ref AND af.AF_Principal = 1) '
            . 'LEFT JOIN f_comptet ct ON (af.CT_Num = ct.CT_Num AND ct.CT_Type = 1) '
     . 'WHERE MATCH (art.indexation) AGAINST ((:saisie4) IN BOOLEAN MODE) '
     . 'ORDER BY commencepar DESC, '
            . 'contient DESC, '
            . 'cpt DESC, '
            . 'art.AR_Ref';
$stmt = $dbCon->prepare($sql);
$saisie = 'iso';
$saisie1 = '+' . $saisie . '*';
$saisie2 = '^' . $saisie . '*';
$saisie3 = '.+' . $saisie . '.*';
$saisie4 = '+' . $saisie . '*';
$stmt->bindParam(':saisie1', $saisie1, PDO::PARAM_STR);
$stmt->bindParam(':saisie2', $saisie2, PDO::PARAM_STR);
$stmt->bindParam(':saisie3', $saisie3, PDO::PARAM_STR);
$stmt->bindParam(':saisie4', $saisie4, PDO::PARAM_STR);
$stmt->execute();
if (($stmt->errorCode() != '00000')){
    var_dump($stmt->errorInfo());
} else {
    while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        var_dump($row);
    }
    $stmt->closeCursor();
}
$dbCon = null;
?>

MySQL query returns no results with PDO

1 Answers1