libmySQL: mySQL Abstraction library (PHP)
The mySQL library aims to make access to data in a SQL database easy. While originally indented for mySQL, it can be adapted to be used with any SQL-based database server, without modifications to your code. The library handles database (re)connection, variable escaping, query buffering, etc. It's written in PHP and can be used in a web application or standalone shell scripts.
This is not an SQL tutorial. To learn more about SQL, see the mySQL website. Several complete usage examples are at the end of this page.
The most current version is available on NFS in ~luki/lib/libmysql.php
Defining Database Connection
Include the library in your script:
include('/data12/users/luki/lib/libmysql.php');
Define server name, user name, password and database name:
$SQL = new mySQL(array(host => 'HOST', user => 'USER', pass => 'PASSWORD', database => 'DATABASE'));
Retrieving Data
SELECT queries retrieve data matching the specified criteria from the database without any modifications to the data. The Select() function will return an array of matching rows. The Select1() function returns only the first row of the result set.
Executing a SELECT query:
The Select() and Select1() functions accept either a SQL query statement string, or an array to build the query. The former gives fine grained control but requires knowledge of the SQL syntax. Use which ever you prefer. Both examples below accomplish the same.
$segment = $SQL->Select1(
array
table => 'segments',
where => array(seq => 'NNQQNY', backboneid => 1),
order => 'date DESC',
limit => 1)
);
or:
$segment = $SQL->Select1('SELECT * FROM segments WHERE seq="NNQQNY" AND backboneid=1 ORDER BY date DESC LIMIT 1');
Gives:
array(12) {
["segid"]=>
string(5) "35618"
["date"]=>
string(19) "2007-12-30 00:08:08"
["seq"]=>
string(6) "NNQQNY"
["status"]=>
string(4) "done"
["host"]=>
string(14) "hhmaster-30246"
["bestvarid"]=>
string(8) "65588626"
["backboneid"]=>
string(1) "1"
["searchspace"]=>
string(9) "-3,7/-5,5"
["resolution"]=>
string(4) "0.25"
["priority"]=>
string(1) "1"
["method"]=>
string(5) "fuzzy"
["score"]=>
string(7) "-24.800"
}
Any user input should be passed as arguments:
$segment = $SQL->Select1('SELECT * FROM segments WHERE seq=@q AND backboneid=1 ORDER BY date DESC LIMIT 1', $_POST);
Where "q" is a form field named "q" from a form that was submitted via a POST request. This ensures any user defined input will be properly escaped to avoid SQL-injection vulnerabilities.
Further, results from a first query can be used to in subsequent queries:
$variations = $SQL->Select('SELECT varid, score, sc, spacing, translation FROM segment_variations WHERE segid=@segid', $segment);
Gives:
array(207) {
[0]=>
array(5) {
["varid"]=>
string(7) "4775047"
["score"]=>
string(7) "-23.500"
["sc"]=>
string(6) "0.5100"
["spacing"]=>
string(5) "2.000"
["translation"]=>
string(6) "-1.750"
}
[1]=>
array(5) {
["varid"]=>
string(7) "4775046"
["score"]=>
string(7) "-23.000"
["sc"]=>
string(6) "0.5590"
["spacing"]=>
string(5) "1.750"
["translation"]=>
string(6) "-2.000"
}
...
Updating Data
Data in the database is typically updated using the UPDATE query. New rows are added using the INSERT query. Rows are deleted using the DELETE query. This library provides a function for each of these query types. The return value is 1 if the query was successful and the changes have been made, 0 otherwise. Note that Update() will return 0 if the query was successful but no changes were made (no difference between present values in the database and requested changes). This is a SQL server behavior.
Insert
$SQL->Insert('table_name', $values);
$values is a named array of values, one for each column, such as array(seq => 'NNQQNY', score => -25.0). Not all columns have to be filled; the SQL server will use defined details for omitted columns. This function is binary safe (including BLOBs) and will escape all required characters.
Update
$SQL->Update('table_name', $changes, $keys);
$changes is a named array of values, one for each column to be changed, such as array(score => -24.0, status => 'done'). $keys is a named array of column names and values to match, such as array(segid => 1234). All matching rows will be updated.
Delete
$SQL->Delete('table_name', $keys);
$keys is a named array of column names and values to match, such as array(segid => 1234). All matching rows will be deleted.
Additional Functions
- InsertIgnore($table_name, $values): Insert with ignore option. If the row cannot be inserted due to a key violation (primary key or unique), no action will be performed and not error will be returned.
- Insert Update($table_name, $values): An INSERT/UPDATE hybrid. An update will be attempted first, if no rows are affected, a new row will be inserted.
- Execute($query, $args): Execute custom $query, optionally using the provided $args when referenced in $query as:
- @0: By numeric array index
- @name: By array element name
- @name@: By array element name, except argument will not be enclosed in quotes but will be escaped properly (useful in matches like seq LIKE '%@q@%')
- InsertedID(): Returns the value of an AUTO_INCREMENT primary key of the last inserted row.
- FoundRows(): Return the value of affected rows, if the preceding query was run with SQL_CALC_ROWS.
- AffectedRows(): Return the number of affected rows of the preceding Update/Delete/Insert query.
- Close(): Close the SQL connection. The connection will re-opened on demand when needed again.
Examples
Coming soon.
