MySQL Query No Results on Production, But Okay on Development

Question

On my development environment I have static function within a Class wherein I can retrieve a list of a table's data and print to screen. It works great. Here is my code:

public static function getList( $numRows=1000000, $order="name ASC" ) {

    $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM posttypes ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows";
    $stmt = $conn->prepare( $sql );
    $stmt->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
    $stmt->execute();
    $list = array();

    while ( $row = $stmt->fetch() ) {
        $postType = new PostType( $row );
        $list[] = $postType;
    }

    //Now get total number of posts that match criteria
    $sql = "SELECT FOUND_ROWS() AS totalRows";
    $totalRows = $conn->query( $sql )->fetch();
    $conn = null;

    return ( array( "results" => $list, "totalRows" => $totalRows[0] ) );
}

However, on my production environment I cannot retrieve any records. But if I run that exact query inside phpMyAdmin I get the exact record I want.

Not sure what is going on? This is my first time setting up to a production environment. I am using Blue Host.

UPDATE: Okay, I fixed it by removing mysql_real_escape_string, however I believe it is safer to escape the string, but this must be returning false and failing the SQL Statement. Thanks everyone for help.

`$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);` (perhaps this was configured in the `php.ini` on your dev server?) — eggyal, Apr 29 '13 at 01:12
you can't bind limit,i quite sure it will fail.in both environment — ajreal, Apr 29 '13 at 01:38
@ajreal: It works fine on my development environment, and limit seems to bind okay. In any case, I tried removing the binding and the result is still the same. — TimNguyenBSM, Apr 29 '13 at 03:48
@eggyal: Checked php.ini and that command doesnt exist. Likewise, I tried that command. False provided the same result, and true gave a blank screen. — TimNguyenBSM, Apr 29 '13 at 04:21

score 1 · Accepted Answer · edited May 23 '17 at 12:18

It's a duplicate of PDO query fails but I can't see any errors. How to get an error message from PDO?
This function is wrong at many points anyway
- Never ever connect from the application function, but connect only once per application and then use single instance of PDO class, passing it to functions.
- 1000000 rows is a WAY TOO MUCH. The moment you forget to set LIMIT, you'd kill your server
- ASC is not a string by any means. It's a syntax keyword. And thus it's totally useless to escape whatever characters in it. Although, on your local server with default credentials it connects to server by itself and returns you the same string, on a live server it can't connect and thus returns false. What to do to properly bind a keyword

score 0 · Answer 2 · answered Apr 29 '13 at 01:36

Simple tests first:

does your production sql database match your development datatbase? It looks like you have with phpMyAdmin.

Open Notebook: cut and past the successful query from PhpNyAdmin. paste it into the notebook file.

Cut and paste the query in your production program into the same file to verify the two queries are identical. Punctuation errors can be hard to cateh.

score 0 · Answer 3 · answered Apr 29 '13 at 05:23

0

UPDATE: Okay, I fixed it by removing mysql_real_escape_string, however I believe it is safer to escape the string, but this must be returning false and failing the SQL Statement. Thanks everyone for help.

answered Apr 29 '13 at 05:23

TimNguyenBSM

817
2
15
33

score 0 · Answer 4 · answered Aug 05 '21 at 09:09

Just in case anyone has this issue...

I had the same problem when moving from development on Xampp to production where I was just getting nothing back from queries. It turned out (after several hours of frustration) that when I imported a copy of my database to Xampp all my table names had been altered from starting with an uppercase letter to lowercase. All my subsequent code was based on this syntax and simply didn't work. So Xampp was the cause of my problem.

I know that I should have used lowercase in the first place, but I created the database 20 years ago when I first started coding and knew no better!

score -1 · Answer 5 · answered Apr 29 '13 at 03:33

-1

Sounds like a db connectivity problem to me. Perhaps double-check your host/un/pw?

answered Apr 29 '13 at 03:33

Ryan

5,959
2
25
24

No connectivity problem here. What I didnt mention but should have is that I can add a record to the table from the user interface just fine, and I can see the record in the table using phpMyAdmin, but cannot get a return result. – TimNguyenBSM Apr 29 '13 at 03:50

MySQL Query No Results on Production, But Okay on Development

5 Answers5