nvquanghuy/lazydb

A simple PHP mySQL library meant for lazy developers

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

Select Data

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_student

Query 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_select
• query_select_manualkey
• query_row
• query_col
• query_scalar

Insert Data Using Array

Use $db->insert:

  // Single insert
  $student = array(
    'name'    => 'Johny',
    'email'   => 'john@random.email',
  );
  $student_id = $db->insert("students", $student);

Batch Insert

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);

Update Data

Use $db->update

    $student = $db->query_row("SELECT * FROM students WHERE id = 1");
    $student['name'] = "Peter O' Really";
    $student['email'] = "peter@email.com";
    $db->update("students", $student, "id = 1");

Batch Update

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'] = "alice@random.email";
    $students[1]['name'] = "Bob";
    $students[1]['email'] = "bob@random.email";
    $students[2]['name'] = "Clara";
    $students[2]['email'] = "clara@random.email";

    // BATCH UPDATE
    $db->insert_batch("students", $students, "ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `email` = VALUES(`email`)");

General Query

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");

Insert mySQL's expression as field's data

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);

Storing array in fields

LazyDB automatically serializes and deserializes PHP array when storing and retrieving from database:

  $extra = array(
    'age'  => 20,
    'gender' => male
  );

  $student = array(
    'name'    => 'John',
    'email'   => 'john@email.com',
    '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'];

Download & Usage

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");

Feedback & Suggestions

You're welcome to fork, comments, give feedback or suggestion at nvquanghuy@gmail.com