Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Oct 2008
    Posts
    79
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Yet Another Database Class

    Ok so below is my version of a DB Class.

    CODES:
    PHP Code:
    require_once('cls/mysql.php'); // YOU CAN USE INCLUDE IF YOU WISH
    $db = new Mysql();

    $db->dbSelect('DBNAME'); // IF NOT SET IN THE CLASS FILE (no need for the connect func)

    $db->connect(); // USE THIS IF ALL PARAMS ARE FILLED OUT IN THE CLASS FILE

    $db->query('type in your mysql query'); // THIS IS FOR INSERT, UPDATE, DELETE

    $db->fetch('type your SELECT mysql query here'); // THIS IS IF YOU ARE FETCHING ROWS

    $myVar $db->getResults(); // WILL PUT YOUR QUERY RESULTS IN A VAR AS ASSOC ARRAY (select queries only)

    $myVarTwo $db->getRows(); // WILL OUTPUT HOW MANY ROWS AS A NUMERIC FIGURE (select queries only)

    $db->getPre(); // THIS IS ECHO OUT YOUR QUERY RESULTS FOR DEBUGGING/ERROR CHECKING 
    So heres the class file I've put together

    PHP Code:
    <?php

      
    class Mysql {

        
    // SETS THE CONNECTION DETAILS
        
    private $db_host 'localhost';
        private 
    $db_user 'DBUSER';
        private 
    $db_pass 'DBPASS';
        private 
    $db_name 'DBNAME';

        
    // THIS IS WHERE THE QUERY RESULT WILL GO
        
    private $result = array();

        
    // THIS IS WHERE NUM ROWS IS STORED
        
    private $numResults;

        
    // CONNECTS TO DB
        
    public function connect() {

          
    $conn mysql_connect($this->db_host,$this->db_user,$this->db_pass);

          if (
    $conn) {
            
    $db mysql_select_db($this->db_name$conn);

            if (
    $db) {
              
    $this->active true;
              return 
    true;
            }
            else {
              return 
    false;
            }
          }
          else {
            return 
    false;
          }
        }

        
    // SELECTS A DB IF NOT SPECIFIED ABOVE
        
    public function dbSelect($dbSelect) {
          
    $this->db_name $dbSelect;
          
    $this->connect();
        }


        
    // GET VAR, ESCAPES THE STRING, EXEC QUERY, SETS RESULT
        
    public function query($query) {
          
    $query mysql_escape_string($query);
          
    $q mysql_query("$query");

          if (
    $q) {
            return 
    true;
          }
          else {
            return 
    false;
          }
        }

        
    // FETCHES QUERY ROWS FROM DB
        
    public function fetch($query) {
          
    $query mysql_escape_string($query);
          
    $q mysql_query("$query");

          if (
    $q) {
            
    $this->numResults mysql_num_rows($q);

            if (
    $this->numResults != 0) {
              while (
    $f mysql_fetch_assoc($q)) {
                
    $this->result[] = $f;
              }
            }
            else {
              
    $this->result null;
            }

            return 
    true;
          }
          else {
            return 
    false;
          }
        }

        
    // SENDS THE RESULT BACK
        
    public function getResults() {
          return 
    $this->result;
        }

        
    // ECHOS OUT THE QUERY ARRAY RESULTS IN A READABLE FORMAT
        
    public function getPre() {
          echo 
    '<pre>';
          
    print_r($this->result);
          echo 
    '</pre>';
        }

        
    // SENDS THE NUM ROWS BACK
        
    public function numResults() {
          return 
    $this->numResults;
        }

      }
    ?>
    It also works in arrays for example.

    PHP Code:
    if ($db->query) {   } // IF YOUR QUERY IS INCORRECT OR FAILS IT WOULD RETURN BACK FALSE CARRYING OUT AN ELSE STATEMENT IF NEED BE
    else {   } 
    Last edited by CarlLawl; 09-13-2010 at 11:13 PM.

  • #2
    Regular Coder
    Join Date
    Sep 2007
    Location
    Grahamstown, South Africa
    Posts
    237
    Thanks
    6
    Thanked 17 Times in 17 Posts
    Can i make a suggestion? Im sure i can :-D

    Why dont you try changing your class so that it works something like this:

    PHP Code:
    //syntax is a little complicated
    //feel free to create your own!
    $db->query(array(
        
    "type"=>"select",
        
    "attribs"=>array(
            
    "table"=>'users',
            
    "fields"=>array('name','surname','id_number','cellphone'),
            
    "conditions"=>array("name"=>"='john'","surname"=>"='doe'"),
            
    "limit"=>array(0,30),
            
    "order"=>array("name"=>'d',surname=>'a')
    )));
    //then you get your class to format it like this:
    SELECT name,surname,id_number,cellphone FROM users WHERE name='john' AND surname 'doe' ORDER BY name ASCsurname DESC LIMIT 0,30
    That way, and this is just a suggestion, you can make your class cross-database-type compatible and its a nice challenge!

  • #3
    New Coder
    Join Date
    Oct 2008
    Posts
    79
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Something like this? (Ps I didnt have time to check it as I have work soon but it should be ok).

    PHP Code:
    <?php

    class Query {

      private 
    $result '';
      private 
    $numResults '';

      public function 
    query($data) {

        switch(
    $data[type]) {
          case 
    select:

            
    $q $data[type].' '.$data[attr][fields].' FROM '.$data[attr][table];

            if (
    $data[attr][where] != '') {
              
    $q .= 'WHERE '.$data[attr][where];
            }

            if (
    $data[attr][limit] != '') {
              
    $q .= 'LIMIT '.$data[attr][limit];
            }

            if (
    $data[attr][order] != '') {
              
    $q .= 'ORDER BY '.$data[attr][order];
            }
            
    $q mysql_query("$q");

            if (
    $q) {
            
    $this->numResults mysql_num_rows($q);

            if (
    $this->numResults != 0) {
              while (
    $f mysql_fetch_assoc($q)) {
                
    $this->result[] = $f;
              }
            }
            else {
              
    $this->result null;
            }

            return 
    true;
          }
          else {
            return 
    false;
          }
          break;

          case 
    update:

            
    $q $data[type].' '.$data[attr][table].' SET '.$data[attr][values];

            if (
    $data[attr][where] != '') {
              
    $q .= ' WHERE '.$data[attr][where];
            }
            
    $q mysql_query("$q");

            if (
    $q) {
              return 
    true;
            }
            else {
              return 
    false;
            }
          break;

          case 
    delete:

            
    $q $data[type].' FROM '.$data[attr][table].' WHERE '.$data[attr][where];
            
    $q mysql_query("$q");

            if (
    $q) {
              return 
    true;
            }
            else {
              return 
    false;
            }
          break;

          case 
    insert:

            
    $q $data[type].' INTO '.$data[attr][table].' '.$data[attr][fields].' VALUES '.$data[attr][values];
            
    $q mysql_query("$q");

            if (
    $q) {
              return 
    true;
            }
            else {
              return 
    false;
            }

          break;
        }

      }

      public function 
    getResults() {
        return 
    $this->result;
      }

      public function 
    GetNumRow() {
        return 
    $this->numResults;

    }

    ?>
    Usage:

    PHP Code:
    $dataSelect = array('type' => 'SELECT',
                                        
    'attr' => array('fields' => '*',
                                                    
    'table' => 'MyTable',
                                                    
    'where' => 'id=3',
                                                    
    'limit' => '1',
                                                    
    'order' => 'id'
    ));

    $dataUpdate = array('type' => 'UPDATE',
                                   
    'attr' => array('table' => 'MyTable',
                                                    
    'values' => 'col1=value, col2=value',
                                                    
    'where' => 'col1=somevalue'
    ));

    $dataDelete = array('type' => 'DELETE',
                                   
    'attr' => array('table' => 'MyTable',
                                                    
    'where' => 'id=3'
    ));

    $dataInsert = array('type' => 'INSERT',
                                    
    'attr' => array('table' => 'MyTable',
                                                    
    'fields' => '(field1,field2,field3)',
                                                    
    'values' => '(value1,value2,value3)',
    ));

    require_once(
    'cls/query.php'); // YOU CAN USE INCLUDE IF YOU WISH
    $q = new Mysql();

    $q->query($dataSelect); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
    $q->query($dataUpdate); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
    $q->query($dataDelete); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
    $q->query($dataInsert); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION

    $nrows $q->getNumRow(); // IF YOU WERE COUNTING NUM ROWS

    $r $q->getResults(); // IF YOU WERE PULLING DATA FROM DB 
    apologise for the horrid array indents they looked fine in the editor


  •  

    Posting Permissions

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