Add letter JO on the autoincrement data produce

Here it is:

<?php
   include("config.php");
   $con = @mysql_connect($db_host, $db_username, $db_password);
   mysql_select_db($db_name) or die(mysql_error());
   
   $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
	   echo mysql_error();
	   die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
	   $currentSR = "JO".date("ymd")."001";
	   echo "$currentSR<br>";
   }
   else
   {
            // Stock Number iteration....
	   $currentYMD = date("ymd");
	   while($row = mysql_fetch_assoc($result))
	   {      
		   $currentSRYMD = substr($row['jo_number'], 0, 6);
		   if ($currentYMD > $currentSRYMD) 
		   {  
			   $currentSRNum = 1;
			   $currentYMD = $currentSRYMD;
		   }
		   else 
		   {  
			   $currentSRNum++;
		   }
		   $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);

		   echo "$currentSR<br>";
	   }      
   }
?>

I inserted the following records into the table:

120505 2012-05-05
120505 2012-05-05
120505 2012-05-05
120504 2012-05-04
120504 2012-05-04
120504 2012-05-04

You can view the result here:
http://www.itsawebthing.org/test/jo.php

I tried your code:


 $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
       echo "$currentSR<br>";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       while($row = mysql_fetch_assoc($result))
       {      
           $currentSRYMD = substr($row['jo_number'], 0, 6);
           if ($currentYMD > $currentSRYMD) 
           {  
               $currentSRNum = 1;
               $currentYMD = $currentSRYMD;
           }
           else 
           {  
               $currentSRNum++;
           }
           $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);

           echo "$currentSR<br>";
       }      
   }


And the output is:

JO120514001
JOJO1205001 //it shoud be: JO120514002

Thank you

As you can see from testing the script, the outputs are what you want them to be. I’m not sure why yours are showing up differently. Can you set up a test site and give us access to it? Maybe we can spot something that will identify the problem.

I don’t know how can I create test site so you can seev it.

here is my joborder.php


<?php
    error_reporting(0);
  date_default_timezone_set("Asia/Singapore"); //set the time zone  
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);
$Date_Shelve =date('Y-m-d H:i:s');  


?>
<html>
<head>
<title>Job Order</title> 
<link rel="stylesheet" type="text/css" href="kanban.css" />
<style type="text/css">
#SR_date{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .9em;
    margin-left: 10px;
    margin-right: 770px;
    width: auto;
    height: auto;
    float: left;
    top : 10px;
}

#disp_btn{
    position: relative;
    font-family: Arial, Helvetica, sans-serif;
    font-size: .8em;
    margin-left: 10px;
    top: 10px;
}

table {          
    margin: 12px;
    margin-left: 3px;
    font-family: Arial, Helvetica, sans-serif;  
    font-size: .9em;
    border: 1px solid #DDD;
}

th {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000; 
}    

td {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    border: 1px solid #DDD;
    text-align: left;
}

</style>    
<script type="text/javascript">
function disp(){
  window.location='JobOrderDisplay.php';
}
</script>  
</head>
<body>

<div id="ddcolortabs">
<ul>
<li id="current"> <a href="SalesOrder.php" title="Sales Order"><span>Order Management</span></a></li>
<li> <a href="ParameterSettings.php" title="Parameter Settings"><span>Parameter Settings</span></a></li>
<li style="margin-left: 1px"><a href="kanban_report.php" title="WIP Report"><span>WIP Report</span></a></li>
<li><a href="fsn.php" title="Finished Stock Note"><span>WMS FG</span></a></li>
<li><a href="ReceivingMaterials.php" title="WMS RM"><span>WMS RM</span></a></li>  
<li><a href="calendar_days.php" title="Calendar Days"><span>Calendar Days</span></a></li>
</ul>
</div>
<div id="ddcolortabs1">
<ul>
<li><a href="SalesOrder.php" title="Sales Order"><span>Sales Order</span></a></li>
<li id="current"><a href="JobOrder.php" title="Job Order"><span>Job Order</span></a></li>    
</ul>
</div> 

<div id="SR_date">

<input type="hidden" name="Date_Shelve" id="Date_Shelve" value="<?php echo $Date_Shelve; ?>" size="16" readonly="readonly" style="border: none;">    
</div>


<?php

//----approve button-----//
function convertToDozen ($total,  $cmpdType) {
     switch ($cmpdType) {
        case "P28" :
            $convTotal = (($total * 1000) / 22.00) / 12;
        break;
        case "P30" :
            $convTotal = (($total * 1000) / 25.10) / 12;
        break;
        case "P32" :
            $convTotal = (($total * 1000) / 22.50) / 12;
        break;
        case "P33" :
            $convTotal = (($total * 1000) / 25.00) / 12;
        break;
        case "P35" :
            $convTotal = (($total * 1000) / 25.25) / 12;
        break;
        case "P35M" :
            $convTotal = (($total * 1000) / 22.60) / 12;
        break;
        case "P35W" :
            $convTotal = (($total * 1000) / 22.50) / 12;
        break;
        case "P38" :
            $convTotal = (($total * 1000) / 26.70) / 12;
        break;
        case "P41" :
            $convTotal = (($total * 1000) / 24.80) / 12;
        break;
        case "P42" :
            $convTotal = (($total * 1000) / 18.90) / 12;
        break;
        case "P43" :
            $convTotal = (($total * 1000) / 25.00) / 12;
        break;
        case "P46" :
            $convTotal = (($total * 1000) / 27.00) / 12;
        break;
        case "P47" :
            $convTotal = (($total * 1000) / 27.60) / 12;
        break;
    }
     return round($convTotal, 2);        
}

$Approved = isset($_POST['priority']);
if ($Approved)
{
    $PO_No = $_POST['PO_No'];  
    $ETD = $_POST['ETD'];
    $SKUCode = $_POST['SKUCode'];
    $Description = $_POST['Description'];
    $POReq = $_POST['POReq'];   
    $priority = $_POST['priority'];
    
    //echo $SKUCode;
   // exit;
//echo "<meta http-equiv='refresh' content='1; url=JobORderApproved.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);
            //$row['jo_number'] = 'JO' . $row['jo_number']; 

           // $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));
            //$currentSRNum = int(substr( 'JO' . $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); 
   
    $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
       echo "$currentSR<br>";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       while($row = mysql_fetch_assoc($result))
       {      
           $currentSRYMD = substr($row['jo_number'], 0, 6);
           if ($currentYMD > $currentSRYMD) 
           {  
               $currentSRNum = 1;
               $currentYMD = $currentSRYMD;
           }
           else 
           {  
               $currentSRNum++;
           }
           $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);

           echo "$currentSR<br>";
       }      
   }

   
    
     $sql = "SELECT SKUCode, Materials, Comp, Qty 
     FROM bom  WHERE SKUCode = '$SKUCode'";
     $res = mysql_query($sql, $con);
     
    //while ($row = mysql_fetch_assoc($res)){
    ($row = mysql_fetch_assoc($res));
     $Materials = $row['Materials'];
     $Qty = $row['Qty'];
     $Comp = $row['Comp'];
     
     $ReqQty = $Qty * $POReq;
     
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);
    // }

//----------kanban----//

$sql = "DELETE FROM MES_REPORT_MAIN";
mysql_query($sql, $con);
$sql = "INSERT INTO MES_REPORT_MAIN 
       (REFNUM, 
        LOT_CODE, 
        WEIGHING_OUTPUT,
        COMPOUNDING_OUTPUT,
        EXTRUSION_OUTPUT,
        FORMING_OUTPUT,
        DIPPING_OUTPUT, 
        MOULDING_OUTPUT, 
        DATE_ENTRY_WEIGHING, 
        DATE_ENTRY_COMPOUNDING, 
        DATE_ENTRY_EXTRUSION,
        DATE_ENTRY_FORMING,
        DATE_ENTRY_DIPPING, 
        DATE_ENTRY_MOULDING)
        SELECT 
        REFNUM, 
        LOT_CODE,
        WEIGHT, 
        NULL,
        NULL,
        NULL,
        NULL, 
        NULL,
        DATE_ENTRY,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
        FROM WEIGHING";
mysql_query($sql,$con);
    
$sql =  "INSERT INTO MES_REPORT_MAIN
        (REFNUM, 
        LOT_CODE, 
        WEIGHING_OUTPUT,
        COMPOUNDING_OUTPUT,
        EXTRUSION_OUTPUT,
        FORMING_OUTPUT,
        DIPPING_OUTPUT, 
        MOULDING_OUTPUT, 
        DATE_ENTRY_WEIGHING, 
        DATE_ENTRY_COMPOUNDING, 
        DATE_ENTRY_EXTRUSION,
        DATE_ENTRY_FORMING,
        DATE_ENTRY_DIPPING, 
        DATE_ENTRY_MOULDING)
        SELECT 
        REFNUM, 
        LOT_CODE, 
        NULL,
        WEIGHT,
        NULL,
        NULL,
        NULL,
        NULL, 
        NULL,
        DATE_ENTRY,
        NULL,
        NULL,
        NULL,
        NULL
        FROM COMPOUNDING";
