View Single Post
  #1 (permalink)  
Old December 2nd, 2009, 01:57 PM
shegxzyl shegxzyl is offline
Registered User
 
Join Date: Jun 2008
Location: amuwo-odofin, lagos, Nigeria.
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
Send a message via Yahoo to shegxzyl
Default MySQL version for sql.phpm [Chapter 7]

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

we then use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.
so here goes the class...

Code:
class sql {
  private $result_rows;  # Result rows hash
  private $query_handle;  # db: the query handle
  private $link_ident;  # db: the link identifier
  private $last_id;		# last id. incase we need it for future use
  
  public function __construct() {

  	$db ="my_db";
	$db_host="localhost";
	$db_username="mysqluser";
	$db_password="mysqlpass";
	
	# create a database connection
    $this->link_ident = mysql_connect($db_host, $db_username, $db_password);
	if ( !is_resource($this->link_ident) )
		die ('Cannot connect to DB');
	else # select a database if the connection was successful
		mysql_select_db($db, $this->link_ident);
  }
  
  public function query($sql, $code_return_mode = 0) {
    $result = mysql_query( $sql, $this->link_ident ) or die( 'error: ' . mysql_error() );
	$mysql_last_id = mysql_insert_id($this->link_ident);    
	
	# INSERT, UPDATE, DELETE, DROP etc queries returns a boolean. only mysql_affected_rows can be used on this.
	# While SELECT, SHOW, DESCRIBE, EXPLAIN returns a resource. so we can apply mysql_num_rows.
	# check if the result is not a resource
	if ( !is_resource($result) ) {
		//do nothing
		$this->last_id = mysql_affected_rows($this->link_ident);
	} else {	# if it is a resource; we can then make use of mysql_num_rows

		for ( $i=0; $i <= mysql_num_rows($result) - 1; $i++ ) {
			$response = mysql_fetch_array($result, $i);
			$return_array[$i] = $response;
			$this->last_id = $mysql_last_id;
		};

	}

	    $this->result_rows = $return_array;
		
    if (!$result) 
	{
      error_log("QUERY FAILED: $sql\n");
    };
	
    if (!$result) 
	{
      return(1);
    } else 
	{
      return(0);  # return 0 if it fails
    };
  }
  
  public function get_result($row_num, $column_name) {
    return ($this->result_rows[$row_num][$column_name]);
  }
  
  public function get_row_hash($row_num) {
    return ($this->result_rows[$row_num]);
  }  
  
  public function get_table_hash() {
    return $this->result_rows;
  }
  # you can as well use this to get the last id of an insert sql statement.
  public function get_last_id() {
  	return $this->last_id;
	}
  
  public function done($close_connection = 0) {
    if ($close_connection) {
      mysql_close($this->link_ident);
    };
  }
};
it works fine. i hope its useful for somebody.
thanks to slurp56 for usersession.phpm mysql conversion.