Creating a Job Number script.

[andrew_biggart]

I would like to create a Job Numbering system for all of my jobs.

 

 

Job number format  :    print-001232

 

I would like the job numbers to be sequential. So  we will have a drop down list with three categories.  which will form the first part of the job number. (print- ) and then I would like the 6 digits after it to be sequential starting from 000001.

 

What is the best way of going about doing this?

[BlueSkyIS]

i would insert a new record, take the mysql_insert_id() from the new record and pad that string with 0's on the left.

[andrew_biggart]

But then you could end up with a six digit number and the 0's padded infront to of it. (0000023456) I just want my auto increment to start at 000001 and add one every time it adds a new record.

[Psycho]

But then you could end up with a six digit number and the 0's padded infront to of it. (0000023456) I just want my auto increment to start at 000001 and add one every time it adds a new record.

 

Do you know what "pad" means?

 

Also, do you need the sequential numbers to be independanct for each category? If not, then BlueSkyIS's solution would work. But, if they need to be unique for each category you could use three separate tables.

[andrew_biggart]

Yes I have looked into it now.

 

I have used this

<?php echo str_pad("456", 6 , "0"); ?>

which displays 456000 but how would I make it display 000456?

[Psycho]

Not really no!

 

From the manual for str_pad():

str_pad — Pad a string to a certain length with another string

 

Example

//Two digit number padded to 6 characters with '0'
echo str_pad('12', 6, '0', STR_PAD_LEFT); //Output: 000012
//Five digit number padded to 6 characters with '0'
echo str_pad('12345', 6, '0', STR_PAD_LEFT); //Output: 012345

[andrew_biggart]

Well preferable I would like the job numbers to be sequential for each category. Basically A user will enter a job into a txt box select a category and submit the job to a database.

 

A unique sequential job number should then be created like this category-123456.

 

[andrew_biggart]

Ok I understand how padding works now but im failing to see how to link everything together to get my desired effect. Could you give me an example of the sql statement I would need to use?

 

Thank you for you help.

[kenrbnsn]

You can also use sprintf

<?php
$x = rand(1,999999); // just get a random number to test
$padded_x = sprintf("%06d",$x);
echo $padded_x;
?>

 

Ken

[andrew_biggart]

I tried a different way of doing it which was to select the last auto increment number and add one to it to create the job number but it hasn't worked. I am going in the right direction with this?

 

<?php


$host=""; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


$category = $_POST['category'];
$name = $_POST['project-name'];


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

$sql = " SELECT job-id FROM job-numbers ORDER BY job-id DESC LIMIT 1 ";
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
$jobid = $rows['job-id'];
$jobnumber = $jobid++;
$newjobid = str_pad($jobnumber, 6, '0', STR_PAD_LEFT);
$sql2="INSERT INTO job-numbers (category, name , jobnumber) VALUES ('$category', '$name', '$newjobid' )";
$result2=mysql_query($sql2);

if ($result2) {
echo"worked";
}
else {
echo"hasnt";
}	
}
else {}

?>
<form action="#" method="post">
    <select id="category" name="category">
        <option>Web</option>
        <option>Print</option>
    </select>
    <input type="text" name="project-name" id="project-name" />
    <input type="submit" name="submit" id="submit" />
</form>

 

 

[Psycho]

You stated you wanted separate job number lists for each category, but your code shows one table. If you only want to maintain one list (which will be the easiest solution) then just set up the ID field to be an auto-increment field and set the type as a separate field. Then don't specify a job ID when inserting and the DB will create  it for you. Very easy solution as the database will do all the work for you.

 

