<?php
/*
* sqlite3 dbed v1.15
*
* made anno 2009 by hide@address.com
*
* v1.01 (25.01.2009)
* - saved as utf-8 and added utf-8 encoding into html template
*
* v1.02 (26.01.2009)
* - fixed add action and errormessages with this operation
*
* v1.03 (27.01.2009)
* - added paginating into view action (30 entries per page), configurable via ini-file
* - added different backgrounds with css onto view action
*
* v1.04 (28.01.2009)
* - added "defaulttable" ini-variable
*
* v1.05 (02.02.2009)
* - added "back" buttons into add and edit pages
* - added "search" action
* - added "search" link onto "view" page
* - fixed "raw" action
*
* v1.06 (04.02.2009)
* - littlebit optimized "search" (removed duplicates)
* - added "search_results" ini setting, which allows to change results output format - horisontally or vertically
*
* v1.07 (06.02.2009)
* - added "list_results" ini setting
* - fixed bug with search, also added field names into results
* - "raw sql" is in menu only for users with certain permissions
* - enchanced index-page
* - added textarea for TEXT and VARCHAR(200) > fieldtypes on add and edit pages
*
* v1.08 (07.02.2009)
* - added new ini setting 'show_default_table' which shows defaulttable (if defined)
* - added default table onto do_index() index page with "add" and "search" links
* - replaced ul/li lists with tables on do_view page
*
* v1.09 (08.02.2009)
* - changed "back" buttons with javascript actions on "add" and "search" pages
* - changed structure action-function from "do_browse" to "do_structure"
*
* v1.10 (10.02.2009)
* - added database backup
* - added global submenu
* - added id-field into editpage (not changeable)
* - added some comments
*
* v1.11 (11.02.2009)
* - changed index-page to show count of rows of default table
* - better view for raw-sql page
* - added select listbox with sql-query templates onto "raw-sql" page
*
* v1.12 (14.02.2009)
* - added results pages to add and edit actions, where is possible to view saved data
*
* v1.13 (13.09.2009) by Walter :-)
* - improved layout and design several pages
* - removed search_results and list_results from ini and script, back to one standard presentation
*
* v1.14 (14.09.2009)
* - made mainmenu and submenus depending from user-permissions
*
* v1.15 (16.09.2009)
* - added edit and delete buttons after adding data into tables page
**/
// configuration file
define('file_ini', 'dbed.ini.php');
$ini = array();
#if(get_magic_quotes_gpc())
# $_POST = array_map('stripslashes', $_POST);
// read config file if it exists
if(is_file(file_ini))
$ini = parse_ini_file(file_ini, 1);
// if config settings not found gives error
if(empty($ini))
exit("can't find ini-file with configuration settings :(");
// start with sessions
session_start();
// set charset to utf-8
header('Content-type: text/html; Charset: UTF-8');
// logout, if isset "?logout" get variable
if(isset($_REQUEST['logout']))
{
session_destroy();
header('Location: '.$_SERVER['SCRIPT_NAME']);
die();
}
$menu = '';
// login
if(empty($_SESSION['user']))
{
if(!empty($_POST) && isset($ini['users'][$_POST['user']]) && strcasecmp($_POST['pass'], $ini['users'][$_POST['user']])==0)
{
$_SESSION['user'] = $_POST['user'];
$path = $_SERVER['SCRIPT_NAME'];
if(isset($ini['defaulttable']) && $ini['show_default_table'])
$path .= '?a=view&tbl='.$ini['defaulttable'];
header('Location: '.$path);
}
echo parse_tpl(get_tpl('html'), array('menu'=>'','body'=>get_tpl('login')));
die();
}
else
{
// making menu
$rawlink = "";
if(is_permitted($_SESSION['user'], 'raw'))
$rawlink = ' <input type="button" value="raw sql" onclick="window.location.href=\'?a=raw\'"> ';
$backuplink = "";
if(is_permitted($_SESSION['user'], 'backup'))
$backuplink = ' <input type="button" value="backup" onclick="window.location.href=\'?a=backup\'"> ';
$menu = parse_tpl(get_tpl('menu'), array('rawlink'=>$rawlink, 'backuplink'=>$backuplink));
// adding submenu
$menu .= parse_tpl(get_tpl('submenu'), array('tablename'=>$ini['defaulttable']));
}
// create a SQLite3 database file with PDO and return a database handle (Object Oriented)
try
{
$dbHandle = new PDO('sqlite:'.$ini['dbname']);
}
catch(PDOException $exception)
{
die($exception->getMessage());
}
$actions = array('index','raw','list','view','structure','add','edit','drop','delete','search', 'backup');
$action = (isset($_GET['a']) && in_array($_GET['a'], $actions)) ? $_GET['a'] : $actions[0];
// check if user is permitted to do certain action
if(is_permitted($_SESSION['user'], $action))
{
// if yes, then make function name from get-parameter
$func = 'do_' . $action;
// call function which generates html
$html = $func(&$dbHandle);
// echo it
echo parse_tpl(get_tpl('html'), array('body'=>$html, 'menu'=>$menu));
}
else
{
exit('action '.htmlspecialchars($action).' not permitted!');
}
// default page
function do_index($db)
{
$t = $GLOBALS['ini']['defaulttable'];
$d = $GLOBALS['ini']['dbname'];
$last_modified = filemtime("$d");
$day = date("j", $last_modified);
$month = date("n", $last_modified);
$year = date("Y", $last_modified);
$hour = date("H", $last_modified);
$minute = date("i", $last_modified);
$result = $db->query('SELECT COUNT(*) AS t FROM '.$GLOBALS['ini']['defaulttable']);
$total_rows = $result->fetch(PDO::FETCH_ASSOC);
$o = '';
$o .= '<br><table cellpadding="0" cellspacing="10">';
$o .= '<tr><td align="right">User:</td><td>'. htmlspecialchars($_SESSION['user']) .'</td></tr>';
$o .= '<tr><td align="right">Database:</td><td>'. $GLOBALS['ini']['dbname'] .'</td></tr>';
$o .= '<tr><td align="right">Filesize:</td><td>'. filesize($d) .' bytes</td></tr>';
$o .= '<tr><td align="right">Last update:</td><td>'.$day.'-'.$month.'-'.$year.' '.$hour.':'.$minute.'</td></tr>';
$o .= '<tr><td align="right">Active table:</td><td>'. $GLOBALS['ini']['defaulttable'] .'</td></tr>';
$o .= '<tr><td align="right">Records:</td><td>'. intval($total_rows['t']) .'</td></tr>';
$o .= '</table><br>';
return $o;
}
// deletes table from database
function do_drop($db)
{
$query='drop table '.strip_tags($_GET['tbl']);
$db->query($query);
header('Location:?');
}
// shows all tables with view, search and structure links
function do_list($db)
{
$query='select name from sqlite_master where type="table"';
$result = $db->query($query);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
if(empty($rows))
{
return 'there are no tables yet';
}
else
{
$o = '<table cellpadding="10" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
foreach($rows as $row)
{
$o .= '<tr><td valign="top" style="border: 1px solid #adc6c8">'.$row['name'].'</td><td valign="top" style="border: 1px solid #adc6c8"><a href="?a=view&tbl='.$row['name'].'">view</a> | <a href="?a=search&tbl='.$row['name'].'">search</a> | <a href="?a=structure&tbl='.$row['name'].'">structure</a></td></tr>';
}
$o .= '</table>';
return $o;
}
}
// lists data from table
function do_view($db)
{
$o = '';
$perpage = (isset($_REQUEST['perpage'])) ? $_REQUEST['perpage'] : $GLOBALS['ini']['itemsperpage'];
$sth = $db->prepare('SELECT COUNT(*) AS total FROM '.$_REQUEST['tbl']);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);
$pager = pager($result['total'], $perpage, 'page', $_SERVER['SCRIPT_NAME'].'?a=view&tbl='.$_REQUEST['tbl'].'&page=__CURRENT_PAGE__', '<b>%u</b>', 20);
$q='SELECT * FROM '.strip_tags($_GET['tbl']).' LIMIT '.$pager['limit'];
$result = $db->query($q);
if($db->errorCode()<>0)
{
$o .= 'err...'.var_export($db->errorInfo(),1);
}
else
{
$o .= '<p>'hide@address.com(" - ", $pager['navbar']).'</p>';
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $f=>$row)
{
$first = array_shift($g=$row);
$o .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
foreach($row as $key=>$it)
{
if(strlen($it))
$o .= sprintf('<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">%s</td><td width="650" style="border: 1px solid #adc6c8">%s</td></tr>', $key, $it);
}
if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
{
$o .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
if(is_permitted($_SESSION['user'],'edit'))
{
$o .= '<a href="?a=edit&tbl='.$_GET['tbl'].'&id='.$first.'">edit</a>';
}
if(is_permitted($_SESSION['user'],'delete'))
{
$o .= ' | <a href="?a=delete&tbl='.$_GET['tbl'].'&id='.$first.'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
}
$o .= ' </td></tr>';
}
$o .= '</table><br>';
}
$o .= '<p>'hide@address.com(" - ", $pager['navbar']).'</p>';
}
return $o;
}
// record editing in database
function do_edit($db)
{
// first we search for primary key from table structure
$query='PRAGMA table_info('.esc($_GET['tbl']).')';
$result = $db->query($query);
$rowsa = $result->fetchAll(PDO::FETCH_ASSOC);
$id = false;
foreach($rowsa as $r)
{
if($r['pk'])
$id = $r['name'];
}
if(count($_POST))
{
$fields = array_keys($_POST['f']);
$values = array_values($_POST['f']);
$q = "UPDATE ".strip_tags($_GET['tbl'])." SET ".a_eq_b($fields, $values)." WHERE ".$id.'='.$_GET['id'];
$res = $db->query($q);
if($res->errorCode()<>'0000')
{
$msg = 'There was error: <pre>'.$res->errorCode().print_r($db->errorInfo(), 1).'</pre>';
echo $q . $msg;
}
else
{
$msg = '<p><b>Successfully updated!</b></p>';
$msg .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">id</td><td width="650" style="border: 1px solid #adc6c8">'.$_GET['id'].'</td></tr>';
foreach($fields as $k=>$value)
{
$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">'.$value.'</td><td width="650" style="border: 1px solid #adc6c8">'.$_POST['f'][$value].'</td></tr>';
}
if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
{
$msg .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
if(is_permitted($_SESSION['user'],'edit'))
{
$msg .= '<a href="?a=edit&tbl='.strip_tags($_GET['tbl']).'&id='.$_GET['id'].'">edit</a>';
}
if(is_permitted($_SESSION['user'],'delete'))
{
$msg .= ' | <a href="?a=delete&tbl='.strip_tags($_GET['tbl']).'&id='.$_GET['id'].'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
}
$msg .= ' </td></tr>';
}
$msg .= '</table>';
}
return $msg;
header('Location: ?a=view&tbl='.$_GET['tbl']);
die();
}
else
{
$q2 = 'SELECT * FROM '.$_GET['tbl'].' WHERE '.$id.'='.esc(intval($_GET['id']));
$result = $db->query($q2);
$rows = $result->fetch(PDO::FETCH_ASSOC);
}
$o .= '<form action="?a=edit&tbl='.htmlspecialchars($_GET['tbl']).'&'.$id.'='.$_GET['id'].'" method="post">';
if(empty($result))
{
$o .= 'no fields in table';
}
else
{
$o .= "<p>Edit record in table <b>'".htmlspecialchars($_GET['tbl'])."'</b></p>";
$o .= '<table cellpadding="0" cellspacing="0" bgcolor="#f7f9f9" style="border: 1px solid #adc6c8; border-spacing: 10px 10px; padding: 10px 15px;">';
$cnt=-1;
foreach($rows as $f=>$row)
{
$cnt++;
$i = $f==$id ? 'p' : 'f';
$value = isset($_POST['f']) ? $_POST['f'][$row['name']] : $row;
if($i == 'p')
{
$o .= '<tr><td align="right">'.$id.'</td><td>'.$_GET['id'].'</td></tr>';
continue;
}
$o .= '<tr><td align="right">'.htmlspecialchars($f).'</td>';
if(strtolower($rowsa[$cnt]['type'])=='text' OR field_length($rowsa[$cnt]['type']) > 200)
$o .= '<td><textarea name="'.$i.'['.htmlspecialchars($f).']" cols="35" rows="3">'.htmlspecialchars($value).'</textarea></td></tr>';
else
$o .= '<td><input type="text" name="'.$i.'['.htmlspecialchars($f).']" value="'.htmlspecialchars($value).'" size="35"></td></tr>';
}
$o .= '</table>';
}
$o .= '<p><input type="submit" value="Update"> <input type=button value="Back" onClick="history.go(-1)"></p>';
$o .= '</form>';
return $o;
}
// deletes entry from table
function do_delete($db)
{
$query='PRAGMA table_info('.esc($_GET['tbl']).')';
$result = $db->query($query);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$id = false;
foreach($rows as $r)
{
if($r['pk'])
$id = $r['name'];
}
$q = 'DELETE FROM '.$_GET['tbl'].' WHERE '.$id.'='.esc($_GET['id']);
$db->query($q);
header('Location:?a=view&tbl='.strip_tags($_GET['tbl']));
}
// shows table structure
function do_structure($db)
{
$o = '<p>';
if(is_permitted($_SESSION['user'],'drop'))
$o .= '<a href="?a=drop&tbl='.htmlspecialchars($_GET['tbl']).'" onclick="return confirm(\'Are You sure to delete whole table?\')">drop table</a> | ';
if(is_permitted($_SESSION['user'],'add'))
$o .= '<a href="?a=add&tbl='.htmlspecialchars($_GET['tbl']).'">add data</a>';
$o .= '</p>';
$query='PRAGMA table_info('.esc($_GET['tbl']).')';
$result = $db->query($query);
if(empty($result))
{
$o .= 'no rows in table';
}
else
{
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$o .= '<table cellpadding="10" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
$o .= '<tr><td style="border: 1px solid #adc6c8"><b>name</td><td style="border: 1px solid #adc6c8"><b>type</b></td><td style="border: 1px solid #adc6c8"><b>primary key</b></td></tr>';
foreach($rows as $f=>$row)
$o .= '<tr><td style="border: 1px solid #adc6c8">'.$row['name'].'</td><td style="border: 1px solid #adc6c8">'.($row['type']?$row['type']:'unknown').'</td><td style="border: 1px solid #adc6c8">'.($row['pk']?'y':'n').'</td></tr>';
$o .= '</table>';
$o .= '<p><a href="?a=list">back to list tables</a></p>';
}
return $o;
}
// adds data into table
function do_add($db)
{
$o = "<p>Add record to table <b>'".htmlspecialchars($_GET['tbl'])."'</b></p>";
if(!empty($_POST))
{
$fields = array_keys($_POST['f']);
$values = array_values($_POST['f']);
$q = "INSERT INTO ".strip_tags($_GET['tbl'])." (".join(',', $fields).") VALUES(".join(',', array_map("esc", $values)).")";
$res = $db->query($q);
if($res->errorCode()<>'0000')
{
$msg = '<br>There was error: <pre>'.$res->errorCode().print_r($db->errorInfo(), 1).'</pre>';
echo $q . $msg;
}
else
{
$last_id = $db->lastInsertId();
$msg = '<p><b>Successfully added!</b></p>';
$msg .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
foreach($fields as $k=>$value)
{
$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">'.$value.'</td><td width="650" style="border: 1px solid #adc6c8">'.$_POST['f'][$value].'</td></tr>';
}
$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8"></td><td width="650" style="border: 1px solid #adc6c8"><a href="?a=edit&tbl='.strip_tags($_GET['tbl']).'&id='.$last_id.'">edit</a> - <a href="?a=delete&tbl='.strip_tags($_GET['tbl']).'&id='.$last_id.'" onclick="return confirm(\'Are You Sure that You want to delete this?\')">delete</a></td></tr>';
#
# add links for delete and edit (see lines 302~314)
#
$msg .= '</table>';
}
return $msg;
}
$o .= '<form action="?a=add&tbl='.htmlspecialchars($_GET['tbl']).'" method="post">';
$query='PRAGMA table_info('.esc($_GET['tbl']).')';
$result = $db->query($query);
if(empty($result))
{
$o .= 'no fields in table';
}
else
{
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$o .= '<table cellpadding="0" cellspacing="0" bgcolor="#f7f9f9" style="border: 1px solid #adc6c8; border-spacing: 10px 10px; padding: 10px 15px;">';
foreach($rows as $f=>$row)
{
$i = !empty($row['pk']) ? 'p' : 'f';
if($i == 'p')
continue;
$o .= '<tr><td align="right">'.htmlspecialchars($row['name']).'</td>';
if(strtolower($row['type'])=='text' OR field_length($row['type']) > 200)
$o .= '<td><textarea name="'.$i.'['.htmlspecialchars($row['name']).']" cols="35" rows="3">'.htmlspecialchars($_POST['f'][$row['name']]).'</textarea></td></tr>';
else
$o .= '<td><input type="text" name="'.$i.'['.htmlspecialchars($row['name']).']" value="'.htmlspecialchars($_POST['f'][$row['name']]).'" size="35"></td></tr>';
}
$o .= '</table>';
}
$o .= '<br><input type="submit" value="Save"> <input type=button value="Back" onClick="history.go(-1)">';
$o .= '</form>';
return $o;
}
// raw sql executing
function do_raw($db)
{
$msg = "<p>Enter your SQL command:</p>";
if(isset($_POST['sqlcmd']) && strlen($_POST['sqlcmd']))
{
$stmnt = $db->query($_POST['sqlcmd']);
if($db->errorCode()!='0000')
{
$msg = 'There was error: <pre>'.print_r($db->errorInfo(), 1).'</pre>';
}
else
{
$rows = $stmnt->fetchAll(PDO::FETCH_ASSOC);
$msg = sprintf('<p>Found total <b>%s</b> rows</p>', count($rows));
$msg .= '<table cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
foreach($rows as $row)
{
$msg .= '<tr><td align="left" valign="top" style="border: 1px solid #adc6c8">'.join('</td><td align="left" valign="top" style="border: 1px solid #adc6c8">', $row);
}
$msg .= '</td></tr></table><br>';
}
}
return parse_tpl(get_tpl('rawform'), array('msg'=>$msg, 'defaulttable'=>$GLOBALS['ini']['defaulttable']));
}
// searching
function do_search($db)
{
$output = '';
$query='PRAGMA table_info('.esc($_GET['tbl']).')';
$result = $db->query($query);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
if(empty($rows))
return 'rows not found';
$id = false;
foreach($rows as $r)
{
if($r['pk'])
$id = $r['name'];
}
$methods=array(1=>'exactly equal', 'not equal', 'contains');
if(!empty($_POST))
{
$sql_where = ' WHERE '.$_REQUEST['field'];
if($_POST['method']==1)
$sql_where .= '=';
if($_POST['method']==2)
$sql_where .=' <> ';
if($_POST['method']==3)
$sql_where .= ' LIKE '. esc('%'.$_POST['kword'].'%');
else
$sql_where .= esc($_POST['kword']);
$sql = 'SELECT * FROM '.$_REQUEST['tbl'].$sql_where;
$o = '<p><pre>'.$sql.'</pre></p>';
$q = $db->query($sql);
if($q->errorCode<>'0000')
{
$items = $q->fetchAll(PDO::FETCH_ASSOC);
foreach($items as $k=>$item)
{
$output .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
foreach($item as $key=>$it)
{
if(strlen($it))
$output .= sprintf('<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">%s</td><td width="650" style="border: 1px solid #adc6c8">%s</td></tr>', $key, $it);
}
$link_id = array_shift($item);
if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
{
$output .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
if(is_permitted($_SESSION['user'],'edit'))
{
$output .= '<a href="?a=edit&tbl='.htmlspecialchars($_GET['tbl']).'&id='.$link_id .'">edit</a>';
}
if(is_permitted($_SESSION['user'],'delete'))
{
$output .= ' | <a href="?a=delete&tbl='.htmlspecialchars($_GET['tbl']).'&id='.$link_id .'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
}
$output .= ' </td></tr>';
}
$output .= '</table><br>';
}
}
}
if(empty($_POST))
{
$o .= '<br>';
}
$o .= '<form action="?a=search&tbl='.$_REQUEST['tbl'].'" method="post"><select name="field">';
foreach($rows as $r)
{
$o .= '<option value="'.$r['name'].'">'.$r['name'].'</option>';
}
$o .= '</select> <select name="method">';
foreach($methods as $k=>$m)
{
$o .= '<option value="'.$k.'">'.$m.'</option>';
}
$o .= '</select> ';
$o .= '<input type="text" size="20" name="kword" value="'.htmlspecialchars($_POST['kword']).'"> <input type="submit" value="Search"> <input type=button value="Back" onClick="history.go(-1)"></form>';
$o .= $output;
return $o;
}
// gives downloadable file
function do_backup()
{
$fn = date("Y-m-d_H-i-s").'.sqlite3';
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fn.'"');
readfile($GLOBALS['ini']['dbname']);
exit;
}
// returns quoted variable for using in sql query
function esc($field)
{
return "'".$field."'";
}
// variable var1 is equal with value var2, used with edit (update sql)
function a_eq_b($var1, $var2)
{
$r = array();
if(count($var1)==count($var2))
foreach($var1 as $k=>$v1)
$r[] = $v1.'='.esc($var2[$k]);
return join(',', $r);
}
// outputs php array as preformated
function print_pre($var)
{
print_r('<pre>'.$var.'</pre>');
}
/*
* @param mixed $array - lehtedele jaotatav massiiv või elmentide arv
* @param int $perpage - mitut asja ühel leheküljel kuvada
* @param string $pagevar - GET muutuja aadressiribal
* @param string $pageurl - sprintf formaadis lehekülje aadress
* @param string $pageactive-sprintf formaadis tekst aktiivse lehe jaoks
* @param string $maxlinks- mitut linki korraga kuvada (orienteeruv suurus)
*/
function pager($array, $perpage, $pagevar='p', $pageurl="?p=__CURRENT_PAGE__", $pageactive="<b>%u</b>", $maxlinks=10)
{
// jagatavate elementide koguarv
$total = is_array($array) ? count($array) : intval($array);
// aktiivne lehekülg
$page = isset($_GET[$pagevar]) ? $_GET[$pagevar] : 1;
if($perpage<=0)
$perpage = 10;
// lehekülgede koguarv
$pages = ceil($total / $perpage);
// kui page muutuja on väiksem 1-st või suurem lehtede
// koguarvust siis aktiivseks leheks määratakse 1
if($page > $pages || $page < 1)
$page = 1;
// leiame lingiriba alguspunkti
$halfstart = $page - ($maxlinks / 2);
$start = $halfstart < 1 ? 1 : $halfstart;
// leiame lingiriba lõpp-punkti
$halfend = $page + ($maxlinks / 2);
$end = $halfend > $pages ? $pages : $halfend;
// tagastatav massiiv
$return = array();
// navigeerimismenüü loome ainult siis kui lehti on vähemalt 1
if($pages > 1)
{
if($page>1)
{
$url = str_replace('__CURRENT_PAGE__',1,$pageurl);
$return['navbar'][] = sprintf('<a href="%s">%s</a>', $url, '|<-');
}
for($i=$start; $i<=$end; $i++)
{
if($page==$i)
{
$return['navbar'][] = sprintf($pageactive, $i);
}
else
{
$url = str_replace('__CURRENT_PAGE__',$i,$pageurl);
$return['navbar'][] = sprintf('<a href="%s">%u</a>', $url, $i);
}
}
if($page<$pages)
{
$url = str_replace('__CURRENT_PAGE__',$pages,$pageurl);
$return['navbar'][] = sprintf('<a href="%s">%s</a>', $url, '->');
}
}
// limit päringus kasutamiseks
$limit_1 = ($page-1)*$perpage;
// $limit_2 asemel võiks panna $perpage
// kuid igaks juhuks arvutab selle ka välja, kuna
// see on abiks nt massiivi korral
$limit_2 = $limit_1 + $perpage > $total ? $total-$limit_1: $perpage;
$return['limit'] = $limit_1 . ', '.$limit_2;
// aktiivne lehekülg
$return['current'] = $page;
// lehekülgede arv kokku
$return['total'] = $total;
return $return;
}
// function checks that user is allowed to execute certain operation
function is_permitted($user, $action="index")
{
global $ini;
return isset($ini['users'][$user]) && in_array($action, explode(',', $ini['permissions'][$user]));
}
// returns table fieldtype length
function field_length($type)
{
preg_match('/.*(\d{3})/',$type, $matches);
return intval($matches[1]);
}
/**
* parse_template - asendab muutujas (mis nüüd sisaldab templatefaili sisu) tagid nende
*
* @param string $source - template sisu (näiteks read_template tulemus)
* @param array $vars - massiiv muutujatest ja nende väärtustest
*
* @return string with html template
* @author Anti Veeranna (hide@address.com)
*/
function parse_tpl($source,$vars)
{
// kontrollime, kas $vars argument on ikka array
if (is_array($vars))
{
// tsükkel üle $vars kõigi elementide
foreach($vars as $key => $value)
{
// moodustame tagi nime. Kui $key väärtuseks on näiteks "name",
// siis tagi nimeks saab "{VAR:name}"
$tag = "<VAR:" . $key . ">";
// asendame tagi tema väärtusega
$source = str_replace($tag,$value,$source);
};
};
// tagastame $source, kus tagid on nüüd juba nende väärtustega asendatud
return $source;
}
function get_tpl($name)
{
$mainmenu = $submenu = "";
if(is_permitted($_SESSION['user'], 'list'))
$mainmenu .= <<<html
<input type="button" value="list tables" onclick="window.location.href='?a=list'">
html;
if(is_permitted($_SESSION['user'], 'view'))
$submenu .= <<<html
<input type="button" value="list all" onclick="window.location.href='?a=view&tbl=<VAR:tablename>'">
html;
if(is_permitted($_SESSION['user'], 'add'))
$submenu .= <<<html
<input type="button" value="add" onclick="window.location.href='?a=add&tbl=<VAR:tablename>'">
html;
if(is_permitted($_SESSION['user'], 'search'))
$submenu .= <<<html
<input type="button" value="search" onclick="window.location.href='?a=search&tbl=<VAR:tablename>'">
html;
$templates = array(
'login'=><<<tpl
<fieldset><legend>dbEdit</legend>
<form action="$s" method="post"><br>
<table>
<tr><td align="right">Username:</td><td><input type="text" name="user"></td></tr>
<tr><td align="right">Password:</td><td><input type="password" name="pass"></td></tr>
<tr><td></td><td align="center"><br><input type="submit" value="login"></td></tr>
</table>
</form>
</fieldset>
tpl
,
'rawform'=><<<rawform
<VAR:msg>
<form action="?a=raw" method="post">
<textarea cols="60" rows="6" name="sqlcmd" id="sqlcmd">{$_POST['sqlcmd']}</textarea><br><br>
<select onchange="document.getElementById('sqlcmd').value=this.value">
<option value="">Choose standard command...</option>
<option value="SELECT * FROM <VAR:defaulttable>">SELECT - search data from table</option>
<option value="VACUUM">VACUUM - compacts database</option>
<option value="ANALYZE <VAR:defaulttable>">ANALYZE - gives info about indexes</option>
<option value="SELECT * FROM sqlite_master">SELECT sqlite_master - info from hidden table</option>
<option value="EXPLAIN SELECT * FROM <VAR:defaulttable>">EXPLAIN SELECT - info about query</option>
</select>
<input type="submit" value="lets go!">
</form>
rawform
,
'searchform'=><<<searchform
<VAR:msg>
<form action="?a=search" method="post">
<input type="text" name="txt" value="{$_POST['txt']}"> from (table){$tables} {$fields}
<br><input type="submit" value="lets go!">
</form>
searchform
,
'menu'=><<<menu
<form>
<input type="button" value="home" onclick="window.location.href='?a=index'">
{$mainmenu}
<VAR:rawlink>
<VAR:backuplink>
<input type="button" value="logout" onclick="window.location.href='?logout'">
</form><hr>
menu
,
'submenu'=><<<submenu
<form><b><VAR:tablename></b>:
{$submenu}</form><hr>
submenu
,
'html'=><<<html
<html>
<head>
<title>dbEdit</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#bdd1d3" link="#2d4648" vlink="#2d4648" alink="#2d4648"><br>
<div align="center">
<table cellpadding="25" cellspacing="0" bgcolor="#dee8e9" style="border: 3px solid #5b8c91">
<tr>
<td align="center" valign="top">
<VAR:menu>
<VAR:body>
</td>
</tr>
</table>
</div>
</body>
</html>
html
);
$tpl = isset($templates[$name]) ? $templates[$name] : 'tpl not found';
return $tpl;
}
?>