0

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.

5
Contributors
15
Replies
66
Views
3 Years
Discussion Span
Last Post by nurul_1
0

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

0

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

0

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
0

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.

0

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";

}




?>

Edited by nurul_1

0

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";
        }
    

    }

Edited by joshuajames.delacruz

0

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.

0

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

No need to start a new thread.

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.