CODEWITHSUNDEEP
phpmysqlpdomysqli
Simeon
Simeon

Reputation: 878

Using PDO connection method

When I first started to learn php I understood that it was good practice to store the db connection file in my root folder, and include it once at the top of every file. The settings would carry throughout the file and work inside functions etc - so the same file was a good place to store global variables.

Now I'm finally updating my code / working on a new project, and I know the mysql functions are depreciated and I ought to be using PDO or MySQLi. Something I read suggested it was harder to do while loops as per the old style with Mysqli, so I thought PDO might be a better place to start.

//e.g.

while($val = mysql_fetch_array($query)){
    $propertyid = $val['propertyid'];
    $regularid = $val['regularid'];
    $oldamount = str_replace(",", "", $val['amount']);
    $datedue = $val['billdate'];
}

So I'm thinking it best to start with PDO. The below works if I include the $dbh connection line immediately before it. However, when I then move the $dbh connection line to my setup file it doesn't.

$query = $dbh->prepare("SELECT username FROM users");
$query->execute();
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  print "hi ";
  print $row['username']."<br/>";
}

I know that the connection needs to be called in every new function. My questions are:

  1. What is the least code intensive way to add in the db connection to every function
  2. Where should I best be storing the username and password - root php? .ini file?
  3. Is there a 'better' way to do a while loop than the one I've been using...

Upvotes: 2

Views: 228

Answers (1)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

While moving to PDO (which is object oriented library), try to move your project to same manner of codding - the object oriented paradigm.

A simple architecture you can build would be a:

  • Class that makes the PDO connection (i.e. Database)
  • Config file that has the credentials
  • A class (i.e. Base) that is extended from each of your other classes that interact with database. This class should contain the instance of your database class (i.e. protected $_db, in constructor $this->_db = new Database())
  • Each of your classes that interact of database (extends Base) can now do $this->_db->prepare(....)

So the most important thing here is your connection to be an object which is shared through your database interaction classes.

About the loop. You will hardly be able to use while() for methods return value. In fact you will be, but it's an overkill.

Your methods should return the fetched result:

public function getUsers() {
    $query = $this->_db->prepare("SELECT username FROM users");
    $query->execute();
    return $query->fetch(PDO::FETCH_ASSOC);
}

or (this should not be necessary):

while ($row = $query->fetch(PDO::FETCH_ASSOC) {
    $users[] = $row;
}
return $users;

This way your getUsers() method will return the result set as an associative array, and the best built-in function in PHP for dealing with arrays is foreach(), so you'd need to iterate through the method return value:

foreach ($obj->getUser() as $row) {
    // do smth
}

Upvotes: 2

Related Questions