Jason Pruim wrote:
On Sep 4, 2007, at 12:20 PM, brian wrote:
Jason Pruim wrote:
so to put my 2¢ in on this, I would say to leave alone the auto
increment field especially if it's included in an index. If you
need a record number, either store it in the database in a separate
field as tedd suggested, and leave the main auto increment field
alone. OR use PHP (Or your favorite programming language) to add a
"Record number" to the display.
Except that, should this seperate field be used, as soon as some row
in the table is deleted, *that* index column value will *also*
suddenly have a gap and you're back to re-ordering.
id PLU index
1 foo-324 1
2 foo-894 2 <- delete this row
3 foo-929 3
4 foo-653 4
I see what you're getting at, but if PHP is controlling a "Record
Number" you could have it loop through the results find the gaps and
truncate the table to provide a record number. No idea how to do it,
just that I'm sure it can be done :)
I assume you mean truncate the resultset. But there's no need to do
that, either. One only needs to set up a counter variable when
displaying the results.
Given the simple table above (with an extra 'name' column, the second
row deleted, and no 'index' column):
<?php
$result = mdb2->query('SELECT id, name, PLU FROM my_table');
/* this is the counter used in place of the 'index' column
*/
$count = 0;
/* We want to display a list of product names along with the
* PLU. We also want to have a running count. Each product
* PLU will be a link to some other script, passing the row's id
* (the AUTO_INCREMENT Primary Key).
* Pretend the HTML table has already been opened.
*/
while (++$count && $row = $result->fetchRow())
{
?>
<tr>
<th><?= $count ?></th>
<td><a href="my_other_script.php?id=<?= $row['id'] "?>">
<?= $row['PLU'] ?>
</td>
<td><?= $row['name'] ?></td>
</tr>
<?php
}
?>
Thus, we have a set of rows, indexed without any gaps, and the primary
key is not shown at all (unless someone looks at the URL). It doesn't
matter what the id is, as long as it always points to the correct row.
brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
