Converting script from mysql to mysqli

Question

I am trying to convert the following script from mysql to mysqli but am having problems.

This is the orginal script using mysql :

    //database connection
    $dbname = 'nursery';
    $link = mysql_connect("localhost","root","") or die("Couldn't make connection.");
    $db = mysql_select_db($dbname, $link) or die("Couldn't select database");
    ?>
    <div style="width:728px;margin:auto;">
        <div id='cssmenu'>
            <ul>
    <?php
    function query($parentid) { //function to run a query
        $query = mysql_query ( "SELECT * FROM menu WHERE parentid=$parentid" );
        return $query;
    }
    function has_child($query) { //This function checks if the menus has childs or not
        $rows = mysql_num_rows ( $query );
        if ($rows > 0) {
            return true;
        } else {
            return false;
        }
    }
    function fetch_menu($query) {
        while ( $result = mysql_fetch_array ( $query ) ) {
            $menu_id = $result ['id'];
            $title = $result ['title'];
            $url = $result ['url'];
            echo "<li  class='has-sub '><a href='{$url}'><span>{$title}</span></a>";
            if (has_child ( query ( $menu_id ) )) {
                echo "<ul>";
                fetch_menu ( query ( $menu_id ) );
                echo "</ul>";
            }
            echo "</li>";
        }
    }
    fetch_menu (query(0)); //call this function with 0 parent id
    ?>
            </ul>
        </div>
        </div>

So I am trying to convert it into mysqli which the rest of my site uses and have got this far :

    <?php
    require("../login/common.php");
    //database connection;
    include '../connect.php';
    ?>
    <div style="width:728px;margin:auto;">
        <div id='cssmenu'>
            <ul>
    <?php
    function query($parentid) { //function to run a query
        $query = mysqli_query ($db, "SELECT * FROM menu WHERE parentid=$parentid" );
        return $query;
    }
    function has_child($query) { //This function checks if the menus has childs or not
        $rows = mysqli_num_rows ( $query );
        if ($rows > 0) {
            return true;
        } else {
            return false;
        }
    }
    function fetch_menu($query) {
        while ( $result = mysqli_fetch_array ( $query ) ) {
            $menu_id = $result ['id'];
            $title = $result ['title'];
            $url = $result ['url'];
            echo "<li  class='has-sub '><a href='{$url}'><span>{$title}</span></a>";
            if (has_child ( query ( $menu_id ) )) {
                echo "<ul>";
                fetch_menu ( query ( $menu_id ) );
                echo "</ul>";
            }
            echo "</li>";
        }
    }
    fetch_menu (query(0)); //call this function with 0 parent id
    ?>
            </ul>
        </div>
        </div>

The problem is I am getting all kinds of errors and am not totally sure why, I know the db error is probably down to the $db not being passed into the function, but even if I write function fetch_menu($query,$db) { I still get the same error ?.

Notice: Undefined variable: db in         C:\easyphpserver\data\localweb\projects\nursery\menu\menu3.php on line 147

  Warning: mysqli_query() expects parameter 1 to be mysqli, null given in    C:\easyphpserver\data\localweb\projects\nursery\menu\menu3.php on line 147

   Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in       C:\easyphpserver\data\localweb\projects\nursery\menu\menu3.php on line 159

The contents of connect.php is :

 <?php
$db = new mysqli('localhost', 'root', '', 'nursery');
$db->set_charset('utf8mb4');  
  if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}  
 ?>

This `$link = mysql_connect("localhost","root","")` wouldn't happen to be inside your `connect.php` file, would it? — Funk Forty Niner, May 14 '14 at 14:46
Your connect also needs to be `mysqli_` that's the problem. `mysql_` and `mysqli_` functions do not mix. — Funk Forty Niner, May 14 '14 at 14:48
set_charset('utf8mb4'); if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } ?> — Iain Simpson, May 14 '14 at 14:48
It's usually a lot easier to spot errors when you use the object-oriented interface to `mysqli. That way you don't need to remember if or where you put the database handle in as an argument. — tadman, May 14 '14 at 14:53
Curious, which MySQL version are you using? I ask this because of your using `set_charset('utf8mb4')` apparently it was only introduced in 5.5.3 as per this answer on SO http://stackoverflow.com/a/21929328/ it may have something to do with it. Plus, just to test, try setting your `$db` as a global just to see if it will change anything. — Funk Forty Niner, May 14 '14 at 14:59

score 0 · Answer 1 · answered May 14 '14 at 15:26

0

pass $db to query()

function query($parentid, $db) {
    $query = mysqli_query ($db, "SELECT * FROM menu WHERE parentid=$parentid" );
    return $query;
}

answered May 14 '14 at 15:26

meda

45,103
14
92
122

I tried this and still got errors, I have come up with a solution though, posting answer in a min. – Iain Simpson May 15 '14 at 08:31

score 0 · Answer 2 · answered May 15 '14 at 08:33

Ok, so this solution seems to work, by putting the db connection inside the function, the thing is if I include the $db in the function by typing function query($parentid,$db) { this doesn't work and causes errors, but if I put the db connection inside the function it works.....very odd.

    <?php
    //error_reporting(0);
    require("../login/common.php");
    //database connection;
    include '../connect.php';
    ?>
    <div style="width:728px;margin:auto;">
        <div id='cssmenu'>
            <ul>
    <?php
    function query($parentid) {
        $db = new mysqli('localhost', 'root', '', 'nursery');
    $db->set_charset('utf8mb4');  
    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }
         //function to run a query
        $query = mysqli_query ($db, "SELECT * FROM menu WHERE parentid=$parentid" );
        return $query;
    }
    function has_child($query) { //This function checks if the menus has childs or not
        $rows = mysqli_num_rows ( $query );
        if ($rows > 0) {
            return true;
        } else {
            return false;
        }
    }
    function fetch_menu($query) {
        while ( $result = mysqli_fetch_array ( $query ) ) {
            $menu_id = $result ['id'];
            $title = $result ['title'];
            $url = $result ['url'];
            echo "<li  class='has-sub '><a href='{$url}'><span>{$title}</span></a>";
            if (has_child ( query ( $menu_id ) )) {
                echo "<ul>";
                fetch_menu ( query ( $menu_id ) );
                echo "</ul>";
            }
            echo "</li>";
        }
    }
    fetch_menu (query(0)); //call this function with 0 parent id
    ?>
            </ul>
        </div>
        </div>

Converting script from mysql to mysqli

2 Answers2