Finding the last row of the mysql table.

[ajoo]

Hi all !

 

another simple mysql query to which i never found any satisfactory answer so far. 

 

I have a table with four fields. id is auto generated and increases as the table is filled. So i fill the table and say i have 4 entries in it as shown.

 

id         name          age      class    

1          aaa              6            1

2          bbb              6            1

3          ccc               7            2

4          ddd              7            2

 

Now i wish to add the 5th entry but before that i wish to query the database to find how many records have already been entered so far. So whats the best way to get that using php and mysql. i can count the number of rows or i can check for the last vale of id and from there i can know that the next row would be 5 and so would the id be. pls some appropriate code is what i am looking for. beginning wid connecting to the database. ( WHY? cos sql has a nasty habit of throwing Warnings which are difficult to get over) Thanks loads.

            

[trq]

Firstly, why would you ever need this information?

[ajoo]

if only for a cross check. or to get some value from the penultimate row. If i know the last row i can then scan the penultimate row. I hope thats a reason good enuff to elicit an answer. Thanks.

[trq]

You need to define what you mean by "last row". Relational databases don't store data in any particular order.

 

If you really want to know what the last row inserted was, you will need to store a timestamp with each record, then find the latest record using that.

[ajoo]

by last row i just mean the one having the last or latest id or highest id value or which would be the same as the number of rows in the database. Thanks.

[trq]

by last row i just mean the one having the last or latest id or highest id value or which would be the same as the number of rows in the database.

The highest id is not necessarily the same number as the number of rows. What if you delete a row?

 

Anyway...

 

SELECT id FROM tbl ORDER BY id DESC LIMIT 1

[mac_gyver]

not trying to give you a hard time, but there's not any good reason to need to do this (as trq already mentioned) and in the cases that people do try to do this, it can result in cross-linked/corrupted data should two different people insert new data at about the same time.

 

how exactly are you trying to use this information?

[ajoo]

hmmm well in what i have in mind, no entry in my table is getting deleted. It kind of proceeds linearly. so the last ID would be the same as the number of rows in this case. I must admit that I am very new to mysql and so my questions might be awkward and possibly there may be better solutions. So i am very open to any ideas that i can get.

 

Again chances of crossed linking is zero because each user has a unique table that can only be manipulated by that very person.

 

I hope that in the light of this reply, the questions that I asked are correct. if there is any way to better to do the same I'ld be happy to learn.

 

Thanks I'll try as suggested by trq. 

[ajoo]

SELECT id FROM tbl ORDER BY id DESC LIMIT 1

 

Kindly show How I might extract the value of id into a variable.

Thanks.

[ajoo]

ok i did this 

 

$result = mysqli_query($con,$query);

$row=mysqli_fetch_assoc($result);

$lastrow = $row['id'];

echo "Last Row found = ".$lastrow."<br>";

 

and got the result. All i want to know now is that if there's a shorter way to extract the value of the 'id'.

Hope this will help many like me who would like to know how to do this.

 

Thanks.

[mac_gyver]

so the last ID would be the same as the number of rows in this case

 

each user has a unique table that can only be manipulated by that very person.

 

 

both of those assumptions, while might be true now, are both bad assumptions to make in database designs. you will end up programming yourself into a corner (paint yourself into a corner) that will take a lot of effort to correct later or in a real design.

 

you also didn't answer "how exactly are you trying to use this information?", because that would let someone here help you with how you should be doing this.