Page 1 of 1
Giving a customer an ID number
Posted: Wed Dec 10, 2003 9:21 am
by x2designs
OK, I have a client list stored in a MySQL database and each time a new row is entered it auto_increases the ID column. So now the client has an ID number
However, I want to be able to do this before entering their name into the database, making sure that the number they are given is only ever used once.
Any help appreciated
Ian
Posted: Wed Dec 10, 2003 9:32 am
by aquila125
if you have MySQL, the auto_increment field IS unique.. nothing you can do about it... it's MUCH safer to let mysql handle this then to do it in PHP...
Posted: Wed Dec 10, 2003 9:34 am
by x2designs
I suppose I could always generate the ID number in the database, then echo it on the webpage, then when it comes to finally inserting all the data. Just use Update where id = $id.
Posted: Wed Dec 10, 2003 9:37 am
by aquila125
why do you need the customer ID up front?
Posted: Wed Dec 10, 2003 4:02 pm
by DuFF
As long as you have auto_increment on, when you do the first MySQL query (such as right after the customer has registered) just leave the ID field blank and MySQL will take care of the rest.
so:
Code: Select all
<?php
$query = "INSERT INTO table VALUES ('','$customername','$customerinfo')"; // the first '' is where the id column is
?>
Then whenever you retrieve info from the database first, just make sure you keep the $id and then later use it to update the database.
Posted: Wed Dec 10, 2003 6:55 pm
by JAM
DuFF wrote:As long as you have auto_increment on, when you do the first MySQL query (such as right after the customer has registered) just leave the ID field blank and MySQL will take care of the rest.
so:
Code: Select all
<?php
$query = "INSERT INTO table VALUES ('','$customername','$customerinfo')"; // the first '' is where the id column is
?>
Then whenever you retrieve info from the database first, just make sure you keep the $id and then later use it to update the database.
Just adding the following based on
DuFF's post:
PHP Manual wrote:
mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.
Look that one up for more info. Might be interesting.
Posted: Wed Dec 10, 2003 8:56 pm
by m3mn0n
Any NULL value will do.
NULL, 0, '', and the likes.
Posted: Thu Dec 11, 2003 1:42 am
by aquila125
or just skip give the column names.. is easier to read and less error prone:
$query="INSERT INTO table (column1,column2) values (value1,value2)";
Posted: Thu Dec 11, 2003 5:48 am
by AnsonM
aquila125 wrote:if you have MySQL, the auto_increment field IS unique.. nothing you can do about it... it's MUCH safer to let mysql handle this then to do it in PHP...
it is unique... but once one is deleted.. then the numbers go all screwed... like if u had 10, 11, 12, 13 and u deleted 11, then it would be weird...

Posted: Thu Dec 11, 2003 5:50 am
by JayBird
why would it be weird, all numbers would be still unique.
Does it matter that customer id numbers are in sequence!?
Mark
Posted: Thu Dec 11, 2003 5:50 am
by twigletmac
AnsonM wrote:aquila125 wrote:if you have MySQL, the auto_increment field IS unique.. nothing you can do about it... it's MUCH safer to let mysql handle this then to do it in PHP...
it is unique... but once one is deleted.. then the numbers go all screwed... like if u had 10, 11, 12, 13 and u deleted 11, then it would be weird...

That shouldn't make any difference to you it's just an identifier for that row - reusing unique id's is a bad idea and if your display depends on unique id's being sequential then you need to change your code so that it doesn't.
Mac
Posted: Thu Dec 11, 2003 5:53 am
by AnsonM
Bech100 wrote:why would it be weird, all numbers would be still unique.
Does it matter that customer id numbers are in sequence!?
Mark
lol yeah... sometimes

Posted: Thu Dec 11, 2003 3:59 pm
by DuFF
When, for example, id number 11 is deleted nothing "wierd" happens. MySQL just continues incrementing the last id that was set. So if you had 30 customer ID's and deleted number 11, then MySQL would just continue to 31, 32, 33 . . .