Problem with multiple SQL queries using class

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Problem with multiple SQL queries using class

Post by Sindarin »

This is my sql database class I made,

Code: Select all

class sqlDatabase
{
 
    public $db_engine = 'mysql';
    public $db_charset = 'utf8';
    public $db_host = 'localhost';
    public $db_username = 'root';
    public $db_password = '';
    public $db_database = '';
    public $db_connection = '';
    public $db_result = '';
    public $db_query = '';
 
    private $SQL_DB_SUCCESS = 0;
    private $SQL_DB_CONNECTION_ERROR = 1;
    private $SQL_DB_CHARSET_ERROR = 2;
    private $SQL_DB_SELECT_ERROR = 3;
    private $SQL_DB_CLOSE_ERROR = 4;
    private $SQL_DB_NO_ENGINE = 5;
    private $SQL_DB_QUERY_ERROR = 6;
    private $SQL_DB_FREE_ERROR = 7;
 
    public function __construct($db_engine = false, $db_charset = false, $db_host = false, $db_database = false)
    {
        $this->db_engine = $db_engine;
        $this->db_charset = $db_charset;
        $this->db_host = $db_host;
        $this->db_database = $db_database;
    }
 
    public function sqlConnect( $db_username = false, $db_password = false )
    {
        $this->db_username = $db_username;
        $this->db_password = $db_password;
 
        switch($this->db_engine)
        {
            case 'mysql':
                //start database connection for mysql
                $this->db_connection = mysql_connect($this->db_host,$this->db_username,$this->db_password);
                if (!$this->db_connection){return $this->SQL_DB_CONNECTION_ERROR;exit;}
                //set the connection charset for mysql
                if (!mysql_set_charset($this->db_charset, $this->db_connection)){return $this->SQL_DB_CHARSET_ERROR;exit;}
                //connect to the database for mysql
                if (!mysql_select_db($this->db_database, $this->db_connection)){return $this->SQL_DB_SELECT_ERROR;exit;} 
                return $this->SQL_DB_SUCCESS;
            break;
            case 'mysqli':
                //start database connection for mysqli
                $this->db_connection = mysqli_connect($this->db_host,$this->db_username,$this->db_password);
                if (!$this->db_connection){return $this->SQL_DB_CONNECTION_ERROR;exit;}
                //set the connection charset for mysqli
                if(!mysqli_set_charset($this->db_connection, $this->db_charset)){return $this->SQL_DB_CHARSET_ERROR;exit;}
                //connect to the database for mysqli
                if(!mysqli_select_db($this->db_connection, $this->db_database)){return $this->SQL_DB_SELECT_ERROR;exit;} 
                return $this->SQL_DB_SUCCESS;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlEscapeString($string)
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //escape string for query
                $string = mysql_real_escape_string($string, $this->db_connection);
                return $string;
            break;
            case 'mysqli':
                //escape string for query
                $string = mysqli_real_escape_string($this->db_connection, $string);
                return $string;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlQuery($db_query = false)
    {
        $this->db_query = $db_query;
        
        switch($this->db_engine)
        {
            case 'mysql':
                //execute query in mysql
                if(!$this->db_result = mysql_query($this->db_query, $this->db_connection)){return $this->SQL_DB_QUERY_ERROR;exit;}
                return $this->SQL_DB_SUCCESS;
            break;
            case 'mysqli':
                //execute query in mysqli
                if(!$this->db_result = mysqli_query($this->db_connection, $this->db_query)){return $this->SQL_DB_QUERY_ERROR;exit;}
                return $this->SQL_DB_SUCCESS;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    
    public function sqlFetch()
    {
    
        /*
    
        sample usage:
        while ($row = $db->sqlFetch())
        {
            $row_username = $row['row_username'];
            
            echo $row_username.'<br />';
        }
        
        */
        
        switch($this->db_engine)
        {
            case 'mysql':
                //fetch array in mysql
                return mysql_fetch_array($this->db_result);
            break;
            case 'mysqli':
                //fetch array in mysqli
                return mysqli_fetch_array($this->db_result);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlNumRows()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //num rows in mysql
                return mysql_num_rows($this->db_result);
            break;
            case 'mysqli':
                //num rows in mysqli
                return mysqli_num_rows($this->db_result);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlAffectedRows()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //affected rows in mysql
                return mysql_affected_rows($this->db_result);
            break;
            case 'mysqli':
                //affected rows in mysqli
                return mysqli_affected_rows($this->db_result);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlGetEncoding()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //get encoding in mysql
                return mysql_client_encoding($this->db_connection);
            break;
            case 'mysqli':
                //get encoding in mysqli
                return mysqli_client_encoding($this->db_connection);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlSetEncoding($encoding)
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //set encoding in mysql
                if(!mysql_set_charset($encoding, $this->db_connection)){return $this->SQL_DB_CHARSET_ERROR;exit;}
                return $this->SQL_DB_SUCCESS;
            break;
            case 'mysqli':
                //set encoding in mysqli
                if(!mysqli_set_charset($this->db_connection, $encoding)){return $this->SQL_DB_CHARSET_ERROR;exit;}
                return $this->SQL_DB_SUCCESS;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlFreeResult()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //free result in mysql
                if(!mysql_free_result($this->db_result)){return $this->SQL_DB_FREE_ERROR;}
                return $this->SQL_DB_SUCCESS;
            break;
            case 'mysqli':
                //free result in mysqli
                if(!mysqli_free_result($this->db_result)){return $this->SQL_DB_FREE_ERROR;}
                return $this->SQL_DB_SUCCESS;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlError()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //display error in mysql
                return mysql_error();
            break;
            case 'mysqli':
                //display error in mysqli
                return mysqli_error($this->db_connection);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
    
    public function sqlDisconnect()
    {
        switch($this->db_engine)
        {
            case 'mysql':
                //close the connection for mysql
                if (!mysql_close($this->db_connection)){return $this->SQL_DB_CLOSE_ERROR;}
                return $this->SQL_DB_SUCCESS;
            break;
            case 'mysqli':
                //close the connection for mysqli
                if(!mysqli_close($this->db_connection)){return $this->SQL_DB_CLOSE_ERROR;}
                return $this->SQL_DB_SUCCESS;
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
}
Now I am trying to select all the entries from my table named 'vehicle_entries', that works okay, but if I try to do another query inside that query in order to select the brand and the model of that entry it doesn't work, it only returns one entry.

Code: Select all

    
$db->sqlQuery("SELECT * FROM `vehicle_entries` ORDER BY row_id ASC");
    while ($row = $db->sqlFetch())
    {
        $row_id = $row['row_id'];
        $row_brand = $row['row_brand'];
        $row_model = $row['row_model'];
            
            /* get brand */
            $db->sqlQuery("SELECT * FROM `vehicle_car_brands` WHERE row_id='$row_brand' ");
            while ($row_2 = $db->sqlFetch())
            {
                $row_brand_title = $row_2['row_title'];
            }
            /* get model */
            $db->sqlQuery("SELECT * FROM `vehicle_car_models` WHERE row_parent='$row_brand' ");
            while ($row_3 = $db->sqlFetch())
            {
                $row_model_title = $row_3['row_title'];
            }
 
//echo entries and brand/model title
}
 
I am sensing that there is something wrong in my sql class. Especially in the sqlFetch() function.
Any help please?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Problem with multiple SQL queries using class

Post by Benjamin »

I would speculate that the previous result set is being discarded once the new query is executed. You'll need to retrieve all the records returned by the first query or modify your class to handle multiple result sets.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Problem with multiple SQL queries using class

Post by Sindarin »

or modify your class to handle multiple result sets.
How would I do that please?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Problem with multiple SQL queries using class

Post by Eran »

Regardless, this query would be better handled using joins instead of querying inside a loop
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Problem with multiple nested SQL queries using class

Post by Sindarin »

I used a variable to fix this, I changed sqlQuery() function to this:

Code: Select all

    public function sqlQuery($db_query = false)
    {
        $this->db_query = $db_query;
        
        switch($this->db_engine)
        {
            case 'mysql':
                //execute query in mysql
                return mysql_query($this->db_query, $this->db_connection);
            break;
            case 'mysqli':
                //execute query in mysqli
                return mysqli_query($this->db_connection, $this->db_query);
            break;
            default:
                return $this->SQL_DB_NO_ENGINE;exit;
        }
    }
sqlFetch() function now requires the result as first argument, so it's:

Code: Select all

    $dresult = $db->sqlQuery("SELECT * FROM `vehicle_entries` ORDER BY row_id ASC");
    while ($row = $db->sqlFetch($dresult))
...

Code: Select all

Regardless, this query would be better handled using joins instead of querying inside a loop
I'd like to know more about JOIN, tried some tutorials and didn't work good for me. Also how this work with my case, as e.g. the second query needs the id from the first.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Problem with multiple SQL queries using class

Post by Eran »

Also how this work with my case, as e.g. the second query needs the id from the first.
That's exactly where joins are useful. The columns connecting between the tables (row_id -> row_brand etc.) define the relationship between the tables and are used to perform the join.

This should get everything with one query:
[sql]SELECT `vehicle_entries`.*,`vehicle_car_brands`.`row_title` AS brand_title,`vehicle_car_models`.`row_title` AS model_title FROM `vehicle_entries` LEFT JOIN `vehicle_car_brands` ON `vehicle_car_brands`.`row_id`=`vehicle_entries`.`row_brand`LEFT JOIN `vehicle_car_models` ON `vehicle_car_models`.`row_parent`=`vehicle_entries`.`row_brand`ORDER BY `vehicle_entries`.`row_id` ASC[/sql]

By the way, any reason why all those column names are prefixed with 'row'?
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Re: Problem with multiple SQL queries using class

Post by jason »

On a side note, most Database classes separate the Query portion of the class and the Result. You might consider doing this. Basically, your query returns a new class, a DatabaseResult class, let's call it. This object would allow you traverse the return data that you retrieved.
Post Reply