PDA

View Full Version : Check Auto Increment



calumogg
11-07-2007, 06:55 PM
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
11-07-2007, 07:16 PM
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
11-08-2007, 10:21 AM
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
11-08-2007, 11:09 PM
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
11-09-2007, 07:53 AM
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>