How to send unique id into database usign mysql and php

Question

i created a application form and it has application no field. but it should be hidden. this application no should be auto increment unique number. i use date() and mysql_insert_id() method to generate that. but always show 0 value in my table. i have posted my code. please anyone can explain me..what is the error..about this case. this code is successfully inseterd. but did not show correct output. always show 0

if(isset($_POST['submitted'])){


    // catch data
    $loantype = $_POST['loantype'];
    $calcno = $_POST['calcno'];
    $memberid = $_POST['memberid'];
    $appno = date('Y-m-d')."-LN/SS-".mysql_insert_id();
    $amount = $_POST['amount'];
    $rental_type = $_POST['rental_type'];
    $apr = $_POST['apr'];
    $npy = $_POST['npy'];
    $flatrate = $_POST['flatRate'];
    $othercharges = $_POST['othercharges'];
    $repayment = $_POST['Repayment'];


    $payment = $_POST['payment'];
    $totpaid = $_POST['totpaid'];
    $intpaid = $_POST['intpaid'];

    date_default_timezone_set("Asia/Calcutta");
    $created = date('Y-m-d h:i:s');
    $status = "PENDING";
    $discription = "NOT DEFINED";
    $accepted_amount = "NOT DEFINED";


    //create new loan application
    $sql ='INSERT INTO tbl_loan_application VALUES (NULL,"'.$loantype.'","'.$calcno.'","'.$memberid.'","'.$appno.'","'.$amount.'","'.$rental_type.'","'.$apr.'","'.$npy.'","'.$flatrate.'","'.$othercharges.'","'.$repayment.'",
    "'.$created.'","'.$status.'","'.$discription.'","'.$accepted_amount.'","'.$payment.'","'.$totpaid.'","'.$intpaid.'")';


    /*else{

    }*/


        mysql_query($sql, $conn) or die(mysql_error());
        echo'<div class="alert alert-dsgn alert-success fade in" style="width:400px; align:center; margin-left:35%;     margin-top:5px;>
                            <button class="close" aria-hidden="true" data-dismiss="alert" type="button"></button>
                        <i class="fa fa-thumbs-up fa-3x"></i>
                        <article>
                        <h4>Nice! </h4>
                        <p>New Loan application successfully saved..<br /> </p>
                        </article>
                    </div>';
    echo '<a href="newloan.php"><p align="center">Back</p></a>';    
    echo '<p align="center">If you want to view loan application! click <a href="approved_loan_list.php">here</a></p>'; 
    echo mysql_insert_id();
    die();


    //

}

score 1 · Answer 1 · answered Jun 15 '14 at 12:50

1

