database.lib.php 60 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This is the main database library for Chamilo.
  5. * Include/require it in your code to use its functionality.
  6. * Because this library contains all the basic database calls, it could be
  7. * replaced by another library for say, PostgreSQL, to actually use Chamilo
  8. * with another database (this is not ready yet because a lot of code still
  9. * uses the MySQL database functions extensively).
  10. *
  11. * If trying to replicate the database layer, don't forget to look for "sql"
  12. * named functions in main_api.lib.php
  13. *
  14. * @package chamilo.library
  15. */
  16. /**
  17. * Database class definition
  18. * @package chamilo.database
  19. */
  20. class Database {
  21. /* Variable use only in the installation process to log errors. See the Database::query function */
  22. static $log_queries = false;
  23. /*
  24. Accessor methods
  25. Usually, you won't need these directly but instead
  26. rely on of the get_xxx_table methods.
  27. */
  28. /**
  29. * Returns the name of the main database.
  30. */
  31. public static function get_main_database() {
  32. global $_configuration;
  33. return $_configuration['main_database'];
  34. }
  35. /**
  36. * Returns the name of the statistics database.
  37. * @todo use main_database
  38. */
  39. public static function get_statistic_database() {
  40. global $_configuration;
  41. return $_configuration['main_database'];
  42. }
  43. /**
  44. * Returns the name of the database where all the personal stuff of the user is stored
  45. * @todo use main_database
  46. */
  47. public static function get_user_personal_database() {
  48. global $_configuration;
  49. return $_configuration['main_database'];
  50. }
  51. /**
  52. * Returns the name of the current course database.
  53. * @return mixed Glued database name of false if undefined
  54. */
  55. public static function get_current_course_database() {
  56. $course_info = api_get_course_info();
  57. if (empty($course_info['dbName'])) {
  58. return false;
  59. }
  60. return $course_info['dbName'];
  61. }
  62. /**
  63. * Returns the glued name of the current course database.
  64. * @return mixed Glued database name of false if undefined
  65. */
  66. public static function get_current_course_glued_database() {
  67. $course_info = api_get_course_info();
  68. if (empty($course_info['dbNameGlu'])) {
  69. return false;
  70. }
  71. return $course_info['dbNameGlu'];
  72. }
  73. /**
  74. * The glue is the string needed between database and table.
  75. * The trick is: in multiple databases, this is a period (with backticks).
  76. * In single database, this can be e.g. an underscore so we just fake
  77. * there are multiple databases and the code can be written independent
  78. * of the single / multiple database setting.
  79. */
  80. public static function get_database_glue() {
  81. global $_configuration;
  82. return $_configuration['db_glue'];
  83. }
  84. /**
  85. * Returns the database prefix.
  86. * All created COURSE databases are prefixed with this string.
  87. *
  88. * TIP: This can be convenient if you have multiple system installations
  89. * on the same physical server.
  90. */
  91. public static function get_database_name_prefix() {
  92. global $_configuration;
  93. return $_configuration['db_prefix'];
  94. }
  95. /**
  96. * Returns the course table prefix for single database.
  97. * Not certain exactly when this is used.
  98. * Do research.
  99. * It's used in local.inc.php.
  100. */
  101. public static function get_course_table_prefix() {
  102. global $_configuration;
  103. return $_configuration['table_prefix'];
  104. }
  105. /*
  106. Table name methods
  107. Use these methods to get table names for queries,
  108. instead of constructing them yourself.
  109. Backticks automatically surround the result,
  110. e.g. COURSE_NAME.link
  111. so the queries can look cleaner.
  112. Example:
  113. $table = Database::get_course_table(TABLE_DOCUMENT);
  114. $sql_query = "SELECT * FROM $table WHERE $condition";
  115. $sql_result = Database::query($sql_query);
  116. $result = Database::fetch_array($sql_result);
  117. */
  118. /**
  119. * A more generic method than the other get_main_xxx_table methods,
  120. * This one returns the correct complete name of any table of the main
  121. * database of which you pass the short name as a parameter.
  122. * Please, define table names as constants in this library and use them
  123. * instead of directly using magic words in your tool code.
  124. *
  125. * @param string $short_table_name, the name of the table
  126. */
  127. public static function get_main_table($short_table_name) {
  128. return self::format_table_name(
  129. self::get_main_database(),
  130. $short_table_name);
  131. }
  132. /**
  133. * A more generic method than the older get_course_xxx_table methods,
  134. * This one can return the correct complete name of any course table of
  135. * which you pass the short name as a parameter.
  136. * Please, define table names as constants in this library and use them
  137. * instead of directly using magic words in your tool code.
  138. *
  139. * @param string $short_table_name, the name of the table
  140. * @param string $database_name, optional, name of the course database
  141. * - if you don't specify this, you work on the current course.
  142. */
  143. public static function get_course_table($short_table_name, $extra = null) {
  144. //forces fatal errors so we can debug more easily
  145. if (!empty($extra)) {
  146. var_dump($extra);
  147. //@todo remove this
  148. echo "<h3>Dev Message: get_course_table() doesn't have a 2nd parameter</h3>";
  149. //exit;
  150. }
  151. return self::format_table_name(self::get_main_database(), DB_COURSE_PREFIX.$short_table_name);
  152. //return self::format_glued_course_table_name(self::fix_database_parameter($database_name), $short_table_name);
  153. }
  154. /**
  155. * This generic method returns the correct and complete name of any
  156. * statistic table of which you pass the short name as a parameter.
  157. * Please, define table names as constants in this library and use them
  158. * instead of directly using magic words in your tool code.
  159. *
  160. * @param string $short_table_name, the name of the table
  161. */
  162. public static function get_statistic_table($short_table_name) {
  163. return self::format_table_name(self::get_statistic_database(), $short_table_name);
  164. }
  165. /**
  166. * This generic method returns the correct and complete name of any user
  167. * table of which you pass the short name as a parameter. Please, define
  168. * table names as constants in this library and use them instead of directly
  169. * using magic words in your tool code.
  170. *
  171. * @param string $short_table_name, the name of the table
  172. */
  173. public static function get_user_personal_table($short_table_name) {
  174. return self::format_table_name(self::get_user_personal_database(), $short_table_name);
  175. }
  176. public static function get_course_chat_connected_table($database_name = '') {
  177. return self::format_glued_course_table_name(self::fix_database_parameter($database_name), TABLE_CHAT_CONNECTED);
  178. }
  179. /*
  180. Query methods
  181. These methods execute a query and return the result(s).
  182. */
  183. /**
  184. * @return a list (array) of all courses.
  185. * @todo shouldn't this be in the course.lib.php script?
  186. */
  187. public static function get_course_list() {
  188. $table = self::get_main_table(TABLE_MAIN_COURSE);
  189. return self::store_result(self::query("SELECT *, id as real_id FROM $table"));
  190. }
  191. /**
  192. * Returns an array with all database fields for the specified course.
  193. *
  194. * @param string The real (system) course code (main course table ID)
  195. * @todo shouldn't this be in the course.lib.php script?
  196. */
  197. public static function get_course_info($course_code) {
  198. $course_code = self::escape_string($course_code);
  199. $table = self::get_main_table(TABLE_MAIN_COURSE);
  200. $result = self::generate_abstract_course_field_names(
  201. self::fetch_array(self::query("SELECT *, id as real_id FROM $table WHERE code = '$course_code'")));
  202. return $result === false ? array('db_name' => '') : $result;
  203. }
  204. /**
  205. * Gets user details from the "user" table
  206. * @param $user_id (integer): the id of the user
  207. * @return $user_info (array): user_id, lname, fname, username, email, ...
  208. * @author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
  209. * @author Roan Embrechts, first version + converted to Database API
  210. * @version 30 September 2004
  211. * @desc find all the information about a specified user. Without parameter this is the current user.
  212. * @todo shouldn't this be in the user.lib.php script?
  213. */
  214. public static function get_user_info_from_id($user_id = '') {
  215. if (empty($user_id)) {
  216. return $GLOBALS['_user'];
  217. }
  218. $table = self::get_main_table(TABLE_MAIN_USER);
  219. $user_id = self::escape_string($user_id);
  220. return self::generate_abstract_user_field_names(
  221. self::fetch_array(self::query("SELECT * FROM $table WHERE user_id = '$user_id'")));
  222. }
  223. /**
  224. * Returns course code from a given gradebook category's id
  225. * @param int Category ID
  226. * @return string Course code
  227. * @todo move this function in a gradebook-related library
  228. */
  229. public static function get_course_by_category($category_id) {
  230. $category_id = intval($category_id);
  231. $info = self::fetch_array(self::query('SELECT course_code FROM '.self::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY).' WHERE id='.$category_id), 'ASSOC');
  232. return $info ? $info['course_code'] : false;
  233. }
  234. /**
  235. * This method creates an abstraction layer between database field names
  236. * and field names expected in code.
  237. *
  238. * This approach helps when changing database names.
  239. * It's also useful now to get rid of the 'franglais'.
  240. *
  241. * @todo add more array entries to abstract course info from field names
  242. * @author Roan Embrechts
  243. *
  244. * @todo What's the use of this method. I think this is better removed.
  245. * There should be consistency in the variable names and the
  246. * use throughout the scripts
  247. * for the database name we should consistently use or db_name
  248. * or database (db_name probably being the better one)
  249. */
  250. public static function generate_abstract_course_field_names($result_array) {
  251. $visual_code = isset($result_array['visual_code']) ? $result_array['visual_code'] : null;
  252. $code = isset($result_array['code']) ? $result_array['code'] : null;
  253. $title = isset($result_array['title']) ? $result_array['title'] : null;
  254. $db_name = isset($result_array['db_name']) ? $result_array['db_name'] : null;
  255. $category_code = isset($result_array['category_code']) ? $result_array['category_code'] : null;
  256. $result_array['official_code'] = $visual_code;
  257. $result_array['visual_code'] = $visual_code;
  258. $result_array['real_code'] = $code;
  259. $result_array['system_code'] = $code;
  260. $result_array['title'] = $title;
  261. $result_array['database'] = $db_name;
  262. $result_array['faculty'] = $category_code;
  263. //$result_array['directory'] = $result_array['directory'];
  264. /*
  265. still to do: (info taken from local.inc.php)
  266. $_course['id' ] = $cData['cours_id' ]; //auto-assigned integer
  267. $_course['name' ] = $cData['title' ];
  268. $_course['official_code'] = $cData['visual_code' ]; // use in echo
  269. $_course['sysCode' ] = $cData['code' ]; // use as key in db
  270. $_course['path' ] = $cData['directory' ]; // use as key in path
  271. $_course['dbName' ] = $cData['db_name' ]; // use as key in db list
  272. $_course['dbNameGlu' ] = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
  273. $_course['titular' ] = $cData['tutor_name' ];
  274. $_course['language' ] = $cData['course_language' ];
  275. $_course['extLink' ]['url' ] = $cData['department_url' ];
  276. $_course['extLink' ]['name'] = $cData['department_name'];
  277. $_course['categoryCode'] = $cData['faCode' ];
  278. $_course['categoryName'] = $cData['faName' ];
  279. $_course['visibility' ] = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
  280. $_course['registrationAllowed'] = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
  281. */
  282. return $result_array;
  283. }
  284. /**
  285. * This method creates an abstraction layer between database field names
  286. * and field names expected in code.
  287. *
  288. * This helps when changing database names.
  289. * It's also useful now to get rid of the 'franglais'.
  290. *
  291. * @todo add more array entries to abstract user info from field names
  292. * @author Roan Embrechts
  293. * @author Patrick Cool
  294. *
  295. * @todo what's the use of this function. I think this is better removed.
  296. * There should be consistency in the variable names and the use throughout the scripts
  297. */
  298. public static function generate_abstract_user_field_names($result_array) {
  299. $result_array['firstName'] = $result_array['firstname'];
  300. $result_array['lastName'] = $result_array['lastname'];
  301. $result_array['mail'] = $result_array['email'];
  302. #$result_array['picture_uri'] = $result_array['picture_uri'];
  303. #$result_array ['user_id'] = $result_array['user_id'];
  304. return $result_array;
  305. }
  306. /**
  307. * Counts the number of rows in a table
  308. * @param string $table The table of which the rows should be counted
  309. * @return int The number of rows in the given table.
  310. */
  311. public static function count_rows($table) {
  312. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table")); //
  313. return $obj->n;
  314. }
  315. /*
  316. An intermediate API-layer between the system and the dabase server.
  317. */
  318. /**
  319. * Returns the number of affected rows in the last database operation.
  320. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  321. * @return int Returns the number of affected rows on success, and -1 if the last query failed.
  322. */
  323. public static function affected_rows($connection = null) {
  324. return self::use_default_connection($connection) ? mysql_affected_rows() : mysql_affected_rows($connection);
  325. }
  326. /**
  327. * Closes non-persistent database connection.
  328. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  329. * @return bool Returns TRUE on success or FALSE on failure.
  330. */
  331. public static function close($connection = null) {
  332. return self::use_default_connection($connection) ? mysql_close() : mysql_close($connection);
  333. }
  334. /**
  335. * Opens a connection to a database server.
  336. * @param array $parameters (optional) An array that contains the necessary parameters for accessing the server.
  337. * @return resource/boolean Returns a database connection on success or FALSE on failure.
  338. * Note: Currently the array could contain MySQL-specific parameters:
  339. * $parameters['server'], $parameters['username'], $parameters['password'],
  340. * $parameters['new_link'], $parameters['client_flags'], $parameters['persistent'].
  341. * For details see documentation about the functions mysql_connect() and mysql_pconnect().
  342. * @link http://php.net/manual/en/function.mysql-connect.php
  343. * @link http://php.net/manual/en/function.mysql-pconnect.php
  344. */
  345. public static function connect($parameters = array()) {
  346. // A MySQL-specific implementation.
  347. if (!isset($parameters['server'])) {
  348. $parameters['server'] = @ini_get('mysql.default_host');
  349. if (empty($parameters['server'])) {
  350. $parameters['server'] = 'localhost:3306';
  351. }
  352. }
  353. if (!isset($parameters['username'])) {
  354. $parameters['username'] = @ini_get('mysql.default_user');
  355. }
  356. if (!isset($parameters['password'])) {
  357. $parameters['password'] = @ini_get('mysql.default_password');
  358. }
  359. if (!isset($parameters['new_link'])) {
  360. $parameters['new_link'] = false;
  361. }
  362. if (!isset($parameters['client_flags'])) {
  363. $parameters['client_flags'] = 0;
  364. }
  365. $persistent = isset($parameters['persistent']) ? $parameters['persistent'] : null;
  366. $server = isset($parameters['server']) ? $parameters['server'] : null;
  367. $username = isset($parameters['username']) ? $parameters['username'] : null;
  368. $password = isset($parameters['password']) ? $parameters['password'] : null;
  369. $client_flag = isset($parameters['client_flags']) ? $parameters['client_flags'] : null;
  370. $new_link = isset($parameters['new_link']) ? $parameters['new_link'] : null;
  371. $client_flags = isset($parameters['client_flags']) ? $parameters['client_flags'] : null;
  372. return $persistent
  373. ? mysql_pconnect($server, $username, $password, $client_flags)
  374. : mysql_connect($server, $username, $password, $new_link, $client_flags);
  375. }
  376. /**
  377. * Returns error number from the last operation done on the database server.
  378. * @param resource $connection (optional) The database server connection,
  379. * for detailed description see the method query().
  380. * @return int Returns the error number from the last database (operation, or 0 (zero) if no error occurred.
  381. */
  382. public static function errno($connection = null) {
  383. return self::use_default_connection($connection) ? mysql_errno() : mysql_errno($connection);
  384. }
  385. /**
  386. * Returns error text from the last operation done on the database server.
  387. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  388. * @return string Returns the error text from the last database operation, or '' (empty string) if no error occurred.
  389. */
  390. public static function error($connection = null) {
  391. return self::use_default_connection($connection) ? mysql_error() : mysql_error($connection);
  392. }
  393. /**
  394. * Escapes a string to insert into the database as text
  395. * @param string The string to escape
  396. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  397. * @return string The escaped string
  398. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  399. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  400. */
  401. public static function escape_string($string, $connection = null) {
  402. return get_magic_quotes_gpc()
  403. ? (self::use_default_connection($connection)
  404. ? mysql_real_escape_string(stripslashes($string))
  405. : mysql_real_escape_string(stripslashes($string), $connection))
  406. : (self::use_default_connection($connection)
  407. ? mysql_real_escape_string($string)
  408. : mysql_real_escape_string($string, $connection));
  409. }
  410. /**
  411. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  412. * @param resource The result from a call to sql_query (e.g. Database::query)
  413. * @param string Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
  414. * @return array Array of results as returned by php
  415. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  416. */
  417. public static function fetch_array($result, $option = 'BOTH') {
  418. if ($result === false) { return array(); }
  419. return $option == 'ASSOC' ? mysql_fetch_array($result, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array($result, MYSQL_NUM) : mysql_fetch_array($result));
  420. }
  421. /**
  422. * Gets an associative array from a SQL result (as returned by Database::query).
  423. * This method is equivalent to calling Database::fetch_array() with 'ASSOC' value for the optional second parameter.
  424. * @param resource $result The result from a call to sql_query (e.g. Database::query).
  425. * @return array Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.
  426. */
  427. public static function fetch_assoc($result) {
  428. return mysql_fetch_assoc($result);
  429. }
  430. /**
  431. * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
  432. * @param resource The result from a call to sql_query (e.g. Database::query)
  433. * @param string Optional class name to instanciate
  434. * @param array Optional array of parameters
  435. * @return object Object of class StdClass or the required class, containing the query result row
  436. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  437. */
  438. public static function fetch_object($result, $class = null, $params = null) {
  439. return !empty($class) ? (is_array($params) ? mysql_fetch_object($result, $class, $params) : mysql_fetch_object($result, $class)) : mysql_fetch_object($result);
  440. }
  441. /**
  442. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  443. * @param resource The result from a call to sql_query (see Database::query()).
  444. * @return array Array of results as returned by php (mysql_fetch_row)
  445. */
  446. public static function fetch_row($result) {
  447. return mysql_fetch_row($result);
  448. }
  449. /**
  450. * Frees all the memory associated with the provided result identifier.
  451. * @return bool Returns TRUE on success or FALSE on failure.
  452. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  453. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  454. */
  455. public static function free_result($result) {
  456. return mysql_free_result($result);
  457. }
  458. /**
  459. * Returns the database client library version.
  460. * @return strung Returns a string that represents the client library version.
  461. */
  462. public static function get_client_info() {
  463. return mysql_get_client_info();
  464. }
  465. /**
  466. * Returns a list of databases created on the server. The list may contain all of the
  467. * available database names or filtered database names by using a pattern.
  468. * @param string $pattern (optional) A pattern for filtering database names as if it was needed for the SQL's LIKE clause, for example 'chamilo_%'.
  469. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  470. * @return array Returns in an array the retrieved list of database names.
  471. */
  472. public static function get_databases($pattern = '', $connection = null) {
  473. $result = array();
  474. $query_result = Database::query(!empty($pattern) ? "SHOW DATABASES LIKE '".self::escape_string($pattern, $connection)."'" : "SHOW DATABASES", $connection);
  475. while ($row = Database::fetch_row($query_result)) {
  476. $result[] = $row[0];
  477. }
  478. return $result;
  479. }
  480. /**
  481. * Returns a list of the fields that a given table contains. The list may contain all of the available field names or filtered field names by using a pattern.
  482. * By using a special option, this method is able to return an indexed list of fields' properties, where field names are keys.
  483. * @param string $table This is the examined table.
  484. * @param string $pattern (optional) A pattern for filtering field names as if it was needed for the SQL's LIKE clause, for example 'column_%'.
  485. * @param string $database (optional) The name of the targeted database. If it is omited, the current database is assumed, see Database::select_db().
  486. * @param bool $including_properties (optional) When this option is true, the returned result has the followong format:
  487. * array(field_name_1 => array(0 => property_1, 1 => property_2, ...), fieald_name_2 => array(0 => property_1, ...), ...)
  488. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  489. * @return array Returns in an array the retrieved list of field names.
  490. */
  491. public static function get_fields($table, $pattern = '', $database = '', $including_properties = false, $connection = null) {
  492. $result = array();
  493. $query = "SHOW COLUMNS FROM `".self::escape_string($table, $connection)."`";
  494. if (!empty($database)) {
  495. $query .= " FROM `".self::escape_string($database, $connection)."`";
  496. }
  497. if (!empty($pattern)) {
  498. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  499. }
  500. $query_result = Database::query($query, $connection);
  501. if ($including_properties) {
  502. // Making an indexed list of the fields and their properties.
  503. while ($row = Database::fetch_row($query_result)) {
  504. $result[$row[0]] = $row;
  505. }
  506. } else {
  507. // Making a plain, flat list.
  508. while ($row = Database::fetch_row($query_result)) {
  509. $result[] = $row[0];
  510. }
  511. }
  512. return $result;
  513. }
  514. /**
  515. * Returns information about the type of the current connection and the server host name.
  516. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  517. * @return string/boolean Returns string data on success or FALSE on failure.
  518. */
  519. public static function get_host_info($connection = null) {
  520. return self::use_default_connection($connection) ? mysql_get_host_info() : mysql_get_host_info($connection);
  521. }
  522. /**
  523. * Retrieves database client/server protocol version.
  524. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  525. * @return int/boolean Returns the protocol version on success or FALSE on failure.
  526. */
  527. public static function get_proto_info($connection = null) {
  528. return self::use_default_connection($connection) ? mysql_get_proto_info() : mysql_get_proto_info($connection);
  529. }
  530. /**
  531. * Retrieves the database server version.
  532. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  533. * @return string/boolean Returns the MySQL server version on success or FALSE on failure.
  534. */
  535. public static function get_server_info($connection = null) {
  536. return self::use_default_connection($connection) ? mysql_get_server_info() : mysql_get_server_info($connection);
  537. }
  538. /**
  539. * Returns a list of tables within a database. The list may contain all of the
  540. * available table names or filtered table names by using a pattern.
  541. * @param string $database (optional) The name of the examined database. If it is omited, the current database is assumed, see Database::select_db().
  542. * @param string $pattern (optional) A pattern for filtering table names as if it was needed for the SQL's LIKE clause, for example 'access_%'.
  543. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  544. * @return array Returns in an array the retrieved list of table names.
  545. */
  546. public static function get_tables($database = '', $pattern = '', $connection = null) {
  547. $result = array();
  548. $query = "SHOW TABLES";
  549. if (!empty($database)) {
  550. $query .= " FROM `".self::escape_string($database, $connection)."`";
  551. }
  552. if (!empty($pattern)) {
  553. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  554. }
  555. $query_result = Database::query($query, $connection);
  556. while ($row = Database::fetch_row($query_result)) {
  557. $result[] = $row[0];
  558. }
  559. return $result;
  560. }
  561. /**
  562. * Gets the ID of the last item inserted into the database
  563. * This should be updated to use ADODB at some point
  564. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  565. * @return int The last ID as returned by the DB function
  566. */
  567. public static function insert_id($connection = null) {
  568. return self::use_default_connection($connection) ? mysql_insert_id() : mysql_insert_id($connection);
  569. }
  570. /**
  571. * Gets the number of rows from the last query result - help achieving database independence
  572. * @param resource The result
  573. * @return integer The number of rows contained in this result
  574. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  575. **/
  576. public static function num_rows($result) {
  577. return is_resource($result) ? mysql_num_rows($result) : false;
  578. }
  579. /**
  580. * Acts as the relative *_result() function of most DB drivers and fetches a
  581. * specific line and a field
  582. * @param resource The database resource to get data from
  583. * @param integer The row number
  584. * @param string Optional field name or number
  585. * @return mixed One cell of the result, or FALSE on error
  586. */
  587. public static function result($resource, $row, $field = '') {
  588. return self::num_rows($resource) > 0 ? (!empty($field) ? mysql_result($resource, $row, $field) : mysql_result($resource, $row)) : null;
  589. }
  590. /**
  591. * This method returns a resource
  592. * Documentation has been added by Arthur Portugal
  593. * Some adaptations have been implemented by Ivan Tcholakov, 2009, 2010
  594. * @author Olivier Brouckaert
  595. * @param string $query The SQL query
  596. * @param resource $connection (optional) The database server (MySQL) connection.
  597. * If it is not specified, the connection opened by mysql_connect() is assumed.
  598. * If no connection is found, the server will try to create one as if mysql_connect() was called with no arguments.
  599. * If no connection is found or established, an E_WARNING level error is generated.
  600. * @param string $file (optional) On error it shows the file in which the error has been trigerred (use the "magic" constant __FILE__ as input parameter)
  601. * @param string $line (optional) On error it shows the line in which the error has been trigerred (use the "magic" constant __LINE__ as input parameter)
  602. * @return resource The returned result from the query
  603. * Note: The parameter $connection could be skipped. Here are examples of this method usage:
  604. * Database::query($query);
  605. * $result = Database::query($query);
  606. * Database::query($query, $connection);
  607. * $result = Database::query($query, $connection);
  608. * The following ways for calling this method are obsolete:
  609. * Database::query($query, __FILE__, __LINE__);
  610. * $result = Database::query($query, __FILE__, __LINE__);
  611. * Database::query($query, $connection, __FILE__, __LINE__);
  612. * $result = Database::query($query, $connection, __FILE__, __LINE__);
  613. */
  614. public static function query($query, $connection = null, $file = null, $line = null) {
  615. $use_default_connection = self::use_default_connection($connection);
  616. if ($use_default_connection) {
  617. // Let us do parameter shifting, thus the method would be similar
  618. // (in regard to parameter order) to the original function mysql_query().
  619. $line = $file;
  620. $file = $connection;
  621. $connection = null;
  622. }
  623. //@todo remove this before the stable release
  624. //Check if the table contains a c_ (means a course id)
  625. if (api_get_setting('server_type')==='test' && strpos($query, 'c_')) {
  626. //Check if the table contains inner joins
  627. if (
  628. strpos($query, 'assoc_handle') === false &&
  629. strpos($query, 'olpc_peru_filter') === false &&
  630. strpos($query, 'allow_public_certificates') === false &&
  631. strpos($query, 'DROP TABLE IF EXISTS') === false &&
  632. strpos($query, 'thematic_advance') === false &&
  633. strpos($query, 'thematic_plan') === false &&
  634. strpos($query, 'track_c_countries') === false &&
  635. strpos($query, 'track_c_os') === false &&
  636. strpos($query, 'track_c_providers') === false &&
  637. strpos($query, 'track_c_referers') === false &&
  638. strpos($query, 'track_c_browsers') === false &&
  639. strpos($query, 'settings_current') === false &&
  640. strpos($query, 'branch_sync') === false &&
  641. strpos($query, 'branch_sync_log') === false &&
  642. strpos($query, 'branch_sync_log') === false &&
  643. strpos($query, 'branch_transaction') === false &&
  644. strpos($query, 'branch_transaction_status') === false &&
  645. strpos($query, 'dokeos_classic_2D') === false &&
  646. strpos($query, 'cosmic_campus') === false &&
  647. strpos($query, 'static_') === false &&
  648. strpos($query, 'public_admin') === false &&
  649. strpos($query, 'chamilo_electric_blue') === false &&
  650. strpos($query, 'wcag_anysurfer_public_pages') === false &&
  651. strpos($query, 'specific_field') === false &&
  652. strpos($query, 'down_doc_path') === false &&
  653. strpos($query, 'INNER JOIN') === false &&
  654. strpos($query, 'inner join') === false &&
  655. strpos($query, 'left join') === false &&
  656. strpos($query, 'LEFT JOIN') === false &&
  657. strpos($query, 'insert') === false &&
  658. strpos($query, 'INSERT') === false &&
  659. strpos($query, 'ALTER') === false &&
  660. strpos($query, 'alter') === false &&
  661. strpos($query, 'c_id') === false &&
  662. strpos($query, 'create table') === false &&
  663. strpos($query, 'CREATE TABLE') === false &&
  664. strpos($query, 'AUTO_INCREMENT') === false
  665. ) {
  666. //@todo remove this
  667. echo '<pre>';
  668. $message = '<h4>Dev message: please add the c_id field in this query or report this error in support.chamilo.org </h4>';
  669. $message .= $query;
  670. echo $message;
  671. echo '</pre>';
  672. //error_log($message);
  673. }
  674. }
  675. if (!($result = $use_default_connection ? @mysql_query($query) : @mysql_query($query, $connection))) {
  676. $backtrace = debug_backtrace(); // Retrieving information about the caller statement.
  677. if (isset($backtrace[0])) {
  678. $caller = & $backtrace[0];
  679. } else {
  680. $caller = array();
  681. }
  682. if (isset($backtrace[1])) {
  683. $owner = & $backtrace[1];
  684. } else {
  685. $owner = array();
  686. }
  687. if (empty($file)) {
  688. $file = $caller['file'];
  689. }
  690. if (empty($line) && $line !== false) {
  691. $line = $caller['line'];
  692. }
  693. $type = isset($owner['type']) ? $owner['type'] : null;
  694. $function = $owner['function'];
  695. $class = isset($owner['class']) ? $owner['class'] : null;
  696. $server_type = api_get_setting('server_type');
  697. if (!empty($line) && !empty($server_type) && $server_type != 'production') {
  698. $info = '<pre>' .
  699. '<strong>DATABASE ERROR #'.self::errno($connection).':</strong><br /> ' .
  700. self::remove_XSS(self::error($connection)) . '<br />' .
  701. '<strong>QUERY :</strong><br /> ' .
  702. self::remove_XSS($query) . '<br />' .
  703. '<strong>FILE :</strong><br /> ' .
  704. (empty($file) ? ' unknown ' : $file) . '<br />' .
  705. '<strong>LINE :</strong><br /> ' .
  706. (empty($line) ? ' unknown ' : $line) . '<br />';
  707. if (empty($type)) {
  708. if (!empty($function)) {
  709. $info .= '<strong>FUNCTION :</strong><br /> ' . $function;
  710. }
  711. } else {
  712. if (!empty($class) && !empty($function)) {
  713. $info .= '<strong>CLASS :</strong><br /> ' . $class . '<br />';
  714. $info .= '<strong>METHOD :</strong><br /> ' . $function;
  715. }
  716. }
  717. $info .= '</pre>';
  718. echo $info;
  719. }
  720. if (isset(self::$log_queries) && self::$log_queries) {
  721. error_log("---------------- SQL error ---------------- ");
  722. error_log($query);
  723. error_log('error #'.self::errno($connection));
  724. error_log('error: '.self::error($connection));
  725. $info = 'FILE: ' .(empty($file) ? ' unknown ' : $file);
  726. $info .= ' +'.(empty($line) ? ' unknown ' : $line);
  727. error_log($info);
  728. if (empty($type)) {
  729. if (!empty($function)) {
  730. $info = 'FUNCTION: ' . $function;
  731. error_log($info);
  732. }
  733. } else {
  734. if (!empty($class) && !empty($function)) {
  735. $info = 'CLASS: ' . $class.' METHOD: '.$function;
  736. error_log($info);
  737. }
  738. }
  739. error_log("---------------- end ----------------");
  740. }
  741. }
  742. return $result;
  743. }
  744. /**
  745. * Selects a database.
  746. * @param string $database_name The name of the database that is to be selected.
  747. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  748. * @return bool Returns TRUE on success or FALSE on failure.
  749. */
  750. public static function select_db($database_name, $connection = null) {
  751. return self::use_default_connection($connection) ? mysql_select_db($database_name) : mysql_select_db($database_name, $connection);
  752. }
  753. /**
  754. * Stores a query result into an array.
  755. *
  756. * @author Olivier Brouckaert
  757. * @param resource $result - the return value of the query
  758. * @param option BOTH, ASSOC, or NUM
  759. * @return array - the value returned by the query
  760. */
  761. public static function store_result($result, $option = 'BOTH') {
  762. $array = array();
  763. if ($result !== false) { // For isolation from database engine's behaviour.
  764. while ($row = self::fetch_array($result, $option)) {
  765. $array[] = $row;
  766. }
  767. }
  768. return $array;
  769. }
  770. /*
  771. Encodings and collations supported by MySQL database server
  772. */
  773. /**
  774. * Checks whether a given encoding is supported by the database server.
  775. * @param string $encoding The encoding (a system conventional id, for example 'UTF-8') to be checked.
  776. * @return bool Returns a boolean value as a check-result.
  777. * @author Ivan Tcholakov
  778. */
  779. public static function is_encoding_supported($encoding) {
  780. static $supported = array();
  781. if (!isset($supported[$encoding])) {
  782. $supported[$encoding] = false;
  783. if (strlen($db_encoding = self::to_db_encoding($encoding)) > 0) {
  784. if (self::num_rows(self::query("SHOW CHARACTER SET WHERE Charset = '".self::escape_string($db_encoding)."';")) > 0) {
  785. $supported[$encoding] = true;
  786. }
  787. }
  788. }
  789. return $supported[$encoding];
  790. }
  791. /**
  792. * Constructs a SQL clause about default character set and default collation for newly created databases and tables.
  793. * Example: Database::make_charset_clause('UTF-8', 'bulgarian') returns
  794. * DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`
  795. * @param string $encoding (optional) The default database/table encoding (a system conventional id) to be used.
  796. * @param string $language (optional) Language (a system conventional id) used for choosing language sensitive collation (if it is possible).
  797. * @return string Returns the constructed SQL clause or empty string if $encoding is not correct or is not supported.
  798. * @author Ivan Tcholakov
  799. */
  800. public static function make_charset_clause($encoding = null, $language = null) {
  801. if (empty($encoding)) {
  802. $encoding = api_get_system_encoding();
  803. }
  804. if (empty($language)) {
  805. $language = api_get_interface_language();
  806. }
  807. $charset_clause = '';
  808. if (self::is_encoding_supported($encoding)) {
  809. $db_encoding = Database::to_db_encoding($encoding);
  810. $charset_clause .= " DEFAULT CHARACTER SET `".$db_encoding."`";
  811. $db_collation = Database::to_db_collation($encoding, $language);
  812. if (!empty($db_collation)) {
  813. $charset_clause .= " DEFAULT COLLATE `".$db_collation."`";
  814. }
  815. }
  816. return $charset_clause;
  817. }
  818. /**
  819. * Converts an encoding identificator to MySQL-specific encoding identifictor,
  820. * i.e. 'UTF-8' --> 'utf8'.
  821. * @param string $encoding The conventional encoding identificator.
  822. * @return string Returns the corresponding MySQL-specific encoding identificator if any, otherwise returns NULL.
  823. * @author Ivan Tcholakov
  824. */
  825. public static function to_db_encoding($encoding) {
  826. static $result = array();
  827. if (!isset($result[$encoding])) {
  828. $result[$encoding] = null;
  829. $encoding_map = & self::get_db_encoding_map();
  830. foreach ($encoding_map as $key => $value) {
  831. if (api_equal_encodings($encoding, $key)) {
  832. $result[$encoding] = $value;
  833. break;
  834. }
  835. }
  836. }
  837. return $result[$encoding];
  838. }
  839. /**
  840. * Converts a MySQL-specific encoding identifictor to conventional encoding identificator,
  841. * i.e. 'utf8' --> 'UTF-8'.
  842. * @param string $encoding The MySQL-specific encoding identificator.
  843. * @return string Returns the corresponding conventional encoding identificator if any, otherwise returns NULL.
  844. * @author Ivan Tcholakov
  845. */
  846. public static function from_db_encoding($db_encoding) {
  847. static $result = array();
  848. if (!isset($result[$db_encoding])) {
  849. $result[$db_encoding] = null;
  850. $encoding_map = & self::get_db_encoding_map();
  851. foreach ($encoding_map as $key => $value) {
  852. if (strtolower($db_encoding) == $value) {
  853. $result[$db_encoding] = $key;
  854. break;
  855. }
  856. }
  857. }
  858. return $result[$db_encoding];
  859. }
  860. /**
  861. * Chooses the default MySQL-specific collation from given encoding and language.
  862. * @param string $encoding A conventional encoding id, i.e. 'UTF-8'
  863. * @param string $language (optional) A conventional for the system language id, i.e. 'bulgarian'. If it is empty, the chosen collation is the default server value corresponding to the given encoding.
  864. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  865. * @author Ivan Tcholakov
  866. */
  867. public static function to_db_collation($encoding, $language = null) {
  868. static $result = array();
  869. if (!isset($result[$encoding][$language])) {
  870. $result[$encoding][$language] = null;
  871. if (self::is_encoding_supported($encoding)) {
  872. $db_encoding = self::to_db_encoding($encoding);
  873. if (!empty($language)) {
  874. $lang = api_purify_language_id($language);
  875. $res = self::check_db_collation($db_encoding, $lang);
  876. if (empty($res)) {
  877. $db_collation_map = & self::get_db_collation_map();
  878. if (isset($db_collation_map[$lang])) {
  879. $res = self::check_db_collation($db_encoding, $db_collation_map[$lang]);
  880. }
  881. }
  882. if (empty($res)) {
  883. $res = self::check_db_collation($db_encoding, null);
  884. }
  885. $result[$encoding][$language] = $res;
  886. } else {
  887. $result[$encoding][$language] = self::check_db_collation($db_encoding, null);
  888. }
  889. }
  890. }
  891. return $result[$encoding][$language];
  892. }
  893. /*
  894. Private methods
  895. You should not access these from outside the class
  896. No effort is made to keep the names / results the same.
  897. */
  898. /**
  899. * Glues a course database.
  900. * glue format from local.inc.php.
  901. */
  902. private static function glue_course_database_name($database_name) {
  903. return self::get_course_table_prefix().$database_name.self::get_database_glue();
  904. }
  905. /**
  906. * @param string $database_name, can be empty to use current course db
  907. *
  908. * @return the glued parameter if it is not empty,
  909. * or the current course database (glued) if the parameter is empty.
  910. */
  911. private static function fix_database_parameter($database_name) {
  912. if (empty($database_name)) {
  913. $course_info = api_get_course_info();
  914. return $course_info['dbNameGlu'];
  915. }
  916. return self::glue_course_database_name($database_name);
  917. }
  918. /**
  919. * Structures a course database and table name to ready them
  920. * for querying. The course database parameter is considered glued:
  921. * e.g. COURSE001`.`
  922. */
  923. private static function format_glued_course_table_name($database_name_with_glue, $table) {
  924. return '`'.$database_name_with_glue.$table.'`';
  925. }
  926. /**
  927. * Structures a database and table name to ready them
  928. * for querying. The database parameter is considered not glued,
  929. * just plain e.g. COURSE001
  930. */
  931. private static function format_table_name($database, $table) {
  932. global $_configuration;
  933. /*if ($_configuration['single_database']) {
  934. $table_name = '`'.$database.'`.`'.$table.'`';
  935. } else {*/
  936. $table_name = '`'.$database.$_configuration['db_glue'].$table.'`';
  937. //}
  938. return $table_name;
  939. }
  940. /**
  941. * This private method is to be used by the other methods in this class for
  942. * checking whether the input parameter $connection actually has been provided.
  943. * If the input parameter connection is not a resource or if it is not FALSE (in case of error)
  944. * then the default opened connection should be used by the called method.
  945. * @param resource/boolean $connection The checked parameter $connection.
  946. * @return boolean TRUE means that calling method should use the default connection.
  947. * FALSE means that (valid) parameter $connection has been provided and it should be used.
  948. */
  949. private static function use_default_connection($connection) {
  950. return !is_resource($connection) && $connection !== false;
  951. }
  952. /**
  953. * This private method tackles the XSS injections. It is similar to Security::remove_XSS() and works always,
  954. * including the time of initialization when the class Security has not been loaded yet.
  955. * @param string The input variable to be filtered from XSS, in this class it is expected to be a string.
  956. * @return string Returns the filtered string as a result.
  957. */
  958. private static function remove_XSS(& $var) {
  959. return class_exists('Security') ? Security::remove_XSS($var) : @htmlspecialchars($var, ENT_QUOTES, api_get_system_encoding());
  960. }
  961. /**
  962. * This private method encapsulates a table with relations between
  963. * conventional and MuSQL-specific encoding identificators.
  964. * @author Ivan Tcholakov
  965. */
  966. private static function & get_db_encoding_map() {
  967. static $encoding_map = array(
  968. 'ARMSCII-8' => 'armscii8',
  969. 'BIG5' => 'big5',
  970. 'BINARY' => 'binary',
  971. 'CP866' => 'cp866',
  972. 'EUC-JP' => 'ujis',
  973. 'EUC-KR' => 'euckr',
  974. 'GB2312' => 'gb2312',
  975. 'GBK' => 'gbk',
  976. 'ISO-8859-1' => 'latin1',
  977. 'ISO-8859-2' => 'latin2',
  978. 'ISO-8859-7' => 'greek',
  979. 'ISO-8859-8' => 'hebrew',
  980. 'ISO-8859-9' => 'latin5',
  981. 'ISO-8859-13' => 'latin7',
  982. 'ISO-8859-15' => 'latin1',
  983. 'KOI8-R' => 'koi8r',
  984. 'KOI8-U' => 'koi8u',
  985. 'SHIFT-JIS' => 'sjis',
  986. 'TIS-620' => 'tis620',
  987. 'US-ASCII' => 'ascii',
  988. 'UTF-8' => 'utf8',
  989. 'WINDOWS-1250' => 'cp1250',
  990. 'WINDOWS-1251' => 'cp1251',
  991. 'WINDOWS-1252' => 'latin1',
  992. 'WINDOWS-1256' => 'cp1256',
  993. 'WINDOWS-1257' => 'cp1257'
  994. );
  995. return $encoding_map;
  996. }
  997. /**
  998. * A helper language id translation table for choosing some collations.
  999. * @author Ivan Tcholakov
  1000. */
  1001. private static function & get_db_collation_map() {
  1002. static $db_collation_map = array(
  1003. 'german' => 'german2',
  1004. 'simpl_chinese' => 'chinese',
  1005. 'trad_chinese' => 'chinese',
  1006. 'turkce' => 'turkish'
  1007. );
  1008. return $db_collation_map;
  1009. }
  1010. /**
  1011. * Constructs a MySQL-specific collation and checks whether it is supported by the database server.
  1012. * @param string $db_encoding A MySQL-specific encoding id, i.e. 'utf8'
  1013. * @param string $language A MySQL-compatible language id, i.e. 'bulgarian'
  1014. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  1015. * @author Ivan Tcholakov
  1016. */
  1017. private static function check_db_collation($db_encoding, $language) {
  1018. if (empty($db_encoding)) {
  1019. return null;
  1020. }
  1021. if (empty($language)) {
  1022. $result = self::fetch_array(self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."' AND `Default` = 'Yes';"), 'NUM');
  1023. return $result ? $result[0] : null;
  1024. }
  1025. $collation = $db_encoding.'_'.$language.'_ci';
  1026. $query_result = self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."';");
  1027. while ($result = self::fetch_array($query_result, 'NUM')) {
  1028. if ($result[0] == $collation) {
  1029. return $collation;
  1030. }
  1031. }
  1032. return null;
  1033. }
  1034. /*
  1035. New useful DB functions
  1036. */
  1037. /**
  1038. * Experimental useful database insert
  1039. * @todo lot of stuff to do here
  1040. */
  1041. public static function insert($table_name, $attributes, $show_query = false) {
  1042. if (empty($attributes) || empty($table_name)) {
  1043. return false;
  1044. }
  1045. $filtred_attributes = array();
  1046. foreach ($attributes as $key => $value) {
  1047. $filtred_attributes[$key] = "'".self::escape_string($value)."'";
  1048. }
  1049. $params = array_keys($filtred_attributes); //@todo check if the field exists in the table we should use a describe of that table
  1050. $values = array_values($filtred_attributes);
  1051. if (!empty($params) && !empty($values)) {
  1052. $sql = 'INSERT INTO '.$table_name.' ('.implode(', ',$params).') VALUES ('.implode(', ',$values).')';
  1053. $result = self::query($sql);
  1054. //error_log($sql);
  1055. if ($show_query) {
  1056. //var_dump($sql);
  1057. error_log($sql);
  1058. }
  1059. if (!$result) {
  1060. error_log("Error in query: $sql");
  1061. }
  1062. return self::get_last_insert_id();
  1063. }
  1064. return false;
  1065. }
  1066. /**
  1067. * Experimental useful database finder
  1068. * @todo lot of stuff to do here
  1069. * @todo known issues, it doesn't work when using LIKE conditions
  1070. * @example array('where'=> array('course_code LIKE "?%"'))
  1071. * @example array('where'=> array('type = ? AND category = ?' => array('setting', 'Plugins'))
  1072. * @example array('where'=> array('name = "Julio" AND lastname = "montoya"))
  1073. */
  1074. public static function select($columns, $table_name, $conditions = array(), $type_result = 'all', $option = 'ASSOC') {
  1075. $conditions = self::parse_conditions($conditions);
  1076. //@todo we could do a describe here to check the columns ...
  1077. $clean_columns = '';
  1078. if (is_array($columns)) {
  1079. $clean_columns = implode(',', $columns);
  1080. } else {
  1081. if ($columns == '*') {
  1082. $clean_columns = '*';
  1083. } else {
  1084. $clean_columns = (string)$columns;
  1085. }
  1086. }
  1087. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  1088. //var_dump($sql);
  1089. $result = self::query($sql);
  1090. $array = array();
  1091. if ($type_result == 'all') {
  1092. while ($row = self::fetch_array($result, $option)) {
  1093. if (isset($row['id'])) {
  1094. $array[$row['id']] = $row;
  1095. } else {
  1096. $array[] = $row;
  1097. }
  1098. }
  1099. } else {
  1100. $array = self::fetch_array($result, $option);
  1101. }
  1102. return $array;
  1103. }
  1104. /**
  1105. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'))
  1106. * @todo known issues, it doesn't work when using LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  1107. * @param array
  1108. * @todo lot of stuff to do here
  1109. */
  1110. static function parse_conditions($conditions) {
  1111. if (empty($conditions)) {
  1112. return '';
  1113. }
  1114. $return_value = $where_return = '';
  1115. foreach ($conditions as $type_condition => $condition_data) {
  1116. if ($condition_data == false) {
  1117. continue;
  1118. }
  1119. $type_condition = strtolower($type_condition);
  1120. switch ($type_condition) {
  1121. case 'where':
  1122. foreach ($condition_data as $condition => $value_array) {
  1123. if (is_array($value_array)) {
  1124. $clean_values = array();
  1125. foreach($value_array as $item) {
  1126. $item = Database::escape_string($item);
  1127. $clean_values[]= $item;
  1128. }
  1129. } else {
  1130. $value_array = Database::escape_string($value_array);
  1131. $clean_values = $value_array;
  1132. }
  1133. if (!empty($condition) && $clean_values != '') {
  1134. $condition = str_replace('%',"'@percentage@'", $condition); //replace "%"
  1135. $condition = str_replace("'?'","%s", $condition);
  1136. $condition = str_replace("?","%s", $condition);
  1137. $condition = str_replace("@%s@","@-@", $condition);
  1138. $condition = str_replace("%s","'%s'", $condition);
  1139. $condition = str_replace("@-@","@%s@", $condition);
  1140. //Treat conditons as string
  1141. $condition = vsprintf($condition, $clean_values);
  1142. $condition = str_replace('@percentage@','%', $condition); //replace "%"
  1143. $where_return .= $condition;
  1144. }
  1145. }
  1146. if (!empty($where_return)) {
  1147. $return_value = " WHERE $where_return" ;
  1148. }
  1149. break;
  1150. case 'order':
  1151. $order_array = $condition_data;
  1152. if (!empty($order_array)) {
  1153. // 'order' => 'id desc, name desc'
  1154. $order_array = self::escape_string($order_array);
  1155. $new_order_array = explode(',', $order_array);
  1156. $temp_value = array();
  1157. foreach($new_order_array as $element) {
  1158. $element = explode(' ', $element);
  1159. $element = array_filter($element);
  1160. $element = array_values($element);
  1161. if (!empty($element[1])) {
  1162. $element[1] = strtolower($element[1]);
  1163. $order = 'DESC';
  1164. if (in_array($element[1], array('desc', 'asc'))) {
  1165. $order = $element[1];
  1166. }
  1167. $temp_value[]= $element[0].' '.$order.' ';
  1168. } else {
  1169. //by default DESC
  1170. $temp_value[]= $element[0].' DESC ';
  1171. }
  1172. }
  1173. if (!empty($temp_value)) {
  1174. $return_value .= ' ORDER BY '.implode(', ', $temp_value);
  1175. } else {
  1176. //$return_value .= '';
  1177. }
  1178. }
  1179. break;
  1180. case 'limit':
  1181. $limit_array = explode(',', $condition_data);
  1182. if (!empty($limit_array)) {
  1183. if (count($limit_array) > 1) {
  1184. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  1185. } else {
  1186. $return_value .= ' LIMIT '.intval($limit_array[0]);
  1187. }
  1188. }
  1189. break;
  1190. }
  1191. }
  1192. return $return_value;
  1193. }
  1194. public static function parse_where_conditions($coditions) {
  1195. return self::parse_conditions(array('where'=>$coditions));
  1196. }
  1197. /**
  1198. * Experimental useful database update
  1199. * @todo lot of stuff to do here
  1200. */
  1201. public static function delete($table_name, $where_conditions, $show_query = false) {
  1202. $result = false;
  1203. $where_return = self::parse_where_conditions($where_conditions);
  1204. $sql = "DELETE FROM $table_name $where_return ";
  1205. if ($show_query) { echo $sql; echo '<br />'; }
  1206. $result = self::query($sql);
  1207. $affected_rows = self::affected_rows();
  1208. //@todo should return affected_rows for
  1209. return $affected_rows;
  1210. }
  1211. /**
  1212. * Experimental useful database update
  1213. * @param string table name use Database::get_main_table
  1214. * @param array array with values to updates, keys are the fields in the database: Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  1215. * @param array where conditions i.e array('id = ?' =>'4')
  1216. * @todo lot of stuff to do here
  1217. */
  1218. public static function update($table_name, $attributes, $where_conditions = array(), $show_query = false) {
  1219. if (!empty($table_name) && !empty($attributes)) {
  1220. $update_sql = '';
  1221. //Cleaning attributes
  1222. $count = 1;
  1223. foreach ($attributes as $key=>$value) {
  1224. if (!is_array($value))
  1225. $value = self::escape_string($value);
  1226. $update_sql .= "$key = '$value' ";
  1227. if ($count < count($attributes)) {
  1228. $update_sql.=', ';
  1229. }
  1230. $count++;
  1231. }
  1232. if (!empty($update_sql)) {
  1233. //Parsing and cleaning the where conditions
  1234. $where_return = self::parse_where_conditions($where_conditions);
  1235. $sql = "UPDATE $table_name SET $update_sql $where_return ";
  1236. if ($show_query) { var_dump($sql); }
  1237. $result = self::query($sql);
  1238. $affected_rows = self::affected_rows();
  1239. return $affected_rows;
  1240. }
  1241. }
  1242. return false;
  1243. }
  1244. /*
  1245. DEPRECATED METHODS
  1246. */
  1247. /**
  1248. * @deprecated Use api_get_language_isocode($language) instead.
  1249. */
  1250. public static function get_language_isocode($language) {
  1251. return api_get_language_isocode($language);
  1252. }
  1253. /**
  1254. * @deprecated Use Database::insert_id() instead.
  1255. */
  1256. public static function get_last_insert_id() {
  1257. return mysql_insert_id();
  1258. }
  1259. }