Set database info once and use in multiple php functions

Question

I am fairly new to using functions and I've always been a "low-end" coder so i decided to spend more time working on normal, safe and quality code.

Now i want to have multiple functions in one .php file and therefore i wan't to set all database info once and use it in all functions that require it. I think i am doing something wrong with not making it global or anything but i tried all kind of different combinations. None would work.

This is my code now:

<?php

function getExperience($userid) {

$dbHost     = "*";
$dbUsername = "*";
$dbPassword = "*";
$dbName     = "*";

$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

$sql = "SELECT * FROM experience WHERE userid = ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $userid);
$stmt->execute();

$result = $stmt->get_result();

    while ($print = $result->fetch_assoc()){
        echo $print['employer'];
    }

}

?>

This code works just fine and does exactly what i want it to do but somehow i feel i don't have to put all database info into each functions. I would love to know if there is a way to set the host, username, password and db name once and use the connect in each function i need it to.

As i told before i tried to global the values after putting them outside of the function but i couldn't get it to work.

Any advice is appreciated. Thanks in advance!

Answer 1

In your script you should only make one call to the database so that your not overloading your database with connections. You are better off to share your original connection to allow for multiple SQL statements to be ran. Without knowing more of exactly what you are looking to do, you its hard to see if this can be broken out more.

//tested - works
<?php

$dbHost     = "localhost";
$dbUsername = "x";
$dbPassword = "x";
$dbName     = "test";

$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

function getExperience($dbcon, $userid) {

    $output = "";

    $sql = "SELECT * FROM experience WHERE userid = ?";

    $stmt = $dbcon->prepare($sql); //can fail if SQL statement isnt valid;
    $stmt->bind_param('i', $userid);
    $stmt->execute();

    $result = $stmt->get_result();

    if($result->num_rows == 1){
        $print = $result->fetch_assoc();
        $output = $print['employer'];
    }
    else {
        while ($print = $result->fetch_assoc()){
            $output .= $print['employer'] .", ";
        }
    }

    $stmt->close();
    return $output;

}

echo "testing loop for function <br />";
//example
for($i = 0; $i <= 10; $i++) {
    echo $i . "<br />";
    echo getExperience($conn, $i);
    echo "<br />";
}

$conn->close(); // always good to close your connection after completed.
?>