FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

mySQL Query/PHP code - get the highest value...

   Frihost Forum Index -> Scripting -> Php and MySQL
 


PMonkey
What I Currently have
I have a table in a database where the primary key (ID) is numbers (1, 2, 3, 4...) and a form that users fill in to enter a record into the table. The primary key in the form is hidden and is automatically put in by adding one on to the total number of records.

<input name="id" type="hidden" value="<?php echo $totalRows_rstPhotos + 1 ?>" />

eg. I have a total of 14 records, the next ID would be 15.

But what if I had deleted one record (ID 8 ), the last ID was 14 but there are only 13 records (making the next 'auto' ID 14) creating a duplicate ID

What I Want to do is...

I could do with an example of an SQL query of piece of PHP code that would get the highest value in the ID column. As '$totalRows_' gets the total number of rows in the table (or results in the query), is there something that gets the highest value in the column.

eg. a column consists of...

1
2
5
6
7
9
10
12
18

There are 9 rows ('$totalRows_rstPhotos + 1' would = 9). But the highest value is 18 ('$SomeCode_rstPhotos + 1' would = 19).

Is there any piece of code to do this.

Any help would be very much appreciated!
Thank You!
hexkid
PMonkey wrote:
What I Currently have
I have a table in a database where the primary key (ID) is numbers (1, 2, 3, 4...) and a form that users fill in to enter a record into the table. The primary key in the form is hidden and is automatically put in by adding one on to the total number of records.

<input name="id" type="hidden" value="<?php echo $totalRows_rstPhotos + 1 ?>" />

eg. I have a total of 14 records, the next ID would be 15.

But what if I had deleted one record (ID 8 ), the last ID was 14 but there are only 13 records (making the next 'auto' ID 14) creating a duplicate ID

What I Want to do is...

I could do with an example of an SQL query of piece of PHP code that would get the highest value in the ID column. As '$totalRows_' gets the total number of rows in the table (or results in the query), is there something that gets the highest value in the column.

eg. a column consists of...

1
2
5
6
7
9
10
12
18

There are 9 rows ('$totalRows_rstPhotos + 1' would = 9). But the highest value is 18 ('$SomeCode_rstPhotos + 1' would = 19).

Is there any piece of code to do this.

Any help would be very much appreciated!
Thank You!



You do not want what you say you want Smile
Imagine two people access your page at the same time ... they will both get the same number in the hidden field, which will generate havoc with your database when they submit the data.

What you want is to automatically generate the next available number independently of how many connections are active to your database at the moment. You do that by specifying the ID column as AUTO INCREMENT and leaving it out of the INSERT instruction (or by specifying NULL for the column).
Code:
insert into table (ID, name) values (NULL, 'hexkid')
If you need the generated ID you can use the database specific code for that
Code:
select last_insert_id();
or the PHP equivalent
Code:
$last_id = mysql_insert_id();



EDIT: If you really want what you say you want, try this
Code:
$sql = "select id from table order by id desc limit 1";
$res = mysql_query($sql) or die(mysql_error());
$new_id = 1 + mysql_result($res, 0);
PMonkey
hexkid wrote:

You do not want what you say you want Smile
Imagine two people access your page at the same time ... they will both get the same number in the hidden field, which will generate havoc with your database when they submit the data.

What you want is to automatically generate the next available number independently of how many connections are active to your database at the moment. You do that by specifying the ID column as AUTO INCREMENT and leaving it out of the INSERT instruction (or by specifying NULL for the column).
Code:
insert into table (ID, name) values (NULL, 'hexkid')
If you need the generated ID you can use the database specific code for that
Code:
select last_insert_id();
or the PHP equivalent
Code:
$last_id = mysql_insert_id();


On a bigger website I know that this would be true. But this is a personal website where there will be a max of about 10 people who actualy get to insert a record. Also, there wont be a great number of records os any errors i could sort out manualy.

hexkid wrote:


EDIT: If you really want what you say you want, try this
Code:
$sql = "select id from table order by id desc limit 1";
$res = mysql_query($sql) or die(mysql_error());
$new_id = 1 + mysql_result($res, 0);



Thanks though, I'll give it a try, and I will keep a copy of this for later days when I will need this Smile
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.