#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2

    MySQL Query to PDO


    The following code does not out put anything, and no errors are displayed on the page nor recorded in my error log.

    PHP Code:
    function getServersNormal() {
        
    $PDOUser "****"//Username for MySQL
        
    $PDOPass "****"//Password for MySQL
        
    $dbh = new PDO('mysql:host=localhost;dbname=mcss'$PDOUser$PDOPass);
        
    $dbh->exec("set names utf8");
        
        if(isset(
    $_GET['pagenumber'])){
            
    $pagenumber $_GET['pagenumber'];
            } else {
            
    $pagenumber 1;
        }
        
    $serversPerPage 10;
        
    $firstPost = ($pagenumber 1) * $serversPerPage;

        if(isset(
    $_GET['sortBy'])) {
            if(isset(
    $_GET['orderBy'])) {
                
    $stmt2 $dbh->prepare("SELECT * FROM servers ORDER BY :sortBy :orderBy LIMIT :firstPost, :serversPerPage");
                
    $sortBy $_GET['sortBy'];
                
    $orderBy $_GET['orderBy'];
                
    $stmt2->bindParam(':sortBy'$sortBy);
                
    $stmt2->bindParam(':orderBy'$orderBy);
                
    $stmt2->bindParam(':firstPost'$firstPost);
                
    $stmt2->bindParam(':serversPerPage'$serversPerPage);
                
    $stmt2->execute();
                
            } else {
                
    $stmt2 $dbh->prepare("SELECT * FROM servers ORDER BY :sortBy LIMIT :firstPost, :serversPerPage");
                
                
    $sortBy $_GET['sortBy'];
                
    $stmt2->bindParam(':sortBy'$sortBy);
                
    $stmt2->bindParam(':firstPost'$firstPost);
                
    $stmt2->bindParam(':serversPerPage'$serversPerPage);
                
    $stmt2->execute();
            }
        } else {
                
    $stmt2 $dbh->prepare("SELECT * FROM servers LIMIT :firstPost, :serversPerPage");
                
                
    $stmt2->bindParam(':firstPost'$firstPost);
                
    $stmt2->bindParam(':serversPerPage'$serversPerPage);
                
    $stmt2->execute();
        }
        
        
                while(
    $row $stmt2->fetch()) {
                    
    $id $row['ID'];
                    
    $file "banners/".$id.".png";
                    
    $option = (file_exists($file) ? "<img class=\"server_img\" src=\"https://mcss-portal.com/banners/" $row['ID'] . ".png" "\" />" "<img class=\"server_img\" src=\"https://mcss-portal.com/banners/blank.png\" />");
                    if (
    $row['serverStatus'] == 1) {
                        echo 
    "<tr class=\"success\">" "<td class=\"table-name\">" "<a href=\"viewServer.php?id=" $row['ID'] . "\">" $option "</br>" "</a>" "<span class=\"label label-success no-link\">" $row['serverVersion'] . "</span> " "<a href=\"viewServer.php?id=" $row['ID'] . "\">" $row['serverName'] . "<span>" " &#x2022 " "</span>" $row['serverIP'] . "</a>" "</td>";
                        echo 
    "<td class=\"table-players\">" $row['serverPlayersOnline'] . " / " $row['serverPlayersTotal'] . "</td>";
                        echo 
    "<td class=\"table-uptime\">" $row['serverUptime'] . "%" "</td>";
                        echo 
    "<td class=\"table-votes\">" $row['serverVotes'] . "</td>" "</tr>";
                    } else {
                        echo 
    "<tr class=\"error\">" "<td class=\"table-name\">" "<a href=\"viewServer.php?id=" $row['ID'] . "\">" $option "</br>" "<span class=\"label label-success no-link\">" $row['serverVersion'] . "</span> " "<a href=\"viewServer.php?id=" $row['ID'] . "\">" $row['serverName'] . "<span>" " &#x2022 " "</span>" $row['serverIP'] . "</a>" "</td>";
                        echo 
    "<td class=\"table-players\">" $row['serverPlayersOnline'] . " / " $row['serverPlayersTotal'] . "</td>";
                        echo 
    "<td class=\"table-uptime\">" $row['serverUptime'] . "%" "</td>";
                        echo 
    "<td class=\"table-votes\">" $row['serverVotes'] . "</td>" "</tr>";
                    }
        }

    Help is appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Are you sure your query is returning a result set? "where" will do nothing if there are not any rows to process.
    Thomas Tremain
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    how are we supposed to help you if you don't read any links?

    PDO doesn't do error reporting by default -- just like the old MySQL extension. If you want it to generate errors or throw exceptions, you have to configure it accordingly. How do you do that? I explain it in the first link of my signature. Here's the relevant part again:

    PHP Code:
     $db_options = array( 
        
    PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements) 
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent) 
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays) 
    ); 
    $database = new PDO('mysql:host=localhost;dbname=YOURDB;charset=utf8''YOURUSER''YOURPW'$db_options);    // important! specify the character encoding in the DSN string, don't use SET NAMES 
    Do not use SET NAMES. It will break the escaping mechanism of PDO, because the extension isn't aware of encoding changes you make via a query. This applies to every database extension, not just PDO.

    When you've finally set up PDO in a correct way, you should get an error for the first query, because the ORDER BY clause is syntactically wrong (you're missing a comma).

    If you get no error, then obviously this query doesn't get executed. Then which one does? What happens when you execute the query "manually" in phpmyadmin?

    There are two ways of handling errors: You can stand there wondering why you get a blank page. Or you can start investigating the issue and checking the program flow and the relevant variables -- all you need is var_dump() and echo.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    I figure I'd toss something in, and also double-checking myself since I'm kinda new with with format, so feel free to correct me.

    This:
    PHP Code:
    $stmt2 $dbh->prepare("SELECT * FROM servers ORDER BY :sortBy :orderBy LIMIT :firstPost, :serversPerPage");
    $sortBy $_GET['sortBy']; 
    $orderBy $_GET['orderBy']; 
    $stmt2->bindParam(':sortBy'$sortBy); 
    $stmt2->bindParam(':orderBy'$orderBy); 
    $stmt2->bindParam(':firstPost'$firstPost); 
    $stmt2->bindParam(':serversPerPage'$serversPerPage); 
    $stmt2->execute(); 
    can be shortend into this:
    PHP Code:
    $stmt2 $dbh->prepare("SELECT * FROM servers ORDER BY ? ? LIMIT ?, ?");
    $stmt2->bindParam('sssi'$_GET['sortBy'], $_GET['orderBy'], $firstPost$serversPerPage); 
    $stmt2->execute(); 
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Triple_Nothing
    can be shortend into this:
    No, tjswebdev is using PDO, not MySQLi.

    But it's possible to pass the parameters as an associative array to the execute() method:

    PHP Code:
    $stmt2 $dbh->prepare('
        SELECT
            *
        FROM
            servers
        ORDER BY
            :sortBy, :orderBy
        LIMIT
            :firstPost, :serversPerPage
    '
    );
    $stmt2->execute(
        
    ':sortBy' => $_GET['sortBy']
        , 
    ':orderBy' => $_GET['orderBy']
        , 
    ':firstPost' => $firstPost
        
    ':serversPerPage' => $serversPerPage
    ); 
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2
    Ah! Okay - sorry for that late reply! Yeah - it worked. Thanks guys!

IMN logo majestic logo threadwatch logo seochat tools logo