<?php
/* Functions that return SQL queries or execute SQL queries.
*
* Naming is as follows:
* sql_* - returns SQL query as string
* (queries do not end in ; to promote nesting)
* query_* - executes db query and returns result (as array or something)
*
* Written by: Chris Studholme
* Copyright: GPL (http://www.fsf.org/copyleft/gpl.html)
* $Id: queries.php,v 1.10 2002/08/15 04:18:07 cstudhol Exp $
*/
/* Query table to find an unused value for the specified integer field.
* If $least=false, the new id is chosen as one more than the maximum
* of all current ids (fast method).
* If $least=true, an attempt is made to find the least positive unused
* value for the id (slow method).
*/
function query_new_id($table,$field,$least=false) {
global $conn;
if ($least) {
$query = "SELECT ".$field." FROM ".$table." ORDER BY ".$field;
$result = $conn->query($query);
$result = 1;
while ($arr=$result->fetch_assoc()) {
$id = (int)$arr[$field];
if ($id>$result)
break;
if ($id==$result)
++$result;
}
return $result;
}
else {
$query = "SELECT MAX(".$field.") AS id FROM ".$table;
$result = $conn->query($query);
$arr = $result->fetch_assoc();
$result = (int)$arr["id"];
return $result>0 ? 1+$result : 1;
}
}
/* Get an associative array of image type data with typeid as key. The array
* will be assigned to the global $image_types, but will also be returned.
* The array is in order of preference and the thumbnail type (typeid -1) will
* always be last. The global $default_typeid will be set to preferred typeid
* (based on preference and $cookie_record).
*/
function get_image_types() {
global $conn, $image_types, $default_typeid, $cookie_record;
if (is_array($image_types)&&$image_types[$default_typeid])
return $image_types;
$query = "SELECT * FROM types ORDER BY preference";
$result = $conn->query($query);
$image_types = array();
while ($arr = $result->fetch_assoc()) {
$id = (int)$arr["typeid"];
if ($id==-1)
$thumbnail = $arr;
else
$image_types[$id] = $arr;
}
$image_types[-1] = $thumbnail;
if ($cookie_record["typeid"]>0&&$image_types[$cookie_record["typeid"]])
$default_typeid = (int)$cookie_record["typeid"];
else {
reset($image_types);
do {
list($default_typeid,$arr) = each($image_types);
} while ($default_typeid<=0);
}
return $image_types;
}
/* Get array (indexed by collid) of name and description from obsolete
* toc_names table. Name and description are returned as (unserialized)
* multilingual arrays.
*/
function query_toc_old_names($collids=false) {
global $conn;
$query = "SELECT * FROM toc_names";
if (is_array($collids))
$query .= " WHERE collid IN (".array2CSV_int($collids).")";
$result = $conn->query($query);
$names = array();
while ($arr=$result->fetch_assoc()) {
$names[$arr["collid"]]["name"][$arr["lang"]] = $arr["name"];
$names[$arr["collid"]]["description"][$arr["lang"]] = $arr["description"];
}
return $names;
}
/* Get table of contents in associative array indexed by collid.
* Fields available are:
* collid, forall, cid, name, description, date_start, date_end
* Note: name and description are returned unserialized
*/
function query_toc_bycollid($collids=false) {
global $conn;
$query = "SELECT * FROM toc";
if (is_array($collids))
$query .= " WHERE collid IN (".array2CSV_int($collids).")";
$result = $conn->query($query);
$toc = $result->fetch_table("collid");
// unserialize names
$need_old = false;
reset($toc);
while (list($collid,$record)=each($toc)) {
if (strlen($record["name"])>0) {
$toc[$collid]["name"] = unserialize($record["name"]);
$toc[$collid]["description"] = unserialize($record["description"]);
}
else
$need_old = true;
}
if ($need_old) {
// read old names
$old_names = query_toc_old_names();
reset($toc);
while (list($collid,$record)=each($toc))
if (strlen($record["name"])==0) {
$toc[$collid]["name"] = $old_names[$collid]["name"];
$toc[$collid]["description"] = $old_names[$collid]["description"];
}
}
return $toc;
}
/* Get table of contents in associative array indexed by picid.
* Fields available are:
* picid, collid, forall, cid, name, description, date_start, date_end
* Note: name and description are returned unserialized
*/
function query_toc_bypicid($picids) {
global $conn;
$query = "SELECT picid,toc.collid,date_start,date_end,forall,cid".
",name,description FROM toc JOIN toc_pictures USING (collid)";
if (is_array($picids))
$query .= " WHERE picid IN (".array2CSV_int($picids).")";
else
$query .= " WHERE picid=".(int)$picids;
$result = $conn->query($query);
$toc = $result->fetch_table("picid");
// unserialize names
$need_old = false;
reset($toc);
while (list($picid,$record)=each($toc)) {
if (strlen($record["name"])>0) {
$toc[$picid]["name"] = unserialize($record["name"]);
$toc[$picid]["description"] = unserialize($record["description"]);
}
else
$need_old = true;
}
if ($need_old) {
// read old names
$old_names = query_toc_old_names();
reset($toc);
while (list($picid,$record)=each($toc))
if (strlen($record["name"])==0) {
$toc[$picid]["name"] = $old_names[$record["collid"]]["name"];
$toc[$picid]["description"] =
$old_names[$record["collid"]]["description"];
}
}
return $toc;
}
/* Return a sorted associative array of sources. The key is srcid.
*/
function query_sources() {
global $conn;
$result = $conn->query("SELECT * FROM sources ORDER BY description");
$sources = $result->fetch_table("srcid");
return $sources;
}
/* Returns a sorted associative array of keywords. The key is wordid. Each
* element is an associative array of table row contents. The extra field
* "lang_all" is created containing all words seperated by ";".
*/
function query_keywords() {
global $conn;
$result = $conn->query("SELECT * FROM keywords");
$keywords = array();
while ($arr=$result->fetch_assoc()) {
$lang = $arr["lang"];
if (!$lang) $lang="MULTILINGUAL";
$keywords[$arr["wordid"]][$lang] = $arr;
}
function list_all($arr) {
reset($arr);
list($l,$w)=each($arr);
$result = $w;
while (list($l,$w)=each($arr))
$result .= "; ".$w;
return $result;
}
reset($keywords);
while (list($wordid,$arr)=each($keywords)) {
if ($arr["MULTILINGUAL"]) {
$wordarr = $arr["MULTILINGUAL"];
$w = unserialize($wordarr["word"]);
$wordarr["lang_all"] = list_all($w);
$wordarr["word"] = best_lang($w);
}
else {
reset($arr);
$w = array();
while (list($l,$a)=each($arr)) {
$w[$l] = $a["word"];
$wordarr = $a;
}
$wordarr["lang_all"] = list_all($w);
$wordarr["word"] = best_lang($w);
}
$keywords[$wordid] = $wordarr;
}
// sort array
function keyword_compare($a,$b) {
return strcmp($a["word"],$b["word"]);
}
uasort($keywords,keyword_compare);
return $keywords;
}
/* Returns a sorted associative array of groups. The key is groupid. Each
* element is an associative array of table row contents. The extra field
* "lang_all" is created containing group name in all languages separated by
* ;'s.
*/
function query_groups() {
global $conn;
global $config,$default_lang,$lang;
// make sure these are initialized in ascending order
$lang_map = array();
$lang_map[$config["default_lang"]] = 1;
$lang_map[$default_lang] = 2;
$lang_map[$lang] = 3;
$result = $conn->query("SELECT * FROM groups");
$groups = array();
while ($arr=$result->fetch_assoc()) {
$groupid = $arr["groupid"];
if (!$groups[$groupid]) {
$arr["lang_all"] = $arr["name_group"];
$groups[$groupid] = $arr;
}
else {
$groups[$groupid]["lang_all"].="; ".$arr["name_group"];
if ($lang_map[$arr["lang"]]>$lang_map[$groups[$groupid]["lang"]]) {
// new version is better
$arr["lang_all"] = $groups[$groupid]["lang_all"];
$groups[$groupid] = $arr;
}
}
}
// sort array
function group_compare($a,$b) {
return strcmp($a["name_group"],$b["name_group"]);
}
uasort($groups,group_compare);
return $groups;
}
?>