SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Function Question

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

    PHP Code:
         function selectadmininfo($table,$column,$condition){
             
    $selQuery="SELECT * FROM ".$table $condition;
              
    $sqlQue=$this->executeQuary($selQuery);
             return 
    $sqlQue;
         } 
    Then call it like this:

    PHP Code:
    echo $database->selectadmininfo($table,$phone,$condition); 
    Is this possible? If yes, what is the best way to write this.

  2. #2
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you meaning like this?

    PHP Code:
    Class DbFunctions{

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

    }
    $database = new DbFunctions

  4. #4
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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 ....

    ?>

  5. #5
    @alexstanford Alex's Avatar
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida
    Posts
    757
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    PHP Code:
    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.
    Alex Stanford @alexstanford tumblog about.me in fb G+ K
    TechTalkin The Premier Community for Technology Enthusiasts and Professionals
    Full Ambit Media Zero Sacrifice Web Design & Development; Made in the USA @fullambit in fb G+ K

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by wickedneat View Post
    Are you meaning like this?

    PHP Code:
    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"
    PHP Code:
    $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:
    PHP Code:
    // 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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •