File Source for MySQLDatabaseTable.class.php

<?php
/*
if( !extension_loaded( 'mysql' ) )
dl( 'mysql.so' );
*/
/**
* For including this file you have to define the constant "CLASSPATH".
* Because every include in the framework depends on the CLASSPATH definition.
* The CLASSPATH means the relative path to the folder that contains the
* framework GilliGan.
*
* @package databases
* @subpackage mysql
*/
if( !defined( "CLASSPATH" ) )
{
echo "<h3>You have to define the constant CLASSPATH!</h3>\r\n";
echo "Example: define( 'CLASSPATH', '../path/to/classes/' );\r\n";
exit();
}
/**
*
*/
include_once CLASSPATH."core/Arrays.class.php";
/**
* Loading the supirior class on which this class depends.
*/
require_once CLASSPATH."databases/ABSTDatabaseTable.class.php";
/**
*
*/
require_once CLASSPATH."databases/mysql/MySQL_constants.inc.php";
/**
* @package databases
* @subpackage mysql
*
* @version 0.4.2
* @author Daniel PlÃ¼cken <daniel@debakel.net>
* @license http://www.gnu.org/copyleft/lesser.html
* GNU Lesser General Public License
* @copyright Copyright (C) 2004 Daniel PlÃ¼cken <daniel@debakel.net>
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General
* Public License along with this library; if not, write to the
* Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307 USA
*/
class MySQLDatabaseTable extends ABSTDatabaseTable
{
/**
* Constructor
*
* @access public
* @version 1.0
* @since 0.1.0
*
* @param string $name
* @param object $parent
*
* @return MySQLDatabaseTable
*/
function MySQLDatabaseTable( $name, $parent = "" )
{ parent::__contructor( $name, "MySQL", $parent ); }
/**
* @todo This isn't programmed yet.
*
* @access public
* @version 1.0
* @since 0.1.0
*
* @return void
*/
function researchFields()
{
if( $this->probeConnection() )
{
//"SHOW COLUMNS FROM ".$this -> name;
}
}
/**
* Iports data from a csv file. You have to create a CSVFiles-Object first.
*
* @access public
* @version 1.0
* @since 0.1.0
*
* @param CSVFiles $csv_file_obj
* @param array $field_arr
*
* @return boolean
*/
function doCSVImport( $csv_file_obj, $field_arr = "" )
{
$sqlOrder = "LOAD DATA INFILE '".$csv_file_obj->path."/"
.$csv_file_obj->filename."' "
."INTO TABLE ".$this->name." "
."FIELDS TERMINATED BY '".$csv_file_obj->separator_char."' "
."ENCLOSED BY '".$csv_file_obj->enclosing_char."' "
."ESCAPED BY '".$csv_file_obj->escape_char."' "
."LINES TERMINATED BY '".$csv_file_obj->line_terminator_char."'"
.(
!empty( $field_arr )
&& is_array( $field_arr )
? "(`".implode( "`, `", $field_arr )."`)"
: ""
);
return $this->parent->query( $sqlOrder, false, $this );
}
/**
* Stores the Flags: (!)not_null <=> $this->field_can_be_null[FIELDINDEX],
* primary_key <=> $this->field_is_primary_member[FIELDINDEX], auto_increment
* <=> $this->field_has_auto_increment[FIELDINDEX]. This is necessary to get
* dump of the table data.
*
* @access public
* @version 1.0
* @since 0.2.9
*
* @return void
*/
function storeFieldFlags()
{
if(
!$this->field_flags_stored
&& $this->probeConnection()
)
{
$sqlOrder = "SELECT * "
."FROM `".$this->name."` "
."LIMIT 1";
$result = $this->parent->query( $sqlOrder, false, $this );
for ( $i = 0; $i < mysql_num_fields( $result ); $i++ )
{
$tmp_str = mysql_field_flags( $result, $i );
$this->field_can_be_null[$i] =
strpos( $tmp_str, "not_null" ) === false;
$this->field_is_primary_member[$i] =
strpos( $tmp_str, "primary_key" ) !== false;
$this->field_has_auto_increment[$i] =
strpos( $tmp_str, "auto_increment" ) !== false;
}
$this->field_flags_stored = true;
}
}
/**
* Returns all field members of the primary key of this database
* table.
*
* @access public
* @version 1.0
* @since 0.2.3
*
* @return array|false
*/
function &getPrimaryKey ()
{
if( is_array( $this->primary_key ) )
return $this->primary_key;
if( $this->probeConnection() )
{
$primary_key = Array();
$sqlOrder = "SHOW KEYS FROM `".$this->name."`";
$result = $this->parent->query( $sqlOrder, false, $this );
while ( $res_arr = @mysql_fetch_assoc( $result ) )
if ( $res_arr["Key_name"] == "PRIMARY" )
$primary_key[] = $res_arr["Column_name"];
$this->last_query = $sqlOrder;
$this->primary_key = $primary_key;
return $this->primary_key;
}
return false;
}
/**
* Returns the fieldname of the auto increment field if there is one.
*
* @access public
* @version 1.0
* @since 0.4.2
*
* @return string
*/
function getAutoIncrementField()
{
if ( is_string( $this->auto_increment_field ) )
{ return $this->auto_increment_field; }
if ( $this->probeConnection() )
{
$sqlOrder = "SHOW COLUMNS FROM `".$this->name."`";
$result = $this->parent->query( $sqlOrder, false, $this );
while ( $res_arr = @mysql_fetch_assoc( $result ) )
if ( $res_arr["Extra"] == "auto_increment" )
$this->auto_increment_field = $res_arr["Field"];
$this->last_query = $sqlOrder;
return $this->auto_increment_field;
}
}
/**
* Returns an SQL-string to create this table. You can give a new name for
* creating the table.
*
* @access public
* @version 1.4
* @since 0.1.0
*
* @param string $newTableName
* @param boolean $drop_order
* @param boolean $if_not_exists_order
*
* @return string
*/
function getCreateOrder(
$newTableName = "",
$drop_order = false,
$if_not_exists_order = false
)
{
if( $this->probeConnection() )
{
if ( $this->field_flags_stored )
$this->storeFieldFlags();
$sqlOrder = "SHOW CREATE TABLE `".$this->name."`";
$result = $this->parent->query( $sqlOrder, false, $this );
$row = @mysql_fetch_row( $result );
$this->last_query = $sqlOrder;
if ( $if_not_exists_order )
$begin = substr( $row[1], 0, 13 )."IF NOT EXISTS `";
else
$begin = substr( $row[1], 0, 14 );
if ( empty( $newTableName ) )
$row[1] = $begin.substr( $row[1], 14 );
else
$row[1] = $begin.$newTableName.substr(
$row[1],
14 + strlen( $this->name )
);
if ( $drop_order )
$row[1] = "DROP TABLE IF EXISTS `".$this->name."`;\n".$row[1];
return str_replace( "\n", "\r\n", $row[1] ).";\r\n";
}
}
/**
* Returns an SQL-string to fill this table. You can give a new name for
* the table to insert in.
*
* @access public
* @version 1.32
* @since 0.1.0
*
* @param string $newTableName
* @param boolean $full_inserts
* @param string $where
*
* @return string
*/
function getDataOrder(
$newTableName = "",
$full_inserts = false,
$where = ""
)
{
if ( !empty( $where ) )
$sqlWhere = " WHERE ".preg_replace( "!^ *WHERE *!i", "", $where );
$this->storeFieldnames();
if ( $this->probeConnection() )
{
if ( empty( $newTableName ) )
$newTableName = $this->name;
$sqlOrder = "SELECT * "
."FROM `".$this->name."`".$sqlWhere;
$result = $this->parent->query( $sqlOrder, false, $this );
$out = "";
while( $row = @mysql_fetch_row( $result ) )
{
for ( $i = 0; $i < count( $row ); $i++ )
$row[$i] = mysql_real_escape_string( $row[$i] );
if ( !in_array( null, $row ) )
$out .= "INSERT INTO `".$newTableName."` "
.(
$full_inserts
? "( `".implode( "`, `", $this->fieldname )."` ) "
: ""
)
."VALUES ( '".implode( "', '", $row )."' );\r\n";
else
{
$tmp = "";
for ( $i = 0; $i < count( $row ); $i++ )
if (
$row[$i] == null
&& $this->field_can_be_null[$i]
)
$tmp .= ", NULL";
else
$tmp .= ", '".$row[$i]."'";
$out .= "INSERT INTO `".$newTableName."` "
.(
$full_inserts
? "( `".implode( "`, `", $this->fieldname )."` ) "
: ""
)
."VALUES ( ".substr( $tmp, 2 )." );\r\n";
}
}
mysql_free_result( $result );
return $out;
}
}
/**
* Copies this table.
*
* @access public
* @version 1.1
* @since 0.1.0
*
* @param string $newTableName
* @param string $targetDatabase
*
* @return boolean
*/
function copyTable( $newTableName, $targetDatabase = "" )
{
$createSQL = $this->getCreateOrder( $newTableName );
if( $this->probeConnection() )
{
if( empty( $targetDatabase ) )
$targetDatabase = $this->parent->name;
$result = @mysql_db_query( $targetDatabase, $createSQL );
if( $result )
{
$sql = "INSERT INTO `".$targetDatabase."`.`".$newTableName."` "
."SELECT * "
."FROM `".$this->parent->name."`.`".$this->name."`";
$res = mysql_query( $sql, $this->parent->parent->resID );
$this->last_query = $sql;
}
}
return $res;
}
/**
* Emptys this table.
*
* @access public
* @version 1.0
* @since 0.1.0
*
* @return boolean
*/
function emptyTable()
{
if( $this -> probeConnection() )
$res = $this->parent->query(
"TRUNCATE TABLE `".$this->name."`",
false, $this
);
return $res;
}
/**
* Renames this table.
*
* @access public
* @version 1.1
* @since 0.1.0
*
* @param string $newTableName
*
* @return boolean
*/
function renameTable( $newTableName )
{
if( $this -> probeConnection() )
{
$sqlOrder = "ALTER TABLE `".$this->name."` RENAME `".$newTableName."`";
$res = $this->parent->query( $sqlOrder, false, $this );
$this->last_query = $sqlOrder;
}
return $res;
}
/**
* Returns an SQL-DUMP.
*
* @access public
* @version 1.2
* @since 0.1.0
*
* @param string $newTableName
* @param boolean $full_inserts
* @param boolean $drop_order
* @param boolean $if_not_exists_order
*
* @return string
*/
function getDump(
$newTableName = "",
$full_inserts = false,
$drop_order = false,
$if_not_exists_order = false
)
{
return $this->getCreateOrder(
$newTableName,
$drop_order,
$if_not_exists_order
)."\r\n\r\n"
.$this->getDataOrder( $newTableName, $full_inserts )."\r\n\r\n";
}
/**
* Returns an SQL-DUMP.
*
* @access public
* @version 1.0
* @since 0.3.8
*
* @param string $newTableName
* @param boolean $full_inserts
* @param boolean $drop_order
* @param boolean $if_not_exists_order
* @param string $where
* @param integer $flush_row_count
*
* @return string
*/
function echoBufferedDump(
$newTableName = "",
$full_inserts = false,
$drop_order = false,
$if_not_exists_order = false,
$where = "",
$flush_row_count = 1000
)
{
echo $this->getCreateOrder(
$newTableName,
$drop_order,
$if_not_exists_order
)."\r\n\r\n";
ob_flush();
flush();
$tmp_cnt = $this->getCount( $sqlWhere );
if ( empty( $where ) )
$where = "1=1";
for ( $i = 0; $i < $tmp_cnt; $i += $flush_row_count )
{
$tmp_where = " LIMIT ".$i.", ".$flush_row_count;
echo $this->getDataOrder(
$newTableName,
$full_inserts,
$where.$tmp_where
);
ob_flush();
flush();
}
echo "\r\n\r\n";
}
/**
* Returns the minimum value from a field.
*
* @access public
* @version 1.0
* @since 0.2.7
*
* @param string $fieldname
* @param string $where The where-clause is optional.
*
* @return integer
*/
function getMinOf( $fieldname, $where = "" )
{
if( $this->probeConnection() )
{
$res = $this->parent->query(
"SELECT MIN( ".$fieldname." ) \r\n"
." FROM `".$this->name."`\r\n"
.(
!empty( $where )
? " WHERE ".$where
: ""
),
false, $this
);
$resArr = @mysql_fetch_row( $res );
return $resArr[0];
}
}
/**
* Returns the maximum value from a field.
*
* @access public
* @version 1.1
* @since 0.1.0
*
* @param string $fieldname
* @param string $where The where-clause is optional.
*
* @return integer
*/
function getMaxOf( $fieldname, $where = "" )
{
if( $this->probeConnection() )
{
$res = $this->parent->query(
"SELECT MAX( ".$fieldname." ) \r\n"
." FROM `".$this->name."`\r\n"
.(
!empty( $where )
? " WHERE ".$where
: ""
),
false, $this
);
$resArr = @mysql_fetch_row( $res );
return $resArr[0];
}
}
/**
* Returns the sum value of a field.
*
* @access public
* @version 1.0
* @since 0.3.3
*
* @param string $fieldname
* @param string $where The where-clause is optional.
*
* @return integer
*/
function getSumOf( $fieldname, $where = "" )
{
if( $this->probeConnection() )
{
$res = $this->parent->query(
"SELECT SUM( ".$fieldname." ) \r\n"
." FROM `".$this->name."`\r\n"
.(
!empty( $where )
? " WHERE ".$where
: ""
),
false, $this
);
$resArr = @mysql_fetch_row( $res );
return $resArr[0];
}
}
/**
* Returns the count of the fields in the where clause.
*
* @access public
* @version 2.5
* @since 0.1.0
*
* @param string $where
*
* @return integer
*/
function getCount( $where = "" )
{
if( $this->probeConnection() )
{
$res = $this->parent->query(
"SELECT COUNT( * ) \r\n"
." FROM `".$this->name."`\r\n"
.(
!empty( $where )
? " WHERE ".$where
: ""
),
false, $this
);
$resArr = @mysql_fetch_row( $res );
return $resArr[0];
}
}
/**
* Returns the datasets as an two dimensional array if the query could be
* execute. The first dimension carries the datasets. The second dimension
* carries the fields's values of the result list.
*
* Usage:
* getDatasetsWithFields( $fieldnamesArr, $where );
* Or:
* getDatasetsWithFields( $where, $array_kind );
* Or:
* getDatasetsWithFields( $where );
*
* @access public
* @version 1.7
* @since 0.1.5
*
* @param array $field_arr The fields that should be in the result list.
* @param string $where The where clause of the query.
* @param string $only_one
* @param string $array_kind The kind how the tuples should be output.
* MYSQL_OUTPUT_ASSOC - to get an associative
* array, with the fieldnames as indices.
* MYSQL_OUTPUT_NUM - to get an array, with
* numbers as indices in order of the
* $fieldnamesArr or of the underlying table.
* MYSQL_OUTPUT_DEFAULT - to get a mix of both
* output kinds.
* @return array
*/
function getDatasetsWithFields(
$field_arr = array(),
$where = "1=1",
$only_one = false,
$array_kind = MYSQL_OUTPUT_DEFAULT
)
{
$param = func_get_args();
if (
is_array( $field_arr )
&& !empty( $where )
)
$sqlWhere = " WHERE ".preg_replace( "!^ *WHERE *!i", "", $where );
else
if (
is_string( $field_arr )
&& !empty( $field_arr )
)
{
$sqlWhere = " WHERE ".preg_replace( "!^ *WHERE *!i", "", $field_arr );
if (
$where === MYSQL_OUTPUT_ASSOC
|| $where === MYSQL_OUTPUT_NUM
|| $where === MYSQL_OUTPUT_DEFAULT
)
$array_kind = $where;
}
if ( !is_array( $field_arr ) )
$sqlOrder = "SELECT * "
."FROM `".$this->name."`".$sqlWhere;
else
{
$sqlResultFields = implode( ", ", $field_arr );
$sqlOrder = "SELECT ".$sqlResultFields
." FROM `".$this->name."`".$sqlWhere;
}
if ( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
if ( $only_one )
{
if ( $array_kind == MYSQL_OUTPUT_ASSOC ) // MYSQL_OUTPUT_ASSOC
return @mysql_fetch_assoc( $result );
elseif ( $array_kind == MYSQL_OUTPUT_NUM ) // MYSQL_OUTPUT_NUM
return @mysql_fetch_row( $result );
else // MYSQL_OUTPUT_DEFAULT
return @mysql_fetch_array( $result );
}
else
{
$i = 0;
$dataset_arr = array();
if ( $array_kind == MYSQL_OUTPUT_ASSOC ) // MYSQL_OUTPUT_ASSOC
while ( $result_arr = @mysql_fetch_assoc( $result ) )
{
$dataset_arr[$i] = array();
$dataset_arr[$i++] = $result_arr;
}
elseif ( $array_kind == MYSQL_OUTPUT_NUM ) // MYSQL_OUTPUT_NUM
while ( $result_arr = @mysql_fetch_row( $result ) )
{
$dataset_arr[$i] = array();
$dataset_arr[$i++] = $result_arr;
}
else // MYSQL_OUTPUT_DEFAULT
while ( $result_arr = @mysql_fetch_array( $result ) )
{
$dataset_arr[$i] = array();
$dataset_arr[$i++] = $result_arr;
}
return $dataset_arr;
}
}
/**
* This method is an alias for the method "getDatasetsWithFields".
* Returns the datasets as an two dimensional array if the query could be
* execute. The first dimension carries the datasets. The second dimension
* carries the fields's values of the result list.
*
* Usage:
* getDatasets( $fieldnamesArr, $where, $array_kind );
* Or:
* getDatasets( $where );
*
* @access public
* @version 1.4
* @since 0.1.5
*
* @param string $fields_or_where The fields to extract from relation.
* @param string $where_or_only_one The where clause of the query.
* @param string $array_kind The kind how the tuples should be
* output.
* MYSQL_OUTPUT_ASSOC - to get an
* associative array, with the fieldnames
* as indices.
* MYSQL_OUTPUT_NUM - to get an array,
* with numbers as indices in order of the
* $fieldnamesArr or of the underlying
* table.
* MYSQL_OUTPUT_DEFAULT - to get a mix of
* both output kinds.
* @param boolean $only_one Whether only one Dataset should be
* output.
* @or
* @param string $fields_or_where The where clause of the query.
* @param boolean $where_or_only_one Whether only one Dataset should be
* output.
* @param string $array_kind The kind how the tuples should be
* output.
* MYSQL_OUTPUT_ASSOC - to get an
* associative array, with the fieldnames
* as indices.
* MYSQL_OUTPUT_NUM - to get an array,
* with numbers as indices in order of the
* $fieldnamesArr or of the underlying
* table.
* MYSQL_OUTPUT_DEFAULT - to get a mix of
* both output kinds.
* @return array
*/
function getDatasets(
$fields_or_where = "1=1",
$where_or_only_one = false,
$array_kind = MYSQL_OUTPUT_DEFAULT,
$only_one = false
)
{
if (
is_string( $fields_or_where )
&& is_bool( $where_or_only_one )
)
{
$field_arr = $this->getFieldnames();
return $this->getDatasetsWithFields(
$field_arr,
$fields_or_where,
$where_or_only_one,
$array_kind
);
}
/*
else
{
*/
if ( !is_array( $fields_or_where ) )
$field_arr = explode( ",", $fields_or_where );
else
$field_arr =& $fields_or_where;
return $this->getDatasetsWithFields(
$fields_or_where,
$where_or_only_one,
$only_one,
$array_kind
);
// }
}
/**
* Returns the datasets as array with the values of the primary key as index.
*
* @access public
* @version 1.21
* @since 0.1.8
*
* @param array $primary_key The fields that should be in the result list.
* @param array $value_fields The fields with the values for the content.
* @param string $where The where clause of the query.
*
* @return array
*/
function getDatasetsWithPrimaryToValue(
$primary_key,
$value_fields,
$where = null
)
{
if ( !empty( $where ) )
{ $sqlWhere = " WHERE ".$where; }
$str_value = "";
if ( is_string( $value_fields ) )
{ $str_value = $value_fields; }
else
if ( is_array( $value_fields ) )
{ $str_value = implode( ", ", $value_fields ); }
$sqlOrder = "SELECT `".$primary_key."`, "
.$str_value." "
."FROM `".$this->name."`".$sqlWhere;
if ( $this->probeConnection() )
{ $result = $this->parent->query( $sqlOrder, false, $this ); }
// strip aliases from primary field list
$primary_key = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $primary_key
);
while ( $resultArr = @mysql_fetch_array( $result ) )
{
if ( is_array( $value_fields ) )
{ $datasets[ $resultArr[$primary_key] ] = $resultArr; }
else
if ( is_string( $value_fields ) )
{
// strip aliases from field list
$value_fields = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $value_fields
);
$datasets[ $resultArr[$primary_key] ] = $resultArr[$value_fields];
}
}
return $datasets;
}
/**
* Returns the datasets as array with the values of the primary key as index.
*
* @access public
* @version 1.11
* @since 0.1.8
*
* @param array $primary_key The fields that should be in the result list.
* @param array $value_fields The fields with the values for the content.
* @param string $where The where clause of the query.
*
* @return array
*/
function getDatasetsWithPrimaryToMultiple(
$primary_key,
$value_fields,
$where = null
)
{
if ( !empty( $where ) )
{ $sqlWhere = "WHERE ".$where." "; }
$str_value = "";
if ( is_string( $value_fields ) )
{ $str_value = $value_fields; }
else
if ( is_array( $value_fields ) )
{ $str_value = implode( ", ", $value_fields ); }
$sqlOrder = "SELECT `".$primary_key."`, "
.$str_value." "
."FROM `".$this->name."` "
.$sqlWhere
."ORDER BY `".$primary_key."` ASC";
if ( $this->probeConnection() )
{ $result = $this->parent->query( $sqlOrder, false, $this ); }
// strip aliases from primary field list
$primary_key = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $primary_key
);
while ( $resultArr = @mysql_fetch_array( $result ) )
{
if ( is_array( $value_fields ) )
{ $datasets[ $resultArr[$primary_key] ][] = $resultArr; }
else
if ( is_string( $value_fields ) )
{
// strip aliases from field list
$value_fields = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $value_fields
);
$datasets[ $resultArr[$primary_key] ][] = $resultArr[$value_fields];
}
}
return $datasets;
}
/**
* Returns a single dataset as an array if the query could be execute.
*
* @access public
* @version 1.3
* @since 0.1.2
*
* @param string $fields_or_where
* @param string $where
* @param string $array_kind The kind how the tuples should be output.
* MYSQL_OUTPUT_ASSOC - to get an associative
* array, with the fieldnames as indices.
* MYSQL_OUTPUT_NUM - to get an array, with
* numbers as indices in order of the
* $fieldnamesArr or of the underlying table.
* MYSQL_OUTPUT_DEFAULT - to get a mix of both
* output kinds.
* @return array
*/
function getSingleDataset(
$fields_or_where,
$where = "",
$array_kind = MYSQL_OUTPUT_DEFAULT
)
{
if( is_array( $fields_or_where ) )
$tmp = implode( ", ", $fields_or_where );
else
{
$where =& $fields_or_where;
$tmp = "*";
}
$sqlWhere = " WHERE ".$where;
$sqlOrder = "SELECT ".$tmp." \r\n"
." FROM `".$this->name."`\r\n".$sqlWhere;
if ( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
if ( $array_kind == MYSQL_OUTPUT_ASSOC ) // MYSQL_OUTPUT_ASSOC
return @mysql_fetch_assoc( $result );
else
if ( $array_kind == MYSQL_OUTPUT_NUM ) // MYSQL_OUTPUT_NUM
return @mysql_fetch_row( $result );
else // MYSQL_OUTPUT_DEFAULT
return @mysql_fetch_array( $result );
}
/**
* Returns a single value of a data field.
*
* @access public
* @version 1.0
* @since 0.2.8
*
* @param string $fieldname
* @param string $where
*
* @return mixed
*/
function getSingleDataFieldValue( $fieldname, $where )
{
$sqlWhere = " WHERE ".$where;
$sqlOrder = "SELECT ".$fieldname." \r\n"
." FROM `".$this->name."`\r\n".$sqlWhere;
if( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
$tmp_row = @mysql_fetch_row( $result );
return $tmp_row[0];
}
/**
* Returns a array of values of a data field.
*
* @access public
* @version 1.0
* @since 0.3.1
*
* @param string $fieldname
* @param string $where
*
* @return mixed
*/
function getArrayOfDataField( $fieldname, $where )
{
$sqlWhere = " WHERE ".$where;
$sqlOrder = "SELECT ".$fieldname." \r\n"
." FROM `".$this->name."`\r\n".$sqlWhere;
if( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
$tmp_arr = array();
while ( $tmp_row = @mysql_fetch_row( $result ) )
$tmp_arr[] = $tmp_row[0];
return $tmp_arr;
}
/**
* Puts a single dataset into request-variables. The names of the request-vars
* accord to the fieldnames of the Databasetable. You can fill variables of
* the types: $_REQUEST, $_GET and $_POST. You only have to list them in an
* array passed as third parameter in this function (see the description for
* third parameter).
*
* @access public
* @version 1.0
* @since 0.4.1
*
* @param string|array$where_or_field_arr This has to be the where-clause
* of the query to the underlying
* database table. If you only want
* to fetch and put a few fields of
* the table, you schould pass here
* an array of fieldnames and pass
* the where-clause to the with
* parameter.
* @param string $where This has to be the where-clause
* of the query to the underlying
* database table, if the first
* parameter should carry the fields
* that are only to fetch.
* @param array $request_type_arr Valid Values are:
* array( "GET", "POST", "REQUEST" )
* You can leave out the array
* values you don't need that the
* field values put in according
* request-variables.
*
* @return void
*/
function putSingleDatasetIntoRequestVars (
$where_or_field_arr,
$where = "",
$request_type_arr = array( "GET", "POST", "REQUEST" )
)
{
if ( is_array( $where_or_field_arr ) )
{ $field_arr =& $where_or_field_arr; }
else
{
$where =& $where_or_field_arr;
$field_arr = $this->getRealFieldnames();
}
$res_arr = $this->getSingleDataset( $where );
# Datatransfer
if ( $res_arr && $field_arr )
{
if ( in_array( "GET", $request_type_arr ) )
foreach ( $field_arr as $fieldname )
{ $_GET[ $fieldname ] = $res_arr[ $fieldname ]; }
if ( in_array( "POST", $request_type_arr ) )
foreach ( $field_arr as $fieldname )
{ $_POST[ $fieldname ] = $res_arr[ $fieldname ]; }
if ( in_array( "REQUEST", $request_type_arr ) )
foreach ( $field_arr as $fieldname )
{ $_REQUEST[ $fieldname ] = $res_arr[ $fieldname ]; }
}
}
/**
* Puts a single dataset into "post"-variables. The names of the "Post"-vars
* accord to the fieldnames of the Databasetable.
*
* @access public
* @version 1.21
* @since 0.1.5
*
* @see MySQLDatabaseTable::putSingleDatasetIntoRequestVars()
*
* @param string|array$where_or_field_arr
* @param string $where
*
* @return void
*/
function putSingleDatasetIntoPostVars( $where_or_field_arr, $where = "" )
{ $this->putSingleDatasetIntoRequestVars( $where_or_field_arr, $where, array( "POST" ) ); }
/**
* Puts a single dataset into "get"-variables. The names of the "get"-vars
* accord to the fieldnames of the Databasetable.
*
* @access public
* @version 1.21
* @since 0.1.5
*
* @see MySQLDatabaseTable::putSingleDatasetIntoRequestVars()
*
* @param string|array$where_or_field_arr
* @param string $where
*
* @return void
*/
function putSingleDatasetIntoGetVars( $where_or_field_arr, $where = "" )
{ $this->putSingleDatasetIntoRequestVars( $where_or_field_arr, $where, array( "GET" ) ); }
/**
* Deletes the specified dataset.
*
* @access public
* @version 1.0
* @since 0.2.1
*
* @param string $where
*
* @return boolean
*/
function delete( $where )
{
if( empty( $where ) )
die(
"It is not allowed to leave the where-clause blank.\r\n "
."Use the method \"emptyTable\" to remove all datasets.\r\n "
."If you want to keep the actual auto-increment-value, then try\r\n"
."to call this function like delete( \"1=1\" );\r\n "
);
else
{
$sqlOrder = "DELETE FROM `".$this->name."`\r\n"
." WHERE ".$where;
if( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
return $result;
}
}
/**
* Inserts the specified dataset. Note that this function will create a global
* variable $GLOBALS["insert_id"] which contains the result of
* mysql_insert_id();
*
* @access public
* @version 1.66
* @since 0.1.1
*
* @param array $fieldArr The array that carries the fieldnames.
* @param array $valueArr The array that carries the values.
*
* @return boolean
*/
function insert( $fieldArr, $valueArr )
{
$sqlSetFields = "";
$fieldname_arr = $this->getRealFieldnames();
$tmp_fields_key_arr = array_keys( $fieldArr );
$tmp_value_key_arr = array_keys( $valueArr );
for ( $i = 0; $i < count( $fieldArr ); $i++ )
if ( in_array( $fieldArr[ $tmp_fields_key_arr[$i] ], $fieldname_arr ) )
$sqlSetFields .= "`".$fieldArr[ $tmp_fields_key_arr[$i] ]."` = "
."'".mysql_real_escape_string(
$valueArr[ $tmp_value_key_arr[$i] ]
)."', ";
$sqlSetFields = substr( $sqlSetFields, 0, strlen( $sqlSetFields ) - 2 );
$sqlOrder = "INSERT INTO `".$this->name."` "
."SET ".$sqlSetFields;
if ( $this->probeConnection() )
{
$result = $this->parent->query( $sqlOrder, false, $this );
$this->insert_id = $GLOBALS["insert_id"] = @mysql_insert_id();
}
return $result;
}
/**
* Updates the specified dataset.
*
* @access public
* @version 1.46
* @since 0.1.1
*
* @param array $fieldArr The array that carries the fieldnames.
* @param array $valueArr The array that carries the values.
* @param string $where The wheredefinition of the query.
*
* @return boolean
*/
function update( $fieldArr, $valueArr, $where = "1=1" )
{
$sqlSetFields = "";
$fieldname_arr = $this->getRealFieldnames();
$tmp_fields_key_arr = array_keys( $fieldArr );
$tmp_value_key_arr = array_keys( $valueArr );
for ( $i = 0; $i < count( $fieldArr ); $i++ )
if ( in_array( $fieldArr[ $tmp_fields_key_arr[$i] ], $fieldname_arr ) )
$sqlSetFields .= "`".$fieldArr[ $tmp_fields_key_arr[$i] ]."` = "
."'".mysql_real_escape_string(
$valueArr[ $tmp_value_key_arr[$i] ]
)."', ";
$sqlSetFields = substr( $sqlSetFields, 0, strlen( $sqlSetFields ) - 2 );
$sqlOrder = "UPDATE `".$this->name."` "
."SET ".$sqlSetFields." "
."WHERE ".$where;
if( $this->probeConnection() )
$result = $this->parent->query( $sqlOrder, false, $this );
return $result;
}
/**
* Replaces the specified dataset.
*
* @access public
* @version 1.16
* @since 0.2.0
*
* @param array $fieldArr The array that carries the fieldnames.
* @param array $valueArr The array that carries the values.
*
* @return boolean
*/
function replace( $fieldArr, $valueArr )
{
$sqlSetFields = "";
$fieldname_arr = $this->getRealFieldnames();
$tmp_fields_key_arr = array_keys( $fieldArr );
$tmp_value_key_arr = array_keys( $valueArr );
for ( $i = 0; $i < count( $fieldArr ); $i++ )
if ( in_array( $fieldArr[ $tmp_fields_key_arr[$i] ], $fieldname_arr ) )
$sqlSetFields .= "`".$fieldArr[ $tmp_fields_key_arr[$i] ]."` = "
."'".mysql_real_escape_string(
$valueArr[ $tmp_value_key_arr[$i] ]
)."', ";
$sqlSetFields = substr( $sqlSetFields, 0, strlen( $sqlSetFields ) - 2 );
$sqlOrder = "REPLACE INTO `".$this->name."` "
."SET ".$sqlSetFields;
if ( $this->probeConnection() )
{
$result = $this->parent->query( $sqlOrder, false, $this );
$this->insert_id = $GLOBALS["insert_id"] = @mysql_insert_id();
}
return $result;
}
/**
* Swaps values of a specified field in two tupel.
*
* @access public
* @version 1.0
* @since 0.3.2
*
* @param string $fieldname
* @param string $where_a
* @param string $where_b
*
* @return boolean
*/
function swapFieldValuesOf( $fieldname, $where_a, $where_b )
{
$help_a = $this->getSingleDataFieldValue( $fieldname, $where_a );
$help_b = $this->getSingleDataFieldValue( $fieldname, $where_b );
// because of unique conflicts
$this->update( array( $fieldname ), array( '' ), $where_a );
$this->update( array( $fieldname ), array( $help_a ), $where_b );
$this->update( array( $fieldname ), array( $help_b ), $where_a );
}
/**
* Stores the names of fields in this object.
*
* @access public
* @version 1.1
* @since 0.1.0
*
* @return boolean
*/
function storeFieldnames()
{
if( !$this->field_names_stored )
{
if(
!empty( $this->parent->name )
&& !empty( $this->name )
)
{
if( $this->probeConnection() )
$result = $this->parent->query(
"SHOW COLUMNS FROM `".$this->name."`",
false, $this
);
if( $result )
{
while( $arr = @mysql_fetch_assoc( $result ) )
$this->fieldname[] = $arr["Field"];
$this->field_names_stored = true;
$this->overruled_fieldnames = false;
return $result;
}
return $result;
}
}
}
/**
* Returns the table's fieldnames independence of overruled fieldnames. If you
* want you can get the fieldnames enclosed in backticks.
*
* @access public
* @version 1.1
* @since 0.1.3
*
* @param boolean $backticks
*
* @return array
*/
function getRealFieldnames( $backticks = false )
{
$fieldname_arr = array();
if(
!empty( $this->parent->name )
&& !empty( $this->name )
)
{
if( $this->probeConnection() )
$result = $this->parent->query(
"SHOW COLUMNS FROM `".$this->name."`",
false, $this
);
if( $result )
while( $arr = @mysql_fetch_assoc( $result ) )
$fieldname_arr[] = $arr["Field"];
}
if( !$backticks )
return $fieldname_arr;
else
{
$tmp_str = "`".implode( "`[[SEPARATOR]]`", $fieldname_arr )."`";
return explode( "[[SEPARATOR]]", $fieldname_arr );
}
}
/**
* Returns the table's fieldnames. If you want you can get the fieldnames
* enclosed in backticks.
*
* @access public
* @version 1.3
* @since 0.1.1
*
* @param boolean $backticks
*
* @return array
*/
function getFieldnames( $backticks = false )
{
if( Arrays::is_empty( $this->fieldname ) )
$this->storeFieldnames();
if( !Arrays::is_empty( $this->fieldname ) )
if( !$backticks )
return $this->fieldname;
else
{
$tmp_str = "`".implode( "`[[SEPARATOR]]`", $this->fieldname )."`";
return explode( "[[SEPARATOR]]", $tmp_str );
}
return array();
}
/**
* Overrules the table's fieldnames. The given array should contain the comma
* separated list of the query between the keywords SELECT and FROM.
* Each element of the array should represent one element of the list.
*
* @access public
* @version 1.0
* @since 0.2.3
*
* @param array $array
*
* @return void
*/
function overruleFieldnames( $array )
{
$this->fieldname = $array;
$this->field_names_stored = true;
$this->overruled_fieldnames = true;
}
/**
* Unsets the overrules of fieldnames.
*
* @access public
* @version 1.0
* @since 0.2.7
*
* @return void
*/
function unsetOverruledFieldnames()
{
unset( $this->fieldname );
$this->fieldname = null;
$this->field_names_stored = false;
$this->overruled_fieldnames = false;
}
/**
* Probes whether there is a connection to the databasehost, if not it will
* attempt to connect to the host.
*
* @access public
* @version 1.2
* @since 0.1.0
*
* @return boolean
*/
function probeConnection()
{
if( function_exists( "debug_backtrace" ) )
$debugArr = debug_backtrace();
if(
(
!function_exists( "is_a" )
&& get_class( $this->parent ) != "mysqldatabase"
|| function_exists( "is_a" )
&& !is_a( $this->parent, "MySQLDatabase" )
)
&& (
defined( "DEBUG" )
|| SCRIPTDEBUGGING
)
)
{
echo "There is no valid parent-object for the object of the "
."databasetable named \"".$this -> name."\". \r\n"
."Please add this table-object to a database-object before you "
."make a call such as on line "
.$debugArr[count($debugArr)-1]["line"]." in file \""
.$debugArr[count($debugArr)-1]["file"]."\"."
." \r\n";
echo "BACKTRACE DEBUG: \r\n";
echo "<pre>\r\n";
print_r( $debugArr );
echo "</pre>\r\n";
exit();
}
else
if(
(
!function_exists( "is_a" )
&& get_class( $this->parent->parent ) != "mysqldatabasehost"
|| function_exists( "is_a" )
&& !is_a( $this->parent->parent, "MySQLDatabaseHost" )
)
&& (
defined( "DEBUG" )
|| SCRIPTDEBUGGING
)
)
{
echo "The parent object of this databasetable-object has no parent."
." \r\n"
."Please add the object of the database named "
."\"".$this->parent->name."\" and which contains the object of "
."the table \"".$this->name."\" to a databasehost-object before "
."you make a call such as on line "
.$debugArr[count($debugArr)-1]["line"]." in file \""
.$debugArr[count($debugArr)-1]["file"]."\"."
." \r\n";
echo "BACKTRACE DEBUG \r\n";
echo "<pre>\r\n";
print_r( $debugArr );
echo "</pre>\r\n";
exit();
}
else
if( !$this->parent->parent->is_connected )
$this->parent->parent->connect();
if( $this->parent->parent->is_connected )
return true;
else
return false;
}
} // End of class MySQLDatabaseTable
?>

Source for file MySQLDatabaseTable.class.php