Results 1 to 4 of 4

Thread: mysql query using PDO not working

  1. #1
    Join Date
    Aug 2013
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default mysql query using PDO not working

    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 Code:

    <?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';
    }
      


    ?>
    Last edited by traq; 04-20-2014 at 09:53 PM. Reason: switched [code] to [php]

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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 Code:
    <?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:
    PHP Code:
    $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, 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)
    Quote Originally Posted by mutago View Post
    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.

  3. #3
    Join Date
    Aug 2013
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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
    Code:
    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

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    fetch only fetches one row; fetchAll fetches all rows.

    Since it is a small number of rows, there is no problem using fetchAll:
    PHP Code:
    $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.
    PHP Code:
    while( $row $query->fetch() ){
        
    /*  do whatever  */


Similar Threads

  1. Mysql Query Problems
    By simsonite in forum MySQL and other databases
    Replies: 2
    Last Post: 01-13-2009, 06:37 PM
  2. Problem with Mysql query
    By ace2721 in forum MySQL and other databases
    Replies: 3
    Last Post: 10-13-2008, 07:26 AM
  3. Need help with MySQL query...
    By vern1271 in forum MySQL and other databases
    Replies: 0
    Last Post: 08-06-2008, 03:51 PM
  4. pls help me on MySQL query......
    By Rai_87 in forum MySQL and other databases
    Replies: 3
    Last Post: 05-26-2008, 04:34 AM
  5. mySQL query?!
    By nikomou in forum MySQL and other databases
    Replies: 3
    Last Post: 03-11-2007, 03:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •