db.inc

  1. 5.x includes/db.inc
  2. 4.x includes/db.inc

This file contains mostly db shortcuts.

Functions & methods

NameDescription
db_affected_rows
db_fetch_array
db_fetch_object
db_get_user_idLook up the user_id based on the the user_name. Returns FALSE if it cannot find it.
db_get_user_id_from_cwid
db_get_user_settingReturn a specific setting's value, based on the var_name given.
db_get_user_settingsReturn the array from the user_settings table.
db_insert_id
db_num_rows
db_query
db_resultReturns back the first result from a resource_handler.
db_set_user_setting
db_table_existsReturns TRUE if the table specified exists or not.
fp_get_degree_advising_weight
fp_get_degree_major_codeReturns the major code for a given degree_id
fp_get_degree_titleQuick method to look up title for a degree.
fp_get_faculty_nameReturns the faculty member's name based on the ID provided.
fp_get_max_catalog_repeats_for_courseFigure out the maximum number of times this course can be repeated for credit, based on what is stored in the course catalog.
fp_get_permissions_for_role
fp_get_student_majorsReturn back the codes or records for a student's degrees, based on what is in the student_degrees table (thanks to system.module), as well as what we get from hooks.
fp_get_student_name
fp_get_system_settings
fp_load_userReturns back a user object for this user_id. If the user is not found in the users table, it will return NULL. If the user_id requested is 0, the anonymous user object is returned.
fp_rebuild_modules_listRe-query the modules table and re-add to our global array.
variable_getGet a variable from the database. We will first look in our GLOBALS array, to see that it hasn't already been retrieved this page load.
variable_setSet a variable value, so we can retrieve it later on.
watchdogAdd a log entry to the watchdog (log) table.

File

includes/db.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * This file contains mostly db shortcuts.
  5. */
  6. /**
  7. * Add a log entry to the watchdog (log) table.
  8. *
  9. * This is adapted from Drupal 6's watchdog system.
  10. *
  11. * @param string $type
  12. * Generally, this is the name of the module, or some other short text
  13. * which can be used to categorize this log entry. Ex: "system" or "routines".
  14. * @param string $message
  15. * This is the actual log message itself. It can be any length, and contain replacement
  16. * patterns (very similar to the t() function.) Ex: "The user @user has logged in."
  17. * @param array $variables
  18. * If replacement patterns exist in the $message, this is where they are defined, similar
  19. * to the t() function. Ex: array("@user" => $user->name) *
  20. * @param int $severity
  21. * One of several constant values, denoting the severity level.
  22. * Available values:
  23. * - WATCHDOG_DEBUG (for development)
  24. * - WATCHDOG_NOTICE (default)
  25. * - WATCHDOG_ALERT (a step above "notice")
  26. * - WATCHDOG_ERROR (highest level of severity)
  27. * @param string $extra_data
  28. * Any extra bit of text you want to add on. Must be 255 characters or less. Good for adding
  29. * extra codes and such which can be queried for later more easily.
  30. */
  31. function watchdog($type, $message, $variables = array(), $severity = WATCHDOG_NOTICE, $extra_data = "") {
  32. global $user;
  33. // TODO: Have a setting where, we do not actually log certain severity levels, like notice or debug
  34. // (to save space)
  35. $user_id = 0;
  36. $cwid = 0;
  37. $user_name = "";
  38. $is_student = 0;
  39. $is_faculty = 0;
  40. if (is_object($user)) {
  41. $user_id = @$user->id;
  42. $cwid = @$user->cwid;
  43. $user_name = @$user->name;
  44. $is_student = (int) @$user->is_student;
  45. $is_faculty = (int) @$user->is_faculty;
  46. }
  47. $is_mobile = (int) fp_screen_is_mobile();
  48. $ip = @$_SERVER["REMOTE_ADDR"];
  49. $location = @$_SERVER["REQUEST_URI"];
  50. $referer = @$_SERVER['HTTP_REFERER'];
  51. $ser_variables = "";
  52. if (count($variables) > 0) {
  53. $ser_variables = serialize($variables);
  54. }
  55. db_query("INSERT INTO watchdog
  56. (user_id, user_name, cwid, type, message, variables, severity, extra_data, location, referer, ip, is_mobile, is_student, is_faculty, timestamp)
  57. VALUES
  58. ('?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?')
  59. ", $user_id, $user_name, $cwid, $type, $message, $ser_variables, $severity, $extra_data, $location, $referer, $ip, $is_mobile, $is_student, $is_faculty, time());
  60. }
  61. /**
  62. * Figure out the maximum number of times this course can be repeated for credit, based on what is stored
  63. * in the course catalog.
  64. *
  65. * We will do this by directly querying the database, for speed reasons.
  66. *
  67. */
  68. function fp_get_max_catalog_repeats_for_course($subject_id, $course_num, $catalog_year, $bool_draft = TRUE) {
  69. $table_name = "courses";
  70. if ($bool_draft) {
  71. $table_name = "draft_courses";
  72. }
  73. $res = db_query("SELECT * FROM $table_name WHERE subject_id = ? AND course_num = ? AND catalog_year = ?", $subject_id, $course_num, $catalog_year);
  74. $cur = db_fetch_array($res);
  75. $min_hours = $cur['min_hours'];
  76. $max_hours = $cur['max_hours'];
  77. $repeat_hours = $cur['repeat_hours'];
  78. if ($repeat_hours <= $min_hours) {
  79. // Meaning, this course cannot be repeated for anything. So, just return 1, meaning, it can be taken only once.
  80. return 1;
  81. }
  82. // Okay, so what we want to do is figure out, if this is a 3 hour course, and it can be repeated for 9 hours, that means it
  83. // can be taken (repeats) 3 times.
  84. // We will use the min_hours for this calculation. If zero, then change it to 1.
  85. if ($min_hours < 1) $min_hours = 1;
  86. // Use intval so we don't have decimals. Whole attempts only.
  87. $repeats = intval($repeat_hours / $min_hours);
  88. return $repeats;
  89. } // fp_get_max_catalog_repeats_for_course
  90. /**
  91. * Returns the major code for a given degree_id
  92. */
  93. function fp_get_degree_major_code($degree_id, $bool_reset_cache = FALSE) {
  94. if ($bool_reset_cache) {
  95. unset($GLOBALS['fp_temp_degree_major_codes']);
  96. }
  97. // We will cache in a GLOBALS variable, to save lookup time.
  98. if (isset($GLOBALS['fp_temp_degree_major_codes'][$degree_id])) {
  99. return $GLOBALS['fp_temp_degree_major_codes'][$degree_id];
  100. }
  101. $res = db_query("SELECT major_code FROM degrees WHERE degree_id = ?", $degree_id);
  102. $cur = db_fetch_array($res);
  103. $major_code = trim($cur['major_code']);
  104. if ($major_code) {
  105. $GLOBALS['fp_temp_degree_major_codes'][$degree_id] = $major_code;
  106. }
  107. return $major_code;
  108. }
  109. function fp_get_degree_advising_weight($degree_id, $bool_reset_cache = FALSE) {
  110. if ($bool_reset_cache) {
  111. unset($GLOBALS['fp_temp_degree_advising_weights']);
  112. }
  113. // We will cache in a GLOBALS variable, to save lookup time.
  114. if (isset($GLOBALS['fp_temp_degree_advising_weights'][$degree_id])) {
  115. return $GLOBALS['fp_temp_degree_advising_weights'][$degree_id];
  116. }
  117. $res = db_query("SELECT advising_weight FROM degrees WHERE degree_id = ?", $degree_id);
  118. $cur = db_fetch_array($res);
  119. $advising_weight = intval(trim($cur['advising_weight']));
  120. if ($advising_weight) {
  121. $GLOBALS['fp_temp_degree_advising_weights'][$degree_id] = $advising_weight;
  122. }
  123. return $advising_weight;
  124. }
  125. /**
  126. * Quick method to look up title for a degree.
  127. */
  128. function fp_get_degree_title($degree_id, $bool_include_track_title = FALSE, $bool_include_html = TRUE, $bool_use_draft = FALSE, $bool_include_major_code = FALSE) {
  129. // TODO: Check cache.
  130. $dtitle = "";
  131. $track_title = "";
  132. // Still no title? Try to load ANY degree title with this degree's
  133. // major_code.
  134. $table_name = "degrees";
  135. if ($bool_use_draft) {$table_name = "draft_$table_name";}
  136. $res = db_query("SELECT title, major_code FROM $table_name
  137. WHERE degree_id = ?
  138. ORDER BY catalog_year DESC LIMIT 1", $degree_id);
  139. $cur = db_fetch_array($res);
  140. $dtitle = $cur["title"];
  141. $major_code = $cur['major_code'];
  142. $o_major_code = $cur['major_code'];
  143. $catalog_year = $cur['catalog_year'];
  144. if ($bool_include_track_title) {
  145. // Get track title
  146. if (strstr($major_code, "_"))
  147. {
  148. // This means that there is a track. Get all the information
  149. // you can about it.
  150. $temp = explode("_", $major_code);
  151. $track_code = trim($temp[1]);
  152. $major_code = trim($temp[0]);
  153. // The major_code might now have a | at the very end. If so,
  154. // get rid of it.
  155. if (substr($major_code, strlen($major_code)-1, 1) == "|")
  156. {
  157. $major_code = str_replace("|","",$major_code);
  158. }
  159. // Now, look up information on the track.
  160. $table_name = "degree_tracks";
  161. if ($bool_use_draft) {$table_name = "draft_$table_name";}
  162. $res = db_query("SELECT track_title FROM $table_name
  163. WHERE major_code = '?'
  164. AND track_code = '?'
  165. AND catalog_year = '?' ", $major_code, $track_code, $catalog_year);
  166. $cur = db_fetch_array($res);
  167. $track_title = $cur["track_title"];
  168. }
  169. if ($track_title != "") {
  170. if ($bool_include_html) {
  171. $dtitle .= "<span class='level-3-raquo'>&raquo;</span>";
  172. }
  173. $dtitle .= $track_title;
  174. }
  175. }
  176. if ($bool_include_major_code) {
  177. $dtitle .= " [$major_code]";
  178. }
  179. return $dtitle;
  180. }
  181. /**
  182. * Returns the faculty member's name based on the ID provided.
  183. */
  184. function fp_get_faculty_name($cwid) {
  185. $db = get_global_database_handler();
  186. $name = $db->get_faculty_name($cwid);
  187. if (!$name) $name = t("Unknown Advisor");
  188. return $name;
  189. }
  190. /**
  191. * Returns back a user object for this user_id.
  192. * If the user is not found in the users table, it will return NULL.
  193. * If the user_id requested is 0, the anonymous user object is returned.
  194. */
  195. function fp_load_user($user_id) {
  196. $rtn = new stdClass();
  197. if ($user_id == 0) {
  198. // Return the anonymous user.
  199. $rtn->id = 0;
  200. $rtn->name = t("Anonymous");
  201. $rtn->roles = array(1 => "anonymous user");
  202. $rtn->permissions = fp_get_permissions_for_role(1);
  203. return $rtn;
  204. }
  205. $res = db_query("SELECT * FROM users WHERE user_id = '?' ", $user_id);
  206. if (db_num_rows($res) == 0) return NULL;
  207. $cur = db_fetch_object($res);
  208. $rtn->id = $cur->user_id;
  209. $rtn->name = $cur->user_name;
  210. $rtn->f_name = $cur->f_name;
  211. $rtn->l_name = $cur->l_name;
  212. $rtn->email = $cur->email;
  213. $rtn->cwid = $cur->cwid;
  214. $rtn->is_student = (bool) $cur->is_student;
  215. $rtn->is_faculty = (bool) $cur->is_faculty;
  216. $rtn->roles = array();
  217. $rtn->permissions = array();
  218. // Load the user's roles and
  219. $res = db_query("SELECT * FROM user_roles a,
  220. roles c
  221. WHERE a.user_id = '?'
  222. AND a.rid = c.rid", $user_id);
  223. while($cur = db_fetch_array($res)) {
  224. $rtn->roles[$cur["rid"]] = $cur["name"];
  225. }
  226. // Let's make sure we get the authenticated user role as well, #2.
  227. $rtn->roles[2] = "authenticated user";
  228. // Go through each role and add in the permissions for each role.
  229. foreach ($rtn->roles as $rid => $val) {
  230. $perms = fp_get_permissions_for_role($rid);
  231. // Merge the arrays while KEEPING the original's key. So don't
  232. // use array_merge, use the + operator.
  233. $rtn->permissions = $rtn->permissions + $perms;
  234. }
  235. return $rtn;
  236. }
  237. /**
  238. * Look up the user_id based on the the user_name. Returns FALSE if it cannot find it.
  239. *
  240. * @param unknown_type $user_name
  241. */
  242. function db_get_user_id($user_name) {
  243. $user_id = db_result(db_query("SELECT user_id FROM users WHERE user_name = '?' ", $user_name));
  244. if ($user_id) {
  245. return $user_id;
  246. }
  247. return FALSE;
  248. }
  249. function db_get_user_id_from_cwid($cwid, $type = "faculty") {
  250. $type_line = " is_faculty='1' ";
  251. if ($type == "student") {
  252. $type_line = " is_student='1' ";
  253. }
  254. $user_id = db_result(db_query("SELECT user_id FROM users WHERE cwid = '?' AND $type_line ", $cwid));
  255. if ($user_id) {
  256. return $user_id;
  257. }
  258. return FALSE;
  259. }
  260. /**
  261. Return back the codes or records for a student's degrees, based on what is in the
  262. student_degrees table (thanks to system.module), as well as what we get from hooks.
  263. */
  264. function fp_get_student_majors($student_cwid, $bool_return_as_csv = FALSE, $bool_return_as_full_record = FALSE, $perform_join_with_degrees = TRUE, $bool_skip_directives = TRUE, $bool_check_for_allow_dynamic = TRUE) {
  265. $params = array($student_cwid, $bool_return_as_full_record, $perform_join_with_degrees, $bool_skip_directives, $bool_check_for_allow_dynamic);
  266. // Execute hook for this too.
  267. $arr = invoke_hook("fp_get_student_majors", $params);
  268. // Results will appear in an array, with each module as the index.
  269. // Ex:
  270. // system
  271. // MAJOR1 => MAJOR1
  272. // MAJOR2 => MAJOR2
  273. // custom_module
  274. // MAJOR1 => MAJOR1
  275. // XYZ => XYZ
  276. $new_arr = array();
  277. $csv = "";
  278. // Go through our modules, combine the identical ones, then prepare as a possible CSV.
  279. foreach ($arr as $module => $results) {
  280. foreach ($results as $k => $v) {
  281. $new_arr[$k] = $v;
  282. }
  283. }
  284. $rtn = $new_arr;
  285. // Returning as a CSV?
  286. if ($bool_return_as_csv) {
  287. foreach ($new_arr as $k => $v) {
  288. $csv .= $k . ",";
  289. }
  290. $csv = rtrim($csv, ",");
  291. $rtn = $csv;
  292. }
  293. return $rtn;
  294. }
  295. function fp_get_student_name($cwid) {
  296. if ($cwid == 0) {
  297. return t("Anonymous");
  298. }
  299. $db = get_global_database_handler();
  300. $name = $db->get_student_name($cwid);
  301. if (!$name) $name = t("Unknown Student");
  302. return $name;
  303. }
  304. function fp_get_permissions_for_role($rid) {
  305. $rtn = array();
  306. $res = db_query("SELECT pid, perm FROM role_permissions
  307. WHERE rid = '?' ", $rid);
  308. while($cur = db_fetch_array($res)) {
  309. $rtn[$cur["pid"]] = $cur["perm"];
  310. }
  311. return $rtn;
  312. }
  313. /**
  314. * Returns back the first result from a resource_handler.
  315. */
  316. function db_result($res) {
  317. if (is_object($res)) {
  318. return $res->fetchColumn(); // fetches the first value returned.
  319. }
  320. return NULL;
  321. }
  322. function db_insert_id() {
  323. $db = get_global_database_handler();
  324. return $db->db_insert_id();
  325. }
  326. /**
  327. * Return the array from the user_settings table.
  328. *
  329. * @param unknown_type $user_id
  330. */
  331. function db_get_user_settings($user_id) {
  332. $db = get_global_database_handler();
  333. return $db->get_user_settings($user_id);
  334. }
  335. /**
  336. * Return a specific setting's value, based on the var_name given.
  337. *
  338. * @param unknown_type $user_id
  339. * @param unknown_type $var_name
  340. */
  341. function db_get_user_setting($user_id, $var_name = "", $default_value = "") {
  342. $settings = db_get_user_settings($user_id);
  343. $val = @$settings[$var_name];
  344. if (!$val) {
  345. $val = $default_value;
  346. }
  347. return $val;
  348. }
  349. function db_set_user_setting($user_id, $var_name, $value) {
  350. $settings = db_get_user_settings($user_id);
  351. $settings[$var_name] = $value;
  352. $ser = serialize($settings);
  353. db_query("DELETE FROM user_settings WHERE user_id = '?' ", $user_id);
  354. db_query("INSERT INTO user_settings (user_id, settings, posted)
  355. VALUES ('?', '?', '?') ", $user_id, $ser, time());
  356. }
  357. function db_query($query) {
  358. // Capture arguments to this function, to pass along to our $db object.
  359. $args = func_get_args();
  360. array_shift($args);
  361. $db = get_global_database_handler();
  362. $res = $db->db_query($query, $args);
  363. return $res;
  364. }
  365. function db_fetch_array($result_handler) {
  366. $db = get_global_database_handler();
  367. return $db->db_fetch_array($result_handler);
  368. }
  369. function db_fetch_object($result_handler) {
  370. $db = get_global_database_handler();
  371. return $db->db_fetch_object($result_handler);
  372. }
  373. function db_num_rows($result_handler) {
  374. $db = get_global_database_handler();
  375. return $db->db_num_rows($result_handler);
  376. }
  377. function db_affected_rows($result_handler) {
  378. $db = get_global_database_handler();
  379. return $db->db_affected_rows($result_handler);
  380. }
  381. /**
  382. * Returns TRUE if the table specified exists or not.
  383. */
  384. function db_table_exists($table_name) {
  385. $res = db_query("SHOW TABLES LIKE ? ", $table_name);
  386. $cur = db_fetch_array($res);
  387. if ($cur[0] == $table_name) {
  388. return TRUE;
  389. }
  390. return FALSE;
  391. }
  392. /**
  393. * Get a variable from the database. We will first look in our GLOBALS array,
  394. * to see that it hasn't already been retrieved this page load.
  395. *
  396. * @param unknown_type $name
  397. * @param unknown_type $default_value
  398. * @return unknown
  399. */
  400. function variable_get($name, $default_value = "") {
  401. $val = null;
  402. // First, check in our GLOBALS array, like a cache...
  403. if (isset($GLOBALS["fp_system_settings"][$name])) {
  404. $val = $GLOBALS["fp_system_settings"][$name];
  405. }
  406. else {
  407. // Not found-- look in the database for it.
  408. $res = db_query("SELECT value FROM variables
  409. WHERE name = '?' ", $name);
  410. $cur = db_fetch_array($res);
  411. @$val = unserialize($cur["value"]);
  412. // Save back to our cache...
  413. $GLOBALS["fp_system_settings"][$name] = $val;
  414. }
  415. if (!$val) {
  416. $val = $default_value;
  417. }
  418. // We must have this down here again, just in case what got stored in the GLOBALS
  419. // array was this placeholder. This can happen, because the settings file doesn't do
  420. // this check when assembling this variable on page load. It's something that needs
  421. // to be fixed.
  422. if ($val === "BOOLEAN_FALSE_PLACEHOLDER") {
  423. $val = FALSE;
  424. }
  425. if ($val === "NULL_PLACEHOLDER") {
  426. $val = NULL;
  427. }
  428. return $val;
  429. }
  430. /**
  431. * Set a variable value, so we can retrieve it later on.
  432. *
  433. * This will write to our variables database table, as well as store in a cache
  434. * array for quick look-up later.
  435. *
  436. * @param unknown_type $name
  437. * @param unknown_type $value
  438. */
  439. function variable_set($name, $value) {
  440. // Save back to our "cache" GLOBALS array:
  441. $GLOBALS["fp_system_settings"][$name] = $value;
  442. // Boolean FALSE presents unusual problems when we try to tell if it got unserialized correctly.
  443. // We will convert it to a placeholder so we can positively store it.
  444. if ($value === FALSE) {
  445. $value = "BOOLEAN_FALSE_PLACEHOLDER";
  446. }
  447. // Same for NULL value
  448. if ($value === NULL) {
  449. $value = "NULL_PLACEHOLDER";
  450. }
  451. db_query("DELETE FROM variables WHERE name = ?", $name);
  452. db_query("INSERT INTO variables (name, value)
  453. VALUES (?, ?) ", $name, serialize($value));
  454. }
  455. /**
  456. * Re-query the modules table and re-add to our global array.
  457. */
  458. function fp_rebuild_modules_list($reinclude = TRUE) {
  459. unset($GLOBALS["fp_system_settings"]["modules"]);
  460. $res = db_query("SELECT * FROM modules WHERE enabled = 1
  461. ORDER BY weight");
  462. while ($cur = db_fetch_array($res)) {
  463. $GLOBALS["fp_system_settings"]["modules"][$cur["name"]] = $cur;
  464. if ($reinclude) {
  465. include_module($cur["name"], FALSE);
  466. }
  467. }
  468. }
  469. function fp_get_system_settings($force_rebuild = FALSE) {
  470. if ($force_rebuild == FALSE && isset($GLOBALS["fp_system_settings"])) {
  471. return $GLOBALS["fp_system_settings"];
  472. }
  473. // Get all of our settings from the variables table.
  474. $res = db_query("SELECT * FROM variables");
  475. while ($cur = db_fetch_array($res)) {
  476. $name = $cur["name"];
  477. $val = unserialize($cur["value"]);
  478. if ($val == "BOOLEAN_FALSE_PLACEHOLDER") {
  479. $val = FALSE;
  480. }
  481. $settings[$name] = $val;
  482. $GLOBALS["fp_system_settings"][$name] = $val;
  483. }
  484. // Make sure some important settings have _something_ set, or else it could cause
  485. // problems for some modules.
  486. if ($settings["current_catalog_year"] == "") {
  487. $settings["current_catalog_year"] = 2006;
  488. }
  489. if ($settings["earliest_catalog_year"] == "") {
  490. $settings["earliest_catalog_year"] = 2006;
  491. }
  492. $GLOBALS["fp_system_variables"] = $settings;
  493. return $settings;
  494. }