PHP7 - MySQLI Error handling issues after moving site to a new server

Question

with a lot of help from some people on here I have been building an online catalogue for my work website, today I moved it from the dev server to the production server so that a couple of other guys can start testing things for me and I seem to be having an issue with the menu query. I get the following errors

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result,
boolean given in /websites/store/includes/menu.php 
on line 15

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result,
boolean given in /websites/store/includes/menu.php on line 34

Warning: Invalid argument supplied for foreach() 
in /websites/store/includes/menu.php on line 34

I have spent all day trying to work out what is going on here, the code works perfectly on the dev server but just throws errors on the production server

can anyone explain to me what is going on or how to fix it

Here is the code for the page

<?php
// Category Listing From Database
// Open MySql Database Connection
    include ('sqlopen.php');

// SQL Query for Category Listing
$sql = mysqli_query($conn, "    SELECT CategoryName, SubcategoryName, SubcategoryID 
                                FROM Products GROUP BY SubcategoryID ORDER BY CategoryName");



// Create Array from Data

$menu = array();
while ($row = mysqli_fetch_assoc($sql)) {
    // Creates First Level Array Items For Parent IDs 
    if (!in_array($row['CategoryName'], $menu['CategoryName'])) {
            $menu['CategoryName'][] = $row['CategoryName'];
            }
    if (!empty($row['SubcategoryName']))
                // Creates Second Level Array for Child IDs
                $menu['SubcategoryName'][$row['CategoryName']][] = $row['SubcategoryName'];
                // Creates Third Level Array for Category IDs
                $menu['SubcategoryID'][$row['SubcategoryName']][$row['CategoryName']][] = $row['SubcategoryID'];    
}

 //__________________________________________________________________________________________________________
// Category Menu
?>

<ul id="storenav">
<?php

foreach ($menu['CategoryName'] as $cat) { ?>
    <li><a class="sub" tabindex="1"><?php echo $cat ?></a><ul>

    <?php
    foreach ($menu['SubcategoryName'][$cat] as $subcat) {
        foreach($menu['SubcategoryID'][$subcat][$cat] as $id) ?>
            <li><a href='/store/prodlist.php?subcatid=<?php echo $id ?>'><?php echo $subcat ?></a></li>
    <?php } ?>  
    </ul></li>
<?php } ?>  

</ul>
<?php




//__________________________________________________________________________________________________________
// Open MySql Database Connection
    include ('sqlclose.php');


?>     

Answer 1

First of all, You're not handling errors correctly. Better told, you're not even checking for them. Try the code below. It will return a better description of what is going wrong.

<?php
/**
 * Created by: PhpStorm.
 * Project: Stackoverflow
 * File name: .php.
 * User: Deathstorm
 * Date: 23-6-2017.
 * Time: 10:14.
 * File Description: ...
 */
?>
<?php
// Category Listing From Database
// Open MySql Database Connection
include ('sqlopen.php');

// SQL Query for Category Listing
$sql = mysqli_query($conn, "SELECT CategoryName, SubcategoryName, SubcategoryID
FROM Products GROUP BY SubcategoryID ORDER BY CategoryName");


if ($conn->query($sql) === TRUE){
// Create Array from Data

$menu = array();
while ($row = mysqli_fetch_assoc($sql)) {
// Creates First Level Array Items For Parent IDs
if (!in_array($row['CategoryName'], $menu['CategoryName'])) {
$menu['CategoryName'][] = $row['CategoryName'];
}
if (!empty($row['SubcategoryName']))
// Creates Second Level Array for Child IDs
$menu['SubcategoryName'][$row['CategoryName']][] = $row['SubcategoryName'];
// Creates Third Level Array for Category IDs
$menu['SubcategoryID'][$row['SubcategoryName']][$row['CategoryName']][] = $row['SubcategoryID'];
}
}

else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}


//__________________________________________________________________________________________________________
// Category Menu
?>

<ul id="storenav">
    <?php

    foreach ($menu['CategoryName'] as $cat) { ?>
        <li><a class="sub" tabindex="1"><?php echo $cat ?></a><ul>

                <?php
                foreach ($menu['SubcategoryName'][$cat] as $subcat) {
                    foreach($menu['SubcategoryID'][$subcat][$cat] as $id) ?>
                        <li><a href='/store/prodlist.php?subcatid=<?php echo $id ?>'><?php echo $subcat ?></a></li>
                <?php } ?>
            </ul></li>
    <?php } ?>

</ul>
<?php




//__________________________________________________________________________________________________________
// Open MySql Database Connection
include ('sqlclose.php');


?>

Answer 2

you assume the line $sql = mysqli_query()

returns a resultset. however, if the query fails it returns FALSE, a boolean.

That's the case here.

write your code like: `

 if($sql = mysqli_query($conn, "your query here")){ 

      $menu = array();
      while($row = ...) {

      }

 }
else {
   echo 'something went wrong'; // and more error handling.
}

?> `

--

and why the query fails? The part group by has little to do in this query, as no aggragation function is present (min(), max(), count() etc) And even then: all columns should be mentioned in the part "group by"

See mysqli_error() to let Mysql tell you why that query failed.