John Davidson

Message:

I have code that returns all items for a given list as such:

    function get_items($wl_id){

        global $db;

        // Get all for this list

        return $db->get_rows('SELECT * FROM wl_items WHERE wl_id = ? ORDER BY priority ASC,date_mod DESC',[$wl_id]);

    }

It's a diagnostic page so it doesn't have any values except the JSON output of the result of that query. It grabs all rows as an array of rows (arrays of columns) for a given wishlist id. It works if I put in 54, 55, or 57, but NOT 56. I've checked and rechecked the code and no matter what I do, it comes up blank. When I put in an id that doesn't exist, I as LEAST get a [] empty array return.

But somehow on 56, I get nothing. Nothing at all. Just a blank page.

If I dump vars along the way, I see everything I'd expect. I see the value of the id as 56 or whatever else, I see the query filled in as I expect. If I run the query manually on the DB, everything is fine.

Just in case there was something weird about that specific row in the DB, I manually changed values in every column (except the ID) to make sure. If I look at the specific items that are supposed to be returned, nothing looks out of the ordinary.

I have no idea what to do next. How can it work flawlessly for any number except a few for "TEH LULZ"? It has this weird behavior with a handful of other numbers (151 for example), but I don't see what would be causing it.

When I send it a key, it should return all items belonging to that list. And it does; flawlessly - if I run the query manually or when it's not 56 (or 114 or 91)

This is the code I'm using to touch the db inside my db class:

        public function exec($query,$params_arr=[])

        {

            // Nonsense causes problems (and isn't readable)

            $query = preg_replace("/[\r\n\t]/",' ',$query);

            $query = preg_replace('/\s+/', ' ', $query);



            $this->err = null;

            $this->sth = $this->dbh->prepare($query);

            try {

                $this->sth->execute($params_arr);

                $this->last_query = $this->sql_debug($query,$params_arr);

                $_SESSION['diag'][] = $this->last_query;

            } 

            catch (Exception $e) {

                $this->err = $e->getMessage();

                return 0;

            }

            return 1;

        }

    

        public function get_rows($query,$params_arr=[])

        {

            $result = $this->exec($query,$params_arr);

            return $this->sth->fetchAll(PDO::FETCH_ASSOC);

        }

I want to stress again that this code works flawlessly for everything else. The page loads, it gets data, it just fails on this ONE query depending on the id used.

Note also that this works flawlessly on my local system and only fails on the live server.

Just to eliminate as much as possible, I manually connected to the DB in a new PDO instance as such:

        $dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER,DB_PASS);

        if (!$sth = $dbh->prepare("SELECT * FROM wl_items WHERE wl_id=56 ORDER BY priority ASC,date_mod DESC"))

            echo "failed";

        echo $wl_id;

        $sth->execute();

    //  $sth->execute([''+$wl_id]);

        output($sth->fetchAll(PDO::FETCH_ASSOC));

==================================== GOT IT!

It was my output function. It converts with "json_encode" which hates certain special characters apparently. I found a translation function to run my results through to prevent the problem:

json_encode function: special characters

---

My Answer:
meets the specified conditions in the WHERE clause. The query will continue to return rows until all the conditions are met or until there are no more rows left in the database that match the conditions.