Add letter JO on the autoincrement data produce

newphpcoder · May 4, 2012, 2:27am

Hi…

I have code for auto increment of JO Number, now it display:
120504001
by using this code:


<?php
  $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
        $result = mysql_query($sql, $con);

        if (!$result) {
            echo 'failed';
            die();
        }
        $total = mysql_num_rows($result);
        if ($total <= 0) {
            $currentSRNum = 1;
             $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));

            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD)
            {
                $currentSRNum = 1;
            }
            else
            {
                $currentSRNum += 1;
            }

        }
        else {
//------------------------------------------------------------------------------------------------------------------
            // Stock Number iteration....
            $row = mysql_fetch_assoc($result);

            $currentSRNum = (int)(substr($row['jo_number'],0,3));

            $currentSRYear  = (int)(substr($row['jo_number'],2,2));
            $currentSRMonth = (int)(substr($row['jo_number'],0,2));
           $currentSRNum = (int)(substr($row['jo_number'],6,4));
            $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));

            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD)
            {
                $currentSRNum = 1;
            }
            else
            {
                $currentSRNum += 1;
            }
        }
//------------------------------------------------------------------------------------------------------------------
        $yearMonth = date('ymd');
        $currentSR = $currentYMD . sprintf("%03d", $currentSRNum);
?>

now I want it to be:
JO120504001
JO120504002
JO120504003
JO120504004
JO120505001
JO120504002
JO120504003

Any help is highly appreciated.

Thank you so much

Mandes · May 4, 2012, 8:19am

Just add the ‘JO’ to the value of the autoinc field once youve retrieved it from the database.


           // Stock Number iteration....  
            $row = mysql_fetch_assoc($result); 
            $row['jo_number'] = 'JO' . $row['jo_number']; 
            $currentSRNum = (int)(substr($row['jo_number'],2,5)); 
            ........
            ........

Note the refernces in your substr statements will have to be updated for the extra 2 characters

Youve also, FYI, a problem in the first half of your script that you run if the database returns no rows,

half way down you calling

$currentSRYMD = substr($row['jo_number'], 0, 6);

but obviously you had no rows (and you havent fetch_array’d yet) so you dont have that data available

StarLion · May 4, 2012, 12:39pm

I’m gonna say be very, very careful of what you’re doing. You’re using an auto-increment that has meaning. (date value at the front). If you have 1000 entries in a day, you’re gonna have problems.

newphpcoder · May 7, 2012, 12:32am

Mandes:

Just add the ‘JO’ to the value of the autoinc field once youve retrieved it from the database.
           // Stock Number iteration....  
            $row = mysql_fetch_assoc($result); 
            $row['jo_number'] = 'JO' . $row['jo_number']; 
            $currentSRNum = (int)(substr($row['jo_number'],2,5)); 
            ........
            ........
 
Note the refernces in your substr statements will have to be updated for the extra 2 characters

Youve also, FYI, a problem in the first half of your script that you run if the database returns no rows,

half way down you calling
$currentSRYMD = substr($row['jo_number'], 0, 6); 
but obviously you had no rows (and you havent fetch_array’d yet) so you dont have that data available

Hi…

I tried your suggested code:


 $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
        $result = mysql_query($sql, $con);
        
        if (!$result) {
            echo 'failed'; 
            die();
        }
        $total = mysql_num_rows($result);
        if ($total <= 0) {
            $currentSRNum = 1;
             $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));
           
            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD) 
            {  
                $currentSRNum = 1;
            } 
            else 
            {  
                $currentSRNum += 1;
            }     
             
        } 
        else {
//------------------------------------------------------------------------------------------------------------------
            // Stock Number iteration.... 
            $row = mysql_fetch_assoc($result);
            $row['jo_number'] = 'JO' . $row['jo_number']; 
            $currentSRNum = (int)(substr($row['jo_number'],2,5)); 

            //$currentSRNum = (int)(substr($row['jo_number'],0,3));
            
            $currentSRYear  = (int)(substr($row['jo_number'],2,2));
            $currentSRMonth = (int)(substr($row['jo_number'],0,2));
           $currentSRNum = (int)(substr($row['jo_number'],6,4));
         // $currentSRNum = (substr($row['jo_number'],6,4));
            $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));
           
            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD) 
            {  
                $currentSRNum = 1;
            } 
            else 
            {  
                $currentSRNum += 1;
            }                                           
        }
