MeekroDB Quick Start Docs FAQ
Download GitHub

Retrieving Data

DB::query()

You pass a MySQL query string with placeholder values, followed by a parameter for every placeholder. Results are returned as an array of assoc arrays. If there are no results, this function returns an empty array.

// no placeholders
DB::query("SELECT * FROM tbl");

// string, integer, and decimal placeholders
$results = DB::query("SELECT * FROM tbl WHERE name=%s AND age > %i AND height <= %d", $name, 15, 13.75);

foreach ($results as $row) {
  echo "Name: " . $row['name'] . "\n";
  echo "Age: " . $row['age'] . "\n";
  echo "Height: " . $row['height'] . "\n";
  echo "-------------\n";
}

You can refer to parameters out of order by adding a number to each placeholder. You can even refer to the same parameter more than once.

// use the parameter number to refer to parameters out of order
DB::query("SELECT * FROM tbl WHERE name=%s2 AND age > %i0 AND height <= %d1", 15, 13.75, $name);

// refer to the same parameter twice
DB::query("SELECT * FROM tbl WHERE firstname=%s0 AND lastname=%s0", 'John');

You can pass an array of named parameters and access them by name.

DB::query("SELECT * FROM tbl WHERE name=%s_name AND age > %i_age AND height <= %d_height", 
  [
    'name' => $name,
    'age' => 15,
    'height' => 13.75
  ]
);

You can use a placeholder to represent a list of strings or integers, for use with IN or NOT IN.

$results = DB::query("SELECT * FROM tbl WHERE name IN %ls AND age NOT IN %li", 
  ['John', 'Bob'], [12, 15]);

List of Placeholders

This table lists all of the available placeholders. The most common ones are %i, %s, and %d.

Placeholder Meaning
%s string
%i integer
%d decimal/double
%t timestamp (can be instance of DateTime or string accepted by strtotime
%? auto-detect data type
%ss search string (string surrounded with % for use with LIKE)
%b backticks (do not use with user-supplied data)
%l literal (do not use with user-supplied data)
%ls list of strings
%li list of integers
%ld list of decimals/doubles
%lt list of timestamps
%lb list of backticks (do not use with user-supplied data)
%ll list of literals (do not use with user-supplied data)

Warning About User-Supplied Data

The purpose of this placeholder system is to properly escape data for use in MySQL. Doing this by hand (such as with mysqli_real_escape_string()) is dangerous and error-prone, whereas our approach doesn't leave any room for dangerous mistakes.

However, there is one exception: the %b and %lb placeholders that surround your input with backticks, such as for table and column names. While we do our best to escape these properly, the MySQL documentation doesn't provide hard information on escaping them in weird edge cases. This isn't usually a problem because table and column names tend to be programmer-supplied rather than user-supplied, and you should make sure to keep it that way.

Also, the %l and %ll placeholders simply dump your input directly into the query string without any escaping. This obviously shouldn't be done with user-supplied data.

DB::queryFirstRow()

Retrieve the first row of results for the query, and return it as an associative array. If the query returned no rows, this returns null.

$account = DB::queryFirstRow("SELECT * FROM accounts WHERE username=%s", 'Joe');
echo "Username: " . $account['username'] . "\n"; // will be Joe, obviously
echo "Password: " . $account['password'] . "\n";

DB::queryFirstField()

Get the contents of the first field from the first row of results, and return that. If no rows were returned by the query, this returns null.

$joePassword = DB::queryFirstField("SELECT password FROM accounts WHERE username=%s", 'Joe');
echo "Joe's password is: " . $joePassword . "\n";

DB::queryFirstList()

Retrieve the first row of results for the query, and return it as a numbered index (non-associative) array. If the query returned no rows, this returns null.

list($username, $password) = DB::queryFirstList("SELECT username, password FROM accounts WHERE username=%s", 'Joe');
echo "Username: " . $username . "\n"; // will be Joe, obviously
echo "Password: " . $password . "\n";

DB::queryFirstColumn()

Retrieve the first column of results for the query, and return it as a regular array. If the query returned no rows, this returns an empty array.

$usernames = DB::queryFirstColumn("SELECT DISTINCT username FROM accounts");
foreach ($usernames as $username) {
  echo "Username: " . $username . "\n";
}

DB::queryFullColumns()

Like DB::query(), except the keys for each associative array will be in the form TableName.ColumnName. Useful if you're joining several tables, and they each have an id field.

$joe = DB::queryFullColumns("SELECT * FROM accounts WHERE username=%s", 'Joe');
print_r($joe);

/*
  Returns something like:
  Array
  (
      [accounts.id] => 3
      [accounts.username] => Joe
      [accounts.password] => whatever
  )
*/

DB::queryRaw()

Like DB::query(), except it returns a standard MySQLi_Result object instead of an array of associative arrays. This is intended for situations where the result set is huge, and PHP's memory is not enough to store the whole thing all at once.

$mysqli_result = DB::queryRaw("SELECT * FROM accounts WHERE username=%s", 'Joe');
$row = $mysqli_result->fetch_assoc();
echo "Joe's password is: " . $row['password'] . "\n";