mysql_query($sql,$con);
       
$sql = "INSERT INTO MES_REPORT_MAIN 
            (REFNUM, 
            LOT_CODE, 
            WEIGHING_OUTPUT,
            COMPOUNDING_OUTPUT,
            EXTRUSION_OUTPUT,
            FORMING_OUTPUT,
            DIPPING_OUTPUT, 
            MOULDING_OUTPUT, 
            DATE_ENTRY_WEIGHING, 
            DATE_ENTRY_COMPOUNDING, 
            DATE_ENTRY_EXTRUSION,
            DATE_ENTRY_FORMING,
            DATE_ENTRY_DIPPING, 
            DATE_ENTRY_MOULDING)
        SELECT 
            REFNUM, 
            LOT_CODE, 
            NULL,
            NULL,
            WEIGHT,
            NULL,
            NULL,
            NULL, 
            NULL,
            NULL,
            DATE_ENTRY,
            NULL,
            NULL,
            NULL
        FROM EXTRUSION";      
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN 
            (REFNUM, 
            LOT_CODE, 
            WEIGHING_OUTPUT,
            COMPOUNDING_OUTPUT,
            EXTRUSION_OUTPUT,
            FORMING_OUTPUT,
            DIPPING_OUTPUT, 
            MOULDING_OUTPUT, 
            DATE_ENTRY_WEIGHING, 
            DATE_ENTRY_COMPOUNDING, 
            DATE_ENTRY_EXTRUSION,
            DATE_ENTRY_FORMING,
            DATE_ENTRY_DIPPING, 
            DATE_ENTRY_MOULDING)
        SELECT 
            REFNUM, 
            LOT_CODE, 
            NULL,
            NULL,
            NULL,
            OUTPUT_QTY,
            NULL,
            NULL, 
            NULL,
            NULL,
            NULL,
            DATE_ENTRY,
            NULL,
            NULL,
        FROM FORMING"; 
mysql_query($sql,$con);

$sql = "INSERT INTO MES_REPORT_MAIN 
            (REFNUM, 
            LOT_CODE, 
            WEIGHING_OUTPUT,
            COMPOUNDING_OUTPUT,
            EXTRUSION_OUTPUT,
            FORMING_OUTPUT,
            DIPPING_OUTPUT, 
            MOULDING_OUTPUT, 
            DATE_ENTRY_WEIGHING, 
            DATE_ENTRY_COMPOUNDING, 
            DATE_ENTRY_EXTRUSION,
            DATE_ENTRY_FORMING,
            DATE_ENTRY_DIPPING, 
            DATE_ENTRY_MOULDING)
        SELECT 
            REFNUM, 
            LOT_CODE, 
            NULL,
            NULL,
            NULL,
            NULL,
            OUTPUT_QTY,
            NULL, 
            NULL,
            NULL,
            NULL,
            NULL,
            DATE_ENTRY,
            NULL
        FROM DIPPING"; 
mysql_query($sql,$con);
$sql = "INSERT INTO MES_REPORT_MAIN 
            (REFNUM, 
            LOT_CODE, 
            WEIGHING_OUTPUT,
            COMPOUNDING_OUTPUT,
            EXTRUSION_OUTPUT,
            FORMING_OUTPUT,
            DIPPING_OUTPUT, 
            MOULDING_OUTPUT, 
            DATE_ENTRY_WEIGHING, 
            DATE_ENTRY_COMPOUNDING, 
            DATE_ENTRY_EXTRUSION,
            DATE_ENTRY_FORMING,
            DATE_ENTRY_DIPPING, 
            DATE_ENTRY_MOULDING
            )
        SELECT 
            REFNUM, 
            LOT_CODE, 
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            OUTPUT_QTY, 
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            DATE_ENTRY,
        FROM MOULDING"; 
mysql_query($sql,$con);


$sql = "DELETE FROM KANBAN_REPORT_OPEN";
mysql_query($sql,$con);

