MySQL Returning null when value = 0?

Question

This is a strange issue. When executing queries it will return null where a value of 0 (type int) is found.

Here is my mysql function:

public function query( $query_string, $params = null, $param_types = null) {

            //prepare the statement
            $stmt = $this->db->prepare($query_string);

            //check if the sql query is wrong.
            if($stmt === false) {
                echo "Wrong SQL: " . $query_string;
                if(DEBUG)
                    echo "<br />Error: " . $this->db->errno . " " . $this->db->error;

                return;
            }

            if($params != null && $param_types != null) {

                //build the array that needs to pass the args to bind_param.
                $a_params = array();
                $a_params[] = &$param_types;

                for($i=0; $i<count($params); $i++)
                    $a_params[] = &$params[$i];

                // $stmt->bind_param('s', $param); equivalent
                call_user_func_array(array($stmt, 'bind_param'), $a_params);
            }

            //run the query
            $stmt->execute();

            //bind the result
            $data = $stmt->result_metadata();
            $fields = $out = $results = array();
            $count = 0;
            $k = 0;

            if( $data != null ) {
                while($field = $data->fetch_field())
                    $fields[$count++] = &$out[$field->name];

                call_user_func_array(array($stmt, 'bind_result'), $fields);

                // fetch the result
                while ($stmt->fetch())
                    $results[$k++] = array_flip(array_flip($out));
            }

            $stmt->close();

            return $results;
        }

    }

MySQL Query:

public function getMenuItems( $id ) {
            $items = $this->db->query("SELECT id, menu, parent_id, name, url, external, position FROM menu_meta WHERE menu = ? ORDER BY position ASC", array($id), "i");
            return $items;
        }

My Database has the following items in the table: https://gyazo.com/d9a0a9472b0b51d43aafeafdd28a75c8

As you can see, I need values with 0 to be displayed. They are not null!

Using json_encode this is the output:

[{"menu":1,"position":0,"name":"Home","url":"..\/"},{"id":5,"menu":1,"parent_id":2,"name":"Add New","url":"#","position":0},{"id":2,"position":1,"external":0,"name":"Customers","url":"..\/?page=customers"},{"id":3,"menu":1,"external":0,"name":"Subscriptions","url":"..\/?page=subscriptions","position":2},{"id":4,"menu":1,"external":0,"name":"Billing","url":"..\/?page=billing","position":3}]

Is this a configuration issue with the server? Or perhaps something with my version of PHP?

EDIT: There is no value of "null" but rather the key + value are missing altogether. The first entry in the JSON is missing key "id" because value is 0. The others are missing "parent_id" because value is 0.

@Barmar Well, there isn't a value of null but rather no value at all.. (or key for that matter) — Jake Dev, Oct 21 '15 at 22:16
What is `array_flip(array_flip($out))` for? Why flip it twice? — Barmar, Oct 21 '15 at 22:20
It works almost like array_unique; and that's most likely the problem. — VolkerK, Oct 21 '15 at 22:22
@Barmar it was used for filtering. I see that it removes duplicate values which may cause the issue but now the arrays are returning duplicate values. — Jake Dev, Oct 21 '15 at 22:25
In your select statement you should use select `id` , using ( ` ) these specific types of quotes prevents mySQL reading it as a keyword and reads it as a column name. — Jujunol, Oct 21 '15 at 22:29

score 1 · Accepted Answer · answered Oct 21 '15 at 22:27

1

I think this is the problem line:

$results[$k++] = array_flip(array_flip($out));

array_flip interchanges the keys and values of an array. Array keys have to be unique. If there are two elements with the same value, flipping will try to convert them both to the same key, and one of them will be discarded. When you flip it back, you end up with just one of them. So if you have multiple fields with value 0, after the double flip you'll only have one of them.

Just use:

$results[] = $out;

(No need for $k++, assigning to $array[] pushes an element onto the end of the array.)

answered Oct 21 '15 at 22:27

Barmar

741,623
53
500
612

With this "fix" it returns this in JSON: [{"id":4,"menu":1,"parent_id":0,"name":"Billing","url":"..\/?page=billing","external":0,"position":3},{"id":4,"menu":1,"parent_id":0,"name":"Billing","url":"..\/?page=billing","external":0,"position":3},{"id":4,"menu":1,"parent_id":0,"name":"Billing","url":"..\/?page=billing","external":0,"position":3},{"id":4,"menu":1,"parent_id":0,"name":"Billing","url":"..\/?page=billing","external":0,"position":3},{"id":4,"menu":1,"parent_id":0,"name":"Billing","url":"..\/?page=billing","external":0,"position":3}] – Jake Dev Oct 21 '15 at 22:29
I was worried about that. It's because you have an array of references. I'm trying to find a way to copy it to a non-reference array. – Barmar Oct 21 '15 at 22:30
See http://stackoverflow.com/questions/894814/deep-copy-of-php-array-of-references – Barmar Oct 21 '15 at 22:31
Awesome I'll check it out – Jake Dev Oct 21 '15 at 22:33

MySQL Returning null when value = 0?

1 Answers1