Creating member Ida

dniezby

I'm trying to figure out how to do this.

I have a membership organization that has members around the country. Each area of the country is divided up into "regions"

When a member joins and their state is X i need the system to create a sequential ID that begins with their region code.

Let's say member X lives in region 212.

He joins the site and assigns a 2121234567

212 is the region the rest is the sequential to make a ten digit ID.

How do I do this?

In the MySQL database? With php?

I do have a region table so the system can look up the region before creating the ID.

Any help will be greatly appreciated.

johanafm

Why do you need this? Without knowing the answer to this question, I'll go ahead and simply suggest that you don't.

Are you aware that you'd be breaking 1NF by doing this (see database normalization

Instead, create your member table with an auto incrementing id field and region id as a foreign key, each stored separately and in a non-related fashion. That is

-- table to store regions
CREATE TABLE region (
	id	INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name	VARCHAR(20)
)

-- table to store members
CREATE TABLE member (
	id			INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	region_id	INT UNSIGNED,
	-- and of course, other stuff goes in this table as well, such as
	name		VARCHAR(30),


FOREIGN KEY (region_id) REFERENCES region(id)	
)

Looking up members by region is now easy

-- by region id
SELECT id, name FROM member WHERE region_id=212;

-- by region name
SELECT m.id, m.name
FROM member m
INNER JOIN region r ON m.region_id = r.id
WHERE r.name = 'some_area';

And counting number of members in an area is also done just as easily

SELECT COUNT(*), r.name
FROM member m
INNER JOIN region r ON r.id = m.region_id
GROUP BY r.name;

dniezby

I've never worked with a database where I didn't create relations.

You think I should create everything without them?

bradgrafelman

He never said creating everything without any relation, johanafm was simply suggesting that you separate the two pieces of data (region # and sequence/ID #) rather than trying to combine them into a single field.

dniezby

Sorry, it didn't come across correctly. I get what he's saying.

I understand how to save it to the database now but how do I get it to do this?

A member fills out a form. On submit, it checks the persons region name and adds them to the database. Then is gives them a "welcome new member, here is your new ID number IDNUMBER"

I'm assuming that whenever I wanted to display the member's ID I would have to use some type of statement that would join the two database fields?

I want to make the site use their ID as their way to track everything. Such as game results, member stats etc.

bradgrafelman

dniezby;10989338 wrote:
I'm assuming that whenever I wanted to display the member's ID I would have to use some type of statement that would join the two database fields?

The only "statement" you'd need is one using concatenation...

$var1 = "123";
$var2 = "456";

$id = $var1 . $var2;

EDIT:

dniezby;10989338 wrote:
I want to make the site use their ID as their way to track everything. Such as game results, member stats etc.

Well how do you propagate their member ID from one page to another? Using sessions?

Regardless of the mechanism, you've got two ways of going about it:

Store the two identifiers separately, e.g. their region id and their user id (or whatever you call it).
Keep them together (e.g. as one continuous identifier) but simply strip off the first three digits (are region ID's always 3 digits?)... in other words, simply reverse the concatenation above.

Both options leave you with the two separate identifiers to use when querying your database. Note that if the region ID # is not a fixed width value, then option #1 would be a lot simpler. (Then again, I'd guess option #1 is simpler even if it is fixed width.)

NogDog

If there is some business rule that requires the ID to include the state code (and which you cannot convince those involved that said rule be changed), if the state code and user number are in separate fields, they can easily* be joined in the query itself, e.g.:

SELECT CONCAT(LPAD(`state_code`, 3, '0'), LPAD(`id`, 7, '0')) AS user_id FROM . . .

for some arbitrary value of "easily" 😉

johanafm

NogDog;10989358 wrote:
* for some arbitrary value of "easily" 😉

🙂