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:

CREATE TABLE IF NOT EXISTS `autonumber` (
  `auto_id` INT(11) NOT NULL AUTO_INCREMENT,
  `autocode` VARCHAR(20) NOT NULL,
  `autoname` VARCHAR(20) NOT NULL,
  `appenchar` VARCHAR(10) NOT NULL,
  `autostart` INT(11) NOT NULL,
  `autoend` INT(11) NOT NULL,
  `incval` INT(11) NOT NULL,
  `datecreated` DATE NOT NULL,
  PRIMARY KEY (`auto_id`)
) 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:

<?php
/**
 * Description:    This is a class for autonumber.
 * Author:        Joken Villanueva
 * Date Created:    June 8, 2013
 * Revised By:        
 */
require_once(LIB_PATH . DS . 'database.php');
class autonumbers
{
    
    protected static $tbl_name = "autonumber";
    function db_fields()
    {
        global $mydb;
        return $mydb->getFieldsOnOneTable(self::$tbl_name);
    }
    function listOfautonumber()
    {
        global $mydb;
        $mydb->setQuery("Select * from " . self::$tbl_name);
        $cur = $mydb->loadResultList();
        return $cur;
        
    }
    static function bPrimary($id = 0)
    {
        global $mydb;
        $mydb->setQuery("SELECT * FROM " . self::$tbl_name . " WHERE auto_id={$id} LIMIT 1");
        $row = $mydb->loadSingleResult();
        $s   = $row->autostart + $row->incval;
        $a   = $row->appenchar;
        return $a . $s;
    }
    static function bPrimaryUpdate($id = 0)
    {
        global $mydb;
        $mydb->setQuery("SELECT * FROM " . self::$tbl_name . " WHERE auto_id={$id} LIMIT 1");
        $row = $mydb->loadSingleResult();
        $s   = $row->autostart + $row->incval;
        
        return $s;
    }
    /*---Instantiation of Object dynamically---*/
    static function instantiate($record)
    {
        $object = new self;
        
        foreach ($record as $attribute => $value) {
            if ($object->has_attribute($attribute)) {
                $object->$attribute = $value;
            }
        }
        return $object;
    }
    
    
    /*--Cleaning the raw data before submitting to Database--*/
    private function has_attribute($attribute)
    {
        // We don't care about the value, we just want to know if the key exists
        // Will return true or false
        return array_key_exists($attribute, $this->attributes());
    }
    
    protected function attributes()
    {
        // return an array of attribute names and their values
        global $mydb;
        $attributes = array();
        foreach ($this->db_fields() as $field) {
            if (property_exists($this, $field)) {
                $attributes[$field] = $this->$field;
            }
        }
        return $attributes;
    }
    
    protected function sanitized_attributes()
    {
        global $mydb;
        $clean_attributes = array();
        // sanitize the values before submitting
        // Note: does not alter the actual value of each attribute
        foreach ($this->attributes() as $key => $value) {
            $clean_attributes[$key] = $mydb->escape_value($value);
        }
        return $clean_attributes;
    }
    
    
    /*--Create,Update and Delete methods--*/
    public function save()
    {
        // A new record won't have an id yet.
        return isset($this->id) ? $this->update() : $this->create();
    }
    
    public function create()
    {
        global $mydb;
        // Don't forget your SQL syntax and good habits:
        // - INSERT INTO table (key, key) VALUES ('value', 'value')
        // - single-quotes around all values
        // - escape all values to prevent SQL injection
        $attributes = $this->sanitized_attributes();
        $sql        = "INSERT INTO " . self::$tbl_name . " (";
        $sql .= join(", ", array_keys($attributes));
        $sql .= ") VALUES ('";
        $sql .= join("', '", array_values($attributes));
        $sql .= "')";
        echo $mydb->setQuery($sql);
        
        if ($mydb->executeQuery()) {
            $this->id = $mydb->insert_id();
            return true;
        } else {
            return false;
        }
    }
    
    public function update($id = 0)
    {
        global $mydb;
        $attributes      = $this->sanitized_attributes();
        $attribute_pairs = array();
        foreach ($attributes as $key => $value) {
            $attribute_pairs[] = "{$key}='{$value}'";
        }
        $sql = "UPDATE " . self::$tbl_name . " SET ";
        $sql .= join(", ", $attribute_pairs);
        $sql .= " WHERE auto_id=" . $id;
        $mydb->setQuery($sql);
        if (!$mydb->executeQuery())
            return false;
        
    }
    
    public function delete($id = 0)
    {
        global $mydb;
        $sql = "DELETE FROM " . self::$tbl_name;
        $sql .= " WHERE auto_id=" . $id;
        $sql .= " LIMIT 1 ";
        $mydb->setQuery($sql);
        
        if (!$mydb->executeQuery())
            return false;
        
    }
    
}
?>

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.

<?php
/**
 * Description:    Create and save autonumbers.
 * Author:        Joken E. Villanueva
 * Date Created:    May 24,2013
 * Date Modified:June 6, 2013        
 */
