I'm attempting to setup a report numbering system that auto-increases by 1. I've been advised by my admin to NOT use auto-increment, since it automatically increases only by increments of 10 instead of 1 (no that doesn't make a lick of sense, but that's how they have it setup) and I have so many missing records between 1 and 104 that it probably would not work out anyhow.

When I have a user create a new record, how do I get the code to automatically grab the last ID in use (104 to start), increase it by 1, and post it in the ID field (hidden)? I've been looking over the documentation and this forum, and have seen recommendations to use "NEWID = LASTID + 1" for this, but am not seeing a crystal clear example of the exact code to put into my existing page.

I'm an absolute beginner here, being charged with a database project, and have been learning on the fly. So I'm going to need the exact lines of codes to add, and be told where to add them...

For reference, here's the original code from the existing page:

<form name="form1" method="post" action="formsubmit.php?mode=add">
<table width="700" border="1" align="center" cellpadding="2" cellspacing="0" bordercolor="#FFFFFF">
<tr bgcolor="#C7D8D6">
<td height="30" colspan="2"><font size="2"><strong>Add New Report </strong></font></td>
</tr><TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif"><b>ID:</b></font></TD>
<TD width="50%"><INPUT TYPE='TEXT' NAME='id' VALUE='' size=20></TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif"><b>Region:</b></font></TD>
<TD width="50%"> <select name="region" size="1" id="region">
<option value="GLB">Global</option>
<option value="AM">Americas</option>
<option value="UK">United Kingdom</option>
<option value="TK">Tokyo</option>
<option value="HK">Hong Kong</option>
<option value="IND">India</option>
<option value="SG">Singapore</option>
<option value="SYD">Sydney</option>
</select></TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif"><b>Status:</b></font></TD>
<TD width="50%"><INPUT TYPE='TEXT' NAME='status' VALUE='Open' size=20></TD>
</TR><br>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Location:</b></TD>
<TD width="50%">
<INPUT TYPE='TEXT' NAME='location' VALUE='' size=40>
</TD>
</TR>

<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Office:</b></TD>
<TD width="50%">

<INPUT TYPE='TEXT' NAME='office' VALUE='' size=40>
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Title:</b></TD>
<TD width="50%">
<INPUT TYPE='TEXT' NAME='title' VALUE='' size=40>
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Domain/Silo:</b></TD>
<TD width="50%">
<INPUT TYPE='TEXT' NAME='domain' VALUE='' size=40>
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Devices:</b></TD>
<TD width="50%">
<input name="devices" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Risk:</b></TD>
<TD width="50%">
<select name="risk" size="1">
<option value="1">1 - Critical</option>
<option value="2">2 - High</option>
<option value="3" selected>3 - Medium</option>
</select>
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Line:</b></TD>
<TD width="50%">
<input name="line" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Originator:</b></TD>
<TD width="50%">
<input name="originator" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Issue Owner:</b></TD>
<TD width="50%">
<input name="owner" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Group:</b></TD>
<TD width="50%">
<input name="segment" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Original Target Date (yyyy-mm-dd):</b></TD>
<TD width="50%">
<input name="origtarget" type="text" value="" size="25">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Current Target Date: (yyyy-mm-dd):</b></TD>
<TD width="50%">
<input name="currtarget" type="text" value="" size="25">
</TD>
</TR>

<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Milestone:</b></TD>
<TD width="50%">
<input name="milestone" type="text" value="" size="40">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Last Update (yyyy-mm-dd)::</b></TD>
<TD width="50%">
<input name="lastupdate" type="text" value="" size="20">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Description:</b></TD>
<TD width="50%">
<textarea name="details" cols="35" rows="3"></textarea>
</TD>
</TR>

<TR bordercolor="#CCCCCC">
<TD width="50%" valign="middle"><b>Start Date:</b></TD>
<TD width="50%" bordercolor="#FFFFFF">
<input name="date" type="text" VALUE="<? echo date("Y-m-d"); ?>" size="35">
</TD>
</TR>
<TR bordercolor="#CCCCCC">
<TD width="50%" valign="top"><b>Issue Details:</b></TD>
<TD width="50%" valign="top">
<textarea name="description" cols="35" rows="5"></textarea>
</TD>
</TR>

<tr>
<td><input type="submit" name="Submit" value="Save"></td>
<td>&nbsp;</td>
</tr>
</table>
</form>

    This will only increment the key where the value already exists.

    $sql = "UPDATE table_name SET id= id+1 WHERE (field='val')

    if you weren't going to be deleting records from this table you could do

    //get last id.
$sql = "SELECT `id` from `table_name` Order By `id` Desc limit 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$last_id = $row['id'];

//assign newid
$new_id = $last_id++;

//run sql insert for new record.

    the problem with this is if you are deleting information, an Id can be deleted and later that same id can be assigned to a new set of data, which might cause problems in some situations.

    This is most commonly handled in a sys_db_sequence table, where you have a table with 2 cols to keep track of field_name/field_value pairs for all of your tables. You select the id from this table then increment it so the next one is unique.

      lvictor wrote:

      I've been advised by my admin to NOT use auto-increment, since it automatically increases only by increments of 10 instead of 1 (no that doesn't make a lick of sense, but that's how they have it setup)

      Sounds like it's time you find a new, competent host.

        Write a Reply...