PHP Classes
elePHPant
Icontem

QDataObject: Access MySQL query results as objects

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Info   Screenshots Screenshots   View files View files (32)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings  
2008-07-25 (7 years ago) RSS 2.0 feedNot yet rated by the usersTotal: 701 All time: 4,387 This week: 1,076Up
Version License PHP version Categories  
qdataobject 0.3BSD License5.0PHP 5, Databases
Description Author  

This package can be used to access MySQL query results as objects.

It can take a MySQL query result set resource handle as parameter and provides several types of functions to access the data from the result set.

It provides check or retrieve the result set data positions, as well retrieve the type and length of the respective columns.

The package provides implementations that use the regular MySQL and MySQLi extensions to access MySQL query results.

New:
DataObjectFactory

=== QMysqlDataObject ===
Test => SELECT * FROM geodb_textdata
TEST ENV:
Windows Vista Xampp, AMD Turion 64x2 Mobile 1.80 GHz
2 MB RAM (average performance)

1.a) object building runtime 0.176911115646s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 22210 by 10 rows/page)

1.b) object building runtime 0.275130033493s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 3000 by 100 rows/page)

2.a) object building runtime 0.0171821117401s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 10 by 10 rows per page)

2.b) object building runtime 0.0609588623047s
on a db table with 337.925 records
(building uncached dataobject from uncached query at page offset 10 by 100 rows per page)

3)

SELECT * FROM orders
LEFT JOIN customers on customers.CustomerID=orders.CustomerID

(northwind demo database)
object building runtime 0.055801153183s
(building uncached dataobject from uncached query at page offset 1 by 10 rows per page)
Mysql DB times:
Status runtime
(initialization) 0.0000377
Opening tables 0.0002247
System lock 0.0000032
Table lock 0.0000067
init 0.0000107
optimizing 0.0000037
statistics 0.0000107
preparing 0.00001
executing 0.0000467
Sending data 0.0002505
end 0.0000042
query end 0.0000035
freeing items 0.0000077
closing tables 0.000002
removing tmp table 0.0001105
closing tables 0.0000042
logging slow query 0.0000025

Conclusion:
Object building becomes much quicker if you operate with sql conditions.
The test above shows, that a cut on a deep offset is slow. So you have to reduce the data cloud by conditioning and so on.
(On a Linux Web server it will run much faster.)

Regards Tom Schaefer

Recommendations  

Extract required data from huge DB about 40 GB
Taking too much time to filter data and get required results

Picture of Tom Schaefer
Name: Tom Schaefer is available for providing paid consulting. Contact Tom Schaefer .
Classes: 39 packages by
Country: Germany Germany
Age: ???
All time rank: 1109 in Germany Germany
Week rank: 230 Down11 in Germany Germany Down
Innovation award
Innovation award
Nominee: 9x

Winner: 1x

Details provided by the author  
This wrapper dynamically builds a data object from a mysql resource.
It dynamically provides set, get, has, is, len, typ, of and add prefixed methods for header and data values.

2-letter-prefixed methods and has-prefixed methods always return a boolean statement;
3-letter-prefixed methods usually get or set data.
set-methods put data into the object once.
add-methods are able to push data at the end of an object property section.

set and add methods are bound to the data properties,

is, of, len, typ methods are are object header specific,

has and get switch between the data and header properties automatically.

This means:

$dataObject = new QDataObject($resource); // here the mysql resource goes into the object

Your query result set ($resource) contains following columns from a sql query like this:

SELECT
e.*,
d.department_id as depid,
d.caption

FROM md_employees e
LEFT JOIN md_departments d
ON e.department_id=d.department_id
LIMIT 5;


NOTE! For getting it working correctly, you have to write down the column names.
Using the asterisk * sign leads to errors if the columns of joined tables are
not unique. You can use an alias for getting unique columns.

The data object provides automatically following methods (You need not write them!!!)
and is behaving like a *data list object*:

$dataObject->setEmployeeId()
$dataObject->setScheduleId()
$dataObject->setDepartmentId()
$dataObject->setLastName()
$dataObject->setFirstName()
$dataObject->setDateBirth()

$dataObject->addEmployeeId()
$dataObject->addScheduleId()
$dataObject->addDepartmentId()
$dataObject->addLastName()
$dataObject->addFirstName()
$dataObject->addDateBirth()

$dataObject->getEmployeeId()
$dataObject->getScheduleId()
$dataObject->getDepartmentId()
$dataObject->getLastName()
$dataObject->getFirstName()
$dataObject->getDateBirth()

$dataObject->hasEmployeeId()
$dataObject->hasScheduleId()
$dataObject->hasDepartmentId()
$dataObject->hasLastName()
$dataObject->hasFirstName()
$dataObject->hasDateBirth()

$dataObject->lenEmployeeId()
$dataObject->lenScheduleId()
$dataObject->lenDepartmentId()
$dataObject->lenLastName()
$dataObject->lenFirstName()
$dataObject->lenDateBirth()

$dataObject->typEmployeeId()
$dataObject->typScheduleId()
$dataObject->typDepartmentId()
$dataObject->typLastName()
$dataObject->typFirstName()
$dataObject->typDateBirth()

$dataObject->ofInt("EmployeeId")
$dataObject->isAutoIncrement("EmployeeId")
$dataObject->isPrimaryKey("EmployeeId")
$dataObject->isNotNull("EmployeeId")
$dataObject->isMultipleKey("EmployeeId")
$dataObject->isUniqueKey("EmployeeId")
$dataObject->isUnsigned("EmployeeId")
$dataObject->isZerofill("EmployeeId")

and so on

The data object does a bit more. It provides some resource header information which
you can get by following methods:

$dataObject->getFieldNames();
$dataObject->getFieldPositions();


Per definition a single row result is the smallest possible list, which means it is
a list with only one object. By this convention addressing a row becomes quite simple.
If you have a data object with one entry then you can get it like this.
$dataObject->getLastName()
which is already the same as
$dataObject->getLastName(0)



If you have a list of objects then you can get a list from a single column by
using following command:
$dataObject->getListByPosition(6) // means: get the list of all values from column 6

If you know a specific colum name you get the list by name:
$dataObject->getListByName('LastName') // means: get the list of all values from a column named last_name


If you want to show an entry of a specific row column you can write
something like this:
$dataObject->getLastName(2) // means: get the last name from row 3

+++++++++++++++++
If your resource includes columns from foreign tables then
you can address a column by following notation, too:
$dataObject->getCaptionOfMdDepartments(2);
$dataObject->getFirstNameOfMdEmployees(2);

SELECT * FROM md_employees
LEFT JOIN md_departments ON md_departments.department_id=md_employees.department_id

As you can see the table name is attached to the column after the of separator.
'FirstNameOfMdEmployees' is called the perspective name of the model perspective.
Why? The data result set gives you a new perspective onto the data cloud.
Here it is a simple left join. By this notation you must not give attention
to the column names. If there is more then one column of the same name, the
table name (relation name) will be attached to the column (attribute name).
Overwriting of columns in array will be avoided automatically.
It is nearly the same as a full qualified name. But it is a php name, first.
I could tell you more about my data model theory, but that shall be enough.

+++++++++++++++++
Further dynamically generated methods:

$dataObject->getNumRows();
$dataObject->hasNumRows();

$dataObject->getNumFields();
$dataObject->hasNumFields();

$dataObject->getFieldTypes(); // list of the field types
$dataObject->getFieldPositions(); // list of the positions of the fields
$dataObject->getFieldFlags(); // list of the field flags
$dataObject->getFieldLengths(); // list of the field lengths
$dataObject->getTableNames(); // list of the table names for fields
$dataObject->getPerspectives(); // perspective names list


Additional Field Flag Functionality:
MySQL delivers following field flags if your MySQL version suports it:
"not_null",
"primary_key",
"unique_key",
"multiple_key",
"blob",
"unsigned",
"zerofill",
"binary",
"enum",
"auto_increment",
"timestamp"

QDataObject provides this by "type" prefix methods. If you want to check a field
for its field type, you can call a flag by following methods:
$dataObject->typLastName(2)