$sql = "INSERT INTO KANBAN_REPORT_OPEN";
$sql =  $sql . " " . "SELECT REFNUM, LOT_CODE, 
        SUM(WEIGHING_OUTPUT) AS WEIGHING, 
        SUM(COMPOUNDING_OUTPUT) AS COMPOUNDING, 
        SUM(EXTRUSION_OUTPUT) AS EXTRUSION, 
        SUM(FORMING_OUTPUT) AS FORMING,
        SUM(DIPPING_OUTPUT)AS DIPPING, 
        SUM(MOULDING_OUTPUT) AS MOULDING, 
        IF(NOT ISNULL(SUM(COMPOUNDING_OUTPUT)), 0, SUM(WEIGHING_OUTPUT)) AS WIP_1,
        IF(NOT ISNULL(SUM(EXTRUSION_OUTPUT)), 0, SUM(COMPOUNDING_OUTPUT)) AS WIP_2,
        IF(NOT ISNULL(SUM(FORMING_OUTPUT)), 0, SUM(EXTRUSION_OUTPUT)) AS WIP_3,
        IF(NOT ISNULL(SUM(DIPPING_OUTPUT)), 0, SUM(FORMING_OUTPUT)) AS WIP_4,
        IF(NOT ISNULL(SUM(MOULDING_OUTPUT)), 0, SUM(DIPPING_OUTPUT)) AS WIP_5,
        SUM(MOULDING_OUTPUT) AS WIP_6
        FROM MES_REPORT_MAIN GROUP BY REFNUM HAVING ISNULL(SUM(MOULDING_OUTPUT))";
$result = mysql_query($sql,$con);

  $sql  =  "SELECT SUM(WIP_1) AS WEIGHING_TOTAL, "; 
    $sql .=  "SUM(WIP_2) AS COMPOUNDING_TOTAL, ";
    $sql .=  "SUM(WIP_3) AS EXTRUSION_TOTAL, ";                                                        
    $sql .=  "SUM(WIP_4) AS FORMING_TOTAL, ";
    $sql .=  "SUM(WIP_5) AS DIPPING_TOTAL, ";
    $sql .=  "SUM(WIP_6) AS MOULDING_TOTAL ";
    $sql .=  "FROM KANBAN_REPORT_OPEN WHERE SUBSTRING(LOT_CODE, 9) = '$Comp'";
    
    
    
    $result = mysql_query($sql, $con);
    $weighingTotal = mysql_result($result, 0, "WEIGHING_TOTAL");
    $weighingTotal = convertToDozen($weighingTotal, $Comp);
    $weighingAccum = $weighingAccum + $weighingTotal;
    
    $compoundingTotal = mysql_result($result, 0, "COMPOUNDING_TOTAL");
    $compoundingTotal = convertToDozen($compoundingTotal, $Comp);
    $compoundingAccum = $compoundingAccum + $compoundingTotal;
    
    $extrusionTotal = mysql_result($result, 0, "EXTRUSION_TOTAL");
    $extrusionTotal = convertToDozen($extrusionTotal, $Comp);
    $extrusionAccum = $extrusionAccum + $extrusionTotal; 
    
    $formingTotal = mysql_result($result, 0, "FORMING_TOTAL");
    $formingTotal = convertToDozen($formingTotal, $Comp);
    $formingAccum = $formingAccum = $formingTotal;
    
    $dippingTotal = mysql_result($result, 0, "DIPPING_TOTAL");
    $dippingTotal = convertToDozen($dippingTotal, $Comp);
    $dippingAccum = $dippingAccum + $dippingTotal;
    
    $totalY = $weighingTotal + $compoundingTotal + $extrusionTotal + $formingTotal + $dippingTotal;

//------Total Kanban Doz - ReqQty----//



/*$sql = "SELECT SKUCode, Materials, Comp, Qty, ReqQty 
     FROM bom  WHERE SKUCode = '$SKUCode'";
     $res = mysql_query($sql, $con);
     
     while ($row = mysql_fetch_assoc($res)){
       $Materials = $row['Materials'];
       $Comp = $row['Comp'];
       $ReqQty = $row['ReqQty'];*/
       
       
if($totalY >= $ReqQty){
    $ReqQty_Update = "0";
    $ReqQty_Issued = $ReqQty;
    //$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials' AND Comp = '$Comp' AND ReqQty = '$ReqQty'";
$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";

    $resReqQty = mysql_query($sql, $con);
}
elseif($totalY < $ReqQty){
    $ReqQty_Update = $ReqQty - $totalY;
    $ReqQty_Issued = $totalY - $ReqQty;
    
    //$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials' AND Comp = '$Comp' AND ReqQty = '$ReqQty'";
$sql = "UPDATE bom SET ReqQty_Update = '$ReqQty_Update', ReqQty_Issued = '$ReqQty_Issued' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
    $resReqQty = mysql_query($sql, $con);
}
    // }