Below is some sample code (I also moved the logic for parsing the post data and connecting to the database. There is no need to do that if nothing was posted since you aren't going to do anything with it)

<?php

if(isset($_POST['submit']))
{
    // Connect to server and select database.
    $host     = ""; // Host name
    $username = ""; // Mysql username
    $password = ""; // Mysql password
    $db_name  = ""; // Database name
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
    
    //Parse & escape POSTed values
    $category = mysql_real_escape_string(trim($_POST['category']));
    $name     = mysql_real_escape_string(trim($_POST['project-name']));
    
    //Create & Run insert query
    $query = "INSERT INTO job-numbers (category, name) VALUES ('$category', '$name')";
    $result = mysql_query($query);
       
    if (!$result)
    {
        //Query failed, display error
        echo"Error: <br />\n" . mysql_error() . "<br />\nQuery:<br />\n{$query}\n";;
    }
    else
    {
        //Display job id of inserted record.
        $id = mysql_insert_id();
        $cat = htmlspecialchars((trim($_POST['category']));
        echo"The record {$cat}-{$id} was created.";
    }
}
?>
<html>
<body>
<form action="#" method="post">
    <select id="category" name="category">
        <option>Web</option>
        <option>Print</option>
    </select>
    <input type="text" name="project-name" id="project-name" />
    <input type="submit" name="submit" id="submit" />
</form>
</body>
</html>

 

If you do need separate consequtive numbers for each category it can be done, but will be more complex

[sasa]

If you need separate consequtive numbers for each category organize your table in this way

CREATE TABLE job_num (
  category varchar(11) NOT NULL,
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(22) NOT NULL,
  PRIMARY KEY (cat,id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

use combinated primary key

 

[Psycho]

use combinated primary key

 

Never used that before. Good to know. Do you know how to set that property for a table in phpmyadmin after the table is created?

[Psycho]

If you need separate consequtive numbers for each category organize your table in this way

CREATE TABLE job_num (
  category varchar(11) NOT NULL,
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(22) NOT NULL,
  PRIMARY KEY (cat,id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

use combinated primary key

 

 

Ok, I just tested that and it doesn't work the way I think you are stating. It does allow for duplicate IDs with records that have different names. But the auto-increment is still "global" - i.e. the value will be 1 greater than the last auto-increment value regardless of the secondary primary key. The combinated primary key just allows the same "key" value to be used mutliple times as long as different secondary primary values are used - but you have to do so manually. The auto-increment parameter will not do it automatically.

 

Using your table example above (note third record)

INSERT INTO job_num ('category', 'name')
VALUES ('A', 'some text'), ('A', 'some text'),
       ('B', 'some text'), ('A', 'some text')

Assuming these are the first four records, the auto-increment ID values will be 1, 2, 3, 4. Not, 1, 2, 1, 3

 

Still, this is a viable approach, but getting the next ID for each category will require a two-step process to query the DB for the highest current ID of the category, increment the value by 1, then insert the new record specifying the ID (i.e. don't let auto-increment do it).

[Psycho]

OK, you can create the next ID for the unique category with a single query. Still, you wouldn't let the auto-increment do it. Here is how:

 

INSERT INTO `job_num` (`id`, `category`, `name`)
VALUES ( (SELECT MAX(id)+1
            FROM `job_num`
            WHERE `category` = '$category'
            GROUP BY `category`),
          '$category', '$name')

[sasa]

sorry i change the field name from cat to category and forget to change  name in primary key

CREATE TABLE job_num (
  category varchar(11) NOT NULL,
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(22) NOT NULL,
  PRIMARY KEY (category,id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

and inser query is

INSERT INTO job_num (category, name)
VALUES ('A', 'some text'), ('A', 'some text'),
       ('B', 'some text'), ('A', 'some text')

[andrew_biggart]

Ok I have had a look at this today with a fresh head and this is what  I have come up with which will suit my needs.

<?php

if(isset($_POST['submit']))
{
    // Connect to server and select database.
    $host     = ""; // Host name
    $username = ""; // Mysql username
    $password = ""; // Mysql password
    $db_name  = ""; // Database name
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
    
    //Parse & escape POSTed values
    $category = mysql_real_escape_string(trim($_POST['category']));
    $name     = mysql_real_escape_string(trim($_POST['name']));

    //Create & Run insert query
    $query = "INSERT INTO jobnumbers (category, name) VALUES ('$category', '$name')";
    $result = mysql_query($query);

    if (!$result)
    {
        //Query failed, display error
        echo"Error: <br />\n" . mysql_error() . "<br />\nQuery:<br />\n{$query}\n";
    }
    else
    {
        //Display job id of inserted record.
        $id = mysql_insert_id();
	$jobid = str_pad($id, 6, '0', STR_PAD_LEFT);
        $cat = htmlspecialchars((trim($_POST['category'])));
	$jobnum = "{$cat}-{$jobid}";

	//Create & Run insert query
	$query2 = "INSERT INTO jobnumbers (jobnumber) VALUES ('$jobnum') WHERE jobid='$id' ";
	$result2 = mysql_query($query2);

	if (!$result2)
	{
		//Query failed, display error
		echo"Error 2: <br />\n" . mysql_error() . "<br />\nQuery:<br />\n{$query}\n";
	}
	else
	{		
        echo"Your job number for $name is $jobnum.";
	}
    }
}
?>

 

I am having a problem with the second sql statement and I am getting the following error.

 

Error 2:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE jobid='11'' at line 1

Query:

INSERT INTO jobnumbers (category, name) VALUES ('Web', '1234')

 

Any ideas?

 

 

 

[Psycho]

query2 = "INSERT INTO jobnumbers (jobnumber) VALUES ('$jobnum') WHERE jobid='$id' ";

 

INSERTS don't use a WHERE clause. I think you meant to do an UPDATE of the query you ran to initially insert the record. But, YOU DON'T NEED TO RUN THE SECOND QUERY! If the Job Number will always be the Category and the Job Number, then you don't need another field to add that concatenated value. It is a waste. When you need to look up a job using the complete job number, just split the value based on the dash and look up the job using something such as

SELECT * FROM jobnumbers  WHERE category='$cat' AND id='$id'