//------------------------------------------------------------------------------------------------------------------         
        $yearMonth = date('ymd');    
        $currentSR = $currentYMD . sprintf("%03d", $currentSRNum);

annd the output is:

120507401

I want it to be : JO120507001

Thank you

Mandes · May 7, 2012, 8:10am

I cant see in your code where you are outputting the whole JO number !!

The line I gave you prepends ‘JO’ to the value retrieved from your database, if you want to show that number to the browser you need to need to actually output it somewhere.

tom8 · May 7, 2012, 1:16pm

Your JO number is set in this line:
$row[‘jo_number’] = ‘JO’ . $row[‘jo_number’];

If you want $currentSR to be that then use this:
$currentSR = $row[‘jo_number’] . sprintf(“%03d”, $currentSRNum);

BTW, the row on this line is undefined:
$currentSRYMD = substr($row[‘jo_number’], 0, 6);

newphpcoder · May 8, 2012, 12:37am

So what should I do in this line ?

$currentSRYMD = substr($row[‘jo_number’], 0, 6);

Thank you

newphpcoder · May 8, 2012, 12:42am

I tried this:


$sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
        $result = mysql_query($sql, $con);

        if (!$result) {
            echo 'failed';
            die();
        }
        $total = mysql_num_rows($result);
        if ($total <= 0) {
            $currentSRNum = 1;
             $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));

            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD)
            {
                $currentSRNum = 1;
            }
            else
            {
                $currentSRNum += 1;
            }

        }
        else {
//------------------------------------------------------------------------------------------------------------------
            // Stock Number iteration....
            $row = mysql_fetch_assoc($result);
           // $row['jo_number'] = 'JO' . $row['jo_number'];
           // $currentSRNum = (int)(substr($row['jo_number'],2,5));
            $row['jo_number'] = 'JO' . $row['jo_number'];

            $currentSRNum = (int)(substr($row['jo_number'],0,3));

            $currentSRYear  = (int)(substr($row['jo_number'],2,2));
            $currentSRMonth = (int)(substr($row['jo_number'],0,2));
           $currentSRNum = (int)(substr($row['jo_number'],6,4));
         // $currentSRNum = (substr($row['jo_number'],6,4));
            $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));

            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD)
            {
                $currentSRNum = 1;
            }
            else
            {
                $currentSRNum += 1;
            }
        }
//------------------------------------------------------------------------------------------------------------------
        $yearMonth = date('ymd');
        //$currentSR = $currentYMD . sprintf("%03d", $currentSRNum);
        $currentSR = $row['jo_number'] . sprintf("%03d", $currentSRNum);

the output is:

JO120507002701
JOJO120507002701508

Thank you for your help even still wrong output

tom8 · May 8, 2012, 2:15pm

From what you showing us the jo_number in the database has already been updated with the prefix JO.

newphpcoder · May 9, 2012, 12:38am

