Part III: Generating Autonumber using PHP/MySQL

Submitted by GeePee on Sunday, April 12, 2015 - 23:34.
In some of our previous tutorial, we discuss how to Create, Read, Update and Delete using object-oriented programmming approach. This tutorial is a continuation of our previous topic called "Part II: Updating and Deleting of MySQL data using an OOP approach in PHP". But this time we’re going to focus on how to generate autonumber. This autonumber can be used as a unique id in your aside from your database. To start in this project, first we need to set up our database table for autonumber. To do this, execute the query in your phpmyadmin. And here’s the code: 
  1. CREATE TABLE IF NOT EXISTS `autonumber` (
  2.   `auto_id` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `autocode` VARCHAR(20) NOT NULL,
  4.   `autoname` VARCHAR(20) NOT NULL,
  5.   `appenchar` VARCHAR(10) NOT NULL,
  6.   `autostart` INT(11) NOT NULL,
  7.   `autoend` INT(11) NOT NULL,
  8.   `incval` INT(11) NOT NULL,
  9.   `datecreated` DATE NOT NULL,
  10.   PRIMARY KEY (`auto_id`)
  11. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
This time, we are now ready to take our next step. To do this, open our “usercrud” file located in our local server, then let’s create a new php file and name it as “autonumbers.php”, and add the following code: 
  1. <?php
  2. /**
  3.  * Description:    This is a class for autonumber.
  4.  * Author:        Joken Villanueva
  5.  * Date Created:    June 8, 2013
  6.  * Revised By:        
  7.  */
  8. require_once(LIB_PATH . DS . 'database.php');
  9. class autonumbers
  10. {
  11.  
  12.     protected static $tbl_name = "autonumber";
  13.     function db_fields()
  14.     {
  15.         global $mydb;
  16.         return $mydb->getFieldsOnOneTable(self::$tbl_name);
  17.     }
  18.     function listOfautonumber()
  19.     {
  20.         global $mydb;
  21.         $mydb->setQuery("Select * from " . self::$tbl_name);
  22.         $cur = $mydb->loadResultList();
  23.         return $cur;
  24.  
  25.     }
  26.     static function bPrimary($id = 0)
  27.     {
  28.         global $mydb;
  29.         $mydb->setQuery("SELECT * FROM " . self::$tbl_name . " WHERE auto_id={$id} LIMIT 1");
  30.         $row = $mydb->loadSingleResult();
  31.         $s   = $row->autostart + $row->incval;
  32.         $a   = $row->appenchar;
  33.         return $a . $s;
  34.     }
  35.     static function bPrimaryUpdate($id = 0)
  36.     {
  37.         global $mydb;
  38.         $mydb->setQuery("SELECT * FROM " . self::$tbl_name . " WHERE auto_id={$id} LIMIT 1");
  39.         $row = $mydb->loadSingleResult();
  40.         $s   = $row->autostart + $row->incval;
  41.  
  42.         return $s;
  43.     }
  44.     /*---Instantiation of Object dynamically---*/
  45.     static function instantiate($record)
  46.     {
  47.         $object = new self;
  48.  
  49.         foreach ($record as $attribute => $value) {
  50.             if ($object->has_attribute($attribute)) {
  51.                 $object->$attribute = $value;
  52.             }
  53.         }
  54.         return $object;
  55.     }
  56.  
  57.  
  58.     /*--Cleaning the raw data before submitting to Database--*/
  59.     private function has_attribute($attribute)
  60.     {
  61.         // We don't care about the value, we just want to know if the key exists
  62.         // Will return true or false
  63.         return array_key_exists($attribute, $this->attributes());
  64.     }
  65.  
  66.     protected function attributes()
  67.     {
  68.         // return an array of attribute names and their values
  69.         global $mydb;
  70.         $attributes = array();
  71.         foreach ($this->db_fields() as $field) {
  72.             if (property_exists($this, $field)) {
  73.                 $attributes[$field] = $this->$field;
  74.             }
  75.         }
  76.         return $attributes;
  77.     }
  78.  
  79.     protected function sanitized_attributes()
  80.     {
  81.         global $mydb;
  82.         $clean_attributes = array();
  83.         // sanitize the values before submitting
  84.         // Note: does not alter the actual value of each attribute
  85.         foreach ($this->attributes() as $key => $value) {
  86.             $clean_attributes[$key] = $mydb->escape_value($value);
  87.         }
  88.         return $clean_attributes;
  89.     }
  90.  
  91.  
  92.     /*--Create,Update and Delete methods--*/
  93.     public function save()
  94.     {
  95.         // A new record won't have an id yet.
  96.         return isset($this->id) ? $this->update() : $this->create();
  97.     }
  98.  
  99.     public function create()
  100.     {
  101.         global $mydb;
  102.         // Don't forget your SQL syntax and good habits:
  103.         // - INSERT INTO table (key, key) VALUES ('value', 'value')
  104.         // - single-quotes around all values
  105.         // - escape all values to prevent SQL injection
  106.         $attributes = $this->sanitized_attributes();
  107.         $sql        = "INSERT INTO " . self::$tbl_name . " (";
  108.         $sql .= join(", ", array_keys($attributes));
  109.         $sql .= ") VALUES ('";
  110.         $sql .= join("', '", array_values($attributes));
  111.         $sql .= "')";
  112.         echo $mydb->setQuery($sql);
  113.  
  114.         if ($mydb->executeQuery()) {
  115.             $this->id = $mydb->insert_id();
  116.             return true;
  117.         } else {
  118.             return false;
  119.         }
  120.     }
  121.  
  122.     public function update($id = 0)
  123.     {
  124.         global $mydb;
  125.         $attributes      = $this->sanitized_attributes();
  126.         $attribute_pairs = array();
  127.         foreach ($attributes as $key => $value) {
  128.             $attribute_pairs[] = "{$key}='{$value}'";
  129.         }
  130.         $sql = "UPDATE " . self::$tbl_name . " SET ";
  131.         $sql .= join(", ", $attribute_pairs);
  132.         $sql .= " WHERE auto_id=" . $id;
  133.         $mydb->setQuery($sql);
  134.         if (!$mydb->executeQuery())
  135.             return false;
  136.  
  137.     }
  138.  
  139.     public function delete($id = 0)
  140.     {
  141.         global $mydb;
  142.         $sql = "DELETE FROM " . self::$tbl_name;
  143.         $sql .= " WHERE auto_id=" . $id;
  144.         $sql .= " LIMIT 1 ";
  145.         $mydb->setQuery($sql);
  146.  
  147.         if (!$mydb->executeQuery())
  148.             return false;
  149.  
  150.     }
  151.  
  152. }
  153. ?>
And save it inside the the includes folder. Next, were going to create new php and name it as “autonum.php” and add the following code: this will accept user inputs for a new autonumber and save this in the database. Autonumber Code – this will serve as a code for a specific autonumber. Autonumber Name – this is for a Name of autonumber. Append Character – this append character will be added in the beginning value of a autonnumber. Autonumber Start –specifies the starting number of a autonmber. Autonumber End- specifies the last value of autonumber. Increment Value – increments the value of an item by the specified value. 
  1. <?php
  2. /**
  3.  * Description:    Create and save autonumbers.
  4.  * Author:        Joken E. Villanueva
  5.  * Date Created:    May 24,2013
  6.  * Date Modified:June 6, 2013        
  7.  */
  8. require_once("includes/initialize.php");
  9. include_layout_template_public('header.php');
  10. ?>
  11.  
  12. <div id="menubar">
  13.  
  14. </div>
  15.  
  16. <div id="module-name">Autonumber[New Entry]
  17.  
  18. </div>
  19.  
  20. <?php
  21.  
  22.  
  23. if (isset($_POST['submit'])) {
  24.     //form has been submitted1
  25.     $autocode    = $_POST['autocode'];
  26.     $autoname    = $_POST['autoname'];
  27.     $appenchar   = $_POST['appenchar'];
  28.     $autostart   = $_POST['autostart'];
  29.     $autoend     = $_POST['autoend'];
  30.     $incval      = $_POST['incval'];
  31.     $datecreated = $_POST['datecreated'];
  32.  
  33.     $auto              = new autonumbers();
  34.     $auto->autocode    = $autocode;
  35.     $auto->autoname    = $autoname;
  36.     $auto->appenchar   = $appenchar;
  37.     $auto->autostart   = $autostart;
  38.     $auto->autoend     = $autoend;
  39.     $auto->incval      = $incval;
  40.     $auto->datecreated = $datecreated;
  41.     $istrue            = $auto->create();
  42.     if ($istrue) {
  43. ?>
  44.                <script type="text/javascript">
  45.                     alert("New Autonumber has successfully Created!");
  46.                     window.location = "autonum.php";
  47.                 </script>
  48.                 <?php
  49.     } else {
  50.         echo "Inserting Failed!";
  51.     }
  52.  
  53. } else {
  54.     $autocode  = "";
  55.     $autoname  = "";
  56.     $appenchar = "";
  57.     $autostart = "";
  58.     $autoend   = "";
  59.     $incval    = "";
  60. }
  61.  
  62. ?>
  63.  
  64. <div id="content">
  65.  
  66. <form  method="post" action="autonum.php">
  67.  
  68.  
  69. <table class="app_listing">
  70.     <tr>
  71.         <th > <div class="app_title" align="left">&nbsp;&nbsp;Autonumber Details</div></th>
  72.     </tr>
  73.     <tr class="form">
  74.         <td class="form">
  75.             <table class="app_form">
  76.  
  77.                 <tr>
  78.                    <td class="label" width="120">Autonumber Code :: </td>
  79.                   <td class="input">
  80.                      <input type="text" name="autocode" id="autocode"  class="txtbox" />
  81.                     </td>
  82.                 </tr>                
  83.                 <tr>
  84.                    <td class="label">Autonumber Name :: </td>
  85.                   <td class="input">
  86.                      <input type="text" name="autoname" id="autoname"  class="txtbox" />
  87.                     </td>
  88.                 </tr>
  89.                 <tr>
  90.                    <td class="label">Append Character :: </td>
  91.                   <td class="input">
  92.                      <input type="text" name="appenchar" id="appenchar"  class="txtbox" />
  93.                     </td>
  94.                 </tr>
  95.                 <tr>
  96.                    <td class="label">Autonumber Start :: </td>
  97.                   <td class="input">
  98.                      <input type="text" name="autostart" id="autostart"  class="txtbox" />
  99.                     </td>
  100.                 </tr>
  101.                 <tr>
  102.                    <td class="label">Autonumber End :: </td>
  103.                   <td class="input">
  104.                      <input type="text" name="autoend" id="autoend"  class="txtbox" />
  105.                     </td>
  106.                 </tr>
  107.                 <tr>
  108.                    <td class="label">Increment Value :: </td>
  109.                   <td class="input">
  110.                      <input type="text" name="incval" id="incval"  class="txtbox" />
  111.                      <input type="hidden" name="datecreated" id="datecreated"  class="txtbox" value="<?php
  112. echo date("Y-m-d");
  113. ?>"/>
  114.                     </td>
  115.                 </tr>
  116.                   <tr>
  117.                 <td class="label"></td>
  118.                 <td>
  119.                      <input type="submit" name="submit" value="Save" class="app_button">
  120.                     <input type="reset" name="reset" value="Reset" class="app_button">                     
  121.                    </td>
  122.                   </tr>
  123.  
  124.  
  125.             </table>    
  126.         </tr>        
  127. </table>
  128. </form>
When this code is executed in the browser it will look like as shown below. autonumberphp Next, we will create a new php file and name it as “autolist.php”, and add the following code: This code will list down all the autonumbers information in a table. 
  1. <?php
  2. /**
  3.  * Description:    List of Autonumber.
  4.  * Author:        Joken E. Villanueva
  5.  * Date Created:    May 24,2013
  6.  * Date Modified:June 6, 2013        
  7.  */
  8. require_once("includes/initialize.php");
  9. include_layout_template_public('header.php');
  10. ?>
  11.  
  12. <div id="menubar">
  13.  
  14. </div>
  15.  
  16.  
  17. <div id="module-name">Autonumber[Listing]
  18. </div>
  19. <div id="content">
  20.  
  21.  
  22. <table class="app_listing" width="100%">
  23.     <tr>
  24.         <th align="left">Autonumber Name</th>
  25.         <th align="left">Autonumber Code</th>
  26.         <th align="left">Next Value</th>
  27.         <th align="center">Date Created</th>
  28.         <th align="center">Options</th>
  29.     </tr>
  30. <?php
  31. $mydb->setQuery("SELECT * from autonumber");
  32. $cur = $mydb->loadResultList();
  33. foreach ($cur as $object) {
  34.  
  35.     echo (@$odd == true) ? ' <tr class="odd_row" > ' : ' <tr class="even_row"> ';
  36.     @$odd = !$odd;
  37.  
  38.     echo ' <td> ';
  39.     echo '<a href="edit_user.php?id=' . $object->auto_id . '" class="app_listitem_key">' . $object->autoname . '</a>';
  40.     echo ' <td> ';
  41.     echo $object->autocode;
  42.     echo ' <td> ';
  43.     $startAndInc = $object->autostart + $object->incval;
  44.     $app         = $object->appenchar;
  45.     echo $app . $startAndInc;
  46.     echo ' <td align = "center"> ';
  47.     echo $object->datecreated;
  48.     echo ' <td align="center"> ';
  49.     echo '<a href="eautonum.php?id=' . $object->auto_id . '" class="app_listitem_key">[Edit Entry]</a>';
  50.     echo '<a href="delAutonum.php?id=' . $object->auto_id . '" class="app_listitem_key">[Delete Entry]</a>';
  51. }
  52. ?>
  53.  
  54. </table>
After executing this code, it looks like as shown below: autolist That’s it for now folks, my next lesson will focus on updating and deleting of autonumber.

Tags

Add new comment

About text formats
  • 1267 views