PDA

View Full Version : Incrementing record number in a form


Beeky
10-19-2010, 09:59 AM
The code I am using below displays data from a table of my MySQL database and an input form. The system works but is a bit archaic for users. First they need to look at the table to determine the next number for a new record and then enter the number in the "eventid" text field on the form. The underlying datatype of "eventid" in the database is Int with auto_increment and not surprisinglyis the "primary key". Is there a way I can get my form - when the page is selected - to enter the next sequential number in the "eventid" input box on the form? I have shown the relevant code for the page below:

/// query db and loop through rows example

$field2 = $_POST['eventid'];
$field3 = $_POST['eventname'];
$field4 = $_POST['eventlocation'];
$field5 = $_POST['eventtype'];
$field6 = $_POST['eventstatus'];
$field7 = $_POST['eventdate'];
$field8 = $_POST['starttime'];
$field9 = $_POST['endtime'];
$field10 = $_POST['coordinator'];
$field11 = $_POST['comments'];
$field12 = $_POST['fundsraised'];
$field13 = $_POST['financialyear'];

if($field2 && $field3){

mysql_query("INSERT INTO tblEvents(eventid,eventname,eventlocation,eventtyp e,eventstatus,eventdate,starttime,endtime,coordina tor,comments,fundsraised,financialyear)VALUES('$fi eld2','$field3','$field4','$field5','$field6','$fi eld7','$field8','$field9','$field10','$field11','$ field12','$field13')");


/// query db and loop through rows:
}

$query = mysql_query("SELECT * FROM tblEvents ORDER BY eventid;");

$table = "<table border=\"1\" cellspacing=\"10\" body bgcolor=\"#AABBCC\" Align=Center>\n";
" <tr>
<td>Event No</td>
<td>Event</td>
<td>Location</td>
<td>Status</td>
<td>Type of Event</td>
<td>Date</td>
<td>Start Time</td>
<td>End Time</td>
<td>Coordinator</td>
<td>Comments</td>
<td>Funds Raised</td>
<td>Financial Year</td>

</tr>";

while($row = mysql_fetch_array($query)){

$table .= " <tr>
<td>".$row['eventid']."</td>
<td>".$row['eventname']."</td>
<td>".$row['eventlocation']."</td>
<td>".$row['eventstatus']."</td>
<td>".$row['eventtype']."</td>
<td>".$row['eventdate']."</td>
<td>".$row['starttime']."</td>
<td>".$row['endtime']."</td>
<td>".$row['coordinator']."</td>
<td>".$row['comments']."</td>
<td>".$row['fundsraised']."</td>
<td>".$row['financialyear']."</td>
</tr>";
}

$table .= "</table>";
$note1 = "The table below shows the current programme list. To add a new event please enter a new 'UNIQUE' Event No (see from curent list)
and then fill in all relevant fields in the table and then click on the Submit button.";
?>Form Code:

<form id="form1" name="form1" method="post" action="">
<table width="49%" border="0" align="center">
<tr>
<td width="32%">eventid</td>
<td><input name="eventid" type="text" id="eventid" /></td>
</tr>
<tr>
<td>eventname</td>
<td><input name="eventname" type="text" id="eventname" size="64"/></td>
</tr>
<tr>
<td>Location</td>
<td><input name="eventlocation" type="text" id="eventlocation" size="64" /></td>
</tr>
<tr>
<td>Status</td>
<td><input name="eventstatus" type="text" id="eventstatus" /></td>
</tr>
<tr>
<td>Type of Event</td>
<td><input name="eventtype" type="text" id="eventtype" /></td>
</tr>
<tr>
<td>Date</td>
<td><input name="eventdate" type="Date" id="eventdate" /></td>
</tr>
<tr>
<td>Start Time</td>
<td><input name="starttime" type="text" id="starttime" /></td>
</tr>
<tr>
<td>End Time</td>
<td><input name="endtime" type="text" id="endtime" /></td>
</tr>
<tr>
<td>Coordinator</td>
<td><input name="coordinator" type="text" id="coordinator" /></td>
</tr>
<tr>
<td>Comments</td>
<td><textarea name="comments" cols="64" id="comments"></textarea></td>
</tr>
<tr>
<td>Funds Raised</td>
<td><input name="fundsraised" type="decimal(5,2)" id="fundsraised" /></td>
</tr>
<tr>
<td>Financial Year</td>
<td><input name="financialyear" type="text" id="financialyear" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit" /></td>
</tr>
</table>
</form></p></td> </tr></table></td> </tr></table>

Corrosive
10-19-2010, 10:33 AM
Could you not just leave it to auto increment in the background?

Beeky
10-19-2010, 11:21 AM
Well yes Corrosive I would be happy with this but the way the page is set up at present if I enter all the data except the eventid field then the database is nto updated. Would I have to take the eventid field off the form and also the query?

Corrosive
10-19-2010, 11:29 AM
If you leave a column at the start of your table (say recordID)and set it to auto increment then it should fill it in automagically without you having to enter anything. At least that's what I have done in the past.

Beeky
10-19-2010, 12:14 PM
Again I am clearly doing something wrong as this does not work. As I say I am using phpMyAdmin to look at my database and the field in question is defintitely set to auto_increment. The table structure is shown below and I have already given you the setup for the page. Thereis no logical reason why it wont auto increment that I can see:

Field Type Null
eventid int(5) No eventname text No eventstatus text No eventlocation text No eventtype text No eventdate date No starttime text No endtime text No coordinator text No comments text No fundsraised decimal(5,2) No financialyear year(4) No

Corrosive
10-19-2010, 12:24 PM
That might be because you are still trying to insert it through a field. Just start with event name and don't specify the ID in the SQL.

Beeky
10-19-2010, 12:34 PM
Thanks I did that and it works - I think I had left off a } in identifying a variable when I first tried to remove the eventid field.

Corrosive
10-19-2010, 12:36 PM
Thanks I did that and it works - I think I had left off a } in identifying a variable when I first tried to remove the eventid field.

That's the one thing that does my head in about coding PHP. I'm forever mising off ; at the end of stuff and then wondering why the hell it doesn't work! Glad you got it fixed :)

Beeky
10-19-2010, 03:51 PM
Yes thanks again. I am sure there will be other problems to overcome but at least as you meet each one you are learning a new aspect. Its fun