LazyDB is an simple, elegant PHP mySQL database library. As the name suggests, it's meant for lazy developers and requires minimal amount of PHP code to work with mySQL database.
Features:
- Fetch and transform table's records into PHP array.
- Batch insert, batch update
- Log SQL error to file
- Automatic escaping of string value (through
mysql_real_escape_string)
See how it works below
The simpicity of LazyDB is in its rich set of functions to help grab data in different formats and parse them into PHP array.
Query list of records
$students = $db->query_select("SELECT * FROM students");
foreach ($students as $student) {
// Deal with $student
}List of records, with array's key to be 1 field
$students = $db->query_select_manualkey("SELECT * FROM students", "email");
foreach ($students as $email => $student) {
// Deal with this student
}Query only 1 record
$first_student = $db->query_row("SELECT * FROM students ORDER BY `id` ASC LIMIT 0, 1");
// Deal with $first_studentQuery only 1 column
$names = $db->query_col("SELECT name FROM students");
foreach ($names as $name) {
// Deal with $name
}Return single scalar value
$random_name = $db->query_scalar("SELECT name FROM students ORDER BY RAND() LIMIT 0, 1");
print $random_name;In summary, pick one of the following functions to query data effectively:
query_selectquery_select_manualkeyquery_rowquery_colquery_scalar
Use $db->insert:
// Single insert
$student = array(
'name' => 'Johny',
'email' => '[email protected]',
);
$student_id = $db->insert("students", $student);Use $db->insert_batch:
// Batch insert
$students = array();
for ($i = 1; $i <= 2; $i++) {
$students[] = array(
'name' => "Alexander the {$i}-th",
'email' => "alex_{$i}@email.com"
);
}
$db->insert_batch("students", $students);Use $db->update
$student = $db->query_row("SELECT * FROM students WHERE id = 1");
$student['name'] = "Peter O' Really";
$student['email'] = "[email protected]";
$db->update("students", $student, "id = 1");Use $db->insert_batch and make use of SQL syntax INSERT ... ON DUPLICATE KEY as follows:
$students = $db->query_select("SELECT * FROM students");
// Assuming we'll get 3 records, change the records' values now
$students[0]['name'] = "Alice";
$students[0]['email'] = "[email protected]";
$students[1]['name'] = "Bob";
$students[1]['email'] = "[email protected]";
$students[2]['name'] = "Clara";
$students[2]['email'] = "[email protected]";
// BATCH UPDATE
$db->insert_batch("students", $students, "ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `email` = VALUES(`email`)");If none of the above fits:
// Drop table
$db->query("DROP TABLE IF EXISTS `students`");
// Delete record
$db->query("DELETE FROM `students` WHERE id = 1");If you want to insert a mysql's expression as a field's value, wrap the expression with LazyDB::E($exp). For example:
INSERT INTO students (`name`, `created`) VALUES ('John', NOW());
become:
$student = array(
'name' => 'John',
'created' => LazyDB::E('NOW()')
);
$student_id = $db->insert('students', $student);LazyDB automatically serializes and deserializes PHP array when storing and retrieving from database:
$extra = array(
'age' => 20,
'gender' => male
);
$student = array(
'name' => 'John',
'email' => '[email protected]',
'extra' => $extra, // will automatically be serialized
);
$student_id = $db->insert("students", $student);
// Now query that data
$student = $db->query_single("SELECT * FROM students WHERE id = $student_id");
print "Age: " . $student['extra']['age'];1/ Download and extract: https://github.com/nvquanghuy/lazydb/zipball/master
2/ Modify database information in example.php. Run it to see a demo.
3/ If you want to log down SQL errors, create folder errorlogs in webroot and make it writable.
4/ Initiate a lazydb object to connect to the database server using:
require "lazydb.php";
$db = new LazyDB("localhost", "root", "", "test");You're welcome to fork, comments, give feedback or suggestion at [email protected]