select using php mysql using where in PDO

Question

i am trying to write a function in php and mysql to select values from PHP and mysql using PDO

function getRec($id=0)
{
    ($id==0?$addQuery="":$addQuery=" where id =".$id);
    $statement = $dbh->prepare("select * from TCMS :name order by id");
    $statement->execute(array(':name' => $addQuery));
    $row = $statement->fetchAll(); 
    return $row ;
}

i got error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' where id =2' order by id' at line 1' in /Applications/XAMPP/xamppfiles/htdoc

actually what i am trying

if value (2) of ID is passed then statement will be

select * from TCMS where id=2 order by id

And if ID=0 then select statement will be

select * from TCMS order by id

i am new to PDO and not sure of exact syntax.

how to do this ?

You can't do that. Placeholders are not arbitrary strings to insert into the SQL, they only work for parameters like WHERE something = :placeholder — Michael Berkowski
– Michael Berkowski, Commented Nov 11, 2012 at 13:18
Dear @Michael Berkowski thanks for reply, then how to solve this? i need help in this — air
– air, Commented Nov 11, 2012 at 13:24

Stegrex · Accepted Answer · 2012-11-11 13:47:44Z

Do this instead:

function getRec($id=0)
{
    //($id==0?$addQuery="":$addQuery=" where id =".$id);
    if ($id == 0)
    {
        $statement = $dbh->prepare("select * from TCMS order by id");
        $statement->execute();
    }
    else
    {
        // Notice the SQL string has changed. Placeholder :name now properly takes the place of a SQL value.
        $statement = $dbh->prepare("select * from TCMS where id = :name order by id");
        $statement->execute(array(':name' => $id));
    }

    $row = $statement->fetchAll(); 
    return $row ;
}

What you're doing wrong is you're attempting to bind and execute the SQL with the placeholder as arbitrary string values, which is not what the placeholder is for.

The placeholder is to be set in the place of the value (not table names or anything else) so that the value when passed in during execution will be properly handled by PDO internally for the correct escaping.

The function I wrote should help to create valid SQL.

Michael Berkowski · Accepted Answer · 2012-11-11 13:32:00Z

2

If you need to dynamically add a WHERE clause, construct the SQL string first and prepare() it. If the condition was met to add parameters, you must then conditionally add the appropriate placeholder/value pairs to the array passed into execute().

You cannot bind a placeholder as an arbitrary SQL string.

// Array to pass into execute()
$values = array();

// Start your SQL...
$sql = "SELECT * FROM TCMS";
// Add the WHERE clause if $id is not zero
if ($id !== 0) {
   $sql .= " WHERE id=:name ";
   // And add the placeholder into the array
   $values[':name'] = $id);
} 
// add the ORDER BY clause
$sql .= " ORDER BY id";

// Prepare the statement
$statement = $dbh->prepare($sql);

$statement->execute($values);
// fetch, etc...

edited Nov 11, 2012 at 13:32

answered Nov 11, 2012 at 13:24

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Collectives™ on Stack Overflow

select using php mysql using where in PDO

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related