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

What is it returning? Any errors? It also looks like you have a syntax error with `$query->execute()`. — Michael Lea Crawford, Dec 04 '15 at 00:40
"Not returning the right values"? And your table data is correct? -- The query itself looks ok. Code too, except for the syntax error on the execute (missing `>`). Anyway: if you need to debug the query, then you might throw a `$query->debugDumpParams();` in. — Jens A. Koch, Dec 04 '15 at 00:46
`SELECT name FROM fruits WHERE ids = :ids` IS exactly the same as `SELECT name FROM fruits WHERE ids = 1;` — VIDesignz, Dec 04 '15 at 00:47
Possible duplicate of [How to debug PDO database queries?](http://stackoverflow.com/questions/2411182/how-to-debug-pdo-database-queries) — Clyff, Dec 04 '15 at 00:51
I would like to get the binded value so I can test it on mysql console or phpmyadmin — Oum Alaa, Dec 04 '15 at 00:53
Wait you just want the `$ids`? Why don't you set a `var_dump($id)` somewhere in your code and change it to just output it (instead of doing all sql)? — Clyff, Dec 04 '15 at 01:00
I would like to get the full statement so I can test it somewhere else — Oum Alaa, Dec 04 '15 at 01:02
you wanting to do math here? `$query-execute();` as in *minus*. If that's your real code, it should read as `$query->execute();` as already stated above. — Funk Forty Niner, Dec 04 '15 at 01:07
when I changed the `on_off` enum('0','1') NOT NULL DEFAULT '0' COMMENT '0: on, 1: off' from ENUM to INT, it works, any reason why? — Oum Alaa, Dec 04 '15 at 04:48

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

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" )

edited May 23 '17 at 12:15

Community

1
1

answered Dec 04 '15 at 00:50

Clyff

4,046
2
17
32

Using debugDumpParams returned this (I tested with another table using news table and on_off parameter): SQL: [41] SELECT * FROM news WHERE on_off = :on_off Params: 1 Key: Name: [7] :on_off paramno=-1 name=[7] ":on_off" is_param=1 param_type=1 – Oum Alaa Dec 04 '15 at 01:00
when I changed the `on_off` enum('0','1') NOT NULL DEFAULT '0' COMMENT '0: on, 1: off' from ENUM to INT, it works, any reason why? – Oum Alaa Dec 04 '15 at 04:48

Binded parameter value

1 Answers1