File Source for MySQLView.class.php

<?php
/**
* 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();
}
/**
* Loading the superior class on which this class depends.
*/
require_once( CLASSPATH."databases/ABSTView.class.php" );
/**
* Loading functions to handle arrays.
*/
include_once( CLASSPATH."core/Arrays.class.php" );
/**
*
*/
require_once CLASSPATH."databases/mysql/MySQL_constants.inc.php";
/**
* @package databases
* @subpackage mysql
*
* @version 0.2.12
* @author Daniel Plücken <daniel@debakel.net>
* @license http://www.gnu.org/copyleft/lesser.html
* GNU Lesser General Public License
* @copyright Copyright (C) 2005 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 MySQLView extends ABSTView
{
/**
* Stores the condition on every query with this view. It is helpful to define
* inner joins in the where clause.
*
* @var object $dbt_right
* @access public
*/
var $fixed_where = "";
/**
* Constructor
*
* @version 1.0
* @since 0.1.0
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $name*
*/
function MySQLView( $name = "" )
{ parent::__constructor( $name ); }
/**
* Sets the condition of first priority.
*
* @version 1.0
* @since 0.1.8
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $string
* @return string
*/
function setFixedWhere( $string )
{ $this->fixed_where = "( ".$string." ) AND ( "; }
/**
* Returns the correct where clause from given where concate the fixed where.
*
* @version 1.0
* @since 0.1.8
* @author Daniel Plücken <daniel@debakel.net>
* @access private
* @param string $string
* @return string
*/
function getCorrectWhere( $string )
{
if ( empty( $this->fixed_where ) )
return $string;
else
{
$out = $this->fixed_where;
if (
preg_match(
"!^(?:GROUP BY|ORDER BY|LIMIT|PROCEDURE|INTO OUTFILE)!i",
$string
)
)
$string = "1=1 ".$string;
$str_copy = strtoupper( $string );
$int_pos = array();
$int_pos[] = strpos( $str_copy, "GROUP BY" );
$int_pos[] = strpos( $str_copy, "ORDER BY" );
$int_pos[] = strpos( $str_copy, "LIMIT" );
$int_pos[] = strpos( $str_copy, "PROCEDURE" );
$int_pos[] = strpos( $str_copy, "INTO OUTFILE" );
// remove failed matching entries
for ( $i = count( $int_pos ) - 1; $i >= 0; $i-- )
if ( $int_pos[$i] === false )
array_splice( $int_pos, $i, 1 );
// no position found
if ( count( $int_pos ) <= 0 )
$out .= $string." )";
else
{
$closing_pos = min( $int_pos );
$out .= substr_replace( $string, " ) ", $closing_pos, 0 );
}
return $out;
}
}
/**
* Returns the order to join the database tables in the query.
*
* @version 1.0
* @since 0.1.8
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param array $field_arr In-out-parameter.
* @return string
*/
function getJoinOrder( &$field_arr )
{
// Fields of the left database table
$tmpFArr1 = array();
// Fields of the right database table
$tmpFArr2 = array();
// Return variable
$tmp_joins = "";
for ( $i = 0; $i < count( $this->join ); $i++ )
{
$tmpFArr1 = $this->join[$i]->dbt_left->getFieldnames(
!$this->join[$i]->dbt_left->overruled_fieldnames
);
for( $k = 0; $k < count( $tmpFArr1 ); $k++ )
{
$tmp_str = preg_replace(
"!^(.+?) +AS +[-_a-z0-9]+$!is",
"$1", $tmpFArr1[$k]
);
// Setting up the field list
Arrays::uniqueInsert(
$field_arr,
preg_replace(
"!(`[-_a-z0-9]+`)!is",
$this->join[$i]->alias_left.".$1",
$tmp_str
)." "
."AS ".$this->join[$i]->alias_left."_"
# extracting aliases
.preg_replace( "!^.*?([-_a-z0-9]+)`?$!is", "$1", $tmpFArr1[$k] )
);
}
$tmpFArr2 = $this->join[$i]->dbt_right->getFieldnames(
!$this->join[$i]->dbt_right->overruled_fieldnames
);
for( $k = 0; $k < count( $tmpFArr2 ); $k++ )
{
$tmp_str = preg_replace(
"!^(.+?) +AS +[-_a-z0-9]+$!is",
"$1", $tmpFArr2[$k]
);
// Setting up the field list
Arrays::uniqueInsert(
$field_arr,
preg_replace(
"!(`[-_a-z0-9]+`)!is",
$this->join[$i]->alias_right.".$1",
$tmp_str
)." "
."AS ".$this->join[$i]->alias_right."_"
# extracting aliases
.preg_replace( "!^.*?([-_a-z0-9]+)`?$!is", "$1", $tmpFArr2[$k] )
);
}
$tmp_joins .= $this->join[$i]->type
." JOIN `".$this->join[$i]->dbt_right->name."` "
."AS ".$this->join[$i]->alias_right." "
.(
strpos( $this->type, "NATURAL" ) === false
? "ON "
: ""
);
$k = 0;
do
{
if( empty( $this->join[$i]->relational_operator[$k] ) )
$this->join[$i]->relational_operator[$k] = "=";
$tmp_joins .= (
$k == 0
? ""
: (
strtoupper( $this->join[$i]->logical_operator[$k-1] ) == "AND"
? " AND "
: " OR "
)
).$this->join[$i]->alias_left."."
.$this->join[$i]->left_field[$k]." "
.$this->join[$i]->relational_operator[$k]." "
.(
strtoupper( $this->join[$i]->right_field[$k] ) != "NULL"
&& strtoupper( $this->join[$i]->right_field[$k] ) != "NOT NULL"
? $this->join[$i]->alias_right."."
: ""
)
.$this->join[$i]->right_field[$k]." \r\n";
$k++;
}
while( $k <= count( $this->join[$i]->logical_operator ) );
}
return $tmp_joins;
}
/**
* Returns the datasets as an array if the query could be execute.
* Notice that the fieldnames will be output in the special form that the
* given alias of a table and the responsed fieldname are separated by an
* underscore, for example "alias_fieldname".
*
* @version 1.7
* @since 0.1.1
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @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 boolean|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 = array();
$str_join = $this->getJoinOrder( $field_arr );
$field_arr = array_merge( $field_arr, $this->custom_field_arr );
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(
$field_arr,
$where_or_only_one,
$only_one,
$array_kind
);
// }
}
/**
* Returns the datasets as array with the values of the primary key as index.
*
* @version 1.0
* @since 0.2.0
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @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 = ""
)
{
$field_arr = array();
$str_join = $this->getJoinOrder( $field_arr );
if ( !empty( $where ) )
$sqlWhere = " WHERE ".$where;
$str_value = "";
if ( is_string( $value_fields ) )
$str_value = $value_fields;
else
if ( is_array( $value_fields ) )
foreach ( $value_fields as $str_value_field )
{
if ( !empty( $str_value ) )
$str_value .= ", ";
$str_value .= $str_value_field;
}
$sqlOrder = "SELECT ".$primary_key.", "
.$str_value." "
."FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." "
.$str_join
.$sqlWhere;
$result = $this->join[0]->dbt_left->parent->query( $sqlOrder, false, $this );
$primary_key = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $primary_key
);
while ( $resultArr = @mysql_fetch_array( $result ) )
if ( is_string( $value_fields ) )
{
$value_fields = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $value_fields
);
$datasets[ $resultArr[$primary_key] ] = $resultArr[$value_fields];
}
else
if ( is_array( $value_fields ) )
$datasets[ $resultArr[$primary_key] ] = $resultArr;
return $datasets;
}
/**
* Returns the datasets as array with the values of the primary key as index.
*
* @version 1.0
* @since 0.2.0
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @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 = ""
)
{
$field_arr = array();
$str_join = $this->getJoinOrder( $field_arr );
if ( !empty( $where ) )
$sqlWhere = "WHERE ".$where." ";
$str_value = "";
if ( is_string( $value_fields ) )
$str_value = $value_fields;
else
if ( is_array( $value_fields ) )
foreach ( $value_fields as $str_value_field )
{
if ( !empty( $str_value ) )
$str_value .= ", ";
$str_value .= $str_value_field;
}
$sqlOrder = "SELECT ".$primary_key.", "
.$str_value." "
."FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." "
.$str_join
.$sqlWhere
."ORDER BY `".$primary_key."` ASC";
$result = $this->join[0]->dbt_left->parent->query( $sqlOrder, false, $this );
$primary_key = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $primary_key
);
while ( $resultArr = @mysql_fetch_array( $result ) )
if ( is_string( $value_fields ) )
{
$value_fields = preg_replace(
"!^.+? +AS +([-_a-z0-9]+)$!is",
"$1", $value_fields
);
$datasets[ $resultArr[$primary_key] ][] = $resultArr[$value_fields];
}
else
if ( is_array( $value_fields ) )
$datasets[ $resultArr[$primary_key] ][] = $resultArr;
return $datasets;
}
/**
* Returns a array of values of a data field.
*
* @version 1.1
* @since 0.1.6
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $fieldname
* @param string $where
* @return array
*/
function getArrayOfDataField( $fieldname, $where = "1=1")
{
$field_arr = array ();
$str_join = $this->getJoinOrder( $field_arr );
$sqlOrder = " SELECT ".$fieldname." \r\n"
." FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." \r\n"
.$str_join
." WHERE ".$this->getCorrectWhere( $where );
$result = $this->join[0]->dbt_left->parent->query( $sqlOrder );
$tmp_arr = array();
while ( $tmp_row = @mysql_fetch_row( $result ) )
$tmp_arr[] = $tmp_row[0];
return $tmp_arr;
}
/**
* 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, $only_one );
*
* @version 1.2
* @since 0.1.42
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param array $field_arr Is a blind parameter whithout any funktion.
* @param string $where The where clause of the query.
* @param boolean $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
)
{
$blind_arr = array();
$str_join = $this->getJoinOrder( $blind_arr );
$sqlOrder = "SELECT ".implode( ", ", $field_arr )." "
."FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." "
.$str_join;
$sqlOrder .= " WHERE ".$this->getCorrectWhere( $where );
$result = $this->join[0]->dbt_left->parent->query( $sqlOrder );
$this->last_query = $sqlOrder;
$err = mysql_error();
$errno = mysql_errno();
if (
$result
&& $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
if ( $result )
{
$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;
}
else
{
if (
// looking for callback function of the database host object.
!empty( $this->join[0]->dbt_left->parent->parent->error_callback )
&& function_exists(
$this->join[0]->dbt_left->parent->parent->error_callback
)
&& in_array(
$errno,
array(
// no connection
2013,
// Access denied for user
1045,
// no such database
1049
)
)
)
{
$tmp_name = $this->join[0]->dbt_left
->parent->parent->error_callback;
$tmp_name( $errno, $err );
}
else
if (
!empty( $this->error_callback )
&& function_exists( $this->error_callback )
)
{
$tmp_name = $this->error_callback;
$tmp_name( $errno, $err );
}
else
if(
defined( "DEBUG" )
|| MYSQLDEBUGGING
)
{
echo "<pre>\r\n";
echo "An error occurs!\r\n";
echo "BACKTRACE DEBUG:\r\n";
$debugArr = debug_backtrace();
print_r( $debugArr[count($debugArr)-1] );
echo "</pre>\r\n";
echo " \r\n";
echo "MySQL says: \r\n";
echo mysql_error()." \r\n";
echo "Your query is: \r\n";
die( $sqlOrder." " );
}
}
}
/**
* Returns a single dataset as an array if the query could be execute.
* Notice that the fieldnames will be output in the special form that the
* given alias of a table and the responsed fieldname are separated by an
* underscore, for example "alias_fieldname".
*
* @version 1.2
* @since 0.1.0
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @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 ) )
return $this->getDatasetsWithFields(
$fields_or_where,
$where, true,
$array_kind
);
else
{
$field_arr = array();
$str_join = $this->getJoinOrder( $field_arr );
$field_arr = array_merge( $field_arr, $this->custom_field_arr );
return $this->getDatasetsWithFields(
$field_arr,
$fields_or_where,
true,
$array_kind
);
}
}
/**
* Returns a single value of a data field.
*
* @version 1.0
* @since 0.1.7
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $fieldname
* @param string $where
* @return mixed
*/
function getSingleDataFieldValue( $fieldname, $where = "1=1" )
{
$tmp_row = $this->getDatasetsWithFields(
array( $fieldname ), $where, true
);
return $tmp_row[0];
}
/**
* Returns the sum value of a field.
*
* @version 1.0
* @since 0.1.9
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $fieldname
* @param string $where The where-clause is optional.
* @param string $additional_arithmetic Additional calculations to the
* sum.
* @return integer
*/
function getSumOf( $fieldname, $where = "1=1", $additional_arithmetic = "" )
{
if ( empty( $where ) )
$where = "1=1";
$blind_arr = array();
$sqlOrder = "SELECT SUM( ".$fieldname." )"
.$additional_arithmetic." "
."FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." \r\n"
.$this->getJoinOrder( $blind_arr )
." WHERE ".$this->getCorrectWhere( $where );
$res = $this->join[0]->dbt_left->parent->query( $sqlOrder, false, $this );
$this->last_query = $sqlOrder;
$resArr = mysql_fetch_row( $res );
return $resArr[0];
}
/**
* Returns the count of datasets.
*
* @version 1.3
* @since 0.1.2
* @author Daniel Plücken <daniel@debakel.net>
* @access public
* @param string $where
* @param string $fieldname
* @return integer
*/
function getCount( $where = "1=1", $fieldname = "*" )
{
$blind_arr = array();
$sqlOrder = "SELECT count(".$fieldname.") "
."FROM `".$this->join[0]->dbt_left->name."` "
."AS ".$this->join[0]->alias_left." "
.$this->getJoinOrder( $blind_arr )
." WHERE ".$this->getCorrectWhere( $where );
$res = $this->join[0]->dbt_left->parent->query( $sqlOrder, false, $this );
$this->last_query = $sqlOrder;
$resArr = mysql_fetch_row( $res );
return $resArr[0];
}
} // End of class MySQLView
?>

Source for file MySQLView.class.php