Querying a database within a PHP function

Question

I'm trying to load some data from a database using PHP, but for some reason it doesn't work when I put it inside a function. If I try the code without a function, it works fine:

//$dbc connection
$call1 = 0;
$output = '';
$query = "select * from artists order by lname limit $call1, 15";
$result = mysqli_query($dbc, $query);
while($row = mysqli_fetch_array($result)){
    $output .= "<ul>";
    $output .= "<li>" . $row['name'] . "</li>";
    $output .= "</ul>";
}

However, when I change the code to be inside a function, I don't get anything from the database (or at least it won't return anything):

//$dbc connection
$call1 = 0;
$output = '';
function loadArtists($call){
    $query = "select * from artists order by lname limit $call, 15";
    $result = mysqli_query($dbc, $query);
    while($row = mysqli_fetch_array($result)){
        $output .= "<ul>";
        $output .= "<li>" . $row['name'] . "</li>";
        $output .= "</ul>";
    }
}
loadArtists($call1);

What am I doing wrong here?

Answer 1

You cannot use $dbc in your function, because it is a global variable.

You can use either

function loadArtists($call){
    global $dbc;
    ...
}

to make $dbc known to loadArtists() or pass it as a second parameter

function loadArtists($dbc, $call){
...
}

and call it as

loadArtists($dbc, $call1);

Answer 2

As I mentioned in one of my comments, using global to fix the scope of your connection is poor practice. The correct way to pass your connection is like so:

$dbc = mysqli_connect("localhost", "my_user", "my_password", "world");

$call1 = 0;
$output = '';
function loadArtists($call, $dbc){
    $query = "select * from artists order by lname limit $call, 15";
    $result = mysqli_query($dbc, $query);
    while($row = mysqli_fetch_array($result)){
        $output .= "<ul>";
        $output .= "<li>" . $row['name'] . "</li>";
        $output .= "</ul>";
    }
}
loadArtists($call1, $dbc);

Answer 3

Declaring your username and password on the same page as the code you're executing every time you want to make a DB connection is bad practice because:

1. You could potentially have more than one page to edit if you move to a different host or a different environment than the dev environment.
2. If you declare it outside of the root you can limit access to the DB password from FTP accounts.

I like to use a function for the connection so this way if the connection closes you can reopen it at will (reducing server overhead). Also you don't have to set it as a global var inside the function (not a good idea because of several reasons).

So for those reasons, this connection should be outside (below) the root.

/../safe/connection.php

function openSQL() {
  $conn = mysqli('localhost', 'my_user', 'my_password', 'my_db');
  return $conn;
}

functions.php

require_once($_SERVER['DOCUMENT_ROOT'].'/../safe/connection.php');
function loadArtists($call){
   $dbc = openSQL();
   $query = "select * from artists order by lname limit $call, 15";
   $result = mysqli_query($dbc, $query);
   while($row = mysqli_fetch_array($result)){
      $output .= "<ul>";
      $output .= "<li>" . $row['name'] . "</li>";
      $output .= "</ul>";
   }
   mysqli_close($dbc);
   return $output;
}

$myOutput = loadArtists(4); 

Answer 4

The problem is variable scope. That variable does not exist within your function.

There are three ways to deal with that:

1. Make it global, that means an outside variable is readible from within a function. (Note that using global variables is often considered a security concern.)

   global $dbc;
   

2. You can pass that variable into the function as an argument

   function loadArtists($connection, $call) { ... }
   

3. you can make a class and that class variable will now be useable inside of class functions:

   class Artists {
       public $dbc;
       public function __construct() {
           $this->dbc = open_the_db_connection(); //etc?
       }
       public function loadArtists($call) {
           $query = "select * from artists order by lname limit $call, 15";
           $result = mysqli_query($this->dbc, $query);
           while($row = mysqli_fetch_array($result)){
               $output .= "<ul>";
               $output .= "<li>" . $row['name'] . "</li>";
               $output .= "</ul>";
           }
           return $output;
       }
   }
   

Answer 5

It looks like a scoping issue to me. The $output you reference in the function isn't the same as the $output you defined outside the function.

You should change your function to the following:

function loadArtists($call){
$output = "";
$query = "select * from artists order by lname limit $call, 15";
$result = mysqli_query($dbc, $query);
while($row = mysqli_fetch_array($result)){
    $output .= "<ul>";
    $output .= "<li>" . $row['name'] . "</li>";
    $output .= "</ul>";
}

return $output;

}

$output = loadArtists($call1);