Hello All,
I am trying to convert a PHP app, originally created in DreamWeaver from mysql_* calls to PDO.
Right now every page has all the queries on the top of that same PHP page (as DW did).
I want to get away from this approach and store functions on a functions.php file.
I am having an issue structuring this correctly.
When I create the functions.php page and I add the db credentials at the top of the page over the functions, this fails to work.So right now I have about 10 functions and I have to include all the db credentials inside every function. IT works but its not practical nor recommended.
In this example I am trying to create a function to pull all cities in a state
Failed Code:
$dsn = 'mysql:host=localhost;dbname=**DBNAME**';
$user = '**MY_DB_USER**';
$pass = '**MY_DB_PW**';
$pdo = new PDO($dsn, $user, $pass);
function getCities($state) {
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = '$state' ORDER BY city";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
Working Code
function getCities($state) {
$dsn = 'mysql:host=localhost;dbname=**DBNAME**';
$user = '**MY_DB_USER**';
$pass = '**MY_DB_PW**';
$pdo = new PDO($dsn, $user, $pass);
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = '$state' ORDER BY city";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
Its a challenge trying to find an answer as this might work on a single php page but not when defining a global functions.php page. I am hesitant to use a PHP framework due to learning curve and the urgency of this project.
If anyone can point me in the right direction that would be awesome.
By the end of the project I want to strip away all database functions from the 50+ php pages and have them all as functions inside functions.php.
Why not create a function which creates the PDO object, then pass that PDO object to all the rest of your functions (or have those functions call the PDO function)?
This keeps the connection information in one place. Something like this:
function getCities($state) {
$pdo = getPDOObject();
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = '$state' ORDER BY city";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
function getPDOObject() {
$dsn = 'mysql:host=localhost;dbname=**DBNAME**';
$user = '**MY_DB_USER**';
$pass = '**MY_DB_PW**';
return new PDO($dsn, $user, $pass);
}
In my fairly simple code, I just pass the database object ($pdo in your example) into each function that accesses it.
$x = getCities("CA", $pdo);
function getCities($state, $dbobj) {
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = '$state' ORDER BY city";
$stmt = $dbobj->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
Interestingly, that everyone who managed to answer here so far, failed to use PDO properly, keeping it as essentially insecure as with old mysql ext.
So, your first and foremost problem is a proper usage of the PDO prepared statements.
Whatever way you choose, you’ll have to run all your queries the way shown below:
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = ? ORDER BY city";
$stmt = $pdo->prepare($sql);
$stmt->execute([$state]);
return $stmt->fetchAll();
As of the problem with PDO access, I have a solution written especially for the late mysql users, which makes PDO usage as simple as old mysql_query, yet with full power and security of PDO - Simple yet efficient PDO wrapper. With it your code will be as smooth as
function getCities($state) {
$sql= "SELECT DISTINCT city FROM ZipCodes WHERE state = ? ORDER BY city";
return DB::run($sql, [$state])->fetchAll();
}
I used to use a single class for this purpose it’s pretty basic and easy to use.
In the constructor for the class create the connection to the database and assign it to a variable using $this-> that makes it available throughout the class.
class dao {
public function __construct() {
try {
$dbh = new PDO(mysql:host='path';dbname='db name', 'db username', 'db password');
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br />";
die();
}
$this->dbh = $dbh;
}
Now you can add methods to the class for each of your database calls
public function getCities($state) {
$stmt= $this->dbh->prepare("SELECT DISTINCT city FROM ZipCodes WHERE state = :state ORDER BY city");
$stmt->bindParam(":state", $state);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
Now when you need to use the database just create an object of this class and call the method you need.
require('/path/to/dao.php');
$dbObj = new dao();
$cities = $dbObj->getCities('State');
I know this doesn’t really use the full power of OOP but it is a way to have all of your database code in one place.
The problem of passing some variable around. The OP has a problem with passing $pdo between functions and your approach keeps the same, only variable name changed.
By the way, you should never catch errors only to report them.
This solution is a bit different than the original posted by the OP. In the original code he is trying to use $PDO inside a function but not passing it into the function. @droopsnoot already addressed that above. Then you addressed the PDO issue of not using parameters. So I was just giving another solution that worked for me in the past. Defining $this->dbh in the constructor also allows you to call it inside all of the methods without passing it as a parameter because of the $this operator.
Imagine the OP wants to get cities inside another function.
So he have to repeat his old approach but with your DAO as well.
function getHotels($state) {
require('/path/to/dao.php');
$dbObj = new dao();
$cities = $dbObj->getCities('State');
// use $cities to get hotels or whatever.
}
In fact you improved nothing in regard of the accessibility of the DB driver.
Not to mention that stuffing all your database -related methods in one single class sounds a bit awkward.
Thank you for the response.
Oddly enough, Everyone elses responses I understand perfectly well and could integrate the code easily. Yours Im having a hard time funding the proper way to put this in my application yet after seeing your responses about the other posts I want to do this proplerly. Can you give example of how the pDO access problem would be solved?
Look, there is a code you have to put into functions.php. The code you may find under the section “Code” by the link provided. You have to put all that code into a file and edit constants at the top, providing your actual database credentials.
That’s all!
Assuming you already have functions.php included in all your scripts, you will have instant access to PDO. Thus you will be able to write getCities function the way I showed above - in just two lines, without any extra code to connect to PDO as connection will be stored in the static DB class.
class MyFunctions{
protected $connection;
public function __construct(PDO $connection)
{
$this->connection = $connection;
}
public function getCities($state){
try{
$cmd = $this->connection->prepare("SELECT DISTINCT city FROM ZipCodes WHERE state = ? ORDER BY city ");
$cmd->bindParam(1, $state,PDO::PARAM_INT);
$cmd->execute();
$data = $cmd->fetchAll(PDO::FETCH_ASSOC);
$cmd = null;
return json_encode($data);
}catch(PDOException $c){
return $c->getMessage();
}
}
}
To use it.
someotherpage.php
<?php
require_once 'connection/config.php';
require_once 'connection/MyFunctions.php';
$db = new PDO(HOST,USER,PASSWORD,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$myapp = new MyFunctions($db);
$returndata = json_decode($myapp->getCities($id),true);
//do something with $returndata
.
?>
@jemz come on! GetCities returning pdoexception makes absolutely no sense. Exceptions have to be thrown, not returned instead of data.
And still you need to pass your $app around. Why you all trying to offer a solution that bears the exactly same drawback as the initial problem?
Why you’re making getCities less accessible than it is in the OP? It was a function that is accessible everywhere. And now it’s a method of a regular variable, prone to all scope issues.
If your application does not catch the exception thrown from the PDO constructor,
the default action taken by the zend engine is to terminate the script and display a back trace.
This back trace will likely reveal the full database connection details,
including the username and password. It is your responsibility to catch this exception,
either explicitly (via a catch statement) or implicitly via set_exception_handler().
@Mittineague you may handle, but should never catch an error to report it.
Regarding this statement from PHP manual, I am going to make it removed,as it’s utter nonsense. Please read the explanation here: PDO Error handling
Although you may want to add an error handler, if you don’t know how to do it, it is not that essential. What you really have to do is just set two PHP settings:
dislpay_errors=off
log_errors=on
That is all error handling that is indeed obligatory for a live site. All other measures are optional.
While it’s actually suggested manual catching that makes PHP users do the very harm they are intend to avoid - by echoing the error message right on the screen, as it can be seen in every tutorial in the world.