5. CRUD simple table (ID,…some data) PDO SQLite

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);
}

 

3 thoughts on “5. CRUD simple table (ID,…some data) PDO SQLite

  1. It’s remarkable to pay a visit this website and reading
    the views of all mates on the topic of this article, while I am also eager of getting
    familiarity.

  2. Very nice post. I just stumbled upon your weblog and wished to mention that I’ve truly
    loved browsing your weblog posts. In any case I’ll be subscribing for your feed
    and I hope you write once more very soon!

    Also visit my weblog … Foot Callous

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.