if($ReqQty_Update == '0.00'){
    $Status = 'Close';
}
else{
    $Status = 'Open';
}
  

     $sql = "INSERT INTO job_order (jo_number, jo_date, ETD, PO_No, SKUCode, Description, POQty, Priority, Status)
VALUES ('$currentSR', '$Date_Shelve', '$ETD', '$PO_No', '$SKUCode', '$Description', '$POReq', '$priority', '$Status')";

$res = mysql_query($sql, $con);


    $sql = "SELECT compound, req_total FROM jo_total";
    $result = mysql_query($sql, $con);
    
    $num = mysql_num_rows($result);
    
    if ($num >= 1){
        $sql = "UPDATE jo_total SET compound = (SELECT DISTINCT Comp FROM bom WHERE Comp = compound), req_total = (SELECT SUM(ReqQty_Issued) AS req_total FROM bom WHERE Comp = compound GROUP BY Comp)";
        $res_up = mysql_query($sql, $con);
    }
    else{
        $sql = "INSERT INTO jo_total (compound, req_total) SELECT Comp, SUM(ReqQty_Issued) AS ReqQty_Issued FROM bom 
        GROUP BY Comp";
        $res_in = mysql_query($sql, $con);
    }
     echo "<meta http-equiv='refresh' content='0; url=JobOrder.php'>";  
     
     
   
}
else{
    
}
 



$sql = "SELECT jo_number, ETD, PO_No, SKUCode, Description, POQty, Priority, Status
FROM job_order ORDER BY jo_number, ETD";
$res_jo = mysql_query($sql,$con);
?>

<table>
<th style="width: 72px;"> JO No.</th>
<th style="width: 72px;"> ETD </th>
<th style="width: 192px;"> PO No. </th>
<th style="width: 101px;"> SKU Code </th>
<th style="width: 222px;"> Description </th>
<th style="width: 72px;"> PO Req </th>
<th style="width: 105px;"> Status</th>

</table>
<div id="kanban_table" style="overflow:auto; height:200px;">
<table>
<?php
$sql = "SELECT FromMonth , ToMonth FROM so_month";
$res = mysql_query($sql, $con);

$row = mysql_fetch_assoc($res);

$FromMonth = $row['FromMonth'];
$ToMonth = $row['ToMonth'];

$sql = "SELECT ETD, PO_No, SKUCode, Description, POReq 
FROM sales_order  WHERE NOT EXISTS (SELECT PO_No FROM job_order WHERE job_order.PO_No = sales_order.PO_No) AND MONTH(ETD) BETWEEN '$FromMonth' AND '$ToMonth' ORDER BY ETD ASC LIMIT 10 ";
$res_so = mysql_query($sql, $con);
//$rowCounter = 1;
while($row = mysql_fetch_assoc($res_so)){ 
echo "<form name='joborder_form' action='' method='post'>";   
$PO_No = $row['PO_No']; 
//echo "<tbody>"; 
  
echo "<tr>
<td><input type='text' value='' style='border:none;' size='10'></td>
<td><input type='text' name='ETD' id='ETD' value='$row[ETD]' style='border:none;' size='10' readonly='readonly'></td> 
<td><input type='text' name='PO_No' id='PO_No' value='$row[PO_No]' style='border:none;' size='30' readonly='readonly'></td>
<td><input type='text' name='SKUCode' id='SKUCode' value='$row[SKUCode]' style='border:none;' size='15' readonly='readonly'></td>
<td><input type='text' name='Description' id='Description' value='$row[Description]' style='border:none;' size='35' readonly='readonly'></td>
<td><input type='text' name='POReq' id='POReq' value='$row[POReq]' style='border:none;' size='10' readonly='readonly'></td> 
<td><input type='submit' name='priority' value='Approved' id='priority'></td>
</tr>";
//$rowCounter = $rowCounter + 1;

echo "</form>";
 
}
echo "</table>";
echo "</div>"; 
?>

