HAPPY NEW YEAR TO EVERYBODY!!!

I hope not many of you had to start the New Year by asking for help. Here I go though.

I need to assign a unique number to every newsletter subscriber e.g: 0001, 0002, 0003 and so on. The PHP4 script must be able to check for the next available number in the database and then assign it to the subscriber.

For example, the database name is “MYDATA” and the table with subscriber details is called “subscribers”. The column where I want to store the unique number is called “uniq_id”.
If somebody wishes to subscribe to my newsletter, the script will check for the next available (not random!!!) number and then displays it. I can take it from there. I just don’t know how to check the column for the number availability and then make it use the available number.

I am using PHP4 and MySQL 5. Can somebody please write the simplest possible script that would work?



Many thanks.

Why don't you just autoicrement the uinq_id field and make it a primary key, then all you have to do is add in the next subscriber.

Originally Posted by AliKat

Why don't you just autoicrement the uinq_id field and make it a primary key, then all you have to do is add in the next subscriber.

Because I need it to happen outside of MySQL. The unique ID is only being assigned to unregistered subscribers and the field is being used by registered subscribers as well. Therefore I cannot change its properties.

The only interaction the script is suppose to have with the database is when it asks it to for the contents of the field. All I want is that the PHP script checks for the available number and then shows it to me. I can deal with it from there.

Run a select query with a descending sort order on uniq_id, the first field should be the last number added into the database, increment it outside of the database.

Originally Posted by AliKat

Run a select query with a descending sort order on uniq_id, the first field should be the last number added into the database, increment it outside of the database.

That could be correct in most cases but except in few situations. If you had the last record with unique id(auto increment) of say 10 and this last record was deleted MySQL would not assign the next record as 9+1 but as 11.

Best to use a query 'show table status'.

http://dev.mysql.com/doc/refman/5.0/...le-status.html

http://blog.jamiedoris.com/geek/560/

This will return an associative array and the value accessed with [Auto_increment] key

Originally Posted by maxxximus

...... If you had the last record with unique id(auto increment) of say 10 and this last record was deleted MySQL would not assign the next record as 9+1 but as 11

That is !!!EXACTLY!!! what I want to avoid. I need to know exactly how many guest subscribers I have. I will investigate those links and see if I find anything I could use.

Just to add more confusion to this, I should tell you that the uniq_id field is being used to store the first names of registered subscribers. My script should attach the unique number to the name "guest" thus making it guest0001, guest0002 etc. I can do the "attach" part of it and write it to the database, but that's pretty much all I can do.

What about something like this:

SELECT MAX(id) AS last_id FROM table

The only trouble I can see with that is once the user clicks on go, if another user is doing the same thing simultaneously, the id's could ultimetly clash.

That could be correct in most cases but except in few situations. If you had the last record with unique id(auto increment) of say 10 and this last record was deleted MySQL would not assign the next record as 9+1 but as 11.

How will a select query result in 11 if there is not 11 in the database?

Yes if you were looking for the next one, but if you just go

select uniq_id From <table_name> Order by uniq_id ASC;

How is that going to return 11?

and if you are looking for a count the number is not a reliable way to do so... use the count function to see how many members you have. Anyone could delete any number between your first and last entry and that total number is never going to be the same. So unless you look for the first missing number then you will never have an accurate count of members by the number in the appended to guest.