Re: Auto generate Auto Increment numbers

Question

I tried to conclude the tread I started last time on how to Auto generate serial numbers as seen here Auto generate serial numbers. On my own I have been able to come out with a code that give me what I want.

Using this line of code

   SELECT COUNT(donor_id) + 1 AS Counter
   FROM tbl_donors

I got the value I want which is 6+1; i.e. 6 being the total number of records and 1 is the additional for the new record. But now how to add it to the tableset that will show all the values as seen here http://www.netdataflow.com/rbme/ is where I am having a problem. I am using dreamweaver to builder my tableset and below is the tableset code

<div align="center">
  <table width="1100" border="0" cellpadding="0" cellspacing="0">
    <tr id="colhead">
      <td width="30" height="30" id="labels"><div align="center"><strong>ID</strong> 
 </div></td>
      <td width="200" height="30" id="labels"><div align="center"><strong>Donor  
Name</strong></div></td>
      <td width="100" height="30" id="labels"><div align="center"> 
<strong>Designation</strong></div></td>
      <td width="250" height="30" id="labels"><div align="center">
<strong>Address</strong></div></td>
      <td width="80" height="30" id="labels"><div align="center"><strong>City</strong>
</div></td>
      <td width="80" height="30" id="labels"><div align="center"><strong>State</strong>
</div></td>
      <td width="80" height="30" id="labels"><div align="center">
<strong>Country</strong></div></td>
      <td width="100" height="30" id="labels"><div align="center"><strong>Phone</strong>
</div></td>
      <td width="150" height="30" id="labels"><div align="center"><strong>Email 
