I have a form and a mysql table name parcel. I need to insert tracking number in the table which is personally generated based on year,category and status. But the tracking number must auto increment. For example :

category has two values : D and L
status has four values : 01, 02, 03, 04
number : will be increment
year (based on current date) :14

for example I insert category: D, status: 02, and year.
so the tracking number will be : (category)/(status)/(here will be number with increment)/(year)
this will be tracking number : D/02/01/14

But the problem is the increment number will be based on the category.
for example if the category is D,then the number will start with 1 and will be increment.
Same goes with category L, it will start with 1 and will increment it.

So the tracking number for D will be D/02/01/14, D/02/02/14, D/02/03/14... and so on.
the tracking number for L will be L/02/01/14, L/02/02/14, L/02/03/14, L/02/04/14.. and so on.

SO, I cant use auto_increment for the number field in my table because the number will be repeated because the category.

Please help me.

you can use a counter for that method

i already guess you can insert the thing's there you only needed the counter
Do this 2 step
1. Add a table counter
2. loop it

$count = 1;
$sql = "SELECT * FROM table_name";
$query = mysql_query($sql);

while($get_data = mysql_fetch_assoc($query)){
    $counter_data = $get_data['counter'];

    $value = $get_data['count'];
        if($value != 0){   
            $count = $get_data['count'] + 1;
        }
        else{
            $count = 0;
        }        

        $count++;
}

hope this helps

Thank you for your helps. Your code solved my problem.

Can you help me with another problem?
The previous problem solved. But now I have another problem.

From my previous problem, the tracking number was generated. But now, the problem is the tracking number must be start from 1 again when there are start a new year. For example:

category has two values : D and L
status has four values : 01, 02, 03, 04
counter : will be increment
year (based on current date) :14

for example I insert category: D, status: 02, and year.
so the tracking number will be : (category)/(status)/(here will be counter and will start from 1 again when new year start)/(year)
this will be tracking number : D/02/01/14,D/02/02/14, D/02/03/14... and so o

So, for 2015:
the tracking number will be : D/02/01/15, D/02/02/15, D/02/03/15... and so o

So, how to know when 2015 start and the counter field in MySQL will automatically restart from 1.

Thank you in advanced

i would recommend you to start an new discussion about your new problem.
because this is thread here is already solved right?

well i guess you have a database table of a date rigth?

you only need to get the final value

$sql ="SELECT date from tbl_name";
// this will get the last data of your table date
$query = mysql_fetch_assoc(mysql_query($sql));
$date_today = date("Y-m-d")//format of date today
$date_table = $query['date'];
$counter = 1;

if($date_today == $date_table){
    //continue counter
    $counter = $row['counter'];
}else{
    //reset here
    $counter = 1;
}


hope this help

Thank you for your reply. But I still didnt get it.

Hello Joshuajames.
Can u help me?
How can I combine the codes that you gave to me?
I want to combine the first and second code you gave me because the system need to check the year first then will proceed with the generates tracking number. Please help me. I tried it but still failed. Thank you in advanced.

can you show me your codes so that i can modify that

Here the combined code. I tried it for a week, but still didnt get it. Thank ou for your helps.

<input name="counterL" type="text" id="counterL" value=" <?php 

if ($saluran == 'L')
{

$count = 1;
$date_today = date("Y");//format of date today


$sql = "SELECT * FROM pendaftaransurat ORDER by counter ASC";
$query = mysql_query($sql);



    $date_table = $get_data['tahun'];
    $value = $get_data['tahun'];
    $counter_data = $get_data['counterL'];
    $value = $get_data['counterL'];

    if($date_today == $date_table){

        $count = $get_data['tahun'];
        $count = $get_data['counterL'];

            if($value != 0){   
                $count = $get_data['counterL'];
            }
            else{
                $count = 1;
            }        
            $count++;

            echo "$count";

        }

        else
        {
            $count = 1;
        echo "$count";
        }

        }   






?>">

This is code for increment the counter before I combined with the code for year:

if ($saluran == 'D')
{


$count = 1;
$sql = "SELECT * FROM pendaftaransurat ORDER by counter ASC";
$query = mysql_query($sql);
while($get_data = mysql_fetch_assoc($query)){
    $counter_data = $get_data['counter'];
    $value = $get_data['counter'];
        if($value != 0){   
            $count = $get_data['counter'];
        }
        else{
            $count = 0;
        }        
        $count++;
}




echo "$count";

}




?>

I added something hope that help also read my comments there

  1. Validate the year
  2. get the count
  3. if the year reset and the count will be at 1 again only get the last data of tha table

    if ($saluran == 'L')
    {
    //first is to validate the year
    $date_today = date("Y");
    $date_table = mysql_fetch_assoc(mysql_query("SELECT * FROM table"));
    $data_table = $date_table['date'];
    if($date_today == $data_table){
    //this is to validate the count inside the table
    $sql = "SELECT * FROM pendaftaransurat ORDER by counter ASC";
    $query = mysql_query($sql);
    while($get_data = mysql_fetch_assoc($query)){
    $date_table = $get_data['tahun'];
    $value = $get_data['tahun'];
    $counter_data = $get_data['counterL'];
    $value = $get_data['counterL'];
    }
    $count = $get_data['tahun'];
    $count = $get_data['counterL'];

            if($value != 0){   
                $count = $get_data['counterL'];
            }
            else{
                $count = 1;
            }        
            $count++;
            echo "$count";
        }
        else
        {
        $count = 1;
        echo "$count";
        }
    

    }

Thank you Joshuajames. I tried it,but I still didn't get it. The counter restart from 1 back. But it didnt based on the year. I tried insert the same year, but the counter start from 1 instead continued the counter.

Kindly start a new thread. Not understanding what is your problem.

Member Avatar for diafol

Kindly start a new thread. Not understanding what is your problem.

No need to start a new thread.

Thank you to all for your reply. I already solved the problem. Thank you Joshuajames for helped me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.