Binded parameter value

Question

I have a query, and I would like to get the full statement of that query, i.e:

$ids = 1;
$query = $db->prepare('SELECT name FROM fruits WHERE ids = :ids');

$query->bindParam(':ids', $ids, PDO::PARAM_INT);
$query-execute();

# Runs the query:
SELECT name FROM fruits WHERE ids = 1

I am looking to get the above query, and not:

SELECT name FROM fruits WHERE ids = :ids

Because I am getting a trouble with this query, is not returning the right values.

As the above table having a huge values, I tested with another table that I just created:

CREATE TABLE IF NOT EXISTS `news` (
`idn` int(5) unsigned NOT NULL AUTO_INCREMENT,
`news_title` varchar(150) NOT NULL,
`news_desc` longtext NOT NULL,
`user_id` int(5) NOT NULL DEFAULT '1' COMMENT 'who added the news',
`randkey` varchar(30) NOT NULL,
`person_ip` varchar(30) NOT NULL,
`when` varchar(30) NOT NULL COMMENT 'when was added',
`on_off` enum('0','1') NOT NULL DEFAULT '0' COMMENT '0: on, 1: off',
PRIMARY KEY (`idn`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `news`
--

INSERT INTO `news` (`idn`, `news_title`, `news_desc`, `user_id`, `randkey`, `person_ip`, `when`, `on_off`) VALUES
(1, 'title', 'desc here', 1, '', '', '645167451764', '0'),
(2, 'news title', 'news description', 1, '', '', '14335983795639', '0');

and the code is:

$OnOff = 1;

$statement = $connexion->prepare("SELECT * FROM news WHERE on_off = :on_off");
$statement->bindParam(':on_off', $OnOff, PDO::PARAM_INT);
$statement->execute();

$row = $statement->fetchAll();

$rowCount = $statement->rowCount();


if($rowCount){

foreach($row as $varr=>$arr)
{
    $idn                    = $arr['idn'];
    $news_title         = $arr['news_title'];
    $news_desc      = $arr['news_desc'];
    $user_id        = $arr['user_id'];
    $when                   = $arr['when'];
    $randkey                = $arr['randkey'];
    $on_off                = $arr['on_off'];

    echo $idn.': '.$news_title.'<br />';
    echo 'desc '.$news_desc.'<br />';
    echo 'id '.$user_id.'<br />';
    echo 'when '.$when.'<br />';
    echo 'key '.$randkey.'<br />';
    echo 'on '.$on_off.'<br />';

} // end foreach
} else {

echo $noResult;
}

OnOff is 1, and in the table I do not have any news with on_off 1, all are 0, so it should not display any news, but it DOES, and vice versa

Thanks in advance

Answer 1

Well there is a PDOStatement::debugDumpParams method, but it doesn't show you the final query as you want.

If you want to debug the queries you are running, i would recommend you to see the answers of this question (the second one, shows a way to see the logs of the queries):

• How to debug PDO database queries?

And more information here:

• Getting raw SQL query string from PDO prepared statements

About your on_off column. If you set enum('0','1'), means they only accept this values. But when you insert the value 1 (integer) you are saying you are inserting the index 1 of your enum values. In that case '0'. You should insert '0' or '1' (as string, like you did in your sql query) or, if you want to use integer values, 1 (for value '0') or 2 (for value '1').

For further information read here (section "Handling of Enumeration Literals" )