<div style='overflow:auto; height:200px;'>
<table>
<?php
while($row_job = mysql_fetch_assoc($res_jo)){
   // echo "<tbody>";
    echo "<tr>
    <td><input type='text' name='JO_No_' id='JO_No_' value='$row_job[jo_number]' style='border:none;width:auto;' size='10' readonly='readonly'></td>
    <td><input type='text' name='ETD_' id='ETD_' value='$row_job[ETD]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='PO_No_' id='PO_No_' value='$row_job[PO_No]' style='border:none;' size='30' readonly='readonly'></td>
    <td><input type='text' name='SKUCode_' id='SKUCode_' value='$row_job[SKUCode]' style='border:none;' size='15' readonly='readonly'></td>
    <td><input type='text' name='Description_' id='Description_' value='$row_job[Description]' style='border:none;' size='35' readonly='readonly'></td>
    <td><input type='text' name='POReq_' id='POReq_' value='$row_job[POQty]' style='border:none;width:auto;' size='10' readonly='readonly'></td> 
    <td><input type='text' name='priority_' value='$row_job[Priority] | $row_job[Status]' id='priority_' style='border:none;width:auto;' size='15' readonly='readonly'></td> 
    </tr>" ;
}

echo "</table>";
echo "</div>"  ;
echo "</div>";
?>
<div id="disp_btn">
<input type="button" name="print" value="display" onclick="disp()">
</div>
</body>
</html>

the jo number was only create after click the approve button.

Thank you so much for your help…

Now that I post my whole code, I hope it could help to find you why when I put your code in my code it did not work,but when it is stand alone ot works.

I really appreciate your help.

I hope this time we could find the problem and solve it …

Thank you very much

kindly visit this site:
http://www.topmovietrailers2012.blogspot.com

thank you

Thanks for posting additional info. I will have a look today and keep you posted with my progress.

Ok, lets try this:

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

Change it to:
$currentSRYMD = substr($row[‘jo_number’], 2, 6);

Do that and let us know what you got.

The above probably will only partially fix your problem.

Try this instead:

<?
   $sql = "SELECT jo_number FROM job_order ORDER BY jo_date DESC LIMIT 1";
   $result = mysql_query($sql, $con);
        
   if (!$result)
   {
	   echo mysql_error();
	   die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
	   $currentSR = "JO".date("ymd")."001";
   }
   else
   {
            // Stock Number iteration....
	   $currentYMD = date("ymd");
	   $row = mysql_fetch_assoc($result);
  
	   $currentSRYMD = substr($row['jo_number'], 2, 6);
	   $currentSRNum = substr($row['jo_number'], 8, 3);
	   if ($currentYMD > $currentSRYMD) 
	   {  
		   $currentSRNum = 1;
		   $currentYMD = $currentSRYMD;
	   }
           else 
           {  
		   $currentSRNum++;
           }
	   $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
   }      
?>

Thank you so much…It works…

Thank you…Thank you…Thank you

I’m glad we figured it out. As you can see, more info provided makes it easier to pinpoint problem area. Good luck with your project.

Thank you very much:)

Thank you very much:)

Hi…

I thought before that my issue in jo auto increment is solved, but Io notice that on the second day that I add approve jo number it was the same on the first date.

for example:

JO120515001
JO120515002
the next day I add, the displayed output is:

JO120515001 // the correct shoulb be JO120516001

here is the code:


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

   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       $row = mysql_fetch_assoc($result);

       $currentSRYMD = substr($row['jo_number'], 2, 6);
       $currentSRNum = substr($row['jo_number'], 8, 3);
       if ($currentYMD > $currentSRYMD)
       {
           $currentSRNum = 1;
           $currentYMD = $currentSRYMD;
       }
           else
           {
           $currentSRNum++;
           }
       $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
   }


Thank you

Looks like we got this line transposed:
$currentYMD = $currentSRYMD;

The code should be this:

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

   if (!$result)
   {
       echo mysql_error();
       die();
   }
   $total = mysql_num_rows($result);
   if ($total <= 0)
   {
       $currentSR = "JO".date("ymd")."001";
   }
   else
   {
            // Stock Number iteration....
       $currentYMD = date("ymd");
       $row = mysql_fetch_assoc($result);

       $currentSRYMD = substr($row['jo_number'], 2, 6);
       $currentSRNum = substr($row['jo_number'], 8, 3);
       if ($currentYMD > $currentSRYMD)
       {
           $currentSRNum = 1;
           $currentSRYMD = $currentYMD;
       }
       else
       {
           $currentSRNum++;
       }
       $currentSR = "JO" . $currentSRYMD . sprintf("%03d", $currentSRNum);
   }
?>

Hopefully we got everything right this time :slight_smile:

Thank you so much it works:)