Stanislas Piotrowski Stanislas Piotrowski - 7 months ago 14
SQL Question

PDO prepare and execution of query returns always error

I have a function,

that returns to me always a mistake, even if in the phpmyadmin when I past the query there is a result.

I guess there is something wrong in my query but I don't know what. I used to do my code doing mysql_real_escape_string, then I turn to PDO, they told me I should do a prepare for escaping GET vars, so I tried do do it.

Below is my query

public static function getDetailService($param) {
global $bdd;
$detail = $bdd->prepare('SELECT
spb_services.spb_services__name,
spb_services.spb_services__description,
spb_services.spb_services__banner,
spb_services.spb_services__tabs,
spb_services.spb_services__category
FROM spb_services
WHERE spb_services.spb_services__name LIKE :service');

$detail->bindValue(':service', $_GET[$param], PDO::PARAM_STR);
$resultat = $detail->fetchAll(PDO::FETCH_ASSOC);

//var_dump($_GET[$param]);

$detail->debugDumpParams();
$lignes = $detail->fetchColumn();

//var_dump($lignes);
$detail = $detail->fetchAll(PDO::FETCH_ASSOC);
$retour = ($lignes > 0) ? array('status' => 'ok') : array('status' => 'error');
var_dump($retour);
}


When I call the function :
$service = nosServices::getDetailService('service');


Var dump of
var_dump($_GET[$param])
return to me what expected (from the url)

Then I did
$detail->debugDumpParams();


I past the query in my localhost phpmyadmin, it returns to me what expected but not when using PDO.

I guess a small things is wrong bu tI have no idea what.

This returns no mistakes, but always error, as if there is no
num_rows_result


To sum up the trouble, the GEt returns what expected, but when we go to the query, it return no result (except in my phpmyadmin copy and paste the query)

Anykind of help will be much appreciated

Edit : modifications done as expected by other users

Answer

There are a number of strange things happening in your code.

I have commented where I think things need to change

public static function getDetailService($param) {
    global $bdd;    // bad practice, see later suggestion
    $detail = $bdd->prepare('SELECT
                     spb_services.spb_services__name,
                     spb_services.spb_services__description,
                     spb_services.spb_services__banner,
                     spb_services.spb_services__tabs,
                     spb_services.spb_services__category
                FROM spb_services
                WHERE spb_services.spb_services__name LIKE :service');

    // $GET? I assume you want to use the `$param you pass as a param to this function
    //$detail->bindValue(':service', $_GET[$param], PDO::PARAM_STR);

    // a LIKE normally requires a string like '%something%'
    // or 'something%'
    // DO we assume you passed $param with the wildcards already applied?
    $detail->bindValue(':service', $param, PDO::PARAM_STR);

    // now the prepared query must be executed
    $detail->execute();

    // fetchAll returns ALL the result set into an array
    $resultat = $detail->fetchAll(PDO::FETCH_ASSOC);

    // as you are using a LIKE we have to assume there will be more 
    // than one row returned. 

    // fetchColumn makes no sense here
    //$lignes = $detail->fetchColumn();

    // You already did a fetchAll so this makes no sence
    //$detail = $detail->fetchAll(PDO::FETCH_ASSOC);

    // as all you appear to be doing is testing if one or more rows are returned
    // then all you need to do is coumt the occurances in the $resultat array

    $retour = (count($resultat) > 0) ? array('status' => 'ok') : array('status' => 'error');

    // Now you need to return something
    return $retour;

}

It is also bad practice to use a global in a class method as it breaks the encapsulation, it is better practice to pass something like that as a parameter.

EG This

public static function getDetailService($param) {
    global $bdd;

Becomes this

public static function getDetailService($bdd, $param) {
    // global $bdd;   <-- no longer needed

Or if it is needed throughout the class then make it a class property!

If I am right and all you want to know from this method is if something exists, a SELECT COUNT(id) as cnt would be a more efficient way of doing that, but lets leave that for another day as it would also chnage how you get at the result and write the rest of this code

Comments