Problem with multiple SQL queries using class
Posted: Tue Jan 05, 2010 2:33 am
This is my sql database class I made,
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.
I am sensing that there is something wrong in my sql class. Especially in the sqlFetch() function.
Any help please?
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;
}
}
}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
}
Any help please?