select

API:DB:select

Description

resource select([string $fields [, string $from [, string $where [, string $orderby [, string $limit]]]]])

The select function is a simple wrapper for the SQL SELECT query. select() sends a query to the currently active database, $this->conn as created when connect() is called. This function is analogous to mysql_query() except the query string is broken up into 5 parts. Only the $from field is required for a meaningful result.

*Parameter $fields are the field or column name(s) that you want returned. If left blank it will default to all (*).
*$from is the table to query. If left blank function will return false.
*$where is the full string of the WHERE clause of the mysql query. Leave blank to do no WHERE matching.
*$orderby, if needed, can either be 'ASC' or 'DESC'.
*$limit is the limit of the number of results to return, leave blank for all

Usage / Examples

$results = $modx->db->select("field1, field2", "table", "field = value", "field to order by", "limit value");

This example login function uses the select function to get the id of a user given the username and password.

function login($username, $password)
{
global $modx;
$username = $modx->db->escape($username); // this should have been done with the POST
$password = $modx->db->escape($password); // values before passing them to the function

$res = $modx->db->select("id", $modx->getFullTableName('web_users'), 
"username='" . $username ."' AND password='".md5($password)."'");
if($modx->db->getRecordCount($res))
{
$id = $modx->db->getValue($res);
$_SESSION['userid'] = $id;
//other log in things...
}
else
{
//incorrect login
}
}

Example2:

$userId = $modx->getLoginUserID();
$table = $modx->getFullTableName("user_messages");
$messages = $modx->db->select("subject, message, sender", $table, "recipient = $userId", "postdate DESC", "10");

This will return the subject, message and sender of the latest 10 messages for the current user.

Related

• getRecordCount
• query
• getRow
• makeArray

Notes

One could easily use mysql_query($qry) instead of this function, however it is preferred to use this function, especially for modx table calls, to ensure future compatibility should their be any changes to the way modx queries the database (eg: changing to mysqli_query(), or using another database type altogether).

Function Source

File: manager/includes/extenders/dbapi.class.inc.php
Line: 169

function select($fields = "*", $from = "", $where = "", $orderby = "", $limit = "") {
if (!$from)
return false;
else {
$table = $from;
$where = ($where != "") ? "WHERE $where" : "";
$orderby = ($orderby != "") ? "ORDER BY $orderby " : "";
$limit = ($limit != "") ? "LIMIT $limit" : "";
return $this->query("SELECT $fields FROM $table $where $orderby $limit");
}
}

Suggest an edit to this page.