require_once("includes/initialize.php");
include_layout_template_public('header.php');
?>
 
<div id="menubar">
 
</div>
 
<div id="module-name">Autonumber[New Entry]
    
</div>
 
<?php
 
 
if (isset($_POST['submit'])) {
    //form has been submitted1
    $autocode    = $_POST['autocode'];
    $autoname    = $_POST['autoname'];
    $appenchar   = $_POST['appenchar'];
    $autostart   = $_POST['autostart'];
    $autoend     = $_POST['autoend'];
    $incval      = $_POST['incval'];
    $datecreated = $_POST['datecreated'];
    
    $auto              = new autonumbers();
    $auto->autocode    = $autocode;
    $auto->autoname    = $autoname;
    $auto->appenchar   = $appenchar;
    $auto->autostart   = $autostart;
    $auto->autoend     = $autoend;
    $auto->incval      = $incval;
    $auto->datecreated = $datecreated;
    $istrue            = $auto->create();
    if ($istrue) {
?>
               <script type="text/javascript">
                    alert("New Autonumber has successfully Created!");
                    window.location = "autonum.php";
                </script>
                <?php
    } else {
        echo "Inserting Failed!";
    }
    
} else {
    $autocode  = "";
    $autoname  = "";
    $appenchar = "";
    $autostart = "";
    $autoend   = "";
    $incval    = "";
}
 
?>
 
<div id="content">
 
<form  method="post" action="autonum.php">
 
 
<table class="app_listing">
    <tr>
        <th > <div class="app_title" align="left">&nbsp;&nbsp;Autonumber Details</div></th>
    </tr>
    <tr class="form">
        <td class="form">
            <table class="app_form">
                
                <tr>
                   <td class="label" width="120">Autonumber Code :: </td>
                  <td class="input">
                     <input type="text" name="autocode" id="autocode"  class="txtbox" />
                    </td>
                </tr>                
                <tr>
                   <td class="label">Autonumber Name :: </td>
                  <td class="input">
                     <input type="text" name="autoname" id="autoname"  class="txtbox" />
                    </td>
                </tr>
                <tr>
                   <td class="label">Append Character :: </td>
                  <td class="input">
                     <input type="text" name="appenchar" id="appenchar"  class="txtbox" />
                    </td>
                </tr>
                <tr>
                   <td class="label">Autonumber Start :: </td>
                  <td class="input">
                     <input type="text" name="autostart" id="autostart"  class="txtbox" />
                    </td>
                </tr>
                <tr>
                   <td class="label">Autonumber End :: </td>
                  <td class="input">
                     <input type="text" name="autoend" id="autoend"  class="txtbox" />
                    </td>
                </tr>
                <tr>
                   <td class="label">Increment Value :: </td>
                  <td class="input">
                     <input type="text" name="incval" id="incval"  class="txtbox" />
                     <input type="hidden" name="datecreated" id="datecreated"  class="txtbox" value="<?php
echo date("Y-m-d");
?>"/>
                    </td>
                </tr>
                  <tr>
                <td class="label"></td>
                <td>
                     <input type="submit" name="submit" value="Save" class="app_button">
                    <input type="reset" name="reset" value="Reset" class="app_button">                     
                   </td>
                  </tr>
                        
                
            </table>    
        </tr>        
</table>
</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.

<?php
/**
 * Description:    List of Autonumber.
 * Author:        Joken E. Villanueva
 * Date Created:    May 24,2013
 * Date Modified:June 6, 2013        
 */
require_once("includes/initialize.php");
include_layout_template_public('header.php');
?>
 
<div id="menubar">
 
</div>
 
 
<div id="module-name">Autonumber[Listing]
</div>
<div id="content">
 
 
<table class="app_listing" width="100%">
    <tr>
        <th align="left">Autonumber Name</th>
        <th align="left">Autonumber Code</th>
        <th align="left">Next Value</th>
        <th align="center">Date Created</th>
        <th align="center">Options</th>
    </tr>
<?php
$mydb->setQuery("SELECT * from autonumber");
$cur = $mydb->loadResultList();
foreach ($cur as $object) {
    
    echo (@$odd == true) ? ' <tr class="odd_row" > ' : ' <tr class="even_row"> ';
    @$odd = !$odd;
    
    echo ' <td> ';
    echo '<a href="edit_user.php?id=' . $object->auto_id . '" class="app_listitem_key">' . $object->autoname . '</a>';
    echo ' <td> ';
    echo $object->autocode;
    echo ' <td> ';
    $startAndInc = $object->autostart + $object->incval;
    $app         = $object->appenchar;
    echo $app . $startAndInc;
    echo ' <td align = "center"> ';
    echo $object->datecreated;
    echo ' <td align="center"> ';
    echo '<a href="eautonum.php?id=' . $object->auto_id . '" class="app_listitem_key">[Edit Entry]</a>';
    echo '<a href="delAutonum.php?id=' . $object->auto_id . '" class="app_listitem_key">[Delete Entry]</a>';
}
?>
           
</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.

Add new comment

1408 views