PHP/MySQL - I need help with PDO execute() and fetch

[mexabet]

I'm trying to automatically populate a navigation menu based on available categories in the database. As follows is my code:

<?php

    $sidenav = query("SELECT cat_id, cat_name FROM prod_cat ORDER By cat_name LIMIT 20");



    if ($sidenav != false)

    {

        $sidenav->execute();

        while($result = $sidenav->fetch(PDO::FETCH_BOTH))

        {

            echo "<div class='left_button'><a href='/customcms/products/index.php?page=list-product&category={$result['cat_id']}'>{$result['cat_name']}</a></div>";



        }

    }



?>

However, the code "$sidenav->execute();" gives the following error message: "Fatal error: Call to a member function execute() on a non-object on line ...".

Note: Please, be advised that "query" is a custom function and I'm connecting to the database via PDO.

Your help would be much appreciated, as always.

[pritaeas]

Apparently your custom function returns null instead of a PDO statement object instance.

[mexabet]

Thanks for the insight. See what I have in my functions.php for the query() function:

 function query(/* $sql [, ... ] */)

    {

        // SQL statement

        $sql = func_get_arg(0);



        // parameters, if any

        $parameters = array_slice(func_get_args(), 1);



        // try to connect to database

        static $handle;

        if (!isset($handle))

        {

            try

            {

                // connect to database

                $handle = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);



                // ensure that PDO::prepare returns false when passed invalid SQL

                $handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 

            }

            catch (Exception $e)

            {

                // trigger (big, orange) error

                trigger_error($e->getMessage(), E_USER_ERROR);

                exit;

            }

        }



        // prepare SQL statement

        $statement = $handle->prepare($sql);

        if ($statement === false)

        {

            // trigger (big, orange) error

            trigger_error($handle->errorInfo()[2], E_USER_ERROR);

            exit;

        }



        // execute SQL statement

        $results = $statement->execute($parameters);



        // return result set's rows, if any

        if ($results !== false)

        {

            return $statement->fetchAll(PDO::FETCH_ASSOC);

        }

        else

        {

            return false;

        }

    }  

Please, how do I modify the above query() function to return the desired values?

[pritaeas]

Line 87, your fetchAll, returns an array (assuming your query does not fail). You cannot execute fetch on an array, only on a PDO statement. So either return $statement in your method, or change your while loop into a foreach.

[jstfsklh211]

your function doesn't return a statement it returns the result

$statement = $handle->prepare($sql);

you would need to return $statement for what you're attempting

[mexabet]

@pritaeas, I was able to fix the issue by following your suggestion - changing the while loop to foreach. Thanks a lot.

@jstfsklh211, lots of thanks for your insight. I'll keep that in mind, as I learn.