Try this version. It contains: prepared statements, exception handling and error reporting/display. I hope it will help you in finding out the solution. At first sight it seems that your fetching returns NULL. So the problem is given by mysqli_query().
I wrote two versions, actually. You can use the OOP style (my recommendation) by including "oopFunctions.php" in "index.php", or you can instead use the procedural style by including "proceduralFunctions.php" in "index.php".
Good luck!
MySQLi with prepared statements and exception handling
index.php (main page)
<?php
require_once 'configs.php';
require_once 'oopFunctions.php';
// require_once 'proceduralFunctions.php';
/*
* Activate PHP error reporting/display.
* Display ONLY on development code, NEVER on production code!
* ALWAYS resolve WARNINGS and ERRORS.
* I recommend to always resolve NOTICES too.
*/
activateErrorReporting(E_ALL, TRUE);
//activateErrorReporting(E_ALL, FALSE);
/**
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
$parent = 0;
// Create db connection.
$connection = createConnection(
MYSQL_HOST
, MYSQL_DATABASE
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_PORT
);
// Fetch categories by parent.
$categories = fetchCategoriesByParent($connection, $parent);
// Close db connection.
closeConnection($connection);
} catch (mysqli_sql_exception $e) {
echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
exit();
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
/*
* Disable internal report functions.
*
* MYSQLI_REPORT_OFF: Turns reporting off.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Test categories</title>
<!-- Bootstrap -->
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" rel="stylesheet" />
<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
<!-- Bootstrap -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" type="text/javascript"></script>
</head>
<body>
<nav class="navbar navbar-default navbar-fixed-top">
<div class="container">
<a href="home.php" class="navbar-brand">
Bizibay
</a>
<ul class="nav navbar-nav">
<?php
foreach ($categories as $key => $category) {
?>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown">
Men<span class="caret"></span>
</a>
<ul class="dropdown-menu" role="menu">
<li><a href="#">Shirts</a></li>
<li><a href="#">Pants</a></li>
<li><a href="#">Shoes</a></li>
<li><a href="#">Accessories</a></li>
</ul>
</li>
<?php
}
?>
</ul>
</div>
</nav>
</body>
</html>
configs.php
<?php
/*
* ----------------
* Database configs
* ----------------
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '3306');
define('MYSQL_DATABASE', '...');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
proceduralFunctions.php
<?php
/**
* Toggle error reporting.
*
* @param integer $level Error level.
* @param bool $displayErrors Display errors if TRUE, hide them otherwise.
* @return void
*/
function activateErrorReporting($level = E_ALL, $displayErrors = TRUE) {
error_reporting($level);
ini_set('display_errors', ($displayErrors ? 1 : 0));
}
/**
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
* @param string $host Host.
* @param string $dbname Database name.
* @param string $username Username.
* @param string $password Password.
* @param string $port [optional] Port.
* @return mysqli Db connection.
* @throws mysqli_sql_exception
*/
function createConnection($host, $dbname, $username, $password, $port = '3306') {
$connection = mysqli_connect($host, $username, $password, $dbname, $port);
if (!$connection) {
throw new Exception('Connect error: ' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
return $connection;
}
/**
* Close db connection.
*
* @param mysqli $connection Connection instance.
* @return void
* @throws Exception
*/
function closeConnection($connection) {
$connectionClosed = mysqli_close($connection);
if (!$connectionClosed) {
throw new Exception('The database connection can not be closed!');
}
}
/**
* Fetch categories by parent.
*
* @param mysqli $connection Connection instance.
* @param integer $parent Parent.
* @return array Categories list.
* @throws Exception
*/
function fetchCategoriesByParent($connection, $parent) {
$fetchedData = array();
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT * FROM categories WHERE parent = ?';
/*
* Prepare the SQL statement for execution.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$statement = mysqli_prepare($connection, $sql);
if (!$statement) {
throw new Exception('Prepare error: The sql statement can not be prepared!');
}
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to mysqli::prepare. The first
* argument of mysqli_stmt::bind_param is a string that contains one
* or more characters which specify the types for the corresponding bind variables.
*
* See: http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$bound = mysqli_stmt_bind_param($statement, 'i', $parent);
if (!$bound) {
throw new Exception('Bind error: The variables could not be bound to the prepared statement!');
}
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* See: http://php.net/manual/en/mysqli-stmt.execute.php
*/
$executed = mysqli_stmt_execute($statement);
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
/*
* Get the result set from the prepared statement. In case of
* failure use errno, error and/or error_list to see the error.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* See:
* http://php.net/manual/en/mysqli-stmt.get-result.php
* https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = mysqli_stmt_get_result($statement);
if (!isset($result) || !$result) {
throw new Exception('Get result error: ' . mysqli_error($connection));
}
/*
* Get the number of rows in the result.
*
* See: http://php.net/manual/en/mysqli-result.num-rows.php
*/
$numberOfRows = mysqli_num_rows($result);
/*
* Fetch data and save it into $fetchedData array.
*
* See: http://php.net/manual/en/mysqli-result.fetch-array.php
*/
if ($numberOfRows > 0) {
// Fetch all rows at once.
$fetchedData = mysqli_fetch_all($result, MYSQLI_ASSOC);
// ...or fetch one row at a time.
// while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
// $fetchedData[] = $row;
// }
}
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* See: http://php.net/manual/en/mysqli-result.free.php
*/
mysqli_free_result($result);
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* See: http://php.net/manual/en/mysqli-stmt.close.php
*/
$statementClosed = mysqli_stmt_close($statement);
if (!$statementClosed) {
throw new Exception('The prepared statement could not be closed!');
}
return $fetchedData;
}
oopFunctions.php
<?php
/**
* Toggle error reporting.
*
* @param integer $level Error level.
* @param bool $displayErrors Display errors if TRUE, hide them otherwise.
* @return void
*/
function activateErrorReporting($level = E_ALL, $displayErrors = TRUE) {
error_reporting($level);
ini_set('display_errors', ($displayErrors ? 1 : 0));
}
/**
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
* @param string $host Host.
* @param string $dbname Database name.
* @param string $username Username.
* @param string $password Password.
* @param string $port [optional] Port.
* @return mysqli Db connection.
* @throws mysqli_sql_exception
*/
function createConnection($host, $dbname, $username, $password, $port = '3306') {
$connection = new mysqli($host, $username, $password, $dbname, $port);
if ($connection->connect_error) {
throw new Exception('Connect error: ' . $connection->connect_errno . ' - ' . $connection->connect_error);
}
return $connection;
}
/**
* Close db connection.
*
* @param mysqli $connection Connection instance.
* @return void
* @throws Exception
*/
function closeConnection($connection) {
$connectionClosed = $connection->close();
if (!$connectionClosed) {
throw new Exception('The db connection could not be closed!');
}
}
/**
* Fetch categories by parent.
*
* @param mysqli $connection Connection instance.
* @param integer $parent Parent.
* @return array Categories list.
* @throws Exception
*/
function fetchCategoriesByParent($connection, $parent) {
$fetchedData = array();
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT * FROM categories WHERE parent = ?';
/*
* Prepare the SQL statement for execution.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('Prepare error: ' . $connection->errno . ' - ' . $connection->error);
}
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to mysqli::prepare. The first
* argument of mysqli_stmt::bind_param is a string that contains one
* or more characters which specify the types for the corresponding bind variables.
*
* See: http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$bound = $statement->bind_param('i', $parent);
if (!$bound) {
throw new Exception('Bind error: The variables could not be bound to the prepared statement');
}
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* See: http://php.net/manual/en/mysqli-stmt.execute.php
*/
$executed = $statement->execute();
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
/*
* Get the result set from the prepared statement. In case of
* failure use errno, error and/or error_list to see the error.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* See:
* http://php.net/manual/en/mysqli-stmt.get-result.php
* https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
if (!isset($result) || !$result) {
throw new Exception('Get result error: ' . $connection->errno . ' - ' . $connection->error);
}
/*
* Get the number of rows in the result.
*
* See: http://php.net/manual/en/mysqli-result.num-rows.php
*/
$numberOfRows = $result->num_rows;
/*
* Fetch data and save it into $fetchedData array.
*
* See: http://php.net/manual/en/mysqli-result.fetch-array.php
*/
if ($numberOfRows > 0) {
// Fetch all rows at once.
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);
// ...or fetch one row at a time.
// while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
// $fetchedData[] = $row;
// }
}
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* See: http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* See: http://php.net/manual/en/mysqli-stmt.close.php
*/
$statementClosed = $statement->close();
if (!$statementClosed) {
throw new Exception('The prepared statement could not be closed!');
}
return $fetchedData;
}
