PHP Forum

**Daman** · 01-21-2007, 12:23 PM

My problem:
If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9. I need the auto increment value of next inserted record to start from 9.

I beleive the code option I need to use is:

PHP Code:


			
ALTER TABLE theTableInQuestion AUTO_INCREMENT=1

Where do i place this exactly?

Here is my code:

PHP Code:


			
<?

include("config.php");

$connection=mysql_connect($hostname,$user,$pass)or die ("Failed to connect.");

mysql_select_db($dbname,$connection) or die ("Cannot connect to DB");



$sql = "ALTER TABLE tablename AUTO_INCREMENT = 1";



$sql="

select 

REG_ID, TITLE, FIRST_NM, LAST_NM, SCHOOL, EMAIL, YEAR, PHONE, SESSION

from int_prep2006

order by

REG_ID

";



$result=mysql_query($sql, $connection) or die ("Could not execute query.");



while ($signed=mysql_fetch_array($result))

{

$reg_id=$signed['REG_ID'];

$title=$signed['TITLE'];

$first_nm=$signed['FIRST_NM'];

$last_nm=$signed['LAST_NM'];

$school=$signed['SCHOOL'];

$email=$signed['EMAIL'];

$year=$signed['YEAR'];

$phone=$signed['PHONE'];

$session=$signed['SESSION'];

echo "<table>";

$students_list .="

<tr><td>$reg_id</td><td>$title $first_nm $last_nm</td><td>$school</td><td>$email</td><td>$year</td><td>$phone</td><td>$session</td></tr>

";

}

//echo $students_list;

?>

**ibbo** · 01-22-2007, 06:10 AM

The alter statement is correct and you must issue the alter before you insert the next record or immediately after deleteing the last one.

Ibbo

**Invictus** · 07:26 AM

I know little about the syntax of SQL so can't comment on that but I can say that doing this...

PHP Code:


			
$sql = "ALTER TABLE tablename AUTO_INCREMENT = 1"; 
 
$sql=" 
select 
REG_ID, TITLE, FIRST_NM, LAST_NM, SCHOOL, EMAIL, YEAR, PHONE, SESSION 
from int_prep2006 
order by 
REG_ID 
";

is not going to alter the auto-increment since you're setting the value of $sql and then changing it on the next line before you've executed the first SQL statement.

Hope that helps.

**Daman** · 01-22-2007, 09:04 AM

Could you please explain to me where and how I would use this in my form code below?

PHP Code:


			
include("config.php");

$connection=mysql_connect($hostname,$user,$pass)or die ("Failed to connect.");

mysql_select_db($dbname,$connection) or die ("Cannot connect to DB");



$count="

select count(REG_ID) as COUNT

from int_prep2006

";



$result=@mysql_query($count, $connection) or die ("Could not execute query.");

while ($row=mysql_fetch_array($result))

{

$total_reg=$row['COUNT'];

}



if ($total_reg >= 45)

{

header ( "Location:http://students.fasken.com/ipw2007/sessionfull.html");

exit;

}



$sql="

insert into int_prep2006

(REG_ID, TITLE, FIRST_NM, LAST_NM, EMAIL, SCHOOL, YEAR, PHONE, SESSION)

values

 ('', '$title', '$first_nm', '$last_nm', '$email', '$school', '$year', '$phone', '$session')

";



$result=mysql_query($sql, $connection) or die ("Could not insert data into the DB.");



if ($result)

{

header ( "Location:http://students.fasken.com/ipw2007/thanks1.html");

}

Thanks!

**Daman** · 01-22-2007, 03:48 PM

Here is my attempt. I used $reset in the $result query.
This newbie still hasnt got it.

Please help. Thanks

PHP Code:


			
<?

if ((!$title)||(!$first_nm)||(!$last_nm)||(!$email)||(!$phone)||(!$school)||(!$year)||(!$session))

{

echo "<html><head><title>Error!</title></head><body bgcolor=\"#ffffff\"><p>&nbsp;</p><p><center><font color=\"#3D007e\" size=4>You have not filled some of the required fields.<br>Click on the \"Back\" button on your browser and fill in the form<b> completely</b>.";

header ( "Location:http://students.fasken.com/ipw2007/index.php");

exit;

}



include("config.php");

$connection=mysql_connect($hostname,$user,$pass)or die ("Failed to connect.");

mysql_select_db($dbname,$connection) or die ("Cannot connect to DB");



$count="

select count(REG_ID) as COUNT

from int_prep2006

";



$result=@mysql_query($count, $connection) or die ("Could not execute query.");

while ($row=mysql_fetch_array($result))

{

$total_reg=$row['COUNT'];

}



if ($total_reg >= 45)

{

header ( "Location:http://students.fasken.com/ipw2007/sessionfull.html");

exit;

}



$reset = "ALTER TABLE tablename AUTO_INCREMENT = 1";



$sql="

insert into int_prep2006

(REG_ID, TITLE, FIRST_NM, LAST_NM, EMAIL, SCHOOL, YEAR, PHONE, SESSION)

values

 ('', '$title', '$first_nm', '$last_nm', '$email', '$school', '$year', '$phone', '$session')

";



$result=mysql_query($reset, $sql, $connection) or die ("Could not insert data into the DB.");



if ($result)

{

header ( "Location:http://students.fasken.com/ipw2007/thanks1.html");

}

?>

**Daman** · 01-23-2007, 09:49 AM

Can anyone assist me with this? I'm some what of a newbie and I just don't know how to place this in my already existing code above. My attempts have not been successful.

Thanks

**Daman** · 01-29-2007, 01:08 PM

Thanks Invictus, your advise helped.

Cheers!