|
|
|
|
|
|
| |
Class Query v1.2
-----------
Copyright (C) 2005 Aristidis Karidis, aris.karidis@bcs.org
----------------------------------------------------------
Automatically sort the results from a SELECT SQL query into an array.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
This program 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 General Public License for more details.
------------------------------------
http://www.gnu.org/copyleft/gpl.html
|
<?
/*
* PHP5
*
* The class provides functions that make SQL queries to a database and return the results.
* The constructor of the class will create an array containing the SQL query results.
* This array can be empty, associative or multidimensional, depending on the parameters
* set when the class was instantiated. The $use_key parameter specifies if the array will
* use custom keys. By default this is set to 1 (yes). The $key_column parameter specifies
* the column of the SQL result that contains those custom keys. The default is 1 (1st column).
* If the $use_key is set to 0 the value of the $key_column is irrelevant. This method was
* designed for SELECT queries in order to sort the results into an array but it will also work
* with other types of queries (returning true or false depending on query success or failure).
*/
class Query
{
private $query; # The SQL query.
private $result; # The raw SQL result.
private $array; # The array of SQL results.
/**
* The class constructor.
*
* @param string $query -- a SELECT SQL query
* @param object $link -- link to DB
* @param int $use_key -- use keys or not
* @param int $key_column -- column with keys
* @return Query
*/
function Query($query, $link, $use_key = 1, $key_column = 1)
{
###############################
### Validate the parameters ###
###############################
if ($use_key !== 0 && $use_key !== 1)
{
printf('Invalid parameter for $use_key. The parameter must be either 0 (for no) or 1 (for yes).');
exit();
}
if (!is_int($key_column) || $key_column <= 0)
{
printf('Invalid parameter for $key_column. This parameter must be a positive, non-zero integer.');
exit();
}
$this->query = $query;
############################################################################
### If a SELECT query sort results in an array, if not just do the query ###
############################################################################
if (substr(strtoupper($this->query), 0, 6) !== 'SELECT')
{
$this->result = mysqli_query($link, $this->query) or die(mysqli_error($link)); # Make the query
}
else
{
$this->result = mysqli_query($link, $this->query) or die(mysqli_error($link)); # Make the query
$rows = mysqli_num_rows($this->result); # The number of rows in the result
$columns = count(mysqli_fetch_row($this->result)); # The number of columns in the result
if ($columns !== 0)
{
if ($key_column > $columns)
{
printf('Invalid parameter for $key_column. This parameter must be less or equal to the number of columns in the query result.');
exit();
}
}
####################################
### Create the appropriate array ###
####################################
if ($rows > 0)
{
for ($i = 0; $i < $rows; $i++)
{
mysqli_data_seek($this->result, $i); # Move myslqi result pointer to the right place
switch ($columns)
{
case 0:
$this->array = array(); # Return an empty array (query returned no results)
break;
case 1:
$temp = mysqli_fetch_row($this->result);
if ($use_key == 0)
{
$this->array[] = $temp[0]; # Return an array without keys (i.e. array(value1, value2)
}
else
{
$this->array[$temp[0]] = $temp[0]; # Return an array with keys (i.e. array(value1 => value1, value1 => value2)
}
break;
case 2:
if ($use_key == 0)
{
$this->array[] = mysqli_fetch_row($this->result); # Return an array without keys (i.e. array(array(value1, value2), array(value3, value4))
}
else
{
$temp = mysqli_fetch_row($this->result);
$key_element = array_slice($temp, $key_column - 1); # Get the element containing the key
$value_element = array_slice($temp, $key_column, 1); # Get the element containing the value
$this->array[$key_element[0]] = $value_element[0]; # Return an array with keys (i.e. array(key1 => value1, key2 => value2)
}
break;
default:
if ($use_key == 0)
{
$this->array[] = mysqli_fetch_row($this->result); # Return an array without keys (i.e. array(array(value1, value2, value3), array(value4, value5, value6))
}
else
{
$temp = mysqli_fetch_row($this->result);
$key_element = array_slice($temp, $key_column - 1); # Get the element containing the key
$this->array[$key_element[0]] = $temp; # Return an array with keys (i.e. array(key1 => array(key1, value1, value2), array(key2, value3, value4))
}
break;
}
}
}
else
{
$this->array = array(); # Return an empty array (query returned no results)
}
mysqli_free_result($this->result);
}
}
/**
* Returns the SQL query results as an array.
*
* @return array
*/
function getMySQLiArray()
{
return $this->array;
}
}
?> | |
|
|
| Convert SQL from oracle,mysql,mssql,sqlite and odbc to SQL compatible
Categories : PHP, PHP Classes, Databases, MySQL, MS SQL Server | | | This simple function will take a few arguments and easily set a associative array for each column in a result from a MySQL query
Categories : Databases, PHP, MySQL, Arrays | | | This program allows you to upload an ODBC ressource - i.e. an MS-Access database to a MySQL server.
Categories : Databases, MySQL, Complete Programs, PHP, Databases | | | Simple Mini Poll class library (SimPoll)
Categories : PHP, PHP Classes, Databases, MySQL, Complete Programs | | | MySQL Handler
Categories : PHP, Databases, MySQL, Classes and Objects, PHP Classes | | | Powerful php/mysql Pagination for up to 6 URL Params
Categories : PHP, PHP Classes, Databases, MySQL, Navigation | | | Simple usersOnline class - keep track of how many users are online on your site
Categories : PHP, PHP Classes, Databases, MySQL | | | Function for retrieving MySQL enum values into a PHP array.
Categories : PHP, Databases, MySQL, Arrays | | | PHP Object Example of the Perl DBI with MySQL
Categories : PHP, PHP Classes, MySQL, Databases, Perl | | | PostGreSQL and MySQL 2 in 1 db Manager
Categories : PHP, PHP Classes, Databases, PostgreSQL, MySQL | | | Finds the median in an array of numbers - Can be used with a MySql database column read into an array
Categories : PHP, Arrays, Databases, MySQL | | | create a grid out of <INPUT TYPE=TEXT> then saving to a database. Uses
a 'multi-dimension array', but not really as the array is just one big array
with the index of "[$i][$j]". Have a look at the code and you'll see what I
mean.
Categories : PHP, MySQL, Arrays, Databases | | | Password reminder
Categories : PHP, PHP Classes, Databases, MySQL, Mail | | | Specify your connection settings and create a link to a MySQL database.
Categories : PHP, PHP Classes, Databases, MySQL, Beginner Guides | | | dynamic table columns
Categories : PHP, HTML and PHP, Arrays, Databases, MySQL | |
|
|
