Increase Number

talkster5

Hi there
I have a number in a database that i want to increase but i do not want it to increase every time a new field is added which is why i have not made it just auto_increment. What i need to do is retrieve the numbers already in the database and then whatever is the highest number make the new entry the next number up. Does anyone know how to do this because it would be a great help if you could let me know.

Thanks,
Matt

therealairness

You can retrieve the highest number from the table

SELECT MAX (tabledId) MaxId
FROM tblSoAndSo ;

talkster5

Thanks,

then what would i use to increase the numbe?

TheDefender

You fetch the value using [man]mysql_fetch_row[/man]
Create a variable and set it's value to that ID you retrieved + 1
Then, you can use that new variable to insert into your DB for the next ID. I caution you against doing this though, without record locking/table locking, becaue you could (in theory) have 2 separate queries try to use the same number (from 2 different people) and that would screw things up (if you want to maintain uniqueness.)

Why not let the auto_increment feature do exactly what it was designed to do?

talkster5

Thanks for the information,

Like i said though i dont want to use the auto_increment feature because im not increasing the value on every record that is being entered.

TheDefender

Just clarifying... It sounds as though you aren't talking about a need for a unique identifier.... so you should be OK from here. post back if you need more clarification/help with the steps.

talkster5

so once i have the variable it would be something like:

$number = $id+1

Thats the only bit i really need to know now just if the +1 would make it go to the next number up?

TheDefender

Yeah, that will do it...

Other methods would be:

$number = $id += 1;  // to increment by whatever value you want
$number = $id += 5;  // another example

$id++;  // to increment by 1

talkster5

Not to good with this kind of stuff but this is what i have and it doesnt appear to be working properly. No doubt i have something wrong.

<?php require_once('Connections/Site_Database.php'); ?>
<?php
mysql_select_db($database_Site_Database, $Site_Database);

$result = mysql_query("SELECT id MaxId FROM client_support ;");
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}
$number = mysql_fetch_row($result);
$id = $number[0];
echo $id+1;
?>

rhiza

I think if you changed $number[0] to $number['MaxId'] it would work, assuming MaxId is the column name of the number you're trying to get.

talkster5

I think MaxID tells it to find the highest number but not totally sure.

rhiza

I thought that was the idea, to get the highest number then add one.

talkster5

Originally posted by rhiza
I thought that was the idea, to get the highest number then add one.

I see what you mean now but it still didnt work. Just gives me the number 1 so i am assuming it is not getting the highest number from the database so it just uses 0 then adds 1 which makes it tell me its 1.

talkster5

I have changed the query to this now

$result = mysql_query("SELECT Max(id) as MaxId FROM client_support ;");

talkster5

Anyone else have an idea on how to do this?

talkster5

nvm, have it working now,

thanks.