Turn 4-level adjacency database into array and then into JSON format

Question

I am trying to learn as I go so I don't reach out for help until I've exhausted all other avenues. I would appreciate some help/direction here because one of the solutions I found on Stack Overflow (near the bottom of the page) does not seem to work for me. After several hours, I'm ready to throw in the towel! I've found references informing me that various extensions have been depreciated and/or removed altogether over time so I'm now wondering if this code snippet is older and doesn't run for a different reason (although I'm aware that I might also be missing something). As reference I am on a shared server so I can't change the PHP version (5.6.40) or the mySQL version (5.6.46).

My table is named (for testing) test_json. I only have three columns: id, parent, name. The adjacency data is max 4-deep. I am trying to build an array that I can then convert (I'm thinking json_encode($arr)). Based on the code snippet I copied/paste/modified, I thought it would work but I get nothing on the screen even after using print_r and var_dump. In Chrome dev tools there are no connection errors.

Here is the code I'm using:

echo '<pre>';

$categories = OrgChart::getTopCategories();
print_r($categories);

echo '</pre>';

class OrgChart
{

    public static function getTopCategories()
    {
        return self::getCategories('parent = 0');
    }

    public static function getCategories($where = '')
    {
        if ($where) $where = " WHERE $where";
        $result = mysql_query("SELECT * FROM test_json $where");

        $categories = array();

        while ($category = mysql_fetch_array($result)){
        $my_id = $category['id'];
        $category['children'] = OrgChart::getCategories("parent = $my_id");
            $categories[] = $category;
        }

        mysql_free_result($result);
        return $categories;
    }
}

Updates after several hours of exploring options:

Christos and Dharman were right and the code I attempted to use had a number of depreciated (removed?) extensions. Now that I've fixed those and have zero errors (using error_reporting(E_ALL); ini_set("display_errors", 1); ini_set('display_startup_errors', 1);) I'm still stymied why I only get this output: Array ( )

from the code below. Any other suggestions on what I'm missing? Thank you for any help that can be provided. I could be looking right at it but my eyes are starting to cross.

echo '<pre>';

$categories = OrgChart::getTopCategories();
print_r($categories);

echo '</pre>';

class OrgChart
{
    public static function getTopCategories()
    {
        return self::getCategories('parent = 0');
    }

    public static function getCategories($where = '')
    {
        if ($where) $where = " WHERE $where";
//      $result = mysqli_query(&db, "SELECT * FROM test_json $where");
        Database::initialize();
        $result = mysqli_query (Database::$conn, "SELECT * FROM test_json $where");
        $categories = array();

        while ($category = mysqli_fetch_array($result)){
        $my_id = $category['id'];
        $category['children'] = OrgChart::getCategories("parent = $my_id");
            $categories[] = $category;
        }

//  mysql_free_result($result);
    return $categories;

    }
}

If you get nothing on the screen, then there might be a PHP error that does not yield because errors are disabled. Try using `error_reporting(E_ALL); ini_set("display_errors", 1);` at the beginning of your script. — Christos Lytras, Feb 22 '20 at 16:39
Hello Christos. Thank you. I have loaded that into my script and it shows errors. I will explore and loop back with my findings. Much appreciated! — Neil_Tinkerer, Feb 22 '20 at 16:44
Hello. It seems I am getting an error (Undefined variable: mysqli) at the SELECT statement even though I have it declared when connecting to the SQL DB ($mysqli = new mysqli($hn, $un, $pw, $db);). Does it make a different that the query is INSIDE a PHP Class statement? I thought the PHP '$' made it global. $query = "SELECT * FROM test_json $where"; $result = $mysqli->query($query); — Neil_Tinkerer, Feb 22 '20 at 17:32
Hello. I may be new to Stack Overflow which means I may not totally understand how everything works but - I thought - this site was for people with VARYING levels of experience. To receive a 'downvote' after struggling for hours and finally giving in and asking SO for some help seems pretty harsh. Would anyone be willing to help me understand what I did/did not do that someone felt obligated to downvote my question? Thank you. — Neil_Tinkerer, Feb 23 '20 at 14:48

score 0 · Accepted Answer · answered Feb 24 '20 at 12:49

Didn't hear back from anyone so I kept exploring line by line. To solve, here is what I found:

In my code, return self::getCategories('parent = 0'); was the culprit. The use of "0" or the use of "= NULL" yielded no results. I'm sure this is well known (but not to me which is why I asked for help) but you must use "IS NULL" or "IS NOT NULL" when working with NULL values for this type of query. I post this in case there are others - like me - who don't currently know this.

Turn 4-level adjacency database into array and then into JSON format

1 Answers1