Address</strong></div></td>
    </tr>
    <?php do { ?>
      <tr <?php 
 // technocurve arc 3 php bv block2/3 start
echo " style=\"background-color:$color\"";
// technocurve arc 3 php bv block2/3 end
?> id="rowlines">
        <td id="labels"><div align="center"><?php echo $row_rsdonors['donor_id']; ?>  
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['donorname']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['designation']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['address']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['city']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['state']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['country']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['phone']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['emailaddr']; ?>
</div></td>
      </tr>
      <?php 
// technocurve arc 3 php bv block3/3 start
if ($color == $color1) {
$color = $color2;
} else {
$color = $color1;
 }

Can anyone help out? If possible, I will want to add to the

 SELECT COUNT(donor_id) + 1 AS Counter
 FROM tbl_donors

to the first row of the table before the ID. I am open to any other idea or way or doing it, possibly manually coding the table instead of using dreamweaver dynamic tableset.

I appreciate you help in advance.

Mike

Why do you believe your cod would be better than letting a database autoincrement field handle this? — Mark Baker, Mar 07 '14 at 13:33
@Strawberry: Sorry for the repetition. I was just trying to say Auto generate or auto_increment number — Prince Michael, Mar 07 '14 at 13:34
@Mark Baker: I am trying to ensure order and better sequence in the table. Using the generated serial number is OK but when a row is deleted what happens? I will understand but the client will not understand. They want it hence the effort on how to make it work. If there is a better way of doing it with MySQL I will gladly accept it — Prince Michael, Mar 07 '14 at 13:37
'auto' means 'self', as in 'let the computer generate this number by it*self*'. This appears to be an attempt at doing the opposite!?!?! — Strawberry, Mar 07 '14 at 13:37
The database doesn't care about the gaps it the sequence, but it does guarantee uniqueness which your approach doesn't (two users filling in the form at the same time?) And why should your client care? — Mark Baker, Mar 07 '14 at 13:41
What you're looking to do is such a bad idea that I'd need 30 minutes to type out all the possible headaches you'll give yourself in exactly 1 hour after you let your client use such a system. — N.B., Mar 07 '14 at 14:13

Andolasoft Inc · Answer 1 · 2014-03-07T14:03:00.703

Just take one variable "$counter" and use this to display the serial number

I am adding just one example using your code. Please go through it.

<div align="center">
  <table width="1100" border="0" cellpadding="0" cellspacing="0">
    <tr id="colhead">
  <td width="30" height="30" id="labels"><div align="center"><strong>SI NO.</strong>     
      </div></td>
      <td width="30" height="30" id="labels"><div align="center"><strong>ID</strong>
      </div></td>
      <td width="200" height="30" id="labels"><div align="center"><strong>Donor Name</strong></div></td>
      <td width="100" height="30" id="labels"><div align="center"><strong>Designation</strong></div></td>
    </tr>
    <?php
    $counter = 1;
            for($i=0;$i<$total_rec;$i++) //"$total_rec" is the total number of records found in your required table
    {
?>
   <tr id="rowlines">
      <td id="labels"><div align="center"><?php echo $counter; ?></div></td>
  <td id="labels"><div align="center"><?php echo $row_rsdonors['donor_id']; ?></div></td>
      <td id="labels"><div align="center"><?php echo $row_rsdonors['donorname']; ?></div></td>
      <td id="labels"><div align="center"><?php echo $row_rsdonors['designation']; ?></div></td>
  </tr>
<?php 
    $counter++;
 }
?>

@Andolasoft: Thank you somuch for your contributions. I follows your steps but had repetitions of the serial number. However I did a search on "generate row number in mysql" using your keyword word "row number" and got this page "http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table" from where I was able to get the right code. I am going to edit the update I made above to show the correct code. — Prince Michael, Mar 08 '14 at 21:49
@Prince Michael: I have already updated the correct code. Please have a look, now you will not get the repetitions of the serial number. — Andolasoft Inc, Mar 10 '14 at 05:44

score 0 · Accepted Answer · edited May 23 '17 at 10:28

I have been able to get the right code for my questions on "Auto generate Auto Increment numbers". I think the issue I had was the title to my question. The right title should have been "How to generate table row numbers"

Anyway find below the code I used. Here is the reference link: With MySQL, how can I generate a column containing the record index in a table?

SELECT  d.*, @curRow := @curRow + 1 AS row_number
FROM    tbl_donors d
JOIN    (SELECT @curRow := 0) r
WHERE user_name = %s"  **//*Please dont add this WHERE clause if you don't need it. In my case I filter records based on the person who entered it hence the WHERE clause**

But because I am using Dreamweaver, Dreamweaver changed it to this format:

mysql_select_db($database_yourdatabasename, $yourdatabasename);
$query_rsdonors = sprintf("SELECT  d.*, @curRow := @curRow + 1 AS row_number 
FROM tbl_donors d JOIN    (SELECT @curRow := 0) r WHERE user_name = %s",  
GetSQLValueString($colname_rsdonors, "text"));
$query_limit_rsdonors = sprintf("%s LIMIT %d, %d", $query_rsdonors, $startRow_rsdonors,   
1$maxRows_rsdonors);
$rsdonors = mysql_query($query_limit_rsdonors, $ProjMonEva) or die(mysql_error());
$row_rsdonors = mysql_fetch_assoc($rsdonors);

Here is the table code structure

<div align="center">
  <table width="1130" border="0" cellpadding="0" cellspacing="0">
    <tr id="colhead">
      <td width="30" height="30" id="labels"><strong>SN</strong></td>
      <td width="30" height="30" id="labels"><div align="center"><strong>ID</strong>  
</div></td>
      <td width="200" height="30" id="labels"><div align="center"><strong>Donor 
Name</strong></div></td>
      <td width="100" height="30" id="labels"><div align="center">
<strong>Designation</strong></div></td>
      <td width="250" height="30" id="labels"><div align="center">
<strong>Address</strong></div></td>
      <td width="80" height="30" id="labels"><div align="center"><strong>City</strong>
</div></td>
      <td width="80" height="30" id="labels"><div align="center"><strong>State</strong>
</div></td>
      <td width="80" height="30" id="labels"><div align="center">
<strong>Country</strong></div></td>
      <td width="100" height="30" id="labels"><div align="center"><strong>Phone</strong>
</div></td>
      <td width="150" height="30" id="labels"><div align="center"><strong>Email 
Address</strong></div></td>
    </tr>
    <?php do { ?>   
      <tr <?php 
// technocurve arc 3 php bv block2/3 start
echo " style=\"background-color:$color\"";
// technocurve arc 3 php bv block2/3 end
?>id="rowlines">
        <td id="labels"><?php echo $row_rsdonors['row_number']; ?></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['donor_id']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['donorname']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['designation']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['address']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['city']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['state']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['country']; ?>
</div></td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['phone']; ?></div>
</td>
        <td id="labels"><div align="center"><?php echo $row_rsdonors['emailaddr']; ?>
</div></td>
      </tr>
      <?php 
// technocurve arc 3 php bv block3/3 start
if ($color == $color1) {
$color = $color2;
} else {
$color = $color1;
}

Here is a brief of what I am getting

SN       Donor Name              Designation
 1    Mr Michael Nwuzor       Chief Consultant
 2    Mr Michael Nwuzor       Chief Consultant
 3    South-Sea Datcomm Ltd

Here is the link from where I was able to get the right code: With MySQL, how can I generate a column containing the record index in a table?

Re: Auto generate Auto Increment numbers

2 Answers2