PHP form to generate sequenced #

[rgracey]

Hi - I am creating a simple PHP Task Request form that will post to an email address. I am fine with everything except one problem. I need this form to generate an ID number which will also display in the Subject line. If someone requests a task and it is number 001, then the next person to request a task using this form is number 002. I would need to have the form write to a database, so that the next number +1 can be generated based on the previous number in the database. Any help is greatly appreciated.

[broj1]

Before writing a next task to the database you read the last task. However, if the user has the form opened in the browser, there might be a problem displaying the current last inserted task ID, since many tasks could have been inserted after opening the form. You can use ajax to refresh the task ID upon a click of a button or display the newly assigned ID ater the request. Maybe you specify y little your requirement bit more and provide some code you might already have.

[rgracey]

Thanks for your quick response. I don't think I was entirely clear (sorry). I'm making a task-request form for my web site so clients can fill it out and request a new task, which will arrive on my side via email. However, I want the request to generate a new Docket #, which would be one number higher than the last one. I'm good on the rest of the form in PHP, but it's the reading the last Docket # in the DB and then using that to +1 to create the new number (which would then post to the subject line in the email the form sends on submit). So, last job was SS000234, so the next time someone submits the form, it adds "SS000235" to the Subject line, so my PM can know the new Docket #. I have no code for this yet - except for a regular form (name, email, notes, etc.).

[broj1]

OK, I'll try to generate some steps I think would be appropriate and you then correct me where I might be wrong.

1. A user visits a task request form (say get_task.php)
2. The form action attribute is e.g. confirmed_task.php
3. The user fills-in all the necessary information and submits the form
4. The confirmed_task.php opens up displaying the task ID. It actually does these things:
   
   
   • It first reads the last inserted ID from the database i.e. SELECT task_id FROM tasks ORDER BY task_id ASC LIMIT 1
   • It then calculates the new ID like $newID = $lastID + 1 (do some juggling here to get a string in a form of SS000234)
   • It displays the task ID message (i.e. echo "You have been assigne a task# $newID")
   • It sends you an email with the info about the user and the task

If this is what you wanted then tell me which step is making problems.

Also maybe post the database table structure if you have designed it yet.

[rgracey]

Yes, that is essentially exactly what I want to do. I haven't created the DB yet but it would be the most basic possible, basically just the ID itself as the important thing is the sending of the email. The email is being picked up by another piece of task management software (Asana) which automatically creates a task based on the ID and title (field in form), both of which must post to the subject line of the email. I think what you gave me is exactly what I needed to go on. I am about to start constructing this now. I am more of a front-end developer so when it comes to interacting with a DB I need assistance. Hopefully I won't be a pain and bug you again. Thank you so much for your help! :)

[broj1]

If you get stuck just come back here with as specific question as possible. You might either continue here or mark this thread as solved and start a new one.

[rgracey]

So, I am apparently less useful than I thought I was. I have created the form and have it doing what I want in every way except generating the docket #. I have created the SQL database, and created a table with one column, but am stumped at creating rows and setting that first value for the form to incrementalize from. I am also stumped on how to implement the code to do what you suggested above. I know these boards don't like people asking others to do the work for them, so I balk at asking for more detailed help, though I desperately want it. Which is likely why my knowledge in this area never gets past this stage. I'm great with working with and manipulating code that already exists and works, but not-so-much in PHP and SQL when creating from scratch. HTML and CSS? Yes. Pretty much ever design/animation program out there, absolutely. This? internal scream

[rgracey]

Update: I'm getting the database done okay now, thanks to YouTube. Now I'm back to just being stuck with the PHP.

[broj1]

For all the database manipulation nad testing it is a good idea to use some database GUI client. Many people use phpmyadmin, a web based client to mysql (mind you, some people hate it, but there are alternatives). I hope you use something like that.

Anyway you can insert first record by hand and it would have a value 0. Then in PHP code always check for the last ID using the corrected SQL statement from my previous post (the one in my previous post has an error in it, sory). This query return the highest ID form the table.

$query = "SELECT task_id FROM tasks ORDER BY task_id DESC LIMIT 1";

I suggest you use PDO to access mysql in PHP. There is a good tutorial here. Do not use the mysql extension which is deprecated and will be kicked out of PHP anytime soon.

This is an example of code for getting new ID. It is not tested and should be adapted for your case. Also any error handling is ommited for clarity

// set up the connection
$db = new PDO('mysql:host=localhost;dbname=<YOURDBNAME>;charset=utf8', '<YOURUSERNAME>', '<YOURPASSWORD>');
// query the database
$stmt = $db->query("SELECT task_id FROM tasks ORDER BY task_id DESC LIMIT 1");
// get the row
$row = $stmt->fetch(PDO::FETCH_ASSOC)
// add 1 to the last ID from the table
$newID = $row['task_id'] + 1;
// insert the new ID tinto the table (you can also use a prepared statement here)
stmt = $db->query("INSERT INTO tasks (task_id) VALUES ':$newID'";

I would suggest docket added to db and then id retrieved (last inserted id). It could happen that 2 requests made simultaneously (very unlikely but possible). Only then email msg constructed.

[rgracey]

Hi - thanks for your response. Unfortunately that code is throwing errors. On a straight cut and paste it's throwing parsing errors. (I think there's a ; missing from line 6, but when I add it I get new errors). If I take out the last INSERT statement (I don't want to update the DB table on this page), then the very first line throws an error and says I don't have access to the DB. I don't know enough about this code you provided to properly troubleshoot it. :(

There should be no need to do a select query to get max id (last row id). You just add with an INSERT and ask for lastInsertId():

$sql = "INSERT INTO tasks (col1,col2,col3) VALUES (:val1,:val2,:val3)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(":val1"=>$val1,":val2"=>$val2,":val3"=>$val3));
$lastId = $dbh->lastInsertId();

The $lastId var now contains the PK id for the table. You now have all the data you need to send in the email.

[lorenzoDAlipio]

I honestly believe that the lastInsertId() is the most error proof for this purpose.

[broj1]

I agree that the lastInsertId() is the safest approach to keep your data consistent. I think it also requires that the ID field is autoincremented/unique so keep that in mind when creating a table.

If you get errors please post the whole error messages (verbatim) here. If you use code from my post above you have to supply valid information (like credentials) to access the database.