Page 1 of 1

PHP 5 DB and Query Classes:

Posted: Wed Nov 04, 2009 3:13 am
by shortaug
Hello.

I have been working on this for the last two days and I just can't wrap my head around why it isn't working.

I have two classes: DB and Query. DB performs the DB connection using the Singleton method. Query does just that, performs the query (with some future added sanitization).

Here they are:

Code: Select all

<?php
    /*
    Defines the MySQL data connection and attempts to connect.
    */
    
    class DB {
        private $user = "---"; 
        private $pass = "---";
        private $host = "---";
        private $db = "---";
        private static $instance;
        
        private function __construct() {
            $this->dbConnect();
        }
 
        public function dbConnect() {
            @mysql_connect($this->host, $this->user, $this->pass);
            @mysql_select_db($this->db);
        }
        
        public static function instance() {
            if (!isset(self::$instance)) {
                $c = __CLASS__;
                self::$instance = new $c;
            }
            return self::$instance;
        }
    }
?>

Code: Select all

<?php
include 'db.php';
/*
SQL Sanitization and Database Query.
*/
    class Query {
        private $query;
        private $fetched = array();
        
        function __construct($query, $db) {
            $this->query = $query;
            $this->sanitize();
            $this->execute($db);
        }
        
        public function sanitize() {
            $this->query = filter_var($this->query, FILTER_SANITIZE_STRING);
            $this->query = filter_var($this->query, FILTER_FLAG_STRIP_LOW);
            $this->query = filter_var($this->query, FILTER_FLAG_STRIP_HIGH);
            $this->query = mysql_real_escape_string($this->query);
        }
    
        public function execute($db) {
            $result = mysql_query($this->query, $db);
            while ($row = mysql_fetch_assoc($result)) {
                if (!isset($fetched[$row["group"]])) $fetched[$row["group"]] = array();
                $fetched[$row["group"]][$row["title"]] = $row["link"];
                    }
        }
        
        public function getResult() {
            return $this->fetched;
        }
            
    }
?>
I call this using:

Code: Select all

 
include 'query.php';
$db = DB::instance();
$query = new Query("SELECT * FROM some_tbl;", $db);
$result = $query->getResult();
 
When passing $db in new Query, I get the following errors:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in ./query.php on line 26

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ./query.php on line 27

When not passing $db in new Query, I get the following errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ./query.php on line 27

---------

Like I mentioned, I have been perusing the PHP Documentation and every tutorial on this I could find and I just cannot figure it out.

To note: I am using PHP 5.2.8 (and cannot upgrade or downgrade).

Each class is in its own file (db.php, query.php).

The test code is in a file called test.php.

Thanks for any help. And, if you can help me solve this by pointing me in the right direction and letting me read up on the mistake rather than posting the solution, I'd be much obliged.

Re: PHP 5 DB and Query Classes:

Posted: Wed Nov 04, 2009 7:02 pm
by shortaug
I knew that, but what is confusing is that, mysql_real_escape_string works, and so I know the connection is there and accessible, because as soon as I comment out the $db = DB::instance(); in site.php, mysql_real_escape_string errors out with:
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Can't connect to local MySQL server through socket '/var/tmp/mysql.sock' (2) in ./query.php on line 24

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in ./query.php on line 24
Now, although it's not the same as "Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in ./query.php on line 26", it is still dealing with the MySQL connection. Right? That is, both mysql_query and mysql_real_escape_string have as an optional parameter: "resource $link_identifier", and in the absence, PHP searches for the last mysql_connection.

Now, why can mysql_real_escape_string access this MySQL connection, but not mysql_query?

Re: PHP 5 DB and Query Classes:

Posted: Wed Nov 04, 2009 7:21 pm
by shortaug
Problem Solved.

I was right in my second post.

I was misinterpreting what the below was doing however, and by the time my query sanitization method was complete, $query was equal to "".

# $this->query = filter_var($this->query, FILTER_FLAG_STRIP_LOW);
# $this->query = filter_var($this->query, FILTER_FLAG_STRIP_HIGH);

Soon as I removed those, it all works hunky dory.

Danke