Check Auto Increment

[calumogg]

Hi,
I am running a site and I am looking at the shop when a person makes a order the Auto Increment goes up as it should but sometimes people make multiple orders by mistake so then I delete the duplicates, but when the next person makes a order the Auto Increment is wrong because a row has been deleted. Does anyone know of a script that I can run to check and if needed correct the Auto Increment so that it is continuous?
Cheers

[blm126]

Well, you can't do that by running another script. The original shop script needs to use a SQL query similiar to

Select COUNT(*) num from `table_with_orders`

on the rows. This will find an accurate number of orders. It sounds like the shop script is checking a unique id. When you delete orders, this would become incorrect, but you can't just modify id's. Once one has been set, it has to stay the same. There isn't a lot of specific advice I can give on this without seeing some PHP code.

[calumogg]

I am using this code to add data into the table:

$host="******"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="******"; // Database name
$tbl_name="******"; // Table namee

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect server ");
mysql_select_db("$db_name")or die("cannot select DB");

// Insert data in MySQL table
$sql="INSERT INTO $tbl_name(orderid, ip, today, first, last, addr, addr2, city, county, post_code, email, b_first, b_last, b_addr, b_addr2, b_city, b_county, b_post_code, b_email, comments, status, reminder, quantity_1, name_1, price_1, quantity_2, name_2, price_2, total)VALUES('$timestamp', '$ip', NOW(), '$first', '$last', '$addr', '$addr2', '$city', '$county', '$post_code', '$email', '$b_first', '$b_last', '$b_addr', '$b_addr2', '$b_city', '$b_county', '$b_post_code', '$b_email','$comments', '$status', '$reminder', '$QUANTITY_1', '$NAME_1', '$PRICE_1', '$QUANTITY_2', '$NAME_2', '$PRICE_2', '$TOTAL')";
$result=mysql_query($sql); 

Then to delete a record:

$host="******"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="******"; // Database name
$tbl_name="******"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$orderid=$_GET['orderid'];

// Delete data in mysql from row that has this id
$sql="DELETE FROM $tbl_name WHERE orderid='$orderid'";
$result=mysql_query($sql); 

I was hoping someone would know of a code that could count the number of entries and make sure the Auto Increment is continious

[blm126]

That isn't the code that needs to be changed. It may be possible to do it in the delete code, but this is impractical. Changing foriegn keys is never a good idea. That defeats there purpose. What is the code that shows the total number? That is the fastest way to change it.

[calumogg]

Here is the code that display the entries:

<table width="95%" border="2" align="center" cellpadding="5" cellspacing="0" bordercolor="#333333">
  <tr>
    <td colspan="13"> <div align="center">
      <script type="text/javascript">ajaxinclude("../menu.html")</script>
    <span class="title">Viewing Records</span></div></td>
  </tr>

<tr>
<td align="center" class="text">Order #</td>
<td align="center" class="text">ID</td>
<td align="center" class="text">Date</td>
<td align="center" class="text">Name</td>
<td align="center" class="text">Email</td>
<td align="center" class="text">Total Cost</td>
<td align="center" class="text">Status</td>
<td align="center" class="text">Reminder</td>
</tr>

<?php
$host="******"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="******"; // Database name
$tbl_name="******"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query  = "SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td class="text"><? echo $rows['id']; ?></td>
<td align="center" class="text"><a href="view.php?orderid=<? echo $rows['orderid']; ?>" class="link"><? echo $rows['orderid']; ?></a></td>
<td class="text"><? echo $rows['today']; ?></td>
<td class="text"><? echo $rows['first']; ?> <? echo $rows['last']; ?></td>
<td class="text"><? echo $rows['email']; ?></td>
<td class="text"><? echo $rows['total']; ?></td>
<td class="text"><? echo $rows['status']; ?></td>
<td class="text"><? echo $rows['reminder']; }?></td>
</tr>
</table>

<div align="center" class="link">
<?
// how many rows we have in database
$query   = "SELECT COUNT(id) AS id FROM $tbl_name";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['id'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page

$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
    if ($page == $pageNum)
    {
        $nav .= " $page |";   // no need to create a link to current page
    }
    else
    {
        $nav .= " <a href=\"$self?page=$page\">$page</a> |";
    }        
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
    $page = $pageNum - 1;
    $prev = " <a href=\"$self?page=$page\">[Prev] |</a> ";
    
    $first = " <a href=\"$self?page=1\">[First Page] |</a> ";
} 
else
{
    $prev  = '&nbsp;'; // we're on page one, don't print previous link
    $first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
    $page = $pageNum + 1;
    $next = " <a href=\"$self?page=$page\">[Next]</a> ";
    
    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
    $next = '&nbsp;'; // we're on the last page, don't print next link
    $last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

mysql_close();
?>
</div>