Forum Moderators: coopster
I essentially want specific members of my website to come and fill out a form to get reimbursement. I would like each form to automatically attach an "invoice number" starting from 0001 and each time the form is filled the number increases.
I am running a MySQL database but I have no idea on how to proceed. I have managed to create the form but do not how to implement the auto-numbering system.
Here is my form so far:
<table border="0" cellpadding="0" cellspacing="0" width="100%" id="table5" height="100%">
<tr>
<td valign="top">
<table border="0" cellpadding="0" width="100%" id="table6">
<tr>
<td>
<table width="100%" border="0">
<tr>
<td width="100%">
<h1>Welcome to the financial reimbursement application page</h1></td>
</tr>
<tr>
<td width="100%">
</td>
</tr>
<tr>
<td width="100%">
<b>Procedure:</b>
<ol style="margin-top:0px; margin-bottom:0px; margin-left:25px;">
<li>
Fill out the forum below and hit submit
<li>
Get back some money!</li>
</ol>
</td>
</tr>
<tr>
<td width="100%">
</td>
</tr>
<tr>
<td width="100%">
<form action="http://www.example.com/cgi-bin/mail_form.cgi" method="post">
<input type="hidden" name="Recipient Address" value="mesa.test01@example.com">
<input type="hidden" name="Auto Cc" value="Yes">
<input type="hidden" name="Background Color" value="#ffffff">
<input type="hidden" name="Link Color" value="#0000ff">
<input type="hidden" name="ActiveLink Color" value="#0000ff">
<input type="hidden" name="VisitedLink Color" value="#d41203">
<input type="hidden" name="Text Color" value="#000000">
<input type="hidden" name="Toggle Graphics" value="Off">
<input type="hidden" name="Magic Word" value="Select One">
<input type="hidden" name="Response Page Title" value="center;Submission Received">
<input type="hidden" name="Greeting" value="Thank you very much for filling out your info! *Press the Back button to return to the previous page.">
<input type="hidden" name="Return URL" value="http://www.example.com">
<input type="hidden" name="Return Link Text" value="Return to MESA Homepage">
<input type="hidden" name="Show Results" value="Yes">
<input type="hidden" name="Show Blanks" value="No">
<div align="center">
<table width="100%" border="0" cellpadding="0" <tr>
<td width="33%">
Date of Birth*(MM/YYYY) <font color="#6F7B91">(i.e.
March 1970 is 03/1970) </td>
<td width="38%">
<input name="Date of Birth*" type="text" id="Date of Birth*" size="44">
</td>
</tr>
<td>Full Name*</td>
<td>
<input name="Full Name*" type="text" id="Full Name*" size="44">
</td>
</tr>
<td>Student Number*</td>
<td>
<input name="Student Number*" type="text" id="Student Number*" size="44">
</td>
</tr>
<tr>
<td>Email* <font color="#6F7B91">(i.e.
student@example.com)</font></td>
<td>
<input name="Email Address*" type="text" id="Email Address*" size="44">
</td>
</tr>
<tr>
<td>Home Phone Number* <font color="#6F7B91">(i.e. 888-888-8888)</font></td>
<td>
<input name="Home Phone*" type="text" id="Home Phone*" size="44">
</td>
</tr>
<tr>
<td>Cell Phone Number <font color="#6F7B91">(if any)</font></td>
<td>
<input name="Cell Phone" type="text" id="Cell Phone" size="44">
</td>
</tr>
<tr>
<td colspan="2">
<b>Any other feedback/thoughts/ideas/questions?</b>
</td>
</tr>
<tr>
<td colspan="2">
<textarea name="Any other feedback/thoughts/ideas/questions?" cols="91" rows="5" id="Any other feedback/thoughts/ideas/questions?"></textarea></td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td colspan="2"><font color="#FF0000">* All information is required.</font>
</td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td colspan="2">
Please email <a href="bizdev@example.com">bizdev@mesa.ca</a>
if you have any questions or comments.</td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td colspan="2">
<INPUT type="submit" value="Submit">
<input type="reset" value="Reset">
</font>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
<input type="hidden" name="Magic Word" value="Select One">
</form>
</td>
</tr>
</table>
</td>
</tr>
</table>
<p> </p>
So any help on how to add my auto-numbering system to the top of this form would be greatly appreciated.
Thank you!
[edited by: dreamcatcher at 5:46 am (utc) on May 20, 2009]
[edit reason] Exemplified domain. [/edit]
Table - Members
Member_ID ¦ Member Name ¦ Other details
Now every time a members requests re-imbursement, let your script populate the table for invoices with member_ID from the form, and thereby assign an auto invoice number and echo the same to the member.
Hope this helps.
I would like each form to automatically attach an "invoice number" starting from 0001 and each time the form is filled the number increases.
Are you sure you need this? (four leading zeros.) "0001" would force you to use a varchar type, because the integer equivalent is "1".
In any case, an auto_increment field will work, but it's not recommended because if you ever have to move the database, the auto increment numbers can potentially change:
id¦unique_id
1¦1234
2¦1235
3¦1236 <-- remove this record
4¦1237
5¦1238
moved DB:
id¦unique_id
1¦1234
2¦1235
3¦1237
4¦1238
Now if you reference record "3" it will be a different record. What it should do is say "record not found" for 1236. Oops! (think table joins . . .)
I can't give you a "cut and paste" solution, but what I would do is:
- use a unique integer field to reference invoice number
- when generating a new record, get the last id in this field and
---- if the last id is > 0, increment by one
---- if it's Not greater than 0, set to a default starting number or use a random number generator to get it started (as said, 0001=1.)
- write this record IMMEDIATELY so it doesn't allow other users to access this new number.
No I would not really need four leading zeros so starting from 1 would be ok.
One thing I forgot, the members when they apply for the reimbursement are not signed in or anything. They just fill out the form and it sends out an email in plain text format.
Sorry, I don't quite understand what you mean by adding auto_incremental values. Is that something I'd add to the MySQL database tables? If so, once that is done, what do I put into the actual form I have up top?
Thank you very much.
`invoice_number` int(11) NOT NULL auto_increment[/pre]And create a unique index for it:
[pre]
UNIQUE KEY `invoice_number` (`invoice_number`)
Or if you're using phpMyAdmin, select "auto_increment" in the "extra" selection, and choose "unique".
When you add a new invoice to your DB, in your INSERT statement either leave out the "invoice_number" field totally or set it to NULL, then immediately after executing your INSERT statement, call mysql_insert_id() to get the invoice number that was auto-generated:
$invoice_number = mysql_insert_id();
All this gives you a numeric invoice number, if you want to display it with leading zeroes in HTML pages or emails, you can do that by calling
sprintf("%04d", $invoice_number)(and to hijack this thread a bit...)
rocknbil, i don't quite follow what the problem is with moving DBs containing auto-increment values (other than replication, where you have to be much more careful). As far as i know, the two common ways of moving DBs in MySQL are through SQL export/import or via files such as XML. With SQL export/import the IDs are preserved and the auto-increment sequence is adjusted at the end of the operation, so it's not a problem. With XML, either you include the ID values in all your XML records (in which case they're preserved again), or you don't specify any IDs and the import program creates new IDs across all joined table.
Can you explain a bit further how you ran into problems? In your example, i'd expect to do a select on `unique_id` instead of `id`, and that would return "record_not_found". Or if you use the ID field as a foreign key in another table, i'd expect the ID's values in the joined table to be in synch even after the move too?
Thank you so much for that reply. Very helpful!
So just to clarify: I will be creating a new table in my current MySQL database called invoice_number and then configure it how you stated above, correct?
And then in my form I just simply add:
$invoice_number = mysql_insert_id();
Is there any particular area I should add that code or would that not matter?
Thank you!
// Returns next value for a counter, taken from
// a table with a single auto-incremented field.
//
function get_next_counter($table_name) {
// Create table if necessary.
$rc = mysql_query('CREATE TABLE IF NOT EXISTS '.$table_name
. ' (counter INT NOT NULL AUTO_INCREMENT,'
. ' PRIMARY KEY (counter),'
. ' UNIQUE (counter))'
);
if($rc === false)
die('MySQL error: '.mysql_error());
// Insert new row, auto-incrementing counter,
// get inserted value which is the next id to use.
$rc = mysql_query('INSERT INTO '.$table_name.' VALUES (NULL)');
if($rc === false)
die('MySQL error: '.mysql_error());
$next_counter = mysql_insert_id();
// Remove olders rows since they're not needed anymore.
$rc = mysql_query('DELETE FROM '.$table_name
. ' WHERE counter < '.$next_counter);
return $next_counter;
}
So you can do something like:
$invoice_id = get_next_counter('invoice_id_counter');
Later on, as mvaz described you can try to incorporate everything into your Invoices table.
I do not quite understand as to what I am supposed to do with that code. Sorry if I am getting on your nerves, I am really new MySQL/PHP business so please bear with me.
I tried doing two different things:
1) I added the top bit
$rc = mysql_query('CREATE TABLE IF NOT EXISTS '.$table_name
. ' (counter INT NOT NULL AUTO_INCREMENT,'
. ' PRIMARY KEY (counter),'
. ' UNIQUE (counter))'
);
if($rc === false)
die('MySQL error: '.mysql_error());As a query in the MySQL database but that gave me an error each time.
2)I tried taking all the code you posted and put that into a PHP file and updated it onto the server. When I visit that page though I get a databsae error saying it cannot reach the localhost.
Are these the steps I should be taking?:
1) Create a new table in MySQL database called Invoice with Number of Fields set to 1
2) Adujust the values (such as Field, Type, etc) to what?
3) Make sure that this is set to Unique
4) Don't quite understand how to proceed from here...
Where am I putting in the code on the table file I posted in my original post (this is the part that has me really confused)?
Thank you, and apologies for being such a "noob" to all this haha.
Can you explain a bit further how you ran into problems?
Actually, at the moment, I can't - it's one of those things I learned early on and forgot about why. :-) I'm sure there are other reasons, and possibly the "auto increment" issue is off track, but I learned early on to always use a different column for your foriegn keys. So I got nuttin' ATM. Back to topic . . .
Have you connected to db before running "create table" query?
Also if you paste complete "php" code here it will easy to rectify the problem.
As for the code, I just used the one idfer provided:
<?php
// Returns next value for a counter, taken from
// a table with a single auto-incremented field.
//
function get_next_counter($table_name) {
// Create table if necessary.
$rc = mysql_query('CREATE TABLE IF NOT EXISTS '.$table_name
. ' (counter INT NOT NULL AUTO_INCREMENT,'
. ' PRIMARY KEY (counter),'
. ' UNIQUE (counter))'
);
if($rc === false)
die('MySQL error: '.mysql_error());// Insert new row, auto-incrementing counter,
// get inserted value which is the next id to use.
$rc = mysql_query('INSERT INTO '.$table_name.' VALUES (NULL)');
if($rc === false)
die('MySQL error: '.mysql_error());
$next_counter = mysql_insert_id();
// Remove olders rows since they're not needed anymore.
$rc = mysql_query('DELETE FROM '.$table_name
. ' WHERE counter < '.$next_counter);
return $next_counter;
}
?>
When I make that into a page and upload it to my website I just get a MySQL error.
Any help on this?
At the very least, you need to call mysql_connect() and mysql_select_db(). Hope this helps.
I think I understand what to do now.
I will post back with results soon.
