How to add parameter to clause in PDO query

Question

I am using code to run a Mysql query that defines a clasue ($datimeClause). I would like to run the query with a second parameter (:method) but if I change the syntax of the clause at all, the query won't run. I am fairly new to PDO could someone please tell me how I can reformat the clause to query for the second parameter.

This is the Query

public static function getList( $numRows=1000000, $datimeId=null ) {
    $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $datimeClause = $datimeId ? "WHERE DatimeId = :datimeId" : "";
    $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM notify $datimeClause";
    $st = $conn->prepare( $sql );
    $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
    $st->bindValue( ":datimeId", $datimeId, PDO::PARAM_INT );
    $st->execute();
    $list = array();
    while ( $row = $st->fetch() ) {
      $text = new Text( $row );
      $list[] = $text;
    }

This is the function that calls it.

function newAutoText() {
    $results = array();
    $datimeId = ( isset( $_GET['datimeId'] ) && $_GET['datimeId'] ) ? (int)$_GET['datimeId'] : null;
    $results['datime'] = Text::getById( $datimeId );
    $data = Text::getList( 100000, $results['datime'] ? $results['datime']->id : null);
    $results['texts'] = $data['results'];
    $results['totalRows'] = $data['totalRows']; 


    require( TEMPLATE_PATH . "/sms.php" );
}

How is it that you know "the query won't run"? Is the query returning zero rows, or is MySQL raising an error? — spencer7593, Feb 01 '15 at 03:47
the :datimeId placeholder isn't always included in the $sql query string. so don't you also need to conditionally bind $datimeId to it? — Rob, Feb 01 '15 at 03:52
Sorry, what I mean by "query won't run" is that it won't return any rows. — Catie Camastro, Feb 01 '15 at 15:59

score 0 · Accepted Answer · answered Feb 01 '15 at 03:53

0

so just try:

public static function getList( $numRows=1000000, $datimeId=null, $andClause=null ) {

and here : $data = Text::getList( 100000, $results['datime'] ? $results['datime']->id : null, 'testMethod');

and off course here:

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM notify $datimeClause";
if ($andClause!=null ) $sql .= " AND method= :method ";
    $st = $conn->prepare( $sql );
    $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
    $st->bindValue( ":datimeId", $datimeId, PDO::PARAM_INT );
if ($andClause!=null ) 
    $st->bindValue( ":method", $andClause, PDO::PARAM_STR );

answered Feb 01 '15 at 03:53

Alex

16,739
1
28
51

Thanks for the help Kim. Unfortunately this gives me an "Invalid parameter number" error. I'll keep playing around with it and see if something works. – Catie Camastro Feb 01 '15 at 15:59
I think you missed something in my response. according tpo your error message you missed the second point where you mus call `ext::getList(` with one more new parameter. or if I am wrong - tell me the line that brings this error. Or maybe you should comment `$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );` it seems like a garbage (I copied from your original question body) – Alex Feb 01 '15 at 16:01
I did call it with the extra variable with the same result. I guess I'm a little unclear as to how the actual value of the :method property is queried when it is technically bound to " AND method= :method ." I tried replacing 'testMethod' in the call function with the method value I want queried but no luck and I still get the same error. The query and the call function are in different files. Does that make a difference? I'm just grasping at straws here. – Catie Camastro Feb 01 '15 at 16:09
*Facepalm* Good point. My table only had values that matched the clause. It just returns all the rows. – Catie Camastro Feb 01 '15 at 16:24

score 0 · Answer 2 · answered Feb 01 '15 at 16:38

Okay, the first getById query I ran in my call function was arbitrary.

This works:

public static function getList( $numRows=1000000, $datimeId, $method=1 ) {
    $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $datimeClause = $datimeId ? "WHERE DatimeId = :datimeId" : "";
    $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM notify $datimeClause AND Method= :method LIMIT :numRows";
    $st = $conn->prepare( $sql );
    $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
    $st->bindValue( ":datimeId", $datimeId, PDO::PARAM_INT );
    $st->bindValue( ":method", $method, PDO::PARAM_INT );
    $st->execute();
    $list = array();

    while ( $row = $st->fetch() ) {
      $text = new Text( $row );
      $list[] = $text;
    }

    // Now get the total number of articles that matched the criteria
    $sql = "SELECT FOUND_ROWS() AS totalRows";
    $totalRows = $conn->query( $sql )->fetch();
    $conn = null;
    return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
  }

function newAutoText() {
    $results = array();
    $datimeId = ( isset( $_GET['datimeId'] ) && $_GET['datimeId'] ) ? (int)$_GET['datimeId'] : null;
    $data = Text::getList( 100000, $datimeId, '1');
    $results['texts'] = $data['results'];
    $results['totalRows'] = $data['totalRows']; 


    require( TEMPLATE_PATH . "/sms.php" );
}

How to add parameter to clause in PDO query

2 Answers2