SQL query in PHP only works outside function

Question

I have the following PHP snippet:

function getFacilities($testName, $dbAdapter) {
  $sql1 = "SELECT * FROM facilities_db WHERE facility_name = '$testName'";
  $result1 = $dbAdapter->query($sql1);

  $facility_details = array();
  while ($row = mysqli_fetch_assoc($result1))
  {
    $facility_details[] = $row;
  }
  return $facility_details;
}

$testName = "Abraham Moss Leisure";
$facility_data = getFacilities($testName, $mysqli);

The value of $testName is arbitrary just for testing purposes. This is the function from one of the answers, but my the only difference in my initial definition was not having $dbAdapter as a parameter.

please post your whole function & how did you call the function with parameter. — Dipanwita Kundu, Apr 11 '16 at 10:56
Also don't forget to check scope of all your variables with and without function!! — Mangesh Sathe, Apr 11 '16 at 11:04
why people still use mysqli is past me but why not just use `foreach` ? like: `foreach ($dbAdapter->query($sql1) as $row) { [...] }` — Jaquarh, Apr 11 '16 at 11:38
That is not my problem. I only want that code snippet to work inside a function so I can call it with a parameter from jQuery, nothing else. — arch1ve, Apr 11 '16 at 11:43

Unex · Accepted Answer · 2016-04-11T12:14:04.570

6

You need to set $testName as a parameter, but also the adapter you are going to use to get the connection to your SGBD (here it's mysqli).

function getFacilities($testName, $dbAdapter) {
    $sql1 = "SELECT * FROM facilities_db WHERE facility_name = '$testName'";
    $result1 = $dbAdapter->query($sql1);

    $facility_details = array();
    while ($row = mysqli_fetch_assoc($result1))
    {
      $facility_details[] = $row;
    }
    return $facility_details;
}

We need to pass $testName and $mysqli as your dbAdapter to the function. Thus, this is how we would call the function :

$facility_data = getFacilities($testName, $dbAdapter);

hope this'll help,

edited Apr 11 '16 at 12:14

answered Apr 11 '16 at 11:04

Unex

1,710
11
16

The array returned is still empty :(. If I call the function using a string instead of $testName for example, the same thing happens. – arch1ve Apr 11 '16 at 11:24
Yes. I just checked it without the function and it works like a charm. – arch1ve Apr 11 '16 at 11:43
Worked after defining $dbAdapter as a global variable with $mysqli value and only parsing the $testName (for whatever reason that is...). Thanks! – arch1ve Apr 11 '16 at 11:59
If `global` answer works for you - you must try to provide `$dbAdapter` instead of `$mysqli` to `getFacilities` call. – vp_arth Apr 11 '16 at 12:03
All right, I added the $dbAdapter parameter back and parsed it when calling the function after defining it as $mysqli. – arch1ve Apr 11 '16 at 12:13

Dobe Lee · Answer 2 · 2016-04-11T12:09:00.493

test query result:

print_r(mysqli_fetch_assoc($result));

test $mysqli in function:

function get($testName, $adapter) { if (!isset($adapter)) echo 'no'; }

use static database connection:

class GlobalClass { public static $db = null; }

class Connection {
public $connection = null;
public function connect() {
    if ($connection == null)
        // operation
    $this->connection = 'obj';
}}


$db = new Connection();
$db->connect();
GlobalClass::$db = new Connection();
GlobalClass::$db->connect();
GlobalClass::$db->connection->query($sql);

function yourFunction($testName) {
    $sql1 = "SELECT * FROM facilities_db WHERE facility_name = '$testName'";

    $result1 = GlobalClass::$db->connection->query($sql1);

    $facility_details = array();
    while ($row = mysqli_fetch_assoc($result1)) {
      $facility_details[] = $row;
    }
    return $facility_details;
}

Thanks for the effort, but the database connection is already written and it works fine for everything else I have! Solved the problem. — arch1ve, Apr 11 '16 at 12:17

score -1 · Answer 3 · answered Apr 11 '16 at 11:47

-1

    function getFacilities($testName) {
          global $dbAdapter;
        $sql1 = "SELECT * FROM facilities_db WHERE facility_name = '$testName'";
        $result1 = $dbAdapter->query($sql1);

        $facility_details = array();
        while ($row = mysqli_fetch_assoc($result1))
        {
          $facility_details[] = $row;
        }
        return $facility_details;
    }

$testName = "Abraham Moss Leisure";
$facility_data = getFacilities($testName);

declare the connection variable as global and pass the value for the $testname

answered Apr 11 '16 at 11:47

Arun Kumaresh

5,734
5
28
45

Even though I don't know why, this worked. Thank you! – arch1ve Apr 11 '16 at 11:59
omg, please, no... Why php maintainers do not removed that `global` yet? – vp_arth Apr 11 '16 at 12:01
You shouldn't use global variables. It leads to very high technical debt. – Unex Apr 11 '16 at 12:05
why cant use global variable can you plz explain then how can use the connection variable inside the function – Arun Kumaresh Apr 11 '16 at 12:08
You should check here : http://stackoverflow.com/questions/1557787/are-global-variables-in-php-considered-bad-practice-if-so-why. – Unex Apr 11 '16 at 12:09
I agree with not using global variables. It is difficult to maintain in the long term. – Caillou Apr 11 '16 at 12:12
Solution is in the comments of the accepted answer. – arch1ve Apr 11 '16 at 12:14

SQL query in PHP only works outside function

3 Answers3