Skip to content

Programmatically build MySQL SELECT queries without the overhead of passing strings between functions. QueryBuilders can also be merged together for easy query modification, with an optional PDO database connection to directly execute the query.

License

Notifications You must be signed in to change notification settings

mlabrum/QueryBuilder

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

75 Commits
 
 
 
 
 
 
 
 

Repository files navigation

Miner

A dead simple PHP class for building SQL statements. No manual string concatenation necessary.

Developed by Justin Stayton while at Monk Development.

Requirements

  • PHP >= 5.1.0

Installation

Composer

The recommended installation method is through Composer, a dependency manager for PHP. Just add jstayton/miner to your project's composer.json file:

{
    "require": {
        "jstayton/miner": "*"
    }
}

More details can be found over at Packagist.

Manually

  1. Copy src/Miner.php to your codebase, perhaps to the vendor directory.
  2. Add the Miner class to your autoloader or require the file directly.

Getting Started

Composing SQL with Miner is very similar to writing it by hand, as much of the syntax maps directly to methods:

$Miner = new Miner();
$Miner->select('*')
      ->from('shows')
      ->innerJoin('episodes', 'show_id')
      ->where('shows.network_id', $networkId)
      ->orderBy('episodes.aired_on', Miner::ORDER_BY_DESC)
      ->limit(20);

Now that the statement is built,

$Miner->getStatement();

returns the full SQL string with placeholders (?), and

$Miner->getPlaceholderValues();

returns the array of placeholder values that can then be passed to your database connection or abstraction layer of choice. Or, if you'd prefer it all at once, you can get the SQL string with values already safely quoted:

$Miner->getStatement(false);

If you're using PDO, however, Miner makes executing the statement even easier:

$PDOStatement = $Miner->execute();

Miner works directly with your PDO connection, which can be passed during creation of the Miner object

$Miner = new Miner($PDO);

or after

$Miner->setPdoConnection($PDO);

Usage

SELECT

SELECT *
FROM shows
INNER JOIN episodes
  ON shows.show_id = episodes.show_id
WHERE shows.network_id = 12
ORDER BY episodes.aired_on DESC
LIMIT 20

With Miner:

$Miner->select('*')
      ->from('shows')
      ->innerJoin('episodes', 'show_id')
      ->where('shows.network_id', $networkId)
      ->orderBy('episodes.aired_on', Miner::ORDER_BY_DESC)
      ->limit(20);

INSERT

INSERT HIGH_PRIORITY shows
SET network_id = 13,
    name = 'Freaks & Geeks',
    air_day = 'Tuesday'

With Miner:

$Miner->insert('shows')
      ->option('HIGH_PRIORITY')
      ->set('network_id', 13)
      ->set('name', 'Freaks & Geeks')
      ->set('air_day', 'Tuesday');

REPLACE

REPLACE shows
SET network_id = 13,
    name = 'Freaks & Geeks',
    air_day = 'Monday'

With Miner:

$Miner->replace('shows')
      ->set('network_id', 13)
      ->set('name', 'Freaks & Geeks')
      ->set('air_day', 'Monday');

UPDATE

UPDATE episodes
SET aired_on = '2012-06-25'
WHERE show_id = 12
  OR (name = 'Girlfriends and Boyfriends'
        AND air_day != 'Monday')

With Miner:

$Miner->update('episodes')
      ->set('aired_on', '2012-06-25')
      ->where('show_id', 12)
      ->openWhere(Miner::LOGICAL_OR)
      ->where('name', 'Girlfriends and Boyfriends')
      ->where('air_day', 'Monday', Miner::NOT_EQUALS)
      ->closeWhere();

DELETE

DELETE
FROM shows
WHERE show_id IN (12, 15, 20)
LIMIT 3

With Miner:

$Miner->delete()
      ->from('shows')
      ->whereIn('show_id', array(12, 15, 20))
      ->limit(3);

Methods

SELECT

INSERT

REPLACE

UPDATE

DELETE

OPTIONS

SET

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

Statement

Connection

Feedback

Please open an issue to request a feature or submit a bug report. Or even if you just want to provide some feedback, I'd love to hear. I'm also available on Twitter as @jstayton.

Contributing

  1. Fork it.
  2. Create your feature branch (git checkout -b my-new-feature).
  3. Commit your changes (git commit -am 'Added some feature').
  4. Push to the branch (git push origin my-new-feature).
  5. Create a new Pull Request.

About

Programmatically build MySQL SELECT queries without the overhead of passing strings between functions. QueryBuilders can also be merged together for easy query modification, with an optional PDO database connection to directly execute the query.

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages

  • PHP 100.0%