Function Question

[wickedneat]

I'm trying to write a select function where I define the table, column and condition each time such as this:

     function selectadmininfo($table,$column,$condition){
         $selQuery="SELECT * FROM ".$table . $condition;
          $sqlQue=$this->executeQuary($selQuery);
         return $sqlQue;
     } 

Then call it like this:

echo $database->selectadmininfo($table,$phone,$condition); 

Is this possible? If yes, what is the best way to write this.

[kduv]

It's completely possible. It would be hard to give you much advice on the "best" way to do so without knowing more about the class that would be implementing it though.

[wickedneat]

Are you meaning like this?

Class DbFunctions{

     function selectadmininfo($table,$column,$condition){
         $selQuery="SELECT * FROM ".$table . $condition;
          $sqlQue=$this->executeQuary($selQuery);
         return $sqlQue;
     }  

}
$database = new DbFunctions; 

[kduv]

Here's a rough example of how you could implement something like that. There are many different ways you could implement this. I'm not saying this is the best, but this example may steer you in the right direction.

NOTE: I didn't optimize for performance, and you may want to use place-holders for security reasons. I also didn't test this, so there may be errors.

<?php

class DB
{
    private $db_host = 'localhost';
    private $db_user = 'username';
    private $db_pass = 'password';
    private $db_name = 'database_name';
    private $db;
    
    public function __construct()
    {
        $dsn = "mysql:dbname={$this->db_name};host={$this->db_host}";
        try {
            $this->db = new PDO($dsn, $this->db_user, $this->db_pass, array(
                PDO::ATTR_ERRMODE                 => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE     => PDO::FETCH_ASSOC));
        } catch (PDOException $e) {
            exit('Connection failed: ' . $e->getMessage());
        }
    }
    
    public function selectAdminInfo($table = 'administrators', $column = '*', $condition = null)
    {
        $sql = "SELECT {$column} FROM {$table}";
        
        if ($condition != null) {
            $sql .= " WHERE {$condition}";
        }
        
        try {
            $sth = $dbh->prepare($sql);
            $sth->execute();
            return $sth->fetchAll();
        } catch (PDOException $e) {
            exit('Query failed: ' . $e->getMessage());
        }
    } 

}

$db = new DB();

// Prints all info from the administrators table
print_r($db->selectAdminInfo());

// Prints the phone_number info for the user with email johndoe@example.com on the table administrators
print_r($db->selectAdminInfo("administrators", "phone_number", "email = 'johndoe@example.com'"));

// etc ....

?>

[Alex]

As indirectly noted by kduv, this can be implemented in a lot of different ways.

If you're using a framework like SiteSense you can do this really easily by writing a query like this:

SELECT !column! FROM !table! !condition!

It can become as dynamic as you need by passing in a list of dynamic parameters. In the free and open source SiteSense framework any query executed through the dynamicPDO class is run through the prepQuery method before being executed:

private function prepQuery($queryName, $module, $parameters) {
  // Replace !prefix! and !table! with actual values
  if (!isset($this->queries[$module])) {
    $this->loadModuleQueries($module);
  }
  if (isset($this->queries[$module][$queryName])) {
    // Make Sure We At Least Have Prefix And Lang....
    if (!is_array($parameters)) $parameters=array();
    if (!isset($parameters['!prefix!'])) $parameters['!prefix!'] = $this->tablePrefix;
    if (!isset($parameters['!lang!'])) $parameters['!lang!'] = '_'.$this->lang;
    $queryString = str_replace(
      array_keys($parameters),
      array_values($parameters),
      $this->queries[$module][$queryName]
    );
    return $queryString;
  } else return false;
} 

This simple yet powerful method gives developers the ability to easily write dynamic queries without security concerns.

I'm sure this can be adapted to the code base you are working with.

[Cups]

Are you meaning like this?

Class DbFunctions{

     function selectadmininfo($table,$column,$condition){
         $selQuery="SELECT * FROM ".$table . $condition;
          $sqlQue=$this->executeQuary($selQuery);
         return $sqlQue;
     }  

}
$database = new DbFunctions; 

If you are going to go through the routine of creating a database management class, you'd ideally want to be able to use that class in as many different projects as possible which would mean leaving it as "loosely coupled" as possible - i.e. not tied to a particular implementation.

So a couple of observations:
Next time you use this class, what if you want to be more specific than "select *" ? (@kduv has shown one way round this, and has mentioned PDO too, Props for showing how to fire up PDO correctly by the way)

What if you want to use another database, with different credentials?

This line looks to have a typo which will up and bite you: "Quary"

$this->executeQuary($selQuery); 

Sometimes it helps to envisage how you would like your new class to appear when you call it and look at a couple of different scenarios in different projects ....

Here's a simple example:

// contains $db_host etc
include 'project_a_settings.php';

// plucked from an incoming request
$fields = array(
'name' => $name,
'postcode' => $postcode,
'phone'=> $phone
);

$table = "mytable";

// do the db connection 
$db = new db($db_host, $db_user, $db_pass);

// return an array of results
$db->getAll($table, $fields);

//OR
// self explanatory
//$db->getWhere($table, $fields, array('name' => 'Joe Bloggs')); 

Then try and build up and out from that, you will probably change your mind as you go through this process. The function names will probably develop and change too. It would be a good idea to build a small database with some test data in it so that you can test this class in one place for when you (inevitably) come back and decide to change it, and want to test how the results compare so that you do not break all the existing code which depends on this class.

It is a good process to go through, but at the end of it you may decide that other code in existing frameworks actually do a better job and are more robust because they have had more eyes testing them - but at least you will have a slightly better appreciation of why you are doing that.

I'f nothing else I'd really urge you, as others have, to build your class around PDO though.