Sorry, I don’t understand what you mean:(
I just want to autoincrement is the date and 001 not the JO letter:

JO120507001
JO120507002
JO120507003
JO120508001
JO120508002

Thank you

tom8 · May 9, 2012, 11:30am

You use this line to insert JO into jo_number:
$row[‘jo_number’] = ‘JO’ . $row[‘jo_number’];

The record you got from the database was JO120507002701 and then that line inserted with another JO prefix and so ended up with what you got:
JOJO120507002701508

508 is added from sprintf() function.

Perhaps we can suggest code changes if you post the entire script of the function you using for the update.

newphpcoder · May 10, 2012, 12:05am

Hi Tom8,

I really appreciate your help…

I tried to understand your suggestion, and still I can’t solve my problem, Can you do me a favor, I will attach my whole code and pin point me what should I change and what shoud I put code?


 $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
        $result = mysql_query($sql, $con);
        
        if (!$result) {
            echo 'failed'; 
            die();
        }
        $total = mysql_num_rows($result);
        if ($total <= 0) {
            $currentSRNum = 1;
             $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));
           
            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD) 
            {  
                $currentSRNum = 1;
            } 
            else 
            {  
                $currentSRNum += 1;
            }     
             
        } 
        else {
//------------------------------------------------------------------------------------------------------------------
            // Stock Number iteration.... 
            $row = mysql_fetch_assoc($result);
         
            $currentSRNum = (int)(substr($row['jo_number'],0,3));
            
            $currentSRYear  = (int)(substr($row['jo_number'],2,2));
            $currentSRMonth = (int)(substr($row['jo_number'],0,2));
           $currentSRNum = (int)(substr($row['jo_number'],6,4));
         
            $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));
           
            $currentSRYMD = substr($row['jo_number'], 0, 6);
            $currentYMD = date("ymd");
            if ($currentYMD > $currentSRYMD) 
            {  
                $currentSRNum = 1;
            } 
            else 
            {  
                $currentSRNum += 1;
            }                                           
        }
//------------------------------------------------------------------------------------------------------------------         
        $yearMonth = date('ymd');    
        $currentSR = $currentYMD . sprintf("%03d", $currentSRNum);

Thank you so much

tom8 · May 10, 2012, 1:55am

It would help us if you do. I’ll do my best to pinpoint it. Other members may spot things I missed.

newphpcoder · May 10, 2012, 1:56am

What should I do?

Thank you

tom8 · May 10, 2012, 2:03am

… I will attach my whole code …

I thought that is what you are going to do.

It is getting late here so I’ll look it over tomorrow. Meanwhile other members here might suggest a solution.

newphpcoder · May 10, 2012, 2:07am

And then, now that I attach my whole code?whats next should we do?

Thank you so much

tom8 · May 10, 2012, 2:15am

I’m new here. I don’t know how to view your attachment. I think best just to post it like your PHP code.

newphpcoder · May 10, 2012, 2:18am

Here is my code:


$sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1"; 
        $result = mysql_query($sql, $con); 
         
        if (!$result) { 
            echo 'failed';  
            die(); 
        } 
        $total = mysql_num_rows($result); 
        if ($total <= 0) { 
            $currentSRNum = 1; 
             $currentYear  = (int)(date('y')); 
            $currentMonth = (int)(date('m')); 
            $currentDay = (int)(date('d')); 
            
            $currentSRYMD = substr($row['jo_number'], 0, 6); 
            $currentYMD = date("ymd"); 
            if ($currentYMD > $currentSRYMD)  
            {   
                $currentSRNum = 1; 
            }  
            else  
            {   
                $currentSRNum += 1; 
            }      
              
        }  
        else { 
//------------------------------------------------------------------------------------------------------------------ 
            // Stock Number iteration....  
            $row = mysql_fetch_assoc($result); 
          
            $currentSRNum = (int)(substr($row['jo_number'],0,3)); 
             
            $currentSRYear  = (int)(substr($row['jo_number'],2,2)); 
            $currentSRMonth = (int)(substr($row['jo_number'],0,2)); 
           $currentSRNum = (int)(substr($row['jo_number'],6,4)); 
          
            $currentYear  = (int)(date('y')); 
            $currentMonth = (int)(date('m')); 
            $currentDay = (int)(date('d')); 
            
            $currentSRYMD = substr($row['jo_number'], 0, 6); 
            $currentYMD = date("ymd"); 
            if ($currentYMD > $currentSRYMD)  
            {   
                $currentSRNum = 1; 
            }  
            else  
            {   
                $currentSRNum += 1; 
            }                                            
        } 
//------------------------------------------------------------------------------------------------------------------          
        $yearMonth = date('ymd');     
        $currentSR = $currentYMD . sprintf("%03d", $currentSRNum);

Thank you

tom8 · May 10, 2012, 2:25am

Hmmm… if this is your whole code I see why you are having problem. I will try to give you some suggestions in the morning.

newphpcoder · May 10, 2012, 2:33am

ok…Thank you so much