1. Zmiana modułu do połączeń z MySQL z mysql na pdo_mysql.
2. Rozdzielenie tabeli pozycz na dwie: pozycz i pozycz_historia.
3. Wydzielenie pokazywania historii wypożyczeń do klasy gotowe.
4. Zmiany w wielu miejscach w kodzie związane z tabelą pozycz.
5. Poprawki w pliku test.php w związku z w/w zmianami.
6. Dodanie pliku UPGRADE z instrukcją aktualizacji.
2 files added
8 files modified
| | |
| | | |
| | | 1.4 Stable |
| | | - Przeniesienie ustawień skryptu do osobnego pliku (includes/config.php) |
| | | - Zmiana modułu do połączeń z MySQL z mysql na pdo_mysql |
| | | - Rozdzielenie tabeli pozycz na dwie: pozycz i pozycz_historia |
| | | - Wydzielenie pokazywania historii wypożyczeń do klasy gotowe |
| | | - Zmiany w wielu miejscach w kodzie związane z tabelą pozycz |
| | | - Poprawki w pliku test.php w związku z w/w zmianami |
| | | - Dodanie pliku UPGRADE z instrukcją aktualizacji |
| | | |
| | | 1.3 Stable |
| | | - Poprawienie okładek - getimagesize() zwraca IMAGETYPE_XXX, a nie IMG_XXX |
New file |
| | |
| | | ================================================= |
| | | Aktualizacja wersji 1.3 Stable i wcześniejszych |
| | | ================================================= |
| | | |
| | | - zapisz następujące informacje: dane połączenia z bazą danych, |
| | | klucz API LibraryThing |
| | | - wgraj wszystkie zmienione pliki, |
| | | - w pliku /includes/config.php podaj zapisane wcześniej dane, |
| | | - wykonaj w bazie danych poniższe zapytania: |
| | | |
| | | CREATE TABLE `pozycz_historia` ( |
| | | `id` mediumint(8) unsigned NOT NULL, |
| | | `kto` varchar(255) COLLATE utf8_polish_ci NOT NULL, |
| | | `od` int(11) NOT NULL, |
| | | `do` int(11) NOT NULL, |
| | | PRIMARY KEY (`id`,`od`) |
| | | ); |
| | | |
| | | INSERT INTO `pozycz_historia` (`id`, `kto`, `od`, `do`) |
| | | SELECT `id`, `kto`, `od`, `do` FROM `pozycz` WHERE `do` IS NOT NULL; |
| | | DELETE FROM `pozycz` WHERE `do` IS NOT NULL; |
| | | |
| | | ALTER TABLE `pozycz` DROP INDEX `do`; |
| | | ALTER TABLE `pozycz` DROP COLUMN `do`; |
| | | |
| | | ALTER TABLE `pozycz` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`); |
| | |
| | | `ISBN` decimal(13,0) unsigned DEFAULT NULL, |
| | | `ISSN` decimal(13,0) unsigned DEFAULT NULL, |
| | | `regal` char(5) COLLATE utf8_polish_ci DEFAULT NULL, |
| | | `polka` tinyint(4) DEFAULT NULL, |
| | | `rzad` tinyint(4) DEFAULT NULL, |
| | | `wycofana` char(1) COLLATE utf8_polish_ci NOT NULL DEFAULT '0', |
| | | `polka` decimal(2,0) unsigned DEFAULT NULL, |
| | | `rzad` decimal(2,0) unsigned DEFAULT NULL, |
| | | `wycofana` enum('0','1') CHARACTER SET ascii NOT NULL DEFAULT '0', |
| | | `powod` varchar(255) COLLATE utf8_polish_ci DEFAULT NULL, |
| | | PRIMARY KEY (`id`), |
| | | KEY `ISBN` (`ISBN`), |
| | |
| | | `id` mediumint(8) unsigned NOT NULL, |
| | | `kto` varchar(255) COLLATE utf8_polish_ci NOT NULL, |
| | | `od` int(11) NOT NULL, |
| | | PRIMARY KEY (`id`,`od`) |
| | | ); |
| | | |
| | | CREATE TABLE `pozycz_historia` ( |
| | | `id` mediumint(8) unsigned NOT NULL, |
| | | `kto` varchar(255) COLLATE utf8_polish_ci NOT NULL, |
| | | `od` int(11) NOT NULL, |
| | | `do` int(11) DEFAULT NULL, |
| | | PRIMARY KEY (`id`,`od`), |
| | | KEY `do` (`do`) |
| | |
| | | $title = 'Historia wypożyczeń książki'; |
| | | include('./design/top.php'); |
| | | |
| | | $nastrone = 5; |
| | | |
| | | if(ctype_digit($_GET['strona'])) { |
| | | $strona = $nastrone*($strona-1); |
| | | } |
| | | else |
| | | { |
| | | $strona = 0; |
| | | } |
| | | |
| | | gotowe::informacje($_GET['kod']); |
| | | |
| | | $num = db2::num('pozycz', '*', array('id' => $_GET['kod'])); |
| | | $dane = db2::get('pozycz', '*', array('id' => $_GET['kod']), array('do' => 'DESC'), $strona, $nastrone); |
| | | |
| | | echo ' |
| | | |
| | | <table id="bhist"> |
| | | <tr> <th>Pożyczający</th> <th>Od</th> <th>Do</th> </tr> |
| | | '; |
| | | |
| | | foreach($dane as $o) { |
| | | echo '<tr> <td>'.$o['kto'].'</td> <td>'.date('Y-m-d H:i:s', $o['od']).'</td> <td>'.($o['do'] ? date('Y-m-d H:i:s', $o['do']) : '').'</td> </tr>'."\n"; |
| | | } |
| | | |
| | | echo '</table> |
| | | |
| | | <p>'; |
| | | |
| | | for($i=0; $i<$num; $i+=$nastrone) { |
| | | $str = ($i/$nastrone)+1; |
| | | if($i>=$strona && $i<$strona+$nastrone) |
| | | echo '<b> [ '.$str.' ] </b> '; |
| | | else |
| | | echo '<a href="borrow_history.php?kod='.$_GET['kod'].'&strona='.$str.'"> [ '.$str.' ] </a> '; |
| | | } |
| | | |
| | | gotowe::historia($_GET['kod']); |
| | | |
| | | include('./design/bottom.php'); |
| | | ?> |
| | | ?> |
New file |
| | |
| | | <?php |
| | | if(!extension_loaded('pdo')) { |
| | | throw new Exception('Brak rozszerzenia PDO. Skrypt nie będzie działał.'); |
| | | } |
| | | |
| | | if(!extension_loaded('pdo_mysql')) { |
| | | throw new Exception('Brak rozszerzenia PDO MySQL. Skrypt nie będzie działał.'); |
| | | } |
| | | |
| | | class PDOO { |
| | | private static $PDO; |
| | | |
| | | static function Singleton() { |
| | | if(self::$PDO === NULL) { |
| | | self::$PDO = new PDO('mysql:dbname='.config::$db_base.';host='.config::$db_host, |
| | | config::$db_user, config::$db_pass); |
| | | self::$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
| | | self::$PDO->query('SET NAMES utf8'); |
| | | } |
| | | |
| | | return self::$PDO; |
| | | } |
| | | } |
| | | ?> |
| | |
| | | <?php |
| | | if(!extension_loaded('mysql')) { |
| | | error::add('Brak rozszerzenia MySQL. Skrypt nie będzie działał.'); |
| | | } |
| | | |
| | | class sql { |
| | | static $db; |
| | | static $queries = 0; |
| | | |
| | | static $PDO; |
| | | |
| | | static function connect() { |
| | | self::$db = @mysql_connect(config::$db_host, config::$db_user, config::$db_pass); |
| | | if(!self::$db) { |
| | | error::add(mysql_error()); |
| | | } |
| | | |
| | | self::query('SET CHARACTER SET \'UTF8\''); |
| | | self::query('SET NAMES \'UTF8\''); |
| | | |
| | | self::$queries = 0; |
| | | |
| | | if(!@mysql_select_db(config::$db_base)) { |
| | | error::add(mysql_error()); |
| | | } |
| | | } |
| | | |
| | | static function query($q) { |
| | | if(!self::$db) { |
| | | self::connect(); |
| | | if(!self::$PDO) { |
| | | self::$PDO = PDOO::Singleton(); |
| | | } |
| | | |
| | | if(!@mysql_ping(self::$db)) { |
| | | self::connect(); |
| | | } |
| | | |
| | | $r = @mysql_query($q, self::$db); |
| | | |
| | | self::$queries++; |
| | | |
| | | if($r===FALSE) { |
| | | error::add(mysql_error().' '.$q); |
| | | } |
| | | |
| | | return $r; |
| | | return self::$PDO->query($q); |
| | | } |
| | | |
| | | static function fetchonea($q) { |
| | | return mysql_fetch_assoc($q); |
| | | return $q->fetch(PDO::FETCH_ASSOC); |
| | | } |
| | | |
| | | static function fetchone($q) { |
| | | return mysql_fetch_array($q); |
| | | return $q->fetch(PDO::FETCH_ASSOC); |
| | | } |
| | | |
| | | static function fetch($q) { |
| | | while ($entry = mysql_fetch_array($q)) { |
| | | $r[] = $entry; |
| | | } |
| | | |
| | | if(!$r) { |
| | | $r = array(); |
| | | } |
| | | |
| | | return $r; |
| | | return $q->fetchAll(); |
| | | } |
| | | |
| | | static function increment_id() { |
| | | return mysql_insert_id(self::$db); |
| | | if(!self::$PDO) { |
| | | self::$PDO = PDOO::Singleton(); |
| | | } |
| | | |
| | | return self::$PDO->lastInsertId(); |
| | | } |
| | | |
| | | static function affected() { |
| | | return mysql_affected_rows(self::$db); |
| | | static function affected($q = NULL) { |
| | | if($q === NULL) { |
| | | return 'nieznana'; |
| | | } |
| | | |
| | | return $q->rowCount(); |
| | | } |
| | | |
| | | static function escape($q) { |
| | | if(!self::$db) { |
| | | self::connect(); |
| | | if(!self::$PDO) { |
| | | self::$PDO = PDOO::Singleton(); |
| | | } |
| | | |
| | | if(!@mysql_ping(self::$db)) { |
| | | self::connect(); |
| | | } |
| | | |
| | | return mysql_real_escape_string($q, self::$db); |
| | | return self::$PDO->quote($q); |
| | | } |
| | | |
| | | static function close() { |
| | |
| | | continue; |
| | | } |
| | | |
| | | $a[] = '`'.sql::escape($key).'`'; |
| | | $b[] = '\''.sql::escape($value).'\''; |
| | | $a[] = '`'.$key.'`'; |
| | | $b[] = sql::escape($value); |
| | | } |
| | | |
| | | return '('.implode(', ', $a).') VALUES ('.implode(', ', $b).')'; |
| | |
| | | $value = NULL; |
| | | } |
| | | if(is_null($value)) { |
| | | $a[] = '`'.sql::escape($key).'`=NULL'; |
| | | $a[] = '`'.$key.'` IS NULL'; |
| | | } |
| | | else |
| | | { |
| | | $a[] = '`'.sql::escape($key).'`=\''.sql::escape($value).'\''; |
| | | $a[] = '`'.$key.'`='.sql::escape($value); |
| | | } |
| | | } |
| | | |
| | |
| | | } |
| | | else |
| | | { |
| | | $a[] = self::__combine_keyn($key).'=\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn($key).'='.sql::escape($value); |
| | | } |
| | | } |
| | | } |
| | |
| | | $a[] = self::__combine_keyn($key).' IS NULL'; |
| | | } |
| | | elseif(substr($key, -1)=='!') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'!=\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'!='.sql::escape($value); |
| | | } |
| | | elseif($key=='^') { |
| | | $a[] = 'MAX('.self::__combine_keyn($value).')'; |
| | | } |
| | | elseif(substr($key, -2)=='~~') { |
| | | $temp = 'MATCH ('.self::__combine_keyn(substr($key, 0, -2)).') AGAINST (\''.sql::escape($value).'\')'; |
| | | $temp = 'MATCH ('.self::__combine_keyn(substr($key, 0, -2)).') AGAINST ('.sql::escape($value).')'; |
| | | if($revelance) { |
| | | self::$revelance = $temp.' AS `revelance`'; |
| | | } |
| | |
| | | $a[] = $temp; |
| | | } |
| | | elseif(substr($key, -1)=='~') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).' LIKE \''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).' LIKE '.sql::escape($value); |
| | | } |
| | | elseif(substr($key, -2)=='>=') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -2)).'>=\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -2)).'>='.sql::escape($value); |
| | | } |
| | | elseif(substr($key, -2)=='<=') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -2)).'<=\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -2)).'<='.sql::escape($value); |
| | | } |
| | | elseif(substr($key, -1)=='>') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'>\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'>'.sql::escape($value); |
| | | } |
| | | elseif(substr($key, -1)=='<') { |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'<\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn(substr($key, 0, -1)).'<'.sql::escape($value); |
| | | } |
| | | else |
| | | { |
| | | $a[] = self::__combine_keyn($key).'=\''.sql::escape($value).'\''; |
| | | $a[] = self::__combine_keyn($key).'='.sql::escape($value); |
| | | } |
| | | } |
| | | } |
| | |
| | | $as = $value; |
| | | $value = $key; |
| | | } |
| | | return '`'.sql::escape($value).'`'.($as ? ' AS `'.sql::escape($as).'`' : ''); |
| | | return '`'.$value.'`'.($as ? ' AS `'.$as.'`' : ''); |
| | | } |
| | | |
| | | static function __combine_keyn($key) { |
| | | if(!self::$SAFE_MODE_KEY AND strpos($key, '.')!==FALSE) { |
| | | $key = explode('.', $key, 2); |
| | | return '`'.sql::escape($key[0]).'`.`'.sql::escape($key[1]).'`'; |
| | | return '`'.$key[0].'`.`'.$key[1].'`'; |
| | | } |
| | | |
| | | return '`'.sql::escape($key).'`'; |
| | | return '`'.$key.'`'; |
| | | } |
| | | |
| | | static function __combine_table($table) { |
| | | if(!is_array($table) OR self::$SAFE_MODE_TABLE) { |
| | | return '`'.sql::escape($table).'` '; |
| | | return '`'.$table.'` '; |
| | | } |
| | | else |
| | | { |
| | |
| | | } |
| | | |
| | | static function add($table, $keys) { |
| | | sql::query('INSERT INTO `'.sql::escape($table).'` '.self::__combine_insert($keys)); |
| | | sql::query('INSERT INTO `'.$table.'` '.self::__combine_insert($keys)); |
| | | return sql::affected(); |
| | | } |
| | | |
| | | static function edit($table, $keys, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) { |
| | | sql::query('UPDATE `'.sql::escape($table).'` SET '.self::__combine_update($keys).self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop)); |
| | | sql::query('UPDATE `'.$table.'` SET '.self::__combine_update($keys).self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop)); |
| | | return sql::affected(); |
| | | } |
| | | |
| | | static function del($table, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) { |
| | | sql::query('DELETE FROM `'.sql::escape($table).'`'.self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop)); |
| | | sql::query('DELETE FROM `'.$table.'`'.self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop)); |
| | | return sql::affected(); |
| | | } |
| | | |
| | |
| | | return $ord; |
| | | } |
| | | |
| | | static function historia($kod) { |
| | | $st = PDOO::Singleton()->prepare( |
| | | 'SELECT * FROM pozycz_historia WHERE id=?'."\n" |
| | | .'UNION'."\n" |
| | | .'SELECT *, \'\' AS do FROM pozycz WHERE id=? ORDER BY od ASC' |
| | | ); |
| | | $st->execute(array($kod, $kod)); |
| | | $dane = $st->fetchAll(); |
| | | |
| | | $info = '<table id="bhist"> |
| | | <tr> <th>Pożyczający</th> <th>Od</th> <th>Do</th> </tr> |
| | | '; |
| | | |
| | | foreach($dane as $o) { |
| | | $info .= '<tr> <td>'.htmlspecialchars($o['kto']).'</td> <td>'.date('Y-m-d H:i:s', $o['od']).'</td> <td>'.($o['do'] ? date('Y-m-d H:i:s', $o['do']) : '').'</td> </tr>'."\n"; |
| | | } |
| | | |
| | | echo $info.'</table>'; |
| | | } |
| | | |
| | | static function informacje($kod, $dane=NULL) { |
| | | if(is_null($dane)) { |
| | | $dane = ksiazki::szukaj_KOD($kod); |
| | |
| | | } |
| | | |
| | | static function cache_add($kod, &$dane) { |
| | | if($dane['od2']) { |
| | | $dane['od'] = $dane['od2']; |
| | | unset($dane['od2']); |
| | | } |
| | | self::$cache[(int)$kod] = $dane; |
| | | } |
| | | |
| | |
| | | } |
| | | |
| | | static function cache_update($kod) { |
| | | $dane = db2::escape_data(sql::fetchone(sql::query('SELECT *, (SELECT MAX(`od`) FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id`) as `od2`, (SELECT `do` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `do`, (SELECT `kto` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `kto` FROM `ksiazki` WHERE `id`=\''.sql::escape($kod).'\''))); |
| | | $dane = db2::escape_data(sql::fetchone(sql::query('SELECT `ksiazki`.*, `pozycz`.`od`, `pozycz`.`kto` FROM `ksiazki` LEFT OUTER JOIN `pozycz` ON `pozycz`.`id`=`ksiazki`.`id` WHERE `ksiazki`.`id`='.sql::escape($kod)))); |
| | | self::cache_add($kod, $dane); |
| | | } |
| | | } |
| | |
| | | else |
| | | { |
| | | if($dane['do']) { |
| | | $num = db2::num('pozycz', 'id', array('do' => NULL)); |
| | | if($num==0) { |
| | | $num = db2::num('pozycz', 'id'); |
| | | if($num == 0) { |
| | | $ret = array(); |
| | | } |
| | | else |
| | | { |
| | | $ret = db2::get(array('pozycz', array('J', 'ksiazki', 'USING', 'id')), '*', array('do' => NULL), $order, $start, $limit); |
| | | $ret = db2::get(array('pozycz', array('J', 'ksiazki', 'USING', 'id')), '*', NULL, $order, $start, $limit); |
| | | } |
| | | } |
| | | else |
| | | { |
| | | $num = db2::num('ksiazki', 'id', $where); |
| | | if($num==0) { |
| | | if($num == 0) { |
| | | $ret = array(); |
| | | } |
| | | else |
| | | { |
| | | $where = db2::__combine_where($where, TRUE); |
| | | $ret = db2::escape_data(sql::fetch(sql::query('SELECT *, (SELECT MAX(`od`) FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id`) as `od2`, (SELECT `do` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `do`, (SELECT `kto` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `kto`'.(db2::revelance() ? ', '.db2::$revelance : '').' FROM `ksiazki`'.$where.db2::__combine_order($order, TRUE).db2::__combine_limit($start, $limit)))); |
| | | $ret = db2::escape_data(sql::fetch(sql::query('SELECT `ksiazki`.*, `pozycz`.`od`, `pozycz`.`kto` FROM `ksiazki` LEFT OUTER JOIN `pozycz` ON `pozycz`.`id`=`ksiazki`.`id`'.(db2::revelance() ? ', '.db2::$revelance : '').' '.$where.db2::__combine_order($order, TRUE).db2::__combine_limit($start, $limit)))); |
| | | } |
| | | } |
| | | |
| | |
| | | error::add('Książka nie jest wypożyczona!'); |
| | | } |
| | | |
| | | db2::edit('pozycz', array('do' => time()), array('id' => $kod, 'do' => NULL)); |
| | | ksiazki::cache_clear($kod); |
| | | $st = PDOO::Singleton()->prepare('INSERT INTO pozycz_historia (id, kto, od, do) |
| | | SELECT id, kto, od, ? FROM pozycz WHERE id=?'); |
| | | $st->execute(array(time(), $kod)); |
| | | |
| | | $st = PDOO::Singleton()->prepare('DELETE FROM pozycz WHERE id=?'); |
| | | $st->execute(array($kod)); |
| | | |
| | | ksiazki::cache_update($kod); |
| | | } |
| | | |
| | | static function pozyczona($kod) { |
| | | $ksiazka = ksiazki::szukaj_KOD($kod); |
| | | if($ksiazka['do']!==NULL OR $ksiazka['od']===NULL) { |
| | | return FALSE; |
| | | } |
| | | else |
| | | { |
| | | return $ksiazka['kto']; |
| | | } |
| | | return $ksiazka['od'] != NULL; |
| | | } |
| | | } |
| | | ?> |
| | |
| | | |
| | | <tr> <th class="head" colspan="2">Baza danych</th> </tr> |
| | | <tr> <th>Ustawienia bazy<br />(/includes/config.php)</th> <td><?php echo NT; ?></td> </tr> |
| | | <tr> <th>Rozszerzenie MySQL</th> <td><?php |
| | | if(!extension_loaded('mysql')) { |
| | | <tr> <th>Rozszerzenie PDO</th> <td><?php |
| | | if(!extension_loaded('pdo')) { |
| | | echo FAIL; |
| | | $mysql = FALSE; |
| | | } |
| | | else echo OK; |
| | | ?></td> </tr> |
| | | <tr> <th>Rozszerzenie PDO MySQL</th> <td><?php |
| | | if(!extension_loaded('pdo_mysql')) { |
| | | echo FAIL; |
| | | $mysql = FALSE; |
| | | } |