Please wait for 15 seconds...

Sunday, 21 October 2012



In MySQL database you have option to set a unique id for every row in a table. It is very convenient for small project. But if you are working on a big project, suppose you are working on a bit.ly like system. There could be millions of URL records in database. To communicate between database tables using their unique ID (auto increment ID), there is no problem. But for users, you need a unique string ID for every URL link.


Today's tutorial is base on this topic "How to Shrink Content ID in Database" OR "How to Create Unique String ID in Database".

[DEMO] [DOWNLOAD]

Create database table using below code.

Database Table

CREATE TABLE IF NOT EXISTS `bitly_tbl` (
  `url_id` int(11) NOT NULL AUTO_INCREMENT,
  `url` text NOT NULL,
  `str_id` varchar(50) NOT NULL,
  PRIMARY KEY (`url_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

Create files create_unique_id.php, create_unique_id.html and paste below code into them.

create_unique_id.php

<?php
/************************************/
mysql_connect('localhost', 'root', '');
mysql_select_db('my_db');
$db_tbl = "bitly_tbl";
/***********************************/
$str_id_init = "aaaaa";
$msg = "";
$recent_str_id = "";
$url = "http://www.google.com";

$query = "SELECT `str_id` FROM `$db_tbl` ORDER BY `url_id` DESC LIMIT 1";
$result = mysql_query($query);
$row = @mysql_fetch_assoc($result);
if($row)
    $recent_str_id = $row['str_id'];

$msg = "<p>Last Str ID: $recent_str_id</p>";

if(!empty($_POST['url'])){
    $url = $_POST['url'];

    $query = "SELECT `url_id` FROM `$db_tbl` WHERE `str_id`='$str_id_init'";
    $result = mysql_query($query);
    $num_row = mysql_num_rows($result);
    if($num_row){
        $query = "SELECT `str_id` FROM `$db_tbl` ORDER BY `url_id` DESC LIMIT 1";
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        $str_id = $row['str_id'];
        $str_id++;

        $query = "INSERT INTO `$db_tbl` (`url`,`str_id`) VALUES('$url','$str_id')";
        mysql_query($query);
    }
    else{
        $str_id = $str_id_init;
     
        $query = "INSERT INTO `$db_tbl` (`str_id`) VALUES('$str_id')";
        mysql_query($query);
    }
    $msg .= "<p>Str ID for $url: $str_id</p>";
}

include 'create_unique_id.html';
?>

create_unique_id.html

<!DOCTYPE>
<html>
<head>
<title>Shrink Content ID in PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="description" content="">
<meta name="author" content="https://plus.google.com/+AtulPrasadGupta24">
<style type="text/css">
.main{
    width: 600px;
    height:300px;
    position:absolute;
    left:50%;
    margin-left:-300px;
    top:50%;
    margin-top:-150px;
}
.url{
    width: 300px;
    height: 25px;
}
.msg{
    font-weight: bold;
    font-size: 12pt;
}
</style>
</head>
<body>
<div class="main">
    <form action="" method="post">
        <table>
            <tr>
                <td colspan="2" class="msg"><?=$msg?></td>
            </tr>
            <tr>
                <td><input type="text" name="url" class="url" value="<?=$url?>"></td>
                <td align="right"><input type="submit" value="Go!"></td>
            </tr>
        </table>
    </form>
</div>
</body>
</html>

Replace database credentials and open create_unique_id.php in your browser. Now enter URL and enjoy the system.

Posted by Atul Prasad Gupta

0 comments:

Post a Comment

Subscribe Techsirius

Techsirius on Facebook