You need to make this while making your mysql database. For example:

    CREATE TABLE IF NOT EXISTS `tablename` (
      `Id` int(11) NOT NULL auto_increment,
      `Username` varchar(255) NOT NULL,
      `Password` varchar(255) NOT NULL,
      PRIMARY KEY  (`Id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

That way you will have ID column that will auto increase with 1 every time you add a database record. You do not need to do it manually, as when you use $_POST method, it will be with unique ID (for example 1, 2, 3, 4, .. etc..).

answered Jun 15 '14 at 12:50

user3214661

17
3

i already created a table and it is name is tbl_loan_application and these data should be go to that table..are you mean other tabe? – Jun 15 '14 at 12:52
No, create your table using the model above. with this row: ["primary key (`Id`)"] and with the last line – user3214661 Jun 15 '14 at 12:55
You should use the auto increment property on one of your columns (the one you use for 'id') then it will automatically increment properly - you can add this at any time, it doesn't need to be when you first create the table. – Jonathan Jun 15 '14 at 13:53

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

First of all, consider dropping mysql, in favour of mysqli or PDO, since the mysql functions are deprecated. More info here.

Second of all, the function you use returns the ID of the LAST query you ran, as stated here, thus it will return 0 if the last query didn't generate a new ID.

To start studying mysqli or PDO, you can consider the official MySQL documentation, or the PHP documentation for PDO and mysqli.

Also on a related notice, it's a good practice to state the fields you wish to affect with insert, like this:

INSERT INTO table1(field_name1, field_name2, ... , field_nameN) VALUES(value1, value2, ... ,  valueN)

Suggestion for resolution: Insert them with some default appno, and add this trigger:

CREATE TRIGGER `UPDATES` AFTER INSERT ON `tbl_loan_application`
FOR EACH ROW BEGIN
    SET NEW.appno = CONCAT(CURDATE(), "-LN/SS-" , NEW.ID);
END

This will automatically assign the code after each insert, inside the database. I assumed that field names inside your DB are 'appno' for the code and 'ID' for your auto increment field.

EDIT:

How do Triggers work:

As stated here, triggers are 'stored function' which executes after or before event (update, insert or delete). The correct syntax is:

CREATE TRIGGER `Trigger_Name` [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON `table_name`
/* Some function usually with FOR EACH ROW */

the affected values are selected by using NEW.field_name or OLD.field_name. Those 2 selector return the value of the affected field before and after the action. So, by having this thing said, the example given in the answer does the following:

Creates a trigger named 'UPDATES'
Sets the event to fire after every time a insert statement is executed for the table 'tbl_loan_application'.
The function selects all the rows inserted, one by one, and does the same thing you id with php, it joins CURDATE(), aka the current date, the string "-LN/SS-" and the ID of the line, providing you have one.

Triggers, just like tables, are written once, usually in the mysql console, but some people use other tools, like phpmyadin, to simplify the process.

Triggers come in handy in many situations and can edit not just the monitored table, but any other table as well.

Long story short, it's a code that get executed every time you edit/insert new/delete a row inside the specified table.

edited May 23 '17 at 12:21

Community

1
1

answered Jun 15 '14 at 12:53

Hristo Valkanov

1,689
22
33

So what am i doing for this...i am newer person for php. and i dont know how to use mysql1 – Jun 15 '14 at 12:56
@user3732725 I added links to some tutorials. It's quite easy to switch, believe me. – Hristo Valkanov Jun 15 '14 at 13:02
yeah..lot of people tell that..Hristo..this is my bad habit..i tried my best. but my question has no answer – Jun 15 '14 at 13:13
this is a loan management system..i created big thing. when i change to mysqli..it is big trouble for me..these is final status in my project Hristo – Jun 15 '14 at 13:18
do you have permissions to add triggers, @user3732725? – Hristo Valkanov Jun 15 '14 at 13:20
@user3732725 I added a trigger example which can solve your problem with some alterations. If I had more info on your database I could have provided a more detailed answer ;). – Hristo Valkanov Jun 15 '14 at 13:33
in mysql we have auto_increment so we don't need to use sequences or triggers to create sequences – Jonathan Jun 15 '14 at 13:54
@Jonathan, care to share a example? – Hristo Valkanov Jun 15 '14 at 14:21
Where should i type this trigger? – Jun 15 '14 at 14:41
What do you want to know about my database? are you want my sql code for give good answer? – Jun 15 '14 at 14:42
@user3732725 the trigger is something you create in the mysql, the same way you create tables and etc. About the database, the structure of the table will be a good start ;) – Hristo Valkanov Jun 15 '14 at 15:23
so how can i apply triggers for solution – Jun 15 '14 at 15:33
@user3732725 explanation added. – Hristo Valkanov Jun 15 '14 at 18:36

user3146425 · Answer 3 · 2014-06-15T13:31:42.607

-1

use this function to generate an well formatted id.

mt_rand(10,100);

edited Jun 15 '14 at 13:31

answered Jun 15 '14 at 13:02

user3146425

25
2
9

sorry.. i cant understand what is that – Jun 15 '14 at 13:16
It is a function of php which generate an unique id(md5). You can use it to generate a unique id and then you can insert it in the mysql by php using INSERT INTO statement. – user3146425 Jun 15 '14 at 13:21
yeah...but this is not well format..i want formatted number – Jun 15 '14 at 13:26
can you please tell me for what purpose you need a formatted number – user3146425 Jun 15 '14 at 13:28
This post is being automatically flagged as low quality because it is only code. Would you mind expanding it by adding some text to explain how it solves the problem? – gung - Reinstate Monica Jun 15 '14 at 13:32
I have edited the answer as per your need , just check it out. – user3146425 Jun 15 '14 at 13:32
These is my client requriement.. they want LN/SS-DATE-ID. LN and SS is normal text. i think their bank code – Jun 15 '14 at 15:31

How to send unique id into database usign mysql and php

3 Answers3