PHP MyQL Brain Buster

[orbi]

I have a problem, which is melting my noodle.
I hope i can even explain it, help would be rewarded with my eternal gratitude 🙂

I am designing a system where you can log a 'job' and a unique job number is allocated. This in its self is not a problem.

I am using PHP and MySQL to try to develop this system.
Here Goes:
The user selects a year that the job was done on (for retro-logging jobs) i then need the database to log a unique number in this format 198900001 (for first job in 1989) or 200400001 (for first job in 2004) I think u get the idea ... The problem is I have no idea how to get it to work..

Please help I will try to answer any questions ASAP.

Orbi

[devinemke]

why not just create an auto_incrementing interger field in your table (as a primay key field)? mySQL will automagically create a new unique integer for each record.

[gotissues68]

You're taking form data, so you could have field such as "year" and year would take the year the job was done in and build in logic that checks the database and if the grabs and attaches the next available ID for the year to the form data when its inserted.

[orbi]

Youll have to excuse me if I haven’t understood you properly im still a bit wet behind the ears.

"why not just create an auto_incrementing integer field in your table (as a primary key field)? mySQL will automagically create a new unique integer for each record."

But the auto increment would allocate lets say 00001 to 2004 and then 0002 to 1989 leaving no 0002 for 2004... if you get my meaning 🙂

gotissues68 That’s what i want to do alright but its how to do it is what has got me.

Here is what i did (which didn’t work).
I set up a table for each year with fields of say job number ((for 2004 table) auto increment from jobid= 200400000 job name, description)

but when i have the form for logging this data you can choose a year, enter a name and enter a description but how would i then add the details to the separate tables??
ie
If selected year = 2004 then add details to job2004 (table)
if selected year = 1989 then add to job1989

I don’t know how to do this

Ant the other thing i tried was to create a first page where you select a year, say 2004 and that would set up a variable which could then be put into the following page (details page) so that it would know what table to send it to ie job2004.

I am very confused and don’t have enough programming experience to know how to solve this. Any one? 🙂

Orbi

[devinemke]

Originally posted by orbi
I am designing a system where you can log a 'job' and a unique job number is allocated. This in its self is not a problem.

if the purpose of this field is to have a unique "job number" for each record then my first advice still stands: create an auto_incrementing interger field in your table (as a primay key field). mySQL will automagically create a new unique integer for each record. if you need to also store the year/month/date/time of the job then store that in a separate datetime field. why create a special formatting for unique record ID's when mySQL will do it for you? database systems are setup with specialized field types for these purposes. this is pretty standard database design.

[orbi]

Ok i may not be explaining myself well ...

If i do what you say and create an auto incrementing field thats fine for entering jobs from now on eg
i have a new job it goes into the table gets the latest number and presto.

But what happens for putting in old or jobs from previous years.

E.G. I put in a job that comes in today it is the first job this year and the first job on the system it gets the number 00001 as a unique num (and its auto incrementing so the next job is 00002) and thats fine

Ok then lets say i place a job in the system from 2001 and i log it after these two jobs the allocated number is 00003, again i can put in 2001 in another field to identify it as a job from 2001 but .. and this is the problem .... then the number 00003 is taken, the next job entered from 2004 will be the next available number which will be 0004!! See I miss the number allocation of 00003 for the year 2004. i need to log sequential job numbers for every year.

I hope this clears it up.

Any ideas??

Orbi

[devinemke]

let's go over some database basics. each record in the database will have a unique number as well as a date/time. these two fields have no relation to each other. the order in which they were put into the database does not matter either. you don't need to worry about the sequence of the auto_incrementing primary key field, it's just an integer. let's say this is our data structure:

jobs
job_id (int)
datetime (datetime)
description (text)

let's say we insert a new record:

INSERT INTO jobs SET datetime = '2004-07-26 10:00:00', description = 'cleaning out closet'

now let's do another record:

INSERT INTO jobs SET datetime = '2003-06-03 08:30:00', description = 'learn spanish'

the first job will have the job_id "1" and the next one would have job_id "2" even though the date of 2 is before 1. again, it doesn't matter. mySQL (and all other databases) have many functions that allow you very precisely select the data you want. let's say you want to pull all jobs in 2002:

SELECT * FROM jobs WHERE YEAR(datetime) = '2002'

lets say you want to grab just the first 3 jobs from June 1998:

SELECT * FROM jobs WHERE MONTH(datetime) = '06' AND YEAR(datetime) = '1998' ORDER BY datetime LIMIT 3

make sense? don't worry about the order they are in the db, you can re-order them any way you want with your SELECT query.

[orbi]

A picture may speak more words

---

I posted this before reading your post above will read and reply.

---

[orbi]

Ok that’s cool but then for invoicing and printed documentation i need to allocate a number in this format 200400001 (fist job in 2004 or 200100002 (sec. job in 2001) to each job. i need to do this when logging the job details. Is there a way i can do that. What do u recommend.

Orbi

[devinemke]

lets' say you want to calculate the "invoice number" for job_id # 651:

$result = mysql_query('SELECT *, YEAR(datetime) AS year FROM jobs WHERE job_id = 651');
$row = mysql_fetch_assoc($result);

$result = mysql_query("SELECT COUNT(*) AS count FROM jobs WHERE YEAR(datetime) = '" . $row['year'] . "' AND datetime >= '" . row['datetime'] . "'");
$count = mysql_result($result, 0);
$invoice_number = $row['year'] . sprintf('%05s', $count);
echo 'the invoice number for job_id ' . $row['job_id'] . ' is ' . $invoice_number;

[bubblenut]

I think one point which is key here is that a database table's unique_id is just that, a unique_id, nothing more. You could put have a random string as your unique_id if you wanted (although you'd have a little harder time avoiding duplicates) so long as it's unique.

[orbi]

devinemke that is cool... I think I need to take that away and digest it . Im sure that is the solution i just have to work out how.
Thanks for your help i have reached my watermark. Could you do one more thing for me. could you comment each line then i will go peacefully into the land of study.

Thanks again

Orbi

[devinemke]

Originally posted by orbi
Could you do one more thing for me. could you comment each line then i will go peacefully into the land of study.

ok, actually let's turn this into a function. this function assumes that the data structure is exactly as i had posted in my earlier example and that you are already connected to the database:

// define the function, it takes the job_id as it's argument
function get_invoice_number($job_id)
{
	// get the datetime and year for the given job_id
	$result = mysql_query('SELECT datetime, YEAR(datetime) AS year FROM jobs WHERE job_id = ' . $job_id);

// stuff the result into an array
$row = mysql_fetch_assoc($result);

// see how many records exit for that year that are less than or equal to the given job_id (this will tell us which job this is in sequence for that year)
$result = mysql_query("SELECT COUNT(*) AS count FROM jobs WHERE YEAR(datetime) = '" . $row['year'] . "' AND datetime >= '" . row['datetime'] . "'");

// put the result into a variable called $count
$count = mysql_result($result, 0);

// the invoice number is the year following by the count which padded with zeros for a 5 digit interger
$invoice_number = $row['year'] . sprintf('%05s', $count);

// spit out the invoice_number
return $invoice_number;
}

// i wonder what the invoice number for job_id 651 is?
$invoice_number = get_invoice_number(651);

[orbi]

This is why i should leave it to the experts.
🙂

That’s fantastic devinemke i will endeavour to learn how to include that in my app. Thank you for your help again you have been very helpful.

I wish i had a designated programmer here 🙁

Orbi
<leaves chuffed and impressed>

[orbi]

😃