Why can't I run query twice in PHP and MySQL?

Question

Below is my code for running a MySQL query from PHP:

mysqli_connect.php

    <?php
        DEFINE ('DB_USER', [my_username]);
        DEFINE ('DB_PASSWORD', [my_password]);
        DEFINE ('DB_HOST', 'localhost');
    
        $databases = array();
        $db_school = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, 'db_school')
        OR die('Could not connect to MySQL.db_school');
        $databases['db_school'] = $db_school;
    
        $db_family_finance = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, 'db_family_finance')
        OR die('Could not connect to MySQL.db_family_finance');
        $databases['db_family_finance'] = $db_family_finance;
    ?>

get_mysql_data.php

    <?php    
        function query_database($query) {
            require_once('../mysqli_connect.php');
    
            $response = mysqli_query($db_school, $query);
    
            if ($response) {
                echo "ran query!<br>";
            } else {
                echo "couldn't run query!<br>";
            }
        
            return $response;
        }
    
        query_database("SELECT * FROM tests"); //prints out "ran query!"
        query_database("SELECT * FROM tests"); //prints out "couldn't run query!"
    ?>

I am running the same function twice. The first time it returns what I want it to. The second time it doesn't. Why would it do this?

I made a few changes to the code as seen above and I now get this printed to the screen:

ran query!

Notice: Undefined variable: db_school in C:\xampp\htdocs\websites\tutorials\get_mysql_data.php on line 6

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\websites\tutorials\get_mysql_data.php on line 6
couldn't run query!

Have you checked the output of `mysqli_error()` (e.g. in the else "couldn't run query"-bit)? — dbrumann, Dec 17 '16 at 07:30
The `@` in front of the methods will suppress errors. You should remove it, at the very least for debugging. — dbrumann, Dec 17 '16 at 07:33
What is the side effect of using require_once inside a function? It's a bit like the connection isn't being established on the 2nd and subsequent calls... Because you've used @ which is BAD, you are masking any errors being reported. — TimBrownlaw, Dec 17 '16 at 07:56
When I add `echo mysqli_error($db_school) . "
";` to the `else` I get this error: `Notice: Undefined variable: db_school in C:\xampp\htdocs\websites\tutorials\get_mysql_data.php on line 11 Warning: mysqli_error() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\websites\tutorials\get_mysql_data.php on line 11` — ToMakPo, Dec 17 '16 at 08:11
The mysqli_error() needs to get the connection as first argument. Judging from your code above: `mysqli_error($databases['db_school']);` or `$db_school` Also it seems that by moving the script you messed up the scope and it can't find `$db_school` anymore. — dbrumann, Dec 17 '16 at 12:04

score 0 · Answer 1 · answered Aug 16 '22 at 13:53

When you include a file inside a function, the scope of all variables, constants etc are local to the function.

Therefore, the first invocation the mysqli_connect.php is included and everything works.

In the second invocation, the mysqli_connect.php is NOT included because of the require_once. Inside the function, this time, those constants and variables do not exist.

This is an example of how to fix it, depending on what you are doing elsewhere.

Here you declare $dbschool global - this is not generally a good idea.

<?php    
    require_once('../mysqli_connect.php');

    function query_database($query) {
        global $db_school;
        $response = mysqli_query($db_school, $query);

        if ($response) {
            echo "ran query!<br>";
        } else {
            echo "couldn't run query!<br>";
        }

        return $response;
    }

    query_database("SELECT * FROM tests"); //prints out "ran query!"
    query_database("SELECT * FROM tests"); //prints out "couldn't run query!"
?>

or

Here you pass the database to the function

<?php    
    require_once('../mysqli_connect.php');

    function query_database($database, $query) {
        $response = mysqli_query($database, $query);

        if ($response) {
            echo "ran query!<br>";
        } else {
            echo "couldn't run query!<br>";
        }

        return $response;
    }

    query_database($db_school, "SELECT * FROM tests"); //prints out "ran query!"
    query_database($db_school, "SELECT * FROM tests"); //prints out "couldn't run query!"
?>
```php

Why can't I run query twice in PHP and MySQL?

1 Answers1