0

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.

6
  • I don't see any null in the JSON. Commented Oct 21, 2015 at 22:14
  • @Barmar Well, there isn't a value of null but rather no value at all.. (or key for that matter) Commented Oct 21, 2015 at 22:16
  • What is array_flip(array_flip($out)) for? Why flip it twice? Commented Oct 21, 2015 at 22:20
  • It works almost like array_unique; and that's most likely the problem. Commented Oct 21, 2015 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. Commented Oct 21, 2015 at 22:25

1 Answer 1

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.)

Sign up to request clarification or add additional context in comments.

4 Comments

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}]
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.
Awesome I'll check it out

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.