QDataObject provides this by "is" prefix methods. If you want to check a field
for its field flags, you can call a flag by following methods:
(All is-prefixed field flag methods return a boolean statement.)
$dataObject->isNotNull()
$dataObject->isUniqueKey()
$dataObject->isMultipleKey()
$dataObject->isBlob()
$dataObject->isUnsigned()
$dataObject->isZerofill()
$dataObject->isBinary()
$dataObject->isEnum()
$dataObject->isAutoIncrement()
$dataObject->isTimestamp()

QDataObject provides this by "of" prefix methods. If you want to check the type of
a field then call following methods:
(All of-prefixed field flag methods return a boolean statement.)
$dataObject->ofInt()
$dataObject->ofReal()
$dataObject->ofBigint()
and so on (It is works on the real mysql field type names.)
@see: http://dev.mysql.com/doc/

QDataObject provides this by "len" prefix methods. If you want to check the length of
a field then call it the following way:

syntax:
$object -> {prefix}{name-of-field-camelcased)()

e.g.
$dataObject->lenEmployeeId();


#######################

Due to the fact, not the lack, that generic or better meta programming
does not show the provided methods directly, you can use the showMethods
method for getting an image of what is going on.

Showing provided methods:
echo $dataObject->showMethods();

#######################

Conclusion:
This class is very compact, it has only some 300 lines of code. But it is mighty, because
it handles columns generically. You need not to write the getter and setter methods
manually. They are already there.
Someone who knows how to handle it, understands immediately what can be done with
this data object. I do know.
Screenshots  
  • uml.png
  • qtag.png
  Files folder image Files  
File Role Description
Files folder imagever.0.4 (7 files)
Accessible without login Plain text file config.inc.php Conf. Configuration and Autoloading
Accessible without login Image file indicator1.gif Icon Indicator
Accessible without login Plain text file interface.js Data jquery interface plugin
Accessible without login Plain text file jquery.dimensions.js Data jquery dimension plugin
Accessible without login Plain text file jquery.js Data JQuery Lib
Accessible without login Plain text file northwind.sql Data Sample Northwind Database for MySQL
Plain text file QDataObject.class.php Class Data Object Class
Plain text file QDataObject.class.v3.php Class version 3: for version 3 adapater
Plain text file QMysqlDataObject.class.php Class Mysql Version v.0.2
Plain text file QMysqlDataObject.v3.class.php Class version 3: for working on large databases
Plain text file QMysqliDataObject.class.php Class MySqli Version v.0.2
Plain text file QMysqliDataObject.v3.class.php Class version 3: for working on large databases
Plain text file QTag.class.php Class QTag - the smallest OO Markup Template Engine
Accessible without login Plain text file readme.txt Doc. README
Accessible without login Plain text file sample.list.php Example sample for a result set as object list
Accessible without login Plain text file sample.mysqli.php Example sample for mysqli
Accessible without login Plain text file sample.mysqli.version.0.2..table.php Example Sample output as table
Accessible without login Plain text file sample.mysqli.version.0.2.php Example MySqli Version 0.2 Sample
Accessible without login Plain text file sample.php Example Sample for data object with one result object
Accessible without login Plain text file sample.using.qtag.php Example Sample Table using QTag
Accessible without login Plain text file sample.v3.mysql.php Example basic mysql version 3
Accessible without login Plain text file sample.v3.mysqli.php Example basic mysqli version 3
Accessible without login Plain text file sample2b.php Example Sample JQUERY
Accessible without login Plain text file sample2b1.php Example Sample JQuery Data Table
Accessible without login Image file slider_back.jpg Icon slider background

  Files folder image Files  /  ver.0.4  
File Role Description
  Accessible without login Plain text file model.xml Data Sample XML out of database scheme nothwind
  Plain text file QAdapterDataObject.class.php Class QAdapterDataObject class
  Plain text file QDataObject.class.php Class QDataObject Factory
  Plain text file QMysqlDataObject.class.php Class QMysqlDataObject
  Plain text file QMySqliDataObject.class.php Class QMySqliDataObject
  Accessible without login Plain text file readme.txt Doc. Readme
  Accessible without login Plain text file sample.php Example build model scheme form database

 Version Control Unique User Downloads Download Rankings  
 0%Total:701All time:4,387
 This week:0This week:1,076Up