unable to assign max(id*1) +1 to dataform

bennie

I'm building a form that will add into a database table. I'm trying to assign the primary ID as the next greatest number in the ID column. But I keep getting a NULL as my ID which causes the form to only be able to submit once, since there can not be any duplicate IDs. Can anyone help?? Here is the part of the code where it's giving me problems:

if ((isset($HTTP_POST_VARS["MM_insert"])) &&

($HTTP_POST_VARS["MM_insert"] == "form2")) {

$insertSQL = sprintf("INSERT INTO tbl_sm (SM_ID, SM_CLLI, SM_ANS_NAME, SM_SERIAL, SM_MODEL) VALUES ('$max_id', %s, %s, %s)",

GetSQLValueString($HTTP_POST_VARS['RedbackCLLI'], "text"),

GetSQLValueString($HTTP_POST_VARS['RedbackName'], "text"),

GetSQLValueString($HTTP_POST_VARS['RedbackChasisSerial'], "text"),

GetSQLValueString($HTTP_POST_VARS['RedbackModel'], "text"),

mysql_select_db($database_ADSL, $connection_ADSL);
$Result1 = mysql_query($insertSQL, $connection_ADSL) or

die(mysql_error());

$ID_result = mysql_query("SELECT MAX(SM_ID*1) + 1 FROM tbl_sm");
$max_id = mysql_result($ID_result,0,0);

$insertGoTo = "Submitted.php";
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

<html>
<head>
...

</html>

TheIceman5

in MAX(SM_ID1), what is the 1 for? i didnt think you were allowed to use asterix as part of the field name.

Why increment ID in the query? why not like

$max_id = mysql_result($ID_result,0,0);
$max_id++;

Another thing, why bother incrementing it at all?? why not just make the ID field an auto increment that will increment on each new record automatically so you dont have to do a thing?? seems the way to do it for me.

BlueMeteor

$ID_result = mysql_query("SELECT MAX(SM_ID*1) + 1 FROM tbl_sm");

SM_ID1 is unuseful.....nullnumber=null

$ID_result = mysql_query("SELECT ifnull(MAX(SM_ID),0) + 1 as New_ID FROM tbl_sm");

🙂

bennie

Thanks for all the help, but I still get an empty string. the database doenst even list the ID result as NULL but as an empty space or something. I've tried both suggestions and both give me the same problem. I can't auto increment the table b/c the database I am inserting data into is not assigned to do so. When i tested my SELECT MAX() statement in MySQL, the only way I could get the actual max value was by multiplying the SM_ID by 1 ...i.e. (MAX( SM_ID*1)). Without the asterick I got the maximum SINGLE digit num, which was 9. So I'm not sure if it's my SQL statement that's causing the problem, but maybe my PHP code. Is there something I'm missing?? Here's the code again (I left a lot of the code i tested out in comments):

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form2")) {

//$max_id = mysql_result($ID_result,0,0);
//$max_id++;

$ID_result = mysql_query("SELECT MAX(SM_ID*1) + 1 FROM tbl_sm");
$max_id = mysql_result($ID_result,0,0);

$insertSQL = sprintf("INSERT INTO tbl_sm (SM_ID, SM_CLLI, SM_ANS_NAME, SM_SERIAL, SM_MODEL) VALUES ('$max_id', %s, %s, %s, %s, %s, %s, %s)",

GetSQLValueString($HTTP_POST_VARS['RedbackCLLI'], "text"),
GetSQLValueString($HTTP_POST_VARS['RedbackName'], "text"),
GetSQLValueString($HTTP_POST_VARS['RedbackChasisSerial'], "text"),
GetSQLValueString($HTTP_POST_VARS['RedbackModel'], "text"),

mysql_select_db($database_ADSL, $connection_ADSL);
$Result1 = mysql_query($insertSQL, $connection_ADSL) or die(mysql_error());

//$ID_result = mysql_query("SELECT if null(MAX(SM_ID),0) + 1 as New_ID FROM tbl_sm");
//$max_id=New_ID;

//$ID_result = mysql_query("SELECT MAX(SM_ID*1) + 1 FROM tbl_sm");
//$max_id = mysql_result($ID_result,0,0);