www.webdeveloper.com
Results 1 to 5 of 5

Thread: PDO and SQLi Queries returning Empty, while MYSQL_QUERY works perfect.

  1. #1
    Join Date
    Apr 2014
    Posts
    18

    PDO and SQLi Queries returning Empty, while MYSQL_QUERY works perfect.

    I was trying to convert my project over to PDO earlier and I couldn't do it, and I was very upset. I'm just a beginner with PHP (Started a week ago) however, typcially i pick up on things really quickly and I'm able to solve my problems relatively quickly. However, this has had me stumped for 3 days now, I went ahead and finished my entire project using the standard mysql_query function, however.... it's vulnerable to injection when it comes to my login scripts and I would like to have it transferred over as soon as possible.

    Lets get started, this is the code that configures all of my connections.
    Code:
    <?php
        $configuration = include_once 'Configuration.php';
    
        $PDOConnection = new PDO('mysql:host' .$Configuration["SQL_HOST"]. ';dbname='.$Configuration["SQL_DATABASE"], $configuration["SQL_USERNAME"], $configuration["SQL_PASSWORD"]);  
        $NormalConnection = mysql_connect($Configuration["SQL_HOST"], $Configuration["SQL_USERNAME"], $Configuration["SQL_PASSWORD"]) or die("Error: Failure to connect to host.");
        $SQLiConnection = new mysqli($Configuration["SQL_HOST"], $Configuration["SQL_USERNAME"], $Configuration["SQL_PASSWORD"], $configuration["SQL_DATABASE"]);             
    
        if(mysqli_connect_errno()) {
           echo "Connection Failed: " . mysqli_connect_errno();
        }
    ?>
    I have thoroughly tested each of these connections and I'm positive that they are all connecting to the database successfully.


    Here's my code when using the mysql_query method(This works perfectly):

    Code:
    mysql_select_db($SQLConfig["SQL_DATABASE"])or die("Error: Cannot locate database");
    $statement = 'SELECT `Date`, SUM(`Amount`), `Quantity Sold` FROM ' . $SQLConfig["SQL_DATA_TABLE"] . '
        WHERE Date BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE() 
        GROUP BY Date ORDER BY Date DESC';
    $results = mysql_query($statement);
    while($row = mysql_fetch_array($results)) {
    Here's my code when using the SQLi method(This one returns 0 rows):
    Code:
    if($statement = $SQLConnection->prepare('
        SELECT `Date`, SUM(`Amount`), `Quantity Sold` FROM ' . $SQLConfig["SQL_DATA_TABLE"] . '
        WHERE Date BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE() 
        GROUP BY Date ORDER BY Date DESC')) 
    {
        $statement->execute();
        $statement->bind_result($result);
        $statement->fetch();
        echo "Row: " .$result;
    }

    Here's my code when using the PDO method(This one returns 0 rows):
    Code:
    try {
        $count=0;
        $statement = $SQLConnection->prepare('
            SELECT `Date`, SUM(`Amount`), `Quantity Sold` FROM ' . $SQLConfig["SQL_DATA_TABLE"] . '
            WHERE Date BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE() 
            GROUP BY Date ORDER BY Date DESC');
        $statement->execute();
    
        //echo "<tr><td>test</td><td>test</td><td>test</td></tr>";
    
        foreach($statement as $row) {
            echo "Row: " . $count;
            $count++;
        }
    } catch (PDOException $e) {
        die("Error!: " . $e->getMessage() . "<br/>");
    }
    I have also tried changing the PDO code to use the following but it didn't work

    Code:
    $statement->setFetchMode(PDO::FETCH_BOTH);
    while($row = $statement->fetch()){
    The following:
    Code:
    var_dump($statement->fetchAll(PDO::FETCH_ASSOC));
    prints out Array(0) { }


    For those curios as to where I'm getting $SQLConnection from

    Code:
    function createIndex($SQLConnection, $SQLConfig) {
    The connection that I'm using is passed as a parameter.

    Example:

    Code:
    createIndex($PDOConnection, $Configuration);
    Please help me get PDO working, as it's my preferred choice.

  2. #2
    Join Date
    Apr 2014
    Posts
    18
    I can't edit the thread, but I'd like to state that I've echo'd the query on all ocassions (not that it would change anyway) and it returned the following

    Code:
    SELECT `Date`, SUM(`Amount`), `Quantity Sold` FROM Transactions WHERE Date BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE() GROUP BY Date ORDER BY Date DESC
    Which if ran in PHPMYADMIN will return:
    Code:
    Showing rows 0 - 6 (7 total, Query took 0.0004 sec) [Date: 2014-05-26 - 2014-05-13]

  3. #3
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    859
    1 -When trying to do anything using php be sure to turn on error checking so you can be informed of those simple little things that bug you.

    PHP Code:
        error_reporting(E_ALL E_STRICT E_NOTICE);
        
    ini_set('display_errors''1'); 
    Of course, turn this off when you put it into production.

    2 - When you are trying to use PDO to access MySQL, it would make sense to use the $PDOConnection var instead of the $SQLConnection var

    3 - One should ALWAYS examine the result of calls to things to be sure that they have happened as you expect. When you open a file for reading, check the result. When you write to a file check the result. If you RTFM you will see that many functions return an argument of true/false just for this purpose. That said - I'm guessing that if you had added:

    PHP Code:
    if (!$PDOConnection)   // check if connection was made
    {
        echo 
    "Error trying to make pdo connection";
        exit();

    You would have seen this message since you are missing an = sign in the host string.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    To help debugging, I tend to write my PDO (which is all I use for DBMS integration these days) something like...
    PHP Code:
    try {
        
    $count=0;
        
    $sql '
            SELECT `Date`, SUM(`Amount`), `Quantity Sold` FROM ' 
    $SQLConfig["SQL_DATA_TABLE"] . '
            WHERE Date BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE() 
            GROUP BY Date ORDER BY Date DESC
        '
    ;
        
    $statement $PDOConnection->prepare($sql);
        if(
    $statement == false) {
            throw new 
    Exception("prepare failed: ".print_r($PDOConnection->errorInfo(),1).PHP_EOL.$sql);
        }
        if(
    $statement->execute() == false) {
            throw new 
    Exception("prepare failed: ".print_r($statement->errorInfo(),1).PHP_EOL.$sql);
        }
    // all you need to loop through a result set:
        
    while($row $statment->fetch(PDO::FETCH_ASSOC)) {
            echo 
    "<pre>".$count++."\n".print_r($row,1)."</pre>"// quick and dirty way to see what you got
        
    }
    } catch (
    PDOException $e) {
        die(
    "Error!: " $e->getMessage() . "<br/>");

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Code:
        foreach($statement as $row) {
            echo "Row: " . $count;
            $count++;
        }
    Do we see a problem here? NogDog corrected it, but didn't mention it. $statement is a PDOStatement object, NOT an array nor does it's object elements contain data rows.

    Code:
    while ($row = $statement->fetch()) {
    See the difference? You can't just 'foreach' a PDOStatement... not quite sure where you got the idea you could -- but.. no.
    Last edited by deathshadow; 05-29-2014 at 06:13 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles