Moving any database code into separate file (PDO)

PHP
1

I have files like this…

file1.php (this file might have  "INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
file2.php  (this file might have  "INSERT INTO SOMETHINGELSE (height, value, something) VALUES (:name, :value, :something)") where x = something;
file3.php (this might be query where I am updating rather then inserting)

So as you can see something I have different queries with different number of parameters and values. Also depending on how many parameters I have that’s how many bindings i have to do…

$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

How would I go about putting all this in separate file?

Thanks

2

You are storing plain SQL queries in a file? What’s the intention behind this? From the numbered naming of the files it looks like there is way more trouble going on…

3

No I am not storing anything. I am just trying to find a way to put database related code in different file that’s all

4

Maybe try two files, one for Mysqli functions and the other for PDO functions.

<?php

function insertUser(string $tbl, $user)
:bool
{
$result = FALSE;

$sql = <<< ____TMP
   INSERT INTO 
        $tbl
   `user` = $user;
____TMP;

try {
  $result = mysqli_query( $sql );
} Exception catch $e }
 //
}

return $result;
}

Not checked because tapped on a tablet.

5

I have only PDO but I can see what you mean above. Each database query one function?

6

Yes only one function for each query.

Think ahead and the common included file should work on all projects with some additions where necessary.

7

ok in my file1.php that has at the top

require 'file2.php'

I have function call…

my_function();

My file2.php has this at the top…

 $user = "####";
 $p = "####";

       try {   /*  connect to db */ 
            $myPDO = new PDO('mysql:host=localhost;dbname=somename', $user, $p); /* Establish new connection, get the PDO object.  */
       }
       catch (PDOException $e) { /* Catch the exception otheriwise whole error trace will display. */
            error_log("Sending this to error log " . $e->getMessage());
            die();
       }
    
      my_function (){
           if($myPDO == NULL) {
               error_log("I am null");
           }
      }

I get “I am null” in my error log. What I am doing wrong. Its obvious $myPDO is not accesible but it is in global scope of file2.php so why is my_function() dont see it?

8

adding

global $myPDO;

resolves this. I do see lot of post that dont recomend this.

9

Couldn’t you pass it to the function? eg.

my_function ($myPDO)

You would need to have the function accept it as an argument, and include it as a parameter where you call the function. But IMHO that would be better than having it global.

2 Likes
10

Just encapsulate your SQL queries within classes with the precise business logic. You can use a factory or dependency injection for the actual database connection.

1 Like
11

I personally don’t like functions that rely on global variables. If you think of a function as a standalone bit of code that you can use and re-use over and over, why would it be desirable to have it rely in certain variable names, external to the function, to make it work?

My view is - if you need stuff inside a function, you need to pass it in to the function. Imagine you write a function that relies on certain global variables being in place, then you try to use that function in another piece of code that already uses those variable names for something else that it’s really complicated to change - you end up with another copy of the function that uses different global names.

1 Like
12

Using global variables is one of the worst things you can do to make variables accessible throughout an application. That being said if you aren’t concerned with testing or maintainability they work.

13

As a contrived example, say I have this code scattered about in different files

$age = 21;   // global scope
...
function check_id() {
  $may_enter = false;
  if ( $age >= 21 ) {
    $may_enter = true;
  }
  return $may_enter;
}
...
function has_birthday() {
  $age = 'twenty-one';
}
...
function bouncer() { 
  $let_inside = false;
  if ( check_id() ) {
    $let_inside = true;
  }
  return $let_inside;
}

If John has a birthday and goes out to celebrate, how will his night go?

I know, it’s easy to think “but I wouldn’t make that mistake”. But what about someone else working with your code? What about you in a years time?

With PHP 7, it is possible to have type declarations, eg.

function check_id(int $age) : bool {
  $may_enter = false;
  if ( $age >= 21 ) {
    $may_enter = true;
  }
  return $may_enter;
}
14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.