mysql query using PDO not working

[mutago]

This mysql PDO query statement when run shows a blank page. I thought its something that has to do with either single or double quote in the query statement. I have tried all i could but no way. If i rewrite the code using mysql_deprecated, everything works 100%
below is the coding so far




<?php
require('db.php');

if(isset($_GET['keyword'])){
$keyword = $_GET['keyword'] ;
$keyword = strip_tags($keyword);


$query = $db->prepare( "

SELECT *, MATCH (topictitle, topicdescription) AGAINST ('$keyword' IN BOOLEAN MODE) AS SCORE
FROM topics
WHERE MATCH (topictitle, topicdescription) AGAINST ('$keyword' IN BOOLEAN MODE)
ORDER BY SCORE, topicid DESC
LIMIT 5
" );

$query->execute(array());


if($row = $query->fetch()){

echo '<p> <b>'.$row['topictitle'].'</b> '.$row['topicdescription'].'</p>' ;
}else {
echo 'No Results for :"'.$_GET['keyword'].'"';
}

}else {
echo 'Parameter Missing';
}



?>

[traq]

1) One of the major reasons to use a prepared statement in the first place is to separate sql code from user data. Don't put the $keyword directly in the query: this makes your code vulnerable to SQL injection attacks and/or plain ol' syntax errors. Use parameter markers.
<?php

$query = $db->prepare( "
SELECT *, MATCH (topictitle, topicdescription) AGAINST ( :keyword IN BOOLEAN MODE) AS SCORE
FROM topics
WHERE MATCH (topictitle, topicdescription) AGAINST ( :keyword IN BOOLEAN MODE)
ORDER BY SCORE, topicid DESC
LIMIT 5
" );

$query->execute( array( ":keyword"=>$_GET["keyword"] ) );

2) To troubleshoot, prep the SQL separately, beforehand, so you can see the same code MySQL sees:
$SQL = "SELECT *, MATCH (topictitle, topicdescription) AGAINST ( :keyword IN BOOLEAN MODE) AS SCORE
FROM topics
WHERE MATCH (topictitle, topicdescription) AGAINST ( :keyword IN BOOLEAN MODE)
ORDER BY SCORE, topicid DESC
LIMIT 5";

//for debugging only
print $SQL;

$query = $db->prepare( $SQL );

3) You never check if anything you do is successful or not. PDO->prepare (http://php.net/pdo.prepare), for example, will return FALSE if there is an error —but you just move on to executing the statement, assuming everything went as expected. Same problem with execute; same problem (I suspect) when you make your initial connection.

Depending on how your system is set up, PDO might be issuing Exceptions when things go wrong instead.

4)
This mysql PDO query statement when run shows a blank page.

A blank page (php's "white screen of death") almost always means that there was a fatal error during script execution. There are no syntax errors in the code you posted, so I would suspect the code where you establish your DB connection ("db.php"). Check your server's error logs, and/or temporarily change your error reporting settings so error messages are printed to the page.

If PDO is configured to throw exceptions, this might also lead to a white screen, depending on your error reporting settings for php.

~ ~ ~ ~ ~

#4 is my bet.

[mutago]

I refreshed my browser with the code i posted above and it works but it just fetch one row instead of 5.
I think the problem lies in this line

if($row = $query->fetch()){

i have tried rowCount() or fetchAll() call but it does not work.
what is it that am doing wrong. Any suggestion to enable it fetch all the rows

[traq]

fetch only fetches one row; fetchAll fetches all rows.

Since it is a small number of rows, there is no problem using fetchAll:

$allRows = $query->fetchAll();

foreach( $allRows as $row ){
/* do whatever */
}

Alternatively, you can use a while loop.
Performance-wise, there is not going to be a big difference in your case. Be aware, however, that using fetchAll with large result sets can use too much memory and crash your script.
while( $row = $query->fetch() ){
/* do whatever */
}