PDO prepare and execution of query returns always error

Question

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

`":service"` remove the double quotes here. In `bindValue` add `:` before `service` — Daan, May 04 '16 at 12:24
it still does not work : SQL: [270] 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 Params: 1 Key: Name: [8] :service paramno=-1 name=[8] ":service" is_param=1 param_type=2 — Stanislas Piotrowski, May 04 '16 at 12:26
SQL: [270] 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 Params: 1 Key: Name: [8] :service paramno=-1 name=[8] ":service" is_param=1 param_type=2 — Stanislas Piotrowski, May 04 '16 at 12:30
you are missing `$detail->execute()`. you need to execute query before fetchAll — undefined_variable, May 04 '16 at 12:31
people get the misconception about the colon in binds, it's only required in the values. Read for yourselves http://stackoverflow.com/q/9778887/ — Funk Forty Niner, May 04 '16 at 12:37
@StanislasPiotrowski You need to check for errors here. So use http://php.net/manual/en/function.error-reporting.php and http://php.net/manual/en/pdo.error-handling.php to see if anything comes of it, besides you're not executing the query. — Funk Forty Niner, May 04 '16 at 12:38
@StanislasPiotrowski *"as if there is no `num_rows_result`"* - What exactly do you mean by that? If that's part of code you're not showing and if that is a `mysqli_` function, then we need to know. If it is a `mysqli_` function, those different APIs do not intermix with each other. You are connecting with PDO, right? — Funk Forty Niner, May 04 '16 at 12:42

score 2 · Answer 1 · answered May 04 '16 at 12:26

2

Parameter binding needs to be corrected.

You are missing : while providing value to parameter.

Corrected code:

...
WHERE spb_services.spb_services__name LIKE  :service');
        $detail->bindValue(':service', $_GET[$param], PDO::PARAM_STR);
...

answered May 04 '16 at 12:26

Pupil

23,834
6
44
66

Thank you for your help, I did this but I still have no results – Stanislas Piotrowski May 04 '16 at 12:28
1

Well first,the OP has to remove some quotes. – Jay Blanchard May 04 '16 at 12:28
I did this to remove the quote – Stanislas Piotrowski May 04 '16 at 12:28
3

Colon isn't the issue, driver auto appends that. http://stackoverflow.com/questions/9778887/is-the-leading-colon-for-parameter-names-passed-to-pdostatementbindparam-opt (follow source code link) Placeholders don't get quoted. – chris85 May 04 '16 at 12:29

Saty · Answer 2 · 2016-05-04T12:59:17.707

2

Only issue in your code Forget to execute query before fetching data It would be

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

edited May 04 '16 at 12:59

answered May 04 '16 at 12:31

Saty

22,443
7
33
51

I did all the modification but it still does not work, I guess I'm wrong somewhere else – Stanislas Piotrowski May 04 '16 at 12:33
I did `$detail->bindValue(':service', $_GET[$param], PDO::PARAM_STR); $detail->execute(); // execute it first $lignes = $detail->fetchColumn(); $resultat = $detail->fetchAll(PDO::FETCH_ASSOC); //var_dump($_GET[$param]); // $detail->debugDumpParams(); //var_dump($lignes); $retour = ($lignes > 0) ? array('status' => 'ok') : array('status' => 'error'); var_dump($retour);` And I have a error – Stanislas Piotrowski May 04 '16 at 12:36
1

colon isn't required in the bind, just values, here http://stackoverflow.com/q/9778887/ – Funk Forty Niner May 04 '16 at 12:37
@StanislasPiotrowski still you are not executeing query before fetching data – Saty May 04 '16 at 12:39
I tried this ` $detail->bindValue('service', $_GET[$param], PDO::PARAM_STR); $detail->execute(); // execute it first $lignes = $detail->fetchColumn(); $resultat = $detail->fetchAll(PDO::FETCH_ASSOC);` but I still have error – Stanislas Piotrowski May 04 '16 at 12:40
no errors display, only there is no result, but when I paste the query in php my admin it works – Stanislas Piotrowski May 04 '16 at 12:43
it is like if the bind value is not applicated to the query – Stanislas Piotrowski May 04 '16 at 12:46
Try with `$keyword = "%".$_GET[$param]."%"; $detail->bindValue(':service', $keyword , PDO::PARAM_STR);` – Saty May 04 '16 at 12:49
ANd please check `$_GET[$param]` have a value!! – Saty May 04 '16 at 12:51
the get param var_dump return string 'demolition' (length=10) I paste your code, it does not work still. When I replace manualy thevalue of get param to the query I paste it on phpmyadmin it works. Is there something I'm missing ? – Stanislas Piotrowski May 04 '16 at 12:54
_Only issue in your code_ I think you missed a few things old chap – RiggsFolly May 04 '16 at 12:58
what is that old chap ? – Stanislas Piotrowski May 04 '16 at 13:00
check updated four line use it in your code and post the value of `print_r($resultat);` if you get it!! – Saty May 04 '16 at 13:01
I have array (size=0) empty – Stanislas Piotrowski May 04 '16 at 13:03
Also follow the steps of `RiggerFolly's` answer!! – Saty May 04 '16 at 13:03

RiggsFolly · Accepted Answer · 2016-05-04T13:16:14.530

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

Final Note: You are doing no error checking after any of the PDO statement taht could possibly go wrong. Almost all of these PDO statements return a status that if false you should display yourself a PDO generated error message See ErrorInfo

you are really helpfull, thank you for all the advice and thank you to everybody for having taking the tipme to answer me. I would have to check all answer as satisfy but this one was the most complete, and solved the issue. — Stanislas Piotrowski, May 04 '16 at 13:09

score 1 · Answer 4 · answered May 04 '16 at 13:02

1

You forgot to do $detail->execute(); To process the SQL request

$detail->bindValue(':service', $_GET[$param], PDO::PARAM_STR);
if( ! $detail->execute()) {
   die('Invalid Mysql Query!');
}
$resultat = $detail->fetchAll(PDO::FETCH_ASSOC);

Hope that helps :)

answered May 04 '16 at 13:02

Yair.R

795
4
11

Bit late to the party there buddy – RiggsFolly May 04 '16 at 13:03

PDO prepare and execution of query returns always error

4 Answers4