HOME 2.Sept.2015 Download – see site_ver2.rar from my article 9.
Excellent code to learn object oriented programming, model-view-controller code separation (structured programming), single page application (require scripts, not URL them), most code outside Apache doc root.
1. Download sqlitestudio and install DDL below.
--***************************************** -- sqlite 3 --***************************************** /* SELECT * FROM message ; select * from zodiac ; */ /* http://dev:8083/test/books/a01cookbook/tema.php J:\dev_web\htdocs\test\books\a01cookbook\tema.php sqlite db is: J:\dev_web\htdocs\test\books\a01cookbook\tema.sqlite SELFJOIN TABLE : 1. C INSERT frm data, 2. R DISPLAY tbl, row, 3. V VALIDATE, E set_exception_handler Not neccessarily here: U UPDATE and D DELETE WITH J:\aplp\aplp\sqlitestudio\SQLiteStudio.exe TEME (msgs-PORUKE I replays-ODGOVORI) SELFJOIN TEME (msgs-PORUKE I replays-ODGOVORI) SELFJOIN ---------------------------------------------------------------- 5 KEYS: id,thread_id,parent_id,level,thread_pos ---------------------------------------------------------------- 1 1 0 0 0 TEMA1 (thread1) by ss @ 2015-03-25 00:41 (99 bytes) 2 1 1 1 1 funkcije by ss @ 2015-03-25 00:42 (242 bytes) 3 1 2 2 2 funkcija save() by ss @ 2015-03-25 00:52 (1335 bytes) 6 1 2 2 3 funkcija frm_post() by ss @ 2015-03-25 19:29 (303 bytes) 4 2 0 0 0 TEMA2 CRUD šifrarnika sqlite3 by ss ... 5 3 0 0 0 TEMA3 MAPE web servera by ss ... ______________________________________________________________ Otvoriti novu temu (nit, thread) */ -- sqlite db: J:\dev_web\htdocs\test\books\a01cookbook\tema.sqlite CREATE TABLE message ( id INTEGER PRIMARY KEY AUTOINCREMENT, posted_on DATETIME NOT NULL, author CHAR(255), subject CHAR(255), body MEDIUMTEXT, thread_id INT UNSIGNED NOT NULL, parent_id INT UNSIGNED NOT NULL, level INT UNSIGNED NOT NULL, thread_pos INT UNSIGNED NOT NULL ); --sqlite db: J:\dev_web\htdocs\test\books\a01cookbook\zodiac.sqlite CREATE TABLE zodiac ( id INT UNSIGNED NOT NULL, sign CHAR(11), symbol CHAR(13), planet CHAR(7), element CHAR(5), start_month TINYINT, start_day TINYINT, end_month TINYINT, end_day TINYINT, PRIMARY KEY(id) ) ; INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19); INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20); INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21); INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22); INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22); INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22); INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23); INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',10,24,11,21); INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,21); INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19); INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,18); INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);
2. Controller and view ~ 120 lines :
<?php
// http://dev:8083/test/books/a01cookbook/
// J:\dev_web\htdocs\test\books\a01cookbook\zodiac.php
// ***********************************
// 1. M O D E L - file where b u i l d_ q u e r y ( ) is defined
// ***********************************
$fields = array(
'sign'
, 'symbol'
, 'planet'
, 'element'
, 'start_month'
, 'start_day'
, 'end_month'
, 'end_day'
);
$lbls = array(
'Znak'
, 'Simbol'
, 'Planeta'
, 'Element'
, 'Od mjeseca'
, 'Od dana'
, 'Do mjeseca'
, 'Do dana'
);
include __DIR__ . '/mdl.php';
$db = new PDO('sqlite:zodiac.sqlite');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// ***********************************
// 2. C O N T R O L L E R
// ***********************************
$cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : 'show';
switch ($cmd)
{
case 'edit':
try {
$st = $db->prepare('SELECT ' . implode(',',$fields) .
' FROM zodiac WHERE id = ?');
$st->execute(array($_GET['id']));
$row = $st->fetch(PDO::FETCH_ASSOC);
} catch (Exception $e) {
$row = array();
}
case 'add':
print '<form method="post" action="' .
htmlentities($_SERVER['PHP_SELF']) . '">';
print '<input type="hidden" name="cmd" value="save">';
print '<table>';
if ('edit' == $cmd) {
printf('<input type="hidden" name="id" value="%d">',
$_GET['id']);
}
foreach ($fields as $field) {
if ('edit' == $cmd) {
$value = htmlentities($row[$field]);
} else {
$value = '';
}
printf('<tr><td>%s: </td><td><input type="text" name="%s" value="%s">',
$field, $field, $value);
printf('</td></tr>');
}
print '<tr>
<td></td>
<td><input type="submit" value="Save"></td>
</tr>';
print '</table></form>';
break;
case 'save':
try {
$st = build_query($db,'id',$fields,'zodiac');
print 'Added info.';
} catch (Exception $e) {
print "Couldn't add info: " . htmlentities($e->getMessage());
}
print '<hr>';
case 'show':
// ***********************************
// 3. V I E W - P A G I N A T O R
// ***********************************
default:
$self = htmlentities($_SERVER['PHP_SELF']);
/* without P A G I N A T O R :
foreach ($db->query('SELECT id,sign FROM zodiac') as $row) {
printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',
$self,$row['id'],htmlentities($row['sign']));
}
*/
$offset = isset($_GET['offset']) ? intval($_GET['offset']) : 1;
if (! $offset) { $offset = 1; }
$per_page = 10;
$total = $db->query('SELECT COUNT(*) FROM zodiac')->fetchColumn(0);
$limitedSQL = 'SELECT * FROM zodiac ORDER BY id ' .
"LIMIT $per_page OFFSET " . ($offset-1);
$lastRowNumber = $offset - 1;
print '<a href="'.$self.'?cmd=add">Dodati redak</a><hr />';
print '<ol>';
foreach ($db->query($limitedSQL) as $row) {
$lastRowNumber++;
//print "{$row['sign']}"
printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',
$self,$row['id'],htmlentities($row['sign']));
echo ", {$row['symbol']} ({$row['id']}) <br/>\n";
}
print '</ol>';
//
indexed_links($total,$offset,$per_page);
print "<br/>";
print "(Prikazani retci $offset - $lastRowNumber od $total)";
//
break;
} // e n d
s w i t c h
3. Model- universal code ~ 70 lines :
<?php
function build_query($db, $key_field, $fields, $table) {
$values = array();
if (! empty($_POST[$key_field])) {
$update_fields = array();
foreach ($fields as $field) {
$update_fields[] = "$field = ?";
// Assume data is coming from a form
$values[] = $_POST[$field];
}
// Add the key field's value to the $values array
$values[] = $_POST[$key_field];
$st = $db->prepare("UPDATE $table SET " .
implode(',', $update_fields) .
"WHERE $key_field = ?");
} else {
// Start values off with a unique ID
// If your DB is set to generate this value, use NULL instead
$values[] = md5(uniqid());
$placeholders = array('?');
foreach ($fields as $field) {
// One placeholder per field
$placeholders[] = '?';
// Assume the data is coming from a form
$values[] = $_POST[$field];
}
$st = $db->prepare(
"INSERT INTO $table ($key_field," .
implode(',',$fields) . ') VALUES ('.
implode(',',$placeholders) .')');
}
$st->execute($values);
return $st;
}
function print_link($inactive,$text,$offset='') {
if ($inactive) {
print "<span class='inactive'>$text</span>";
} else {
print "<span class='active'>".
"<a href='" . htmlentities($_SERVER['PHP_SELF']) .
"?offset=$offset'>$text</a></span>";
}
}
function indexed_links($total,$offset,$per_page) {
$separator = ' | ';
//
print_link($offset == 1, '<< Preth', max(1, $offset - $per_page));
// print all groupings except last one
for ($start = 1, $end = $per_page;
$end < $total;
$start += $per_page, $end += $per_page) {
print $separator;
print_link($offset == $start, "$start-$end", $start);
}
/* print the last grouping -
* at this point, $start points to the element at the beginning
* of the last grouping
*/
/* the text should only contain a range if there's more than
* one element on the last page. For example, the last grouping
* of 11 elements with 5 per page should just say "11", not "11-11"
*/
$end = ($total > $start) ? "-$total" : '';
print $separator;
print_link($offset == $start, "$start$end", $start);
//
print $separator;
print_link($offset == $start, 'Sljed >>',$offset + $per_page);
}