Building queries with PDO?

I have some ancient code which I want to convert to PDO:

<?php
    function build_query() {
        // db connection here

        $the_query = "";

        if ( empty( $_GET['c'] ) ) {
            $the_query = "select * from table1";

            if ( ( isset( $_GET['y'] ) ) && ( isset( $_GET['m'] ) ) ) {
                $the_query .= " where y = " . $_GET['y'] . " and m = " .  $_GET['m'];
            }
        } elseif ( ( $_GET['c'] == "1" ) || ( $_GET['c'] == "2" ) ) {
            $the_query = "select * from table1 where GGG = " . $_GET['c'];

            if ( ( isset( $_GET['y'] ) ) && ( isset( $_GET['m'] ) ) ) {
                $the_query .= " and y = " . $_GET['y'] . " and m = " .  $_GET['m'];
            }
        } else {
            $the_query = "select * from table1";

            if ( ( isset( $_GET['y'] ) ) && ( isset( $_GET['m'] ) ) ) {
                $the_query .= " where y = " . $_GET['y'] . " and m = " .  $_GET['m'];
            }

            $the_query .= " and c = " . $_GET['c'];
        }

        return // use the query to return results $the_data;
    }
?>

I can't seem to figure out how to recode this using PDO. I have made a start below, but can't seem to get any further:

<?php
    function build_query() {
        $the_data = "";

        $DBH = new PDO( "mysql:host=server;dbname=database", "user", "pass" );
        $DBH -> setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

        $STH = $DBH -> prepare( "build query here" );

        $STH -> bindParam( ':c', $_GET['c'], PDO::PARAM_INT );
        $STH -> bindParam( ':y', $_GET['y'],开发者_运维百科 PDO::PARAM_INT );
        $STH -> bindParam( ':m', $_GET['m'], PDO::PARAM_INT );

        $STH -> execute();

        $ROWS = $STH -> fetchAll();

            foreach($ROWS as $ROW) {
            $output .= $ROW["a"] . " - " . $ROW["b"] . " - " . $ROW["c"] . " - " . $ROW["d"] . "<br />";
            }

        $DBH = null;

        return $output; 
    }       
?>

Well, it's quite tricky with prepared statements
(that's why I prefer my home-brewed placeholders over prepared statements)

First of all you have to make this ancient code sensible, without all that current mess. Check every parameter only once.

here is a code to give you an idea

$w = array();
if ( !empty($_GET['c']) AND ($_GET['c'] == "1" ) || ( $_GET['c'] == "2") )
{
    $w[] = $db->parse("GGG = ?i", $_GET['c']);
}
if ( isset($_GET['y']) && isset($_GET['m']) )
{
    $w[] = $db->parse("where y = ?i and m = ?i",$_GET['y'],$_GET['m']);
}
$where = '';
if ($w) $where = implode(' AND ',$w);
$query = "select * from table1 $where";

to make use of prepared statements you have to add your values into array and then use it with execute()

You are changing your function to do a lot more that it used to do. If you want to stick to the original design (that is consistent with the name of your function...), you need to change your function so that it still returns a query but does not execute it as the old version did not connect to a database or query a database either.

To solve your problem, you can have the function return an array with 2 elements, the query with the named parameters and another array with the name - value pairs.

If you do want to return the results of the query, you can use a global variable for your database connection or pass it as a variable to the function.