The SQL Class - Page 4

Tanax · 01-17-2009, 09:51 PM

Sorry. Didn't notice.

Quote:

It's not clear that this member actually means "database name" instead of some holder of data.

I don't know what others think, but it's a database class, and since the variable is grouped with host, user, and pass, it's pretty clear to me that it's the dbname.

Quote:

I'm unclear about getting everything set up and ready with this class. In order to get the ball rolling it appears one must first create an instance of this class, then explicitly call setHandler, then connect, then select (in that order only)? This feels like something to be done automatically in a constructor.

That's correct.
And that's because I've sometimes noticed that I wanted to set up the class before actually connecting, so that's why I've seperated them.
And yes, you have to call connect before the select, I think that's pretty obvious. You can't select the db when you haven't even connected.

I haven't heard anyone else say anything about this, but if it's a big problem, I could always change it.

Quote:

Suggest unsetting $this->pass after connecting

Good idea.

Quote:

Perhaps also call this method in a DBmysql::__destruct method to make sure it's always closed?

Now that I know that we will probably use a factory to allow different dbengine support, I can use the destruct now!

Quote:

Under what circumstances will this line get executed? Either $sql is NULL or not NULL.

Haha, ooops xD

Quote:

Why empty something you've just unset?

Just to be sure. Will it cause troubles?

Quote:

After this assignment, $sql will be either a "mysql result resource", TRUE or FALSE ... so why is this called $sql?

No. After that assignment $sql will always be a mysql result resource. What do you suggest it to be called then? I could change name to $query_result.

Quote:

Scenario:
1. Magic quotes is on: remove slashes from the string
2. Magic quotes is off: add slashes to the string

Given input "3 o' clock":
1. Magic quotes on means input is changed to "3 o\' clock" then we remove the slashes to make $secured "3 o' clock"
2. Magic quotes off means input isn't slashed ("3 o' clock") but we then add slashes to make $secured "3 o\' clock"

Result: different $secured values for the same input: that's not what we want.

Hmm, must've thought wrong. How would I do that then? Simply by removing the addslashes?

Quote:

Not 100% sure but if mysql_real_escape_string isn't available, I don't think mysql_escape_string will be available either. Just use mysql_real_escape_string.

But then again, you're not 100% sure, and it doesn't harm to keep it like it's now, unless you find out and become 100% sure

Quote:

Why are there so many places where one can provide a SQL query as an argument? I count 5.

There also appears to be a confusing selection of ways to achieve a usually simple task: grab an assoc. array of results. More clear documentation of the use of this class is requested (perhaps on the forums).

To make it more flexible. Sometimes you want to assign a sql statement, but not run it. Sometimes you want to run a query, but not get the result(for example when you're deleting a record. Sometimes you want to run a query and get the results. Obviously, you can achieve those by combining these.

Like:

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

But, for those lazy people, I designed the exeQuery to accept an sql statement aswell, so it will loadQuery auto.

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

About the arrays, I wanted people to have as much "freedom" there.
Some people might want to load a fetched array, but not actually retrieve it until later. Some people might want to retrieve it at the same time.

So either, you execute a query before.

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

Or, you execute the query WHILE fetching the array

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

It's really simple. At least I think so. First parameter in loadFetch decides if it's assoc. True or false. Second parameter in loadFetch is the sql statement. Both of them are optional.
First parameter in getFetch is if you skipped the loadFetch function. True if you skipped it(so that it autoloads the fetch), false if you have it. Second parameter is the same as loadFetch's first parameter. Third parameter is the same as loadFetch's second parameter.

Salathe · 01-17-2009, 10:30 PM

So let me get this straight with an example. Basically we'll connect to the database and return some results:

PHP Code:


			
$db = new DBmysql;

$db->setHandler('localhost', 'root', 'mysupersecretpassword', 'phlox_dev')

   ->connect()

   ->select();



$sql  = 'SELECT * FROM sample'; // Assume columns: id, title



// Get associative array of rows

$rows = $db->getFetch(true, true, $sql);

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

// or $rwos = $db->loadQuery($sql)->exeQuery()->getFetch(true, true);

// or $rows = $db->loadFetch(true, $sql)->getFetch();



foreach ($rows as $row)

{

    sprintf("Yay I got record ID: %d\n", $row['id']);

}

Ross · 01-17-2009, 10:34 PM

Quote:

Originally Posted by Tanax

And that's because I've sometimes noticed that I wanted to set up the class before actually connecting, so that's why I've seperated them.

Could you give an example? Sorry, just never been in that position before ;)

Tanax · 01-17-2009, 11:17 PM

Quote:

Originally Posted by Salathe

So let me get this straight with an example. Basically we'll connect to the database and return some results:

PHP Code:


			
$db = new DBmysql;
$db->setHandler('localhost', 'root', 'mysupersecretpassword', 'phlox_dev')
   ->connect()
   ->select();

$sql  = 'SELECT * FROM sample'; // Assume columns: id, title

// Get associative array of rows
$rows = $db->getFetch(true, true, $sql);
// or $rows = $db->exeQuery($sql)->loadFetch(true)->getFetch();
// or $rwos = $db->loadQuery($sql)->exeQuery()->getFetch(true, true);
// or $rows = $db->loadFetch(true, $sql)->getFetch();

foreach ($rows as $row)
{
    sprintf("Yay I got record ID: %d\n", $row['id']);
}

All of that was correct, indeed. Obviously, since you want to get the fetch directly, the code you didn't comment out is the preferred one(but you probably already figured that out!).

Quote:

Originally Posted by Ross

Could you give an example? Sorry, just never been in that position before ;)

Sure.
In my previous projects, I had a factory, which managed all of the classes I wanted to import into it, not only the DBclass. Some of those classes, did not use any params in the construct, so I found it best to start all the classes first.

core.php

PHP Code:


			
include('classes/TANAXIA.php');

$db = TANAXIA::getInstance('DBmysql');
$pagination = TANAXIA::getInstance('Pagination');
// etc

And then
classes.php

PHP Code:


			
include('core.php');

$db->setHandler($host, $user, $pass, $data);
$pagination->setConfiguration($maxperpage);

And finally:
config.php

PHP Code:


			
$host = 'localhost';
$user = 'root';
$pass = 'secret';
$data = 'testing';

$maxperpage = 10;

include('classes.php');

In my index files, I would then only have to do this

PHP Code:


			
include('includes/config.php');

to have acccess to all my classes, which are managed from the classes.php file, and settings configurable from config.php.

If I want to add another class, I simply include it in my core file, set it up in my classes file(assuming it needs any functions runned before you can use it from index files), and have the settings stored in the config file.

A simple, but quite straight to the point example.

Tanax · 01-22-2009, 02:28 PM

Can someone answer the questions in this post

I need clarifications before I submit another rev of this file.
Thank you.

Village Idiot · 01-22-2009, 03:50 PM

I'll leave you to decide on everything but mysql_real_escape_string(), always use real escape string since it adapts it method to that specific version of MySql. I did some digging and it is supported from version 4.3, I don't care to cater to anything below version 5 so it will not be an issue.

Tanax · 01-22-2009, 08:22 PM

Quote:

Originally Posted by Village Idiot

I'll leave you to decide on everything but mysql_real_escape_string(), always use real escape string since it adapts it method to that specific version of MySql. I did some digging and it is supported from version 4.3, I don't care to cater to anything below version 5 so it will not be an issue.

Great!
Updated it.

Unsure about the __destruct function. Does it need something more?
I've submitted the update anyhow. Comment if you have any suggestions. Thanks

Tanax · 01-28-2009, 04:29 PM

Quote:

For readability, how about using an if-else here?

What does that mean? Or I mean.. how would that look?

Salathe · 01-28-2009, 05:20 PM

It just means that the line like:

PHP Code:


			
$query_result = (is_null($sql) && isset($this->query_result)) ? $this->query_result : $this->exeQuery($sql)->getQueryResult();

might as well be:

PHP Code:


			
if (is_null($sql) && isset($this->query_result))

{

    $query_result = $this->query_result;

}

else

{

    $query_result = $this->exeQuery($sql)->getQueryResult();

}

It's just much easier to scan the code quickly and see what's happening in the if/else than it is in one long ternary line. If you still want to use the ternary operator, at least split it onto multiple lines.

Tanax · 01-28-2009, 07:15 PM

Okay, it was just that xD
Ye okey, I've edited it, along with some other small things.

Check the updated version.
Thanks!

sketchMedia · 01-28-2009, 08:09 PM

Can we please use brackets regardless if its one line or not, I can't tell you how much easier it makes code debugging not to mention readability.

Also a few methods could do we a quick refactor, alot of unneeded else's :

PHP Code:


			
public function connect()
 {
     $this->con = mysql_connect($this->host, $this->user, $this->pass);
     unset($this->pass);
            
     if($this->con) return $this;
     else return false;
}

Because you are going to jump out with return if the connection was successful, this means that anytime the interpreter comes to the lines under the return, means that the above condition didnt equate to true, thus the operation failed. It could be written like this and still have the desired effect:

PHP Code:


			
public function connect()
 {
     $this->con = mysql_connect($this->host, $this->user, $this->pass);
     unset($this->pass);
            
     if($this->con) { return $this; }
     return false;
}

Dont make the interpreter work for little to no gain :)

Tanax · 01-28-2009, 09:25 PM

I thought it would be easier with less lines, but still.. ye I dno. Sure, I can use brackets for all.

I'll do it in the next update. Any more you can think of right now before I get the chance to commit?

Tanax · 01-29-2009, 12:29 AM

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();
        
        public function __destruct()
        {
            
            if(isset($this->con))
            {
                
                $this->disconnect();
                
            }
            
        }
       
        /**
         * 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);
            unset($this->pass);
            
            if($this->con) 
            {
                
                return $this;
                
            }
            
            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;
                
            }
           
            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;
                
            }
           
            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;
                        
                    }
                    
                    return false;
                   
                }
               
                return false;
               
            }
           
            $this->loadQuery($sql)->exeQuery();
           
           
        }
       
        /**
         * Basic description:
         * Gets the results from the query
         *
         * Conditions:
         * You have to execute a query before you can use this function
         *
         * Detailed description:
         * If you want a specific row, fill in the first parameter with the number of the row.
         * If you just want a specific column, fill in the second parameter with the name of the field
         * aswell as the first parameter with the number of the row
         *
         * @param int(optional) $row
         * @param string(optional) $name
         * @return $this->query_result
         */
        public function getQueryResult($row = NULL, $name = NULL)
        {
            if(isset($this->query_result))
            {
                
                if($row != NULL && is_numeric($row))
                {
                    
                    if($name != NULL)
                    {
                        
                        return mysql_result($this->query_result, $row, $name);
                        
                    }
                    
                    return mysql_result($this->query_result, $row);
                    
                }
                
                return $this->query_result;
                
            }
            
            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);
         
            if(is_null($sql) && isset($this->query_result)) 
            {
                
                $query_result = $this->query_result;
                
            }
            
            else 
            {
                
                $query_result = $this->exeQuery($sql)->getQueryResult();
                
            }
            
            if($assoc == true) 
            {
                
                $fetchType = MYSQL_ASSOC;
                
            }
            
            else 
            {
                
                $fetchType = MYSQL_NUM;
                
            }
       
            if(!empty($query_result))
            {
               
                while($fetch = mysql_fetch_array($query_result, $fetchType))
                {
                   
                    $this->query_fetch[] = $fetch;
                   
                }
       
                if(is_array($this->query_fetch)) 
                {
                    
                    return $this;
                    
                }
                
                return false;
                
            }
            
            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);
         *
         * @param boolean(optional) $autoload
         * @param boolean(optional) $assoc
         * @param string(optional) $sql
         * @return $this->query_fetch
         */
        public function getFetch($autoload = false, $assoc = false, $sql = NULL)
        {
           
            if($autoload == false)
            {
               
                if(isset($this->query_fetch) && is_array($this->query_fetch)) 
                {
                    
                    return $this->query_fetch;
                    
                }
               
                return false;
               
            }
           
            $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);
                    
                }
                
                return false;
               
            }
           
            $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 $value
         * @return $secured
         */
        public function secure($value)
        {
           
            if(get_magic_quotes_gpc())
            {
                
                $value = stripslashes($value);
                
            }
            
            else 
            {
                
                $value = $value;
                
            }
            
            if(is_numeric($value)) 
            {
                
                $secured = $value;
                
            }
            
            else 
            {
                
                $secured = mysql_real_escape_string($value);
                
            }
           
            return $secured;
                       
        }
       
    }



?>

Better?

verheesj · 01-29-2009, 12:49 AM

Woh, that's a very nice class and well documented. Thanks for sharing :)

sketchMedia · 01-29-2009, 01:43 AM

Quote:

Better?

Much

(char limit grrrr)

Tanax · 01-29-2009, 11:00 AM

Quote:

Originally Posted by verheesj

Woh, that's a very nice class and well documented. Thanks for sharing :)

Thank you

Quote:

Originally Posted by sketchMedia

Much

(char limit grrrr)

Great

Salathe · 01-29-2009, 12:53 PM

Just a quick question: how would I make a write query (cf. read queries: SELECT, SHOW, etc.) such as INSERT or UPDATE?

Tanax · 01-29-2009, 01:00 PM

php Code:

$sql = "INSERT INTO `table` SET `col` = 'value'";

$db->exeQuery($sql);
//$db->loadQuery($sql)->exeQuery();

An update query we don't need to get results from, so you don't need to call the getQueryResult() after you've executed.

However, if you need a result(such as when you're using "read" queries), you'd have to do this:

php Code:

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

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

Tanax · 01-30-2009, 10:14 AM

Revision has been updated.

Tanax · 01-30-2009, 04:15 PM

I found this kinda cool function on the net, which would allow you to insert content easier. It's good if you have long queries(which I'm sure we will have later on).

Transforms this:

PHP Code:


			
$sql = "INSERT INTO wp_comments (comment_post_ID, comment_author, comment_author_email,
comment_author_url, comment_author_IP, comment_date, comment_date_gmt,
comment_content, comment_approved, comment_agent, comment_type, comment_parent, user_id)
VALUES
('$comment_post_ID', '$comment_author', '$comment_author_email', '$comment_author_url',
 '$comment_author_IP', '$comment_date', '$comment_date_gmt', '$comment_content',
'$comment_approved', '$comment_agent', '$comment_type', '$comment_parent', '$user_id')";

$db->exeQuery($sql);

into this:

PHP Code:


			
$sql_data = array('comment_post_ID'      => $comment_post_ID,
                  'comment_author'       => $comment_author,
                  'comment_author_email' => $comment_author_email, // some comments here
                  'comment_author_url'   => $comment_author_url,
                  'comment_author_IP'    => $comment_author_IP,   // IP of the author
                  'comment_date'         => $comment_date,    // Comment date
                  'comment_date_gmt'     => $comment_date_gmt,
                  'comment_content'      => $comment_content,
                  'comment_approved'     => $comment_approved,
                  'comment_agent'        => $comment_agent,
                  'comment_type'         => $comment_type,
                  'comment_parent'       => $comment_parent,
                  'user_id'              => $user_id);

$db->insert('wp_comments', $sql_data);

Implement?

Function found here:
http://www.bitrepository.com/web-pro...cient-way.html