I have a MYSQL database that house user names and user numbers. the process is user goes to a url inputs all the information. Goes to the office they search for the user makes sure all the information is correct and assigns them a number inputs it into the the page that writes to the database and updates the cell. No harm no foul. Now they want to be able to track all of the times a number was assigned and the numbers that was assigned. What would be the cleanest way to track this?

Member Avatar for diafol

I'm abit unsure what you're trying to do. Let me get this right...

1) User fills in a form on your website and submits the info.
2) Somebody at the office picks up the request and manually assigns the user with a number.
3) This number is tagged on to the user's record in the DB or a record is created with all the info (inc. number).

I have no idea what this number is - I assume it has some meaning as it seems that it can be re-used - so it's not a unique identifier / primary key.

user_id (pk|int)
username (varchar)
user_number (int)

You can use COUNT and GROUP BY clauses in your SQL to get a list of unique numbers and the number of times that they appear in the table. Have a look at the mysql manual for examples of usage:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

right now the assigned number is supposed to be unique number per user.
but it is set as a varchar in the database, they said in the past they did reuse numbers and at times went to an alpha numeric value
So I was trying to find away to retain all assigned numbers.

It is like going to the old style deli. Walkup to counter order a sandwich get a number. a hour later go back order another sandwich you get a new number. I want to retain ever number that was given to the user.

Member Avatar for diafol

So an user can receive multiple numbers. I didn't realise that. OK in that case:

user table:
user_id (PK, int)
username (varchar)
...
numbers table
supply_id (PK - optional)
user_id (FK, int)
supplied_num (varchar!)

Then do the COUNT / GROUP BY. I'd use an inner join for linking both tables in the query.

ok cool thank you
Ill give that a whirl

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.