Sr.No Not coming Properly in PHP MYSQL

Question

I want to show list of my product as below :

Sr.No | Name | Type 
--------------------
1     |  aa  | 2
2     |  bb  | 2
3     |  cc  | 3
4     |  dd  | 2
5     |  cc  | 4 

But its coming as below

Sr.No | Name | Type 
--------------------
3     |  aa  | 2
5     |  bb  | 2
1     |  cc  | 3
4     |  dd  | 2
2     |  cc  | 4 

and when any new entry made then : But its coming as below

Sr.No | Name | Type 
--------------------
4     |  aa  | 2
5     |  bb  | 2
2     |  cc  | 3
6     |  dd  | 2
3     |  cc  | 4 
1     |  ec  | 5     // this below are two new entry made 
7     |  fc  | 4 

above should be as below :

Sr.No | Name | Type 
--------------------
1     |  aa  | 2
2     |  bb  | 2
3     |  cc  | 3
4     |  dd  | 2
5     |  cc  | 4 
6     |  ec  | 5     // this below are two new entry made 
7     |  fc  | 4 

Its not that i want order by name , i just want normal Sr.No as per entry .. means if i add any entry first then Sr.No 1 , 2 , 3 and so on..

But when i add any new entry then its Sr.No get changed but it should get last sr.no

Below is what i have tried :

SELECT (@cnt := @cnt + 1) AS rowNumber, orn.name, orn.type ,  u.* 
FROM  ofr AS u 
CROSS JOIN (SELECT @cnt := 0) AS dummy
LEFT JOIN  or_name AS orn ON u.id=orn.id 
GROUP BY u.id ORDER BY rowNumber AND u.add_datetime DESC

please note (@cnt := @cnt + 1) AS rowNumber is generating serial no and this value is pass below under $cols "Sr.No" .... Sr.No is column name and nothing else ... its value is coming by " rowNumber "

Listing is call in below way :

$result =   get_details()

$cols = array( 
        _("Sr.No.")=>array('align'=>'center'),
        _("Name")=>array('align'=>'center'),
        _("Type")=>array('align'=>'center')
       );

$table =& new_db_pager('tablename', $result, $cols, 'ofr','id',10); 

$table->width = "95%";

display_db_pager($table);

Database :

id : bigint(20)     UNSIGNED    No  None    AUTO_INCREMENT

Note : above format is from http://frontaccounting.com/ demo link : http://demo.frontaccounting.eu/

Answer 1

After reading the question a little more carefully, I'm deleting my old answer and writing a new one.

It seems like you're trying to automatically assign a serial number to each new entry in the table, and you want that number to be persistent. There is a tool specifically for this kind of problem: the auto-incrementing integer. You never have to set this field when inserting records -- the database will assign the next number automatically.

In your DDL you specify that you want a field that is an integer, auto-incrementing, and not null:

CREATE TABLE ofr (
    serial MEDIUMINT NOT NULL AUTO_INCREMENT,
    ... the rest of your fields ...
)

When you query the table you don't have to do any fancy row-counting. Just select the serial field along with everything else you want.

People use this all the time for generating unique IDs within a table. It is often used as a primary indexing key, too. More information is available at the MySQL reference.