Auto incrementing a second field

NiallThistle

With an INSERT, I am currently creating new invoices with each row, but I also want each new invoice number to increment. I have no naming system for the invoices other than 1, 2, 3, 4, 5.

The one condition I have is that the invoices have to be associated to a job number.

I.e.

In table val_valuations, fields:

valuation_id is the primary, auto incrementing field
valuation_job refers to the job number
valuation_request refers to the invoice number

Each job will have a series of invoices from 1 to whatever number, but they will also increment from 1.

This is my current INSERT statement:

mysql_query("INSERT INTO val_valuations (valuation_job, valuation_man, valuation_mw, valuation_var, valuation_dw, valuation_date) VALUES('".$_POST["job"]."', '".$_POST["mukman"]."', '".$_POST["mworks"]."', '".$_POST["vos"]."', '".$_POST["dayworks"]."','$date') ") or die(mysql_error());

echo "Valuation successfully created.";

How best can I do this?

awbrowni30

How about the following...


$jobid = $_POST["job"];

// use the job number to determine the last invoice
$q = mysql_query("select MAX(valuation_request) from  val_valuations where valuation_job=$jobid")
$r = mysql_fetch_array($q);

$newInvoiceId = $r[0]+1;


mysql_query("INSERT INTO val_valuations (valuation_job, valuation_request, valuation_man, valuation_mw, valuation_var, valuation_dw, valuation_date) VALUES('".$_POST["job"]."', '".$newInvoiceId."', '".$_POST["mukman"]."', '".$_POST["mworks"]."', '".$_POST["vos"]."', '".$_POST["dayworks"]."','$date') ") or die(mysql_error());

In addition, you will want to ensure that your input is cleaned and not just blindly inserted into your table (*SQL Injection).

bradgrafelman

Doing a SELECT MAX and then a subsequent INSERT INTO query is going to leave you vulnerable to what is known as a race condition. If two requests are executing (somewhat) simultaneously; they could both SELECT the same value and then subsequently try to INSERT that duplicated value at the same time.

If you really want a second AUTO_INCREMENT-type, create a new table with two columns; invoice_nbr (PK, AUTO_INC), job_id (FK: val_valuations.valuation_job).

Then, perform your current INSERT query into val_valuations, use [man]mysql_insert_id/man to get the row's AUTO_INCREMENT value, INSERT that value into the new table above (which will generate your new invoice number), and finally use [man]mysql_insert_id/man again to get that new invoice number.

NiallThistle

Works a treat, thank you very much!!