Auto ID for events

[Bradley99]

So, I have a table in my DB, Betting. It's for a kinda fantasy pick'em/betting game. My table is as follows. . .(It's for MMA).

 

bet_id match_id user_id fighter_id method round bonus

 

Now, I need to have a different MATCH ID for EVERY fight that users pick for. So Guy1 vs Guy2 the Match ID is 1. Guy3 vs Guy4 the Match ID is 2. I need it to be this so when I update the results of the fights, I can do IF match_id = '2' AND fighter_id = '1' UPDATE. . . . .

 

How can I make so this happens? ANY help is appreciated. (Please bare in mind, I'm using no CMS or anything, I have created a very basic Admin back-end.)

[Mahngiel]

When you create your matches in the admin section, the values (fighters and such) will populate their own table.  One row per match, with an auto incrementing match_id column.

[Bradley99]

I already have auto incrementing on the bet_id table though? Any idea's? I suppose I could ENTER my own match ID when i create the fights, Silva vs Sonnen = 'slvavssonen' type thing, It's just then keeping up with them making sure i dont repeat.

[Mahngiel]

You're losing focus.

 

We talked about this the other day - you need several tables, because each table represents a set of data.  The data set from those tables needs to be unique, and the easiest way to accomplish this is to give them an *_id that auto increments.  You can positively identify data with this id, and reference it in other tables just as easily.  There's nothing you need to do to manually ID it.

 

In your bet table:

[*] bet_id is an AI row unique to this table

[*] match_id is the AI ID for the match your users are betting on

[*] user_id is the AI ID for the user who's making the bet

[*] fighter_id, method, round, and bonus are the values the user has chosen that corresponds to the match_id above

 

This is what and why they call it a relational database.  All of these items correspond / relate to one another through various methods.

[Bradley99]

I have no idea how to auto increment two columns in 1 table though?

 

I currently have Betting Table as you said, Now I have events table & fights table - the latter two are for my own use for adding events and adding fights to events.

 

 

[Mahngiel]

Assuming you have phpMyAdmin for database management, when you create a table and are given the opportunity to create the columns for said table, you are presented with a wide variety of properties.  One of those properties is going to be a checkbox for Auto Increment.

[Psycho]

I have no idea how to auto increment two columns in 1 table though?

 

You don't need (and can't have) two auto-increment ids in a single table. Each table can have one auto-increment ID and then you use those keys in other tables to relate the data. In your first post you stated you had a table with the following fields:

 

    bet_id  match_id  user_id  fighter_id    method  round  bonus

 

Assuming that is the bet tables then those fields should be something as follows:

 

bet_id (auto-increment field for this table)

match_id (a foreign key of the auto-increment field from the matches tables)

user_id (a foreign key of the auto-increment field from the users tables)

fighter_id (a foreign key of the auto-increment field from the fighters tables)

method (???)

round (a numeric field)

bonus (assume this is a float field for an amount)

[Bradley99]

Thanks Psycho you got it spot on! I think I was just rushing things and trying not to have more than 2 tables for the full thing, but makes more sense and easier to do a table for everything.

 

Thanks everyone who helped!!