user id in php+mysql register

[dhruv_arora]

Hey,
I created a simple user register script which later stores the data in a mysql database. What I did initially was that a small code would count the number of rows in the database and then automatically increase the variable by 1 to set the user id. For example if there are 8 records the new user's ID is 9.

After sometime I created another script meant to delete a record, now the problem if is if I delete a record whose ID is 6, so the total no. of records is now 8 (initially there were 9 records) but the record with ID as 9 already exists. Now during the execution of the script, it won't register the user because of that thing. How do I solve that problem?

Here's my register.php script.
P.S I am newbie.

<?php
include("header.html");
include("nav.html");
include("sidebars.html");
?>
<div id="content">
<?php
$errors=array();
if(isset($_POST['submitted'])){
	if(empty($_POST['fname'])){
		$errors[]="You forgot to enter first name";
	}
	   else{
		   $fn=$_POST['fname'];
	   }
	if(empty($_POST['lname'])){
		$errors[]="You forgot to enter last name";
	}
	   else{
		   $ln=$_POST['lname'];
	   }
	if(empty($_POST['email'])){
		$errors[]="You forgot to enter email";
	}
	  else{
		  $email=$_POST['email'];
	  }
	if(empty($_POST['usern'])){
		$errors[]="You forgot to enter username";
	}
	  else{
		  $usern=$_POST['usern'];
	  }
if(!empty($_POST['pass1']))
{
	if(($_POST['pass1'])==($_POST['pass2']))
	{
		$pass=$_POST['pass1'];
	}
	   else
	   {
		   $errors[]="The two passwords do not match";
	   }
}
else
{
	$errors[]="You forgot to enter a password";
}
}
$result="";
    if(empty($errors)){
	require_once("connect.php");
	$q1="SELECT * FROM users";
	$r=mysql_query($q1,$dbc);
	$num=mysql_num_rows($r);
	$id=$num+1;
    $query="INSERT INTO users VALUES ('$id','$usern',SHA1('$pass'),'$fn','$ln','$email')";
    $result = mysql_query($query, $dbc) or die(mysql_error());
    }if($result){
	echo "You are now a registered user";
}
else{
	echo "You could not be registered beacuse :-"."<br>";
	foreach($errors as $msg){
		echo $msg."<br>";
	}
}
?>
</div>
<?php
include("footer.html");
?>

[urtrivedi]

do not count, use max function

select ifnull(max(id),0)+1 as maxid from tablename

[dhruv_arora]

do not count, use max function

select ifnull(max(id),0)+1 as maxid from tablename

what does this ifnull function does?

[urtrivedi]

IF your table do not have any record max will return null. since you can not add 1 to null, so i converted null to 0 then added 1,

In short if no record is there it will give 1 else it will return max + 1

[Pampas]

Hey,
I created a simple user register script which later stores the data in a mysql database. What I did initially was that a small code would count the number of rows in the database and then automatically increase the variable by 1 to set the user id. For example if there are 8 records the new user's ID is 9.

After sometime I created another script meant to delete a record, now the problem if is if I delete a record whose ID is 6, so the total no. of records is now 8 (initially there were 9 records) but the record with ID as 9 already exists. Now during the execution of the script, it won't register the user because of that thing. How do I solve that problem?

Here's my register.php script.
P.S I am newbie.

<?php
include("header.html");
include("nav.html");
include("sidebars.html");
?>
<div id="content">
<?php
$errors=array();
if(isset($_POST['submitted'])){
	if(empty($_POST['fname'])){
		$errors[]="You forgot to enter first name";
	}
	   else{
		   $fn=$_POST['fname'];
	   }
	if(empty($_POST['lname'])){
		$errors[]="You forgot to enter last name";
	}
	   else{
		   $ln=$_POST['lname'];
	   }
	if(empty($_POST['email'])){
		$errors[]="You forgot to enter email";
	}
	  else{
		  $email=$_POST['email'];
	  }
	if(empty($_POST['usern'])){
		$errors[]="You forgot to enter username";
	}
	  else{
		  $usern=$_POST['usern'];
	  }
if(!empty($_POST['pass1']))
{
	if(($_POST['pass1'])==($_POST['pass2']))
	{
		$pass=$_POST['pass1'];
	}
	   else
	   {
		   $errors[]="The two passwords do not match";
	   }
}
else
{
	$errors[]="You forgot to enter a password";
}
}
$result="";
    if(empty($errors)){
	require_once("connect.php");
	$q1="SELECT * FROM users";
	$r=mysql_query($q1,$dbc);
	$num=mysql_num_rows($r);
	$id=$num+1;
    $query="INSERT INTO users VALUES ('$id','$usern',SHA1('$pass'),'$fn','$ln','$email')";
    $result = mysql_query($query, $dbc) or die(mysql_error());
    }if($result){
	echo "You are now a registered user";
}
else{
	echo "You could not be registered beacuse :-"."<br>";
	foreach($errors as $msg){
		echo $msg."<br>";
	}
}
?>
</div>
<?php
include("footer.html");
?>

Why not use the Autoincrement in MySQL, using it as Primary Index?

If that's not right for your application, what I've done in the past is create a NEXT_UP table, it holds the last UID# used, so you do use a select query to get it, increment it by 1, then run a UPDATE query to store the new value. Typically I do all this in a function that "returns" the UID I need. For safety I typically also store the LAST NUMBER USED, and the NEXT NUMBER AVAILABLE that way I know exactly what the next number is.

[dhruv_arora]

IF your table do not have any record max will return null. since you can not add 1 to null, so i converted null to 0 then added 1,

In short if no record is there it will give 1 else it will return max + 1

Thanks for the new information.