Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
A MySQL PHP5 class
<?php
/**
* @note MySQL class::PHP5::php-mysql-class
* @author Stefan Antonowicz
*/
class MySQL {
private static $db = '';
/**
* Acceptable connection options - only static and persistent in this case
* @access private
* @var array
*/
private $acceptable_types = array( 'static', 'persistent' );
/**
* The tags which should not be stripped, passed to the PHP strip_tags() function
* @access public
* @var array
*/
public $clean_allowed = array( );
/**
* The DSN for the constructor i.e. <connection_type>://<username>:<password>@<host>/<db_name>
* @access public
* @var string
*/
public $dsn = '';
/**
* Whether or not magic_quotes is set
* @access private
* @var boolean
*/
private $quotes = FALSE;
/**
* The type of connector (either 'static' or 'persistent')
* @link $acceptable_types
* @access private
* @var string
*/
private $connect_type = '';
/**
* The MySQL result passed in by mysql_query
* @access public
* @var string
*/
public $result = '';
/**
* Constructor sets up {@link $dsn}, {@link $connect_type} and {@link $quotes}
* @param string $dsn the DSN
* @param string $type either static or dynamic
* @return boolean
*/
function __construct( $dsn, $type='static' ) {
$this->connect_type = in_array( $type, $this->acceptable_types ) ? $type : 'static';
$this->dsn = $dsn;
$this->quotes = get_magic_quotes_gpc( );
if(! $this->connect( ) ) return( FALSE );
return( TRUE );
}
/**
* Attempt to connect - die and return error message if unable to connect
* @return boolean
*/
private function connect( ) {
extract( parse_url( $this->dsn ) );
switch( $this->connect_type ) {
case 'static':
if (! ( $conn = mysql_connect( $host,$user,$pass, true ) ) )
die ( mysql_errno().' : '.mysql_error() );
break;
case 'persistent':
if (! ( $conn = mysql_pconnect( $host,$user,$pass, true ) ) )
die ( mysql_errno().' : '.mysql_error() );
break;
}
$this->db = str_replace( '/', '', $path );
if (! mysql_select_db( $this->db,$conn ) )
die ( mysql_errno().' : '.mysql_error() );
$this->conn = $conn;
return( TRUE );
}
/**
* Static function to cache a MySQL connection.
* @return resource|string
*/
public static function staticDB( ) {
if(! MySQL::$db ) {
MySQL::$db = new MySQL( DSN );
}
return( MySQL::$db );
}
/**
* Run a query against the mysql connection. Sets mysql result in {@link $result}
* @param string $sql a sql statement
* @return resource|boolean
* @example $this->query( "SELECT 1 FROM foo" );
*/
public function query( $sql='' ) {
if( $sql ) $this->sql = $sql;
if( empty( $this->sql ) )
die( 'No SQL statement specified' );
if( $this->conn && ! $this->checkResource( $this->conn ) )
return( false );
if(! ( $res = mysql_query( $this->sql, $this->conn ) ) )
$this->error( );
$this->result = $res;
$this->sql = NULL;
return( TRUE );
}
/**
* Check the MySQL resource to be sure it's valid. Die if the connection is invalid.
* @return boolean
*/
private function checkResource( ) {
if(! is_resource( $this->conn ) )
die( "The database $this->db isn't a resource" );
if ( get_resource_type( $this->conn ) !== 'mysql link' && get_resource_type( $this->conn ) !== 'mysql link persistent' )
die ( "The resource $this->db isn't a mysql connection - it's a type: " . get_resource_type( $this->conn ) );
return( TRUE );
}
/**
* Simple error wrapper. Die with error information. Here in case one wants to handle errors differently.
*/
private function error( ) {
die( mysql_errno($this->conn ).' ) '. mysql_error( $this->conn ).
'<hr />query was: ' . $this->sql . ' on DB '. $this->db );
}
/**
* Cleaning function for data that might be written to the database
*@link $clean_allowed
*@param string
*/
private function clean( $str ) {
$str = strip_tags( $str, $this->clean_allowed );
if(! $this->quotes ) $str = mysql_real_escape_string( $str );
return( $str );
}
/**
* Submission processing - either UPDATE or INSERT, depending on whether the id is set. Returns
* the ID of the UPDATE or the mysql_insert_id(). Hint: if you want to make a really fast form, make
* the name of the input elements the same name as the mysql fields, and just process the $_POST directly.
* i.e. if you have a mysql field named foo in a table bar, make the input element like so:
* <input type="text" name="foo"> and run this method as follows on submit:
* $db->process_submission( 'bar', $_POST );
*
*@param string $table the name of the MySQL table you're writing data to
*@param array $submission an array of fields and values you wish to insert into or update. should
* be in format fieldname=>fieldvalue
*@param array $id the name of the primary key field and it's value i.e. fooID=>7. If set, assumes
* an UPDATE, if not set assumes an INSERT
*@return integer
*/
public function process_submission( $table, $submission, $id=array( ) ) {
$fieldArray = array( );
$this->sql = "SELECT * FROM $table LIMIT 0";
if(! $this->query( ) ) $this->error( );
/**
* Get the array of fields from the table, and free the result
*/
for( $i = 0; $i < mysql_num_fields( $this->result ); ++$i ) {
$info = mysql_fetch_field( $this->result );
$fieldArray[$info->name] = 1;
}
/**
* Check the submission against this array of fields. If a
* field in submission is in the array of fields, they match up
* Using keys here because it's faster. Also, cleaning
* the values here.
*/
foreach( $submission as $key=>$val ) {
if( array_key_exists( $key, $fieldArray ) ) {
$val = $this->clean( $val );
$fields[$key] = "'$val'";
}
}
if (! count( $fields ) ) {
/** none of the fields match, so we can bail out **/
return( FALSE );
//die( var_dump( $fieldArray )."<hr />None of the submission fields match the table fields for table $table on database $db. The table fields are listed above" );
}
if ( empty( $id ) ) {
$this->sql = "INSERT INTO `$table` (" . implode(',', array_keys( $fields ) ). ") VALUES (". implode(',',array_values( $fields ) ). ")";
$this->query( );
if( mysql_affected_rows( $this->conn ) > 0 ) {
return( mysql_insert_id( $this->conn ) );
} else {
return( 0 );
}
} else {
$db_id = array_keys( $id );
$db_val = array_values( $id );
foreach ($fields as $key=>$val) {
$update[] = "$key = $val";
}
$this->sql = "UPDATE `$table` SET " . implode( ',', $update ) . " where $db_id[0] = $db_val[0]";
$this->query( );
return( $db_val[0] );
}
return( false );
}
/**
table - name of table
insert - the data to insert. should be field=>value
update - the data to update on duplicate key. should be field=>value
**/
public function insert_update( $table, $insert, $update ) {
$fieldArray = array( );
$this->sql = "SELECT * FROM $table LIMIT 0";
if(! $this->query( ) ) $this->error( );
/**
* Get the array of fields from the table, and free the result
*/
for( $i = 0; $i < mysql_num_fields( $this->result ); ++$i ) {
$info = mysql_fetch_field( $this->result );
$fieldArray[$info->name] = 1;
}
/**
* Check the submission against this array of fields. If a
* field in submission is in the array of fields, they match up
* Using keys here because it's faster. Also, cleaning
* the values here.
*/
$i_sql = array( );
$u_sql = array( );
foreach( $insert as $key=>$val ) {
if( array_key_exists( $key, $fieldArray ) ) {
$val = $this->clean( $val );
$i_sql[$key] = "'$val'";
}
}
foreach( $update as $key=>$val ) {
if( array_key_exists( $key, $fieldArray ) ) {
$val = $this->clean( $val );
$u_sql[] = "$key='$val'";
}
}
if(! sizeof( $i_sql ) || ! sizeof( $u_sql ) ) {
return( FALSE );
//die( var_dump( $fieldArray )."<hr />None of the submission fields match the table fields for table $table on database $db. The table fields are listed above" );
}
$this->sql = "INSERT INTO `$table` (" . implode(',', array_keys( $i_sql ) ). ")
VALUES (". implode(',',array_values( $i_sql) ). ")
ON DUPLICATE KEY UPDATE " . implode( ',', $u_sql );
$this->query( );
return( TRUE );
}
/** make an array from enum entries **/
public static function make_array_from_enum( $table, $field, $keyed = FALSE ) {
$db = MySQL::staticDB( );
$sql = "SHOW COLUMNS FROM $table LIKE '$field'";
$db->query( $sql );
$row = mysql_fetch_assoc( $db->result );
preg_match_all( "/'([^\']*)'/", $row['Type'], $matches );
if(! $keyed ) {
return( $matches[1] );
}
if(! $matches[1] ) {
return( array( ) );
}
foreach( $matches[1] as $val ) {
$arr[$val] = $val;
}
return( $arr );
}
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment