This class can be used to split MySQL query results displayed in pages retrieved using AJAX.
It takes a MySQL query and computes the total number of rows it returns to generate HTML links in order to browse different pages of the query results.
Each page displays up to a given limit number of results.
The generated links trigger the execution of JavaScript code that retrieves the contents of the respective page using AJAX requests.
The class can also execute queries to perform searches for information in given columns.
It has a jquery plugin that handles requests and responses just for flexibility
This is a simple class that can parse text files with a list of MySQL query statements to be executed.
The class splits the file lines, skips comment lines and gathers all consecutive lines until the one ends with semi-colon to get the complete SQL statement.
Each statement is executed as MySQL query to the current default MySQL database connection.
This class can be used to dynamically build SQL database queries.
It provides functions for defining query parameters such as the query type, tables, columns, the clauses where, order by, group by and having, limit row range.
The class builds the query SQL statement according the query type and returns the SQL string.
The documentation is available in Engligh and Spanish.
Export SQL query results to Excel files with ADODB
This class can be used to generate Excel files in the XLS format from database query results.
This is an altered version of of the SQL 2 Excel class, originally provided by Dzaiacuck, that uses ADODB API instead of MySQL. It extends the GeraExcel class to generate Excel files from the results of execution of an SQL query.
It takes an SQL query string and the array of titles of columns of the Excel spreadsheet file to be generated.
This package can be used to parse and compose SQL queries programatically.
It can take an SQL query and parse it to extract the different parts of the query like the type of command, fields, tables, conditions, etc..
It can also be used to do the opposite, i.e. compose SQL queries from values that define each part of the query.
Features:
I. Parser
- insert
- replace
- update
- delete
- select
- union
- subselect
- recognizes flow control function (IF, CASE - WHEN - THEN)
- recognition of many sql functions
II. Composer (Compiler)
- insert
- replace
- update
- delete
- select
- union
III. Wrapper SQL
- object oriented writing of SQL statements from the scratch
#################################################
a more advanced example:
#################################################
$sql = 'SELECT
countrylanguage.CountryCode,
country.Name,
country.Continent,
country.Region,
country.SurfaceArea,
city.District,
country.IndepYear,
country.Population,
city.CountryCode,
city.Name,
city.Population,
countrylanguage.Language AS lang,
countrylanguage.IsOfficial,
countrylanguage.Percentage,
country.GovernmentForm,
country.LocalName,
country.GNPOld,
country.GNP,
country.LifeExpectancy
FROM
country co
LEFT JOIN city ct ON co.Code = ct.CountryCode
LEFT JOIN countrylanguage cl ON cl.CountryCode = ct.CountryCode
WHERE
(co.Continent='Asia' AND cl.Language='Pashto')
GROUP BY
co.Name
HAVING
ct.CountryCode = 'AFG'
LIMIT 100
':
// using wrapper class
$sqlDef = new Sql();
$sqlDef->parse($sql);
echo $sqlout;
#################################################
SELECT countrylanguage.CountryCode, country.Name, country.Continent, country.Region, country.SurfaceArea, city.District, country.IndepYear, country.Population, city.CountryCode, city.Name, city.Population, countrylanguage.Language AS lang, countrylanguage.IsOfficial, countrylanguage.Percentage, country.GovernmentForm, country.LocalName, country.GNPOld, country.GNP, country.LifeExpectancy
FROM country AS co
LEFT JOIN city AS ct ON co.Code = ct.CountryCode
LEFT JOIN countrylanguage AS cl ON cl.CountryCode = ct.CountryCode
LEFT JOIN employees ON employees.employeeID = 1
WHERE (co.Continent = 'Asia' and cl.Language = 'Pashto')
GROUP BY co.Name
HAVING ct.CountryCode = 'AFG'
LIMIT 0,100
#################################################
IV. Hint
Sql_Compiler is none validating, but throws errors on type mismatch or corrupt statements
Note:
If you have good ideas to improve this set of classes, let me know.
Acknowledgement:
Thanks a lot to George Antoniadis (Author: rephp framework, www.rephp.net) from noodles.gr for his qualified feedback.
Many thanks to all, which gave me qualified feedback and voted for this set of classes at the contest.
This package implements several interfaces to provide a DBMS independent access to SQL databases.
The interfaces define functions to manage database conections, execute SQL queries and retrieve results.
There are classes that implement these interfaces for MySQL and Microsoft SQL servers. Applications using these classes may use any of the supported databases eventually without changing the applications code.
The classes handle errors by throwing standard exceptions.
This is a simple class that converts date and time values between the ISO format (YYYY-MM-DD hh:mm:ss) used by SQL databases (DATE, TIME, DATETIME) and timestamps used in Unix systems.
The class converts ISO date and time strings guessing their type according to the string length.