So here's a little background to help with what I'm trying to accomplish (be mindful I'm also fairly new to MySQL):

I'm creating a dynamically built navigation for a website using PHP and MySQLi. I've created a class called Database where I've got helper functions, such as connect() for connecting to the database, and the like. Then I have another class called Navigation which extends Database, and there I'm creating helper functions to do specific things for the database that holds my navigation links, such as selecting and inserting and so on.

Now, the following code works flawlessly when working with my data:

function get() {
    $statement = $this->connect()->prepare(
        "SELECT * FROM `category` ORDER BY `parent_id`, `sort_order`, `category_name`"
    );

    $statement->execute();
    return $statement->get_result();
}

Later, I retrieve my data using:

$navigation_library = new Navigation();
$navigation = $navigation_library->get();

Finally, I throw the data it into some arrays I've set up:

while ($row = $navigation->fetch_assoc()) {
    $category['categories'][$row['category_id']] = $row;
    $category['parent_categories'][$row['parent_id']][] = $row['category_id'];
}

I've got get() for SELECT, add() for INSERT queries, edit() for UPDATE queries, etcetera. Doing it that way, though, I'm reusing code by going through the prepared statements over and over again for each of those... and while that's perfectly fine, I'm trying to keep it cleaner by doing the prepared statements like the following:

function perform($sql) {
    try {
        $statement = $this->connect()->prepare($sql);
        $statement->execute();
    } catch (Exception $exception) {
        $this->error = $exception;
    }

    return $statement->get_result();
}

Then pulling the data in get() like this:

$this->perform("SELECT * FROM `category` ORDER BY `parent_id`, `sort_order`, `category_name`");

This throws an exception later when the later code gets to the $navigation->fetch_assoc() part and gives me the Call to a member function fetch_assoc() on null error. I've been working almost non-stop for days and I'm feeling like I'm missing something that should be crazy obvious... but I'm stumped, as it doesn't particularly make sense: obviously there's no data there... yet there's no error in my error variable I have setup to collect the Exception result in try.

So now I come to you, DaniWeb denizens, to hopefully save the rest of the hair on my head. Please?

Hi,

Unfortunately, unless I'm misunderstanding you, it's really hard to be able to help you without fully understanding what's behind your execute() and get_result() functions.

The error is basically saying that $navigation is NULL and so you aren't able to call the fetch_assoc() method on it. $navigation is most likely NULL because the MySQL query syntax was bad (syntax error, etc.) and therefore get_result() returned NULL on error.

So I went into my perform() function, supplied an $sql variable directly, and just arbitrarily threw in some echos just to see what's going on:

function perform() {
    $sql = "SELECT * FROM `category` ORDER BY `parent_id`, `sort_order`, `category_name`";

    try {
        $statement = $this->connect()->prepare($sql);
        $statement->execute();
    } catch (Exception $exception) {
        $this->error = $exception;
    }

    echo $error;
    echo $sql;
    echo $statement;

    $thing = $statement->get_result();

    echo $thing;

    return $statement->get_result();
}

Then I called it: I got nothing for $error, I did get an echo of my $sql variable, and I got the the obvious Object of class mysqli_stmt could not be converted to string error for $statement. The echo for $thing produced nothing, it's just not there. How would I go about doing some further debugging on this?

Instead of echo, try using var_dump($statement). This will spit out the mysqli_stmt object.

You got nothing for $error because you set $this->error in the catch block, which is different than $error.

This is the error output by vardump($statement):

object(mysqli_stmt)#3 (10) { ["affected_rows"]=> int(-1) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(0) ["field_count"]=> int(5) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) } -1

So at first glance it seems like it's not being executed? Playing around with it, I used $statement->store_result() after my execution, and got back this:

object(mysqli_stmt)#3 (10) { ["affected_rows"]=> int(14) ["insert_id"]=> int(0) ["num_rows"]=> int(14) ["param_count"]=> int(0) ["field_count"]=> int(5) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) }

I get the gist of what I'm looking at, but not a clue what it means in-depth. Something isn't being stored and passed back to my get() function? I'm completely out of my depth and only my intense curiosity is keeping me going.

What is the specific MySQL statement you're trying to execute? What if you just try SELECT * FROM category on its own?

Doesn't make a difference.

I'm suspecting either data isn't getting passed correctly, or I'm trying to retrieve the data incorrectly. Or I'm just declaring something wrong, which wouldn't surprise me as I'm in the "this SHOULD work... theoretically..." territory with MySQLi. I've only been messing with it for about five days and I'm still not sure of PHP and MySQLi's rules of engagement. It's most likely simple and I'm just not seeing it, and you can't either due to not having the entire picture.

So, for the sake of transparency, here's the entirety of database.php, where I began:

<?php
class Database {
    private $host = DB_HOST;
    private $username = DB_USERNAME;
    private $password = DB_PASSWORD;
    private $database = DB_NAME;

    public $error = "";
    public $connect;

    function connect() {
        $instance = new mysqli($this->host, $this->username, $this->password, $this->database);

        if ($instance->connect_error) {
            $error = $instance->connect_error;
            die();
        } else {
            $this->connect = $instance;
        }

        return $this->connect;
    }

    function destroy() {
        $instance->close();
    }

    function perform() {
        $sql = "SELECT * FROM `category`";

        try {
            $statement = $this->connect()->prepare($sql);
            $statement->execute();
            $statement->store_result();
        } catch (Exception $exception) {
            printf($exception);
        }

        var_dump($statement);
        return $statement->get_result();
    }
}
?>

And here is the entirety of menu.php (you'll notice I did change some things around since posting my snippets above):

<?php
class Navigation extends Database {
    function get() {
        $this->perform();
//        $statement = $this->connect()->prepare(
//            "SELECT * FROM `category` ORDER BY `parent_id`, `sort_order`, `category_name`"
//        );
//
//        $statement->execute();
//        return $statement->get_result();
    }

    function add($category_name, $category_link, $parent_id, $sort_order) {
        $statement = $this->connect()->prepare(
            "INSERT INTO `category` (`category_name`, `category_link`, `parent_id`, `sort_order`)
            VALUES (?, ?, ?, ?)"
        );

        $statement->bind_param("ssii", $category_name, $category_link, $parent_id, $sort_order);
        $statement->execute();
    }

    function edit($category_name, $category_link, $parent_id, $sort_order, $category_id) {
        $statement = $this->connect()->prepare(
            "UPDATE `category` SET `category_name`=?, `category_link`=?, `parent_id`=?, `sort_order`=? WHERE `category_id`=?"
        );

        $statement->bind_param("ssiii", $category_name, $category_link, $parent_id, $sort_order, $category_id);
        $statement->execute();
    }

    function delete($category_id) {
        $statement = $this->connect()->prepare(
            "DELETE FROM `category` WHERE `category_id`=?"
        );

        $statement->bind_param("i", $category_id);
        $statement->execute();
    }
}

$navigation_library = new Navigation();
$navigation = $navigation_library->get();

$navigation_links = array(
    'links' => array(),
    'parent_links' => array()
);

while ($item = $navigation->fetch_assoc()) {
    $navigation_links['links'][$item['category_id']] = $item;
    $navigation_links['parent_links'][$item['parent_id']][] = $item['category_id'];
}

function buildNavigation($parent, $navigation_links) {
    $html = "";

    if (isset($navigation_links['parent_links'][$parent])) {
        $html .= "<ul>";

        foreach ($navigation_links['parent_links'][$parent] as $link_id) {
            if (!isset($navigation_links['parent_links'][$link_id])) {
                $html .= "<li><a href='" . $navigation_links['links'][$link_id]['category_link'] . "'>" . $navigation_links['links'][$link_id]['category_name'] . "</a></li>";
            }

            if (isset($navigation_links['parent_links'][$link_id])) {
                $html .= "<li><a href='" . $navigation_links['links'][$link_id]['category_link'] . "'>" . $navigation_links['links'][$link_id]['category_name'] . "</a>";
                $html .= buildNavigation($link_id, $navigation_links);
                $html .= "</li>";
            }
        }

        $html .= "</ul>";
    }

    return $html;
}
?>

And here's the dummy data I put into my MySQL database, for completionists sake:

CREATE TABLE `category` (
  `category_id` int(10) UNSIGNED NOT NULL,
  `category_name` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `category_link` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `category` (`category_id`, `category_name`, `category_link`, `parent_id`, `sort_order`) VALUES
(1, 'Home', '', 0, 0),
(2, 'Tutorials', '#', 0, 1),
(3, 'Java', 'java', 2, 1),
(4, 'Python', 'python', 2, 1),
(5, 'Frameworks', '#', 0, 2),
(6, 'JSF', 'jsf', 5, 2),
(7, 'Struts', 'struts', 5, 2),
(8, 'Spring', 'spring', 5, 2),
(9, 'Hibernate', 'hibernate', 5, 2),
(10, 'Webservices', '#', 0, 3),
(11, 'REST', 'rest', 10, 3),
(12, 'SOAP', 'soap', 10, 3),
(13, 'Contact', 'contact', 0, 4),
(14, 'About', 'about', 0, 5);

ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`),
  ADD UNIQUE KEY `unique` (`category_name`);

ALTER TABLE `category`
  MODIFY `category_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
COMMIT;

I figured out my issue: I was going about fetching the data completely incorrectly. I can't provide a specific solution as I haven't figured out exactly how to fetch the data as of yet, due to mysqlnd not behind supported (for fetch_all) on my development server. I'd build from source, but my hosting provider doesn't provide it either, so it's generally moot.

I did find a roundabout solution, but it just seems complex to my eye and I just don't understand how to implement it in my code. While the learning opportunity is there, I'm actually more interested in just converting over to PDO as my Question Solved solution. It'll save myself a lot of time and effort, and my stomach from ingesting more coffee than should healthily be consumed, plus everything will work on a universal database level regardless. It's a win-win for me as I'm only a week invested in MySQLi and what I've learned can be easily translated over.

I sincerely appreciate all your help, Dani.

You're very welcome! Do you mind posting your solution (as roundabout as it may be) in case it could be of help to others who stumble upon this thread? Good luck with PDO.

I searched the Interwebs and came across this piece of genius from someone else while I was searching for ways to retrieve a SELECT query functionally (I'm not 100% positive where I got it, nor do I 100% understand it):

function fetch($sql, $key = null, $value = null) {
    $result = false;

    try {
        $this->statement = $this->connect()->prepare($sql);
        $this->statement->execute();

        if (isset($key)) {
            $result = array();

            if (isset($value)) {
                while ($row = $this->statement->fetch()) {
                    $result[$row[$key]] = $row[$value];
                }
            } else {
                while ($row = $this->statement->fetch()) {
                    $result[$row[$key]] = $row;
                }
            }
        } else {
            $result = $this->statement->fetch_all();
        }
    } catch (Exception $exception) {
        $this->error = $exception;
        return false;
    }

    $this->statement = null;
    return $result;
}

However, fetch_all() requires that PHP be built with the mysqlnd driver. This poses problems with some popular PHP software available on the market, as they don't support it (such as Roundcube). Additionally, mysqlnd seems to be somewhat unstable and cause PHP to crash on occasion, which is why it's seldom used. Overall I was not recommended to use it.

Further searching lead me to someone saying, "The typical way to construct an associative array is in a while loop," so this:

$results_array = array();
$result = $mysqli->query($query);

while ($row = $result->fetch_assoc()) {
    $results_array[] = $row;
}

After staring at that and my code, I just didn't get it. Hopefully it'll help someone, though.