Retrieving Values and using them Php/MySQL

Jangle

Hi all,
I'm very new so be kind 🙂
I have a database which I need to get the value from the last index field from to increment and use as the index for the record being entered.

It was all working fine until I deleted a couple of records so while mysql_num_rows reports the correct number of rows, what I need is the value to avoid a duplicate entry.

the following code doesn't work but it is as far as I have got. I have searched endlessly and bashed my head against it for ages.

//$result is from a previous query which also displays the contents of the db
$rowsInDb = mysql_num_rows($result);
echo 'number of rows in DB= ' . $rowsInDb . '<br><br>';// just there for debugging

$lastRow = mysql_query("SELECT `Index` FROM `myDatabase` WHERE 1 LIMIT".$rowsInDb." , 1");

echo 'last row in DB= ' . $lastRow . '<br><br>';// just there for debugging
$num_rows = $lastRow +1;

//$num_rows is then used for the record being added to the database[/SIZE]

The last echo doesn't print a value.

I know I am missing something important here, so any help would be much appreciated.
Ta
Jangle

severndigital

for starters change this

$lastRow = mysql_query("SELECT `Index` FROM `myDatabase` WHERE 1 LIMIT".$rowsInDb." , 1");

to this

$lastRow = mysql_query("SELECT `Index` FROM `myDatabase` WHERE 1 LIMIT'$rowsInDb' , 1");

tjohnson_nb

How about this?

$sql="SELECT `Index` FROM `myDatabase`";
$result=mysql_query($sql);
$n=mysql_num_rows($result);
mysql_data_seek($result, $n-1);
$row=mysql_fetch_row($result);
$index=$row[0];

Jangle

Thanks. That did the trick.

Piranha

WARNING

That is a very bad way to handle IDs in databases. What if two persons creates new rows at the same time? They might get the same ID, and it would be a big problem in the database. It is much better to use auto_increment.

If you have and will have only one user there will of course be no such problem. Then the solutions from others are still not any good. They work fine when there are few rows in the database, but when the database begins to grow it will take lots of time to get the right id. If you will use this way I suggest the following instead:

$sql="SELECT max(`Index`) as ind FROM `myDatabase`";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$index = $row['ind'];
$index++;

EDIT: To make it really clear you should use the first suggestion with auto_increment and not the second one.