我真的需要一个MySQL查询类吗？

Question

I have a simple login and registration form, with some extra insert and select queries for various different things.

I have completed this tutorial (http://code.tutsplus.com/tutorials/real-world-oop-with-php-and-mysql--net-1918) which is to create a PHP class that supposedly makes it easier to select, insert, disconnect, update, to a MySQL server.

The thing is, now that I have done the tutorial and have started to implement some of the changes from the static select and insert queries to the new one that refers to functions inside the class, I am seeing that the new code is longer and more complicated, which I feel beats the objective of the class.

Any thoughts and suggestions?

For example, a general insert query goes:

mysqli_query("INSERT INTO statuses(User_ID, Status)VALUES('$userid', '$statusupdate')") or die(myself_error());

Yet, the tutorial mentioned above required the following:

$db->insert('mysqlcrud',array(3,"Name 4","[e-mail]<script type="text/javascript">
/* <![CDATA[ */
(function(){try{var s,a,i,j,r,c,l,b=document.getElementsByTagName("script");l=b[b.length-1].previousSibling;a=l.getAttribute('data-cfemail');if(a){s='';r=parseInt(a.substr(0,2),16);for(j=2;a.length-j;j+=2){c=parseInt(a.substr(j,2),16)^r;s+=String.fromCharCode(c);}s=document.createTextNode(s);l.parentNode.replaceChild(s,l);}}catch(e){}})();
/* ]]> */
</script>"));

The class file shows :

    <?php
/**
 * Created by PhpStorm.
 * User: marshall
 * Date: 01/03/14
 * Time: 21:34
 */

namespace MySQL\lib;


class Database {


        private $db_host = 'localhost';
        private $db_user = 'root';
        private $db_pass = 'pass';
        private $db_name = 'database';


    public function connect() {
        if(!$this->con) {
            $myconn = mysql_connect($this->db_host, $this->db_user, $this->db_pass);
            if(myconn) {
                $seldb = mysql_select_db($this->db_name,$myconn);
                if($seldb) {
                    $this->con = true;
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } else {
            return true;
        }
        }

    public function disconnect() {
        if($this->con)
        {
            if(mysql_close())
            {
                $this->con = false;
                return true;
            }
            else
            {
                return false;
            }
        }
    }

    private $result = array();

    private function tableExists($table)
    {
        $tablesInDb = mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
        if($tablesInDb)
        {
            if(mysql_num_rows($tablesInDb)==1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }


    public function select($table, $rows = '*', $where = null, $order = null)
    {
        $q = 'SELECT '.$rows.' FROM '.$table;
        if($where != null)
            $q .= ' WHERE '.$where;
        if($order != null)
            $q .= ' ORDER BY '.$order;
        if($this->tableExists($table))
       {
        $query = mysql_query($q);
        if($query)
        {
            $this->numResults = mysql_num_rows($query);
            for($i = 0; $i < $this->numResults; $i++)
            {
                $r = mysql_fetch_array($query);
                $key = array_keys($r);
                for($x = 0; $x < count($key); $x++)
                {
                    // Sanitizes keys so only alphavalues are allowed
                    if(!is_int($key[$x]))
                    {
                        if(mysql_num_rows($query) > 1)
                            $this->result[$i][$key[$x]] = $r[$key[$x]];
                        else if(mysql_num_rows($query) < 1)
                            $this->result = null;
                        else
                            $this->result[$key[$x]] = $r[$key[$x]];
                    }
}
}
return true;
}
else
    {
        return false;
    }
}
else
return false;
}
    public function insert($table,$values,$rows = null)
    {
        if($this->tableExists($table))
        {
            $insert = 'INSERT INTO '.$table;
            if($rows != null)
            {
                $insert .= ' ('.$rows.')';
            }

            for($i = 0; $i < count($values); $i++)
            {
                if(is_string($values[$i]))
                    $values[$i] = '"'.$values[$i].'"';
            }
            $values = implode(',',$values);
            $insert .= ' VALUES ('.$values.')';
            $ins = mysql_query($insert);
            if($ins)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }



    public function delete($table,$where = null)
    {
        if($this->tableExists($table))
        {
            if($where == null)
            {
                $delete = 'DELETE '.$table;
            }
            else
            {
                $delete = 'DELETE FROM '.$table.' WHERE '.$where;
            }
            $del = mysql_query($delete);

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


    public function update($table,$rows,$where)
    {
        if($this->tableExists($table))
        {
            // Parse the where values
            // even values (including 0) contain the where rows
            // odd values contain the clauses for the row
            for($i = 0; $i < count($where); $i++)
            {
                if($i%2 != 0)
                {
                    if(is_string($where[$i]))
                    {
                        if(($i+1) != null)
                            $where[$i] = '"'.$where[$i].'" AND ';
                        else
                            $where[$i] = '"'.$where[$i].'"';
                    }
                }
            }
            $where = implode('=',$where);


            $update = 'UPDATE '.$table.' SET ';
            $keys = array_keys($rows);
            for($i = 0; $i < count($rows); $i++)
            {
                if(is_string($rows[$keys[$i]]))
                {
                    $update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
                }
                else
                {
                    $update .= $keys[$i].'='.$rows[$keys[$i]];
                }

                // Parse to add commas
                if($i != count($rows)-1)
                {
                    $update .= ',';
                }
            }
            $update .= ' WHERE '.$where;
            $query = mysql_query($update);
            if($query)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
    }

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

}

Answer 1

It all depends on the size of your application: if it's simple there's no need to abstract database queries and you can just use PDO or a similar library to access your database.

However, if the code base grows larger, you might need an ORM that handles the records and their relationships for you.

One famous PHP ORM is Doctrine. It follows the Repository Pattern, which means that your records are little more than collections of getters and setters, and all the logic (querying, insertion etc.) is done using another object. For instance:

$user = new User();
$user
  ->setName('John Doe')
  ->setUsername('j.doe')
  ->setPassword('random123')
;

$em = $doctrine->getEntityManager();

$em->persist($user);
$em->flush();

Propel, on the other hand, uses the Active Record Pattern, where your entity classes are also used to handle querying and persistence. For example:

$user = new User();
$user
  ->setName('John Doe')
  ->setUsername('j.doe')
  ->setPassword('random123')
;
$user->save();

Which pattern to choose is mainly a matter of subjective preference.