The SQL Class

Tanax · 12-30-2008, 02:42 AM

Hi!

Just thought I'd create a thread for the SQL class.
This is what I have so far. Please come with suggestions or submit updated code if you feel that you wish to help out.

php Code:

<?php

    class DBmysql {
        
        private $host;
        private $user;
        private $pass;
        private $data;
        private $con;
        
        private $query_sql;
        private $query_result;    
        private $query_fetch = array();
        
        /**
         * Basic description:
         * Sets the connection details
         * 
         * Conditions:
         * -
         * 
         * Detailed description:
         * -
         *
         * @param array $array
         * @return $this
         */
        public function setHandler($host, $user, $pass, $data) 
        {
            
            $this->host = $host;
            $this->user = $user;
            $this->pass = $pass;
            $this->data = $data;
            
            return $this;
            
        }
        
        /**
         * Basic description:
         * Connects to the database using the connection details set by setHandler function
         * 
         * Conditions:
         * You have to set the connection-values with setHandler before using this function
         * 
         * Detailed description:
         * -
         *
         * @return $this
         */
        public function connect() 
        {
            
            $this->con = mysql_connect($this->host, $this->user, $this->pass);

            if($this->con) return $this;
            else return false;
            
        }
        
        /**
         * Basic description:
         * Selects the database using the connection details set by setHandler function
         * 
         * Conditions:
         * The mysql has to be connected
         * 
         * Detailed description:
         * -
         *
         * @return $this
         */
        public function select() 
        {
            
            if(mysql_select_db($this->data, $this->con)) return $this;
            
            else return false;
            
        }
        
        /**
         * Basic description:
         * Disconnects the current connection
         * 
         * Conditions:
         * The mysql has to be connected
         * 
         * Detailed description:
         * -
         * 
         * @return true or false
         */
        public function disconnect() 
        {
            
            if(mysql_close($this->con)) return true;
            
            else return false;
            
        }
        
        /**
         * Basic description:
         * Loads a query, specified in the parameter
         * 
         * Condition:
         * -
         * 
         * Detailed description:
         * -
         *
         * @param string $query
         * @return $this
         */
        public function loadQuery($query) 
        {
            
            $this->query_sql = $query;
            
            return $this;
            
        }
        
        /**
         * Basic description:
         * Executes a query
         * 
         * Conditions:
         * You have to load a query before using this function
         * 
         * Detailed description:
         * If you haven't loaded a query, you can set the first parameter to the sql statement and it will load the query automaticly
         *
         * @param string(optional) $sql
         * @return $this
         */
        public function exeQuery($sql = NULL) 
        {
            
            if($sql == NULL) 
            {
                
                if(isset($this->query_sql)) 
                {
                
                    $this->query_result = mysql_query($this->query_sql, $this->con);
                    
                    if($this->query_result) return $this;
                    else return false;
                    
                }
                
                else return false;
                
            }
            
            elseif($sql != NULL) 
            {
                
                $this->loadQuery($sql)->exeQuery();
                    
            }
            
            else return false;
            
            
        }
        
        /**
         * Basic description:
         * Gets the results from the query
         * 
         * Conditions:
         * You have to execute a query before you can use this function
         * 
         * Detailed description:
         * -
         *
         * @return $this->query_result
         */
        public function getQueryResult() 
        {
            
            if(isset($this->query_result)) return $this->query_result;
            
            else return false;
            
        }
        
        /**
         * Basic description:
         * Loads and executes a fetch array
         * 
         * Conditions:
         * You have to execute a query before you can use this function
         * 
         * Detailed description:
         * If you want it to fetch the array as ASSOC, you need to specify that by setting the first parameter to true. 
         * If you haven't executed an query, you can set the second parameter to the sql statement and it will execute the query automaticly
         *
         * @param boolean(optional) $assoc
         * @param string(optional) $sql
         * @return $this
         */
        function loadFetch($assoc = false, $sql = NULL) 
        {
        
            unset($this->query_fetch);
            empty($this->query_fetch);
        
            $sql = (is_null($sql) && isset($this->query_result)) ? $this->query_result : $this->exeQuery($sql)->getQueryResult();
            $fetchType = ($assoc == true) ? MYSQL_ASSOC : MYSQL_NUM;
        
            if(!empty($sql)) 
            {
                
                while($fetch = mysql_fetch_array($sql, $fetchType)) 
                {
                    
                    $this->query_fetch[] = $fetch;
                    
                }
        
                if(is_array($this->query_fetch)) return $this;
                else return false;
                
            }
            
            else return false;
        
        }
        
        /**
         * Basic description:
         * Gets the results from the loaded fetch
         * 
         * Conditions:
         * You have to load the fetch by using loadFetch before using this function
         * 
         * Detailed description:
         * If you haven't loaded the fetch, you can autoload it by setting the first parameter to true.
         * If autoload is set to true, you can specify if you want to fetch the array as assoc by setting the second parameter
         * to true or false.
         * 
         * If autoload is set to true, you can specify the sql statement if you haven't already executed the query like this (example):
         * $fetch = $db->exeQuery($sql)->getFetch(true, true);
         * 
         * You can, as said, skip the exeQuery function by specifying the sql parameter in this function like this (example):
         * $fetch = $db->getFetch(true, true, $sql);
         *
         * @return $this->query_fetch
         */
        public function getFetch($autoload = false, $assoc = false, $sql = NULL) 
        {
            
            if($autoload == false)
            {
                
                if(is_array($this->query_fetch) && isset($this->query_fetch)) return $this->query_fetch;
                
                else return false;
                
            }
            
            else 
            {
                
                $this->loadFetch($assoc, $sql)->getFetch();
                
            }
            
        }
        
        /**
         * Basic description:
         * Gets the number of rows
         * 
         * Conditions:
         * A query must be specified, either in the parameter, or a previously executed query
         * 
         * Detailed description:
         * You can get the number of rows the query returned in 2 ways, either by executing a query before using this function, like this (example):
         * $numrows = $db->exeQuery($sql)->getRows();
         * 
         * Or by setting the parameter to the sql statement, like this (example):
         * $numrows = $db->getRows($sql);
         *
         * @param string(optional) $query
         * @return number of rows based on the query
         */
        public function getRows($query = NULL)
        {
            
            if(is_null($query))
            {
            
                if(isset($this->query_result))return mysql_num_rows($this->query_result);
                else return false;
                
            }
            
            else 
            {
                
                $this->loadQuery($query)->exeQuery()->getRows();
                
            }
            
        }
        
        /**
         * Basic description:
         * Secures a string against possible mysql injections
         * 
         * Conditions:
         * The parameter must be set
         * 
         * Detailed description:
         * If the parameter is set to an int, it will return just the integer without cleaning it.
         * If the parameter is set to a string, it will clean the string from mysql injection before returning it
         *
         * @param string $string
         * @return $secured
         */
        public function secure($string) 
        {
            
            if(is_string($string)) 
            {
                
                get_magic_quotes_gpc() ? $secured = stripslashes($string) : $secured = addslashes($string);
                function_exists('mysql_real_escape_string') ? $secured = mysql_real_escape_string($secured) : $secured = mysql_escape_string($secured);
                
            }
            
            elseif(is_numeric($string)) $secured = $string;
            else return false;
            
            return $secured;
                        
        }
        
    }



?>

Howto use:

PHP Code:


			
$db = new DBmysql();

Set connection, connect and select db:

PHP Code:


			
$db->setHandler('localhost', 'root', '', 'testdb')->connect()->select();

//or if you don't want to use singleton
$db->setHandler('localhost', 'root', '', 'testdb');
$db->connect();
$db->select();

For future reference, I will use singleton all the way. You can however choose not to use it.

Execute query:

PHP Code:


			
$sql = "DELETE * FROM `table`";

//There's alot of ways you can do this, here's one way:
$db->loadQuery($sql)->exeQuery();

//Here's another way:
$db->exeQuery($sql);

Retrieve query result:

PHP Code:


			
$sql = "SELECT * FROM `table`";

//1:
$result = $db->loadQuery($sql)->exeQuery()->getQueryResult();

//2:
$result = $db->exeQuery($sql)->getQueryResult();

Fetch something as an array:

PHP Code:


			
$sql = "SELECT * FROM `table`";

//1:
$fetch = $db->loadQuery($sql)->exeQuery()->loadFetch()->getFetch();

//2:
$fetch = $db->exeQuery($sql)->loadFetch()->getFetch();

//3, if you remove the loadFetch function, you must set the first parameter in getFetch to true:
$fetch = $db->exeQuery($sql)->getFetch(true);

//4, if you skip the exeQuery function and the loadFetch function, you must set the first parameter in getFetch to true, and the third parameter to the SQL statement:
$fetch = $db->getFetch(true, , $sql);

//You can also decide if you want to use MYSQL_ASSOC:
$fetch = $db->getFetch(true, true, $sql);

//or:
$fetch = $db->exeQuery($sql)->loadFetch(true)->getFetch();

Check num rows:

PHP Code:


			
$sql = "SELECT * FROM `table`";

//1:
$rows = $db->exeQuery($sql)->getRows();

//2:
$rows = $db->getRows($sql);

Using secure method:

PHP Code:


			
$string = "' OR 1 = 1'";
$int = 2;

//Will clean the string from the mysql-injection
$secure_string = $db->secure($string);

//Will return int's as they are
$secure_int = $db->secure($int);

Village Idiot · 12-30-2008, 03:39 AM

A good class, but some revisions need to be made.

-Please use allman style indentation to conform to project standards.
-For setHandler, do not use an array to pass paramaters, just pass multiple paramaters.
-Have connect assign the connection to a variable so we can pass it to other functions that need database interactions.
-Don't have it use a seperate function to set a query, just have the command functions accept a text parameter to the commend from. I work with a set command then execute it method in asp at work, its a pain.

Those are all rather small things, other then what I named above, its a good class.

Tanax · 12-30-2008, 04:26 AM

Quote:

Originally Posted by Village Idiot

A good class, but some revisions need to be made.

-Please use allman style indentation to conform to project standards.
-For setHandler, do not use an array to pass paramaters, just pass multiple paramaters.
-Have connect assign the connection to a variable so we can pass it to other functions that need database interactions.
-Don't have it use a seperate function to set a query, just have the command functions accept a text parameter to the commend from. I work with a set command then execute it method in asp at work, its a pain.

Those are all rather small things, other then what I named above, its a good class.

- Allman style is what?
- I thought it would be better to have it an array, cause then you can assign the parameters at different locations. For example someone might want to process some data to decide which table to select(remember that this SQL class is actually a wide-use class, so it's not really designed specificly for THIS project). Trying to keep it general, so people can re-use it for other projects. But if it's neccessary, we can change that.
- I'm not sure I follow. I have it assigned to $this->con, and I use it in other functions aswell.
- As I said, I'm trying to make things general. Someone might want to assign a query, but not execute it before something has happend. However, to do what you want, I designed the exeQuery to accept an SQL statement, so to execute a query is simply done like this:

PHP Code:


			
$db->exeQuery("DELETE * FROM `table` WHERE `col` = '3'");
// or if you're selecting something, you want to retrieve the query:
$query = $db->exeQuery("SELECT * FROM `table`")->getQueryResult();

But, if you really want, I could design the getQueryResult to accept a SQL statement, so you only have to call 1 function to do a query AND retrieve the results. However, I find it unneccessary to return a query result when you're for example deleting something. It's useful when retrieving a list, or a specific row. That's why I think it should have the option to actually only execute a query and not return anything, and the option to execute a query and retrieve the results.

But that's me..

Village Idiot · 12-30-2008, 06:21 AM

-Allman style
- I still think it would be best to have the parameters hard coded.
-Misread that, never mind.
-Thats not what I was saying. Looking at it agian, I'm not sure I understand all the code, what is the purpose of loadQuery?

Sorry if I am being over-particular, I won't be like this once we get past the foundations of the script.

Salathe · 12-30-2008, 08:12 AM

Why use this over, for example, PDO or other currently available approaches?

Tanax · 12-30-2008, 01:36 PM

Quote:

Originally Posted by Village Idiot

-Allman style
- I still think it would be best to have the parameters hard coded.
-Misread that, never mind.
-Thats not what I was saying. Looking at it agian, I'm not sure I understand all the code, what is the purpose of loadQuery?

Sorry if I am being over-particular, I won't be like this once we get past the foundations of the script.

- I see, okay then.
- Okey then, I'll edit that.
-

- To load a query, without having it executed. But you don't even need to use it, you can remove it from this class if you wish, however you need to change some stuff in the exeQuery then. Isn't it easier to just.. not use the loadQuery then?

Np, we'll have a better end-script if we get a solid base.

Quote:

Originally Posted by Salathe

Why use this over, for example, PDO or other currently available approaches?

I don't know.

Village Idiot · 12-30-2008, 03:56 PM

For the last point, just leave it as is then.

Orc · 12-30-2008, 05:24 PM

Quote:

Originally Posted by Salathe

Why use this over, for example, PDO or other currently available approaches?

Tanax could improve PDO? Lol, what would he improve though.

Village Idiot · 12-30-2008, 09:03 PM

We won't use PDO because not all shared server have it. I want this application to be as stand alone as possible.

Orc · 12-30-2008, 09:05 PM

Quote:

Originally Posted by Village Idiot

We won't use PDO because not all shared server have it. I want this application to be as stand alone as possible.

Informative. Also, by stand alone, you mean..?

Tanax · 12-30-2008, 09:06 PM

Quote:

Originally Posted by Orc

Informative. Also, by stand alone, you mean..?

Meaning we don't want to rely on that everyone has PDO library installed.

Village Idiot · 12-30-2008, 09:06 PM

I mean plug and play. Since shared servers have virtually no flexibility, there are very few resources we can count on having. ImageGD is absolutely essential and almost everyone has it.

Since it will be an open source project made by volunteers, chances are that 99% of our users will be on a cheap shared server. Anyone willing to keep up a dedicated probably wouldn't go to a small corner of the internet for their script.

Orc · 12-30-2008, 09:27 PM

Quote:

Originally Posted by Village Idiot

I mean plug and play. Since shared servers have virtually no flexibility, there are very few resources we can count on having. ImageGD is absolutely essential and almost everyone has it.

Since it will be an open source project made by volunteers, chances are that 99% of our users will be on a cheap shared server. Anyone willing to keep up a dedicated probably wouldn't go to a small corner of the internet for their script.

ImageGD? lol, the Image class is on Rendairs head, correct?

Village Idiot · 12-30-2008, 09:33 PM

Yes, that is his job, but I am saying we are using GD because its required. PDO is not.

sketchMedia · 12-30-2008, 10:32 PM

can we PLEASE stop using error suppression i.e. '@'

For one it slows down your script and secondly it makes it hard to debug and I for one can't be bothered grep'ing through the code for '@' every time something just blows up and no error is shown.

For the production version, set error_reporting(0).

Orc · 12-30-2008, 10:33 PM

Quote:

Originally Posted by sketchMedia

can we PLEASE stop using error suppression i.e. '@'

For one it slows down your script and secondly it makes it hard to debug and I for one can't be bothered grep'ing through the code for '@' every time something just blows up and no error is shown.

For the production version, set error_reporting(0).

I hardly use it myself. :P Mainly well, cause, I never get errors. :P

Tanax · 12-31-2008, 12:16 AM

Quote:

Originally Posted by sketchMedia

can we PLEASE stop using error suppression i.e. '@'

For one it slows down your script and secondly it makes it hard to debug and I for one can't be bothered grep'ing through the code for '@' every time something just blows up and no error is shown.

For the production version, set error_reporting(0).

But I throw an exception if it didn't work? So you'll get your error msg.

Village Idiot · 12-31-2008, 12:43 AM

As he said, error_reporting(0) will suppress them all.

I don't really care what you do for it, although it would be nice if you leave less for everyone during the optimization phase.

Tanax · 12-31-2008, 01:48 AM

Uhm, yea. If you want, I could remove them later.

I've updated the class now. Added new method: getRows, and also edited some of the old functions.

Some simple howto's have been posted aswell.

masfenix · 12-31-2008, 04:44 AM

A lot of programmers are not fans of using exceptions to control the flow of the program. Most of your functions are using exceptions to exit out which is generally bad practice. Throw an exception when your program can not function.

For example, your secure function should be returning a true/false. It should also be a private function. Call the function in your exeQuery() method which would tell the exeQuery() method to finish execution. If it returns false the exeQuery() dosnt even execute the SQL query, and returns an empty dataset.