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. If your query was not a SELECT, this function returns the affected_rows count.

// 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.

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.

Literal % Character

If you want a literal % character in your query (like for DATE_FORMAT()), there are two ways. First, if your query doesn't have any parameters then any % characters in the query will be ignored and you can use them normally. If your query does have parameters, then the % characters can be inserted by escaping it as %%. See the examples below:

// this query has no parameters, so % works as normal
DB::queryFirstField("SELECT DATE_FORMAT('2009-10-04 22:23:00', '%H:%i:%s')");

// this query has one parameter (15), so % characters have been escaped
DB::queryFirstField("SELECT DATE_FORMAT(created_at, '%%H:%%i:%%s') 
    FROM accounts WHERE id=%i", 15);

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::queryWalk()

This is for situations where you need to return a large dataset that can't be read into memory all at once. It lets you iterate through the results as shown below.

$Walk = DB::queryWalk("SELECT * FROM bigtable");
while ($row = $Walk->next()) {
    var_dump($row); // assoc array for one row
}

If you don't read all of the results, you must use free() before running any other queries! Otherwise your next query will fail.

$Walk = DB::queryWalk("SELECT * FROM bigtable");
$firstrow = $Walk->next(); // we actually only want one row!
$Walk->free(); // free remaining results