SQL Conventions
Intro
The following common SQL conventions appear throughout the code in many places. We use a simple DBA (DataBase Abstraction layer) to handle differences between databases. Please be sure to use only standards-compliant SQL.
Rule One
Worth Repeating: Don't use non-standard SQL. This goes for addons as well. If you do use non-standard SQL, and the dba funcs are insufficient, do a if()/switch() or similar for all currently supported databases. Currently nothing red# does requires non-standard SQL.
Using a format string
To be written
// Example
$r = q("SELECT * FROM profile WHERE uid = %d",
local_channel()
);
Checking bit flags in a where clause
// Example
$r = q("SELECT abook_id, abook_flags, abook_my_perms, abook_their_perms, xchan_hash, xchan_photo_m, xchan_name, xchan_url from abook left join xchan on abook_xchan = xchan_hash where abook_channel = %d and not (abook_flags & %d)>0 ",
intval($uid),
intval(ABOOK_FLAG_SELF)
);
$y = q("update xchan set xchan_flags = (xchan_flags & ~%d) where (xchan_flags & %d)>0 and xchan_hash = '%s'",
intval(XCHAN_FLAGS_ORPHAN),
intval(XCHAN_FLAGS_ORPHAN),
dbesc($rr['hubloc_hash'])
);
$y = q("update xchan set xchan_flags = (xchan_flags | %d) where xchan_hash = '%s'",
intval(XCHAN_FLAGS_ORPHAN),
dbesc($rr['hubloc_hash'])
);
Using relative times (INTERVALs)
// Example
$r = q("SELECT * FROM abook left join xchan on abook_xchan = xchan_hash
WHERE abook_dob > %s + interval %s and abook_dob < %s + interval %s",
db_utcnow(), db_quoteinterval('7 day'),
db_utcnow(), db_quoteinterval('14 day')
);
Paged results
// Example
$r = q("SELECT * FROM mail WHERE uid=%d AND $sql_extra ORDER BY created DESC LIMIT %d OFFSET %d",
intval(api_user()),
intval($count), intval($start)
);
NULL dates
// Example
$r = q("DELETE FROM mail WHERE expires != '%s' AND expires < %s ",
dbesc(NULL_DATE),
db_utcnow()
);
Storing binary data
// Example
$x = q("update photo set data = '%s', height = %d, width = %d where resource_id = '%s' and uid = %d and scale = 0",
dbescbin($ph->imageString()),
intval($height),
intval($width),
dbesc($resource_id),
intval($page_owner_uid)
);
Current timestamp
// Example
$randfunc = db_getfunc('rand');
$r = q("select xchan_url from xchan left join hubloc on hubloc_hash = xchan_hash where hubloc_connected > %s - interval %s order by $randfunc limit 1",
db_utcnow(), db_quoteinterval('30 day')
);
SQL Function and Operator Abstraction
// Example
$randfunc = db_getfunc('rand');
$r = q("select xchan_url from xchan left join hubloc on hubloc_hash = xchan_hash where hubloc_connected > %s - interval %s order by $randfunc limit 1",
db_utcnow(), db_quoteinterval('30 day')
);
Return to the Main documentation page