Jump to content

MySQL Table Relation and triggers


stephend330

Recommended Posts

Hi I’m a novice php and MySQL developer trying to develop a game. The problem I’m having is i need to copy the username field to multiple tables when the users register so the rest of my app works

 

so if anyone has an insight to how i might do this then plz reply or email me will supply code if needed

 

thanks

 

Link to comment
Share on other sites

If you're copying username around, that's a pretty good indication that your database structure is not good.  You should have a user table, keyed by a number like user_id.  Usuallly people will have user_id be AUTO_INCREMENT.  When you have related tables what gets stored is the user_id rather than the username. 

 

Regardless of this, another option is to use transactions.  However, in order to use transactions with MySQL you need to use a mysql engine type that supports them.  Most people use InnoDB for this purpose.  Then you can do:

 


// 'START TRANSACTION'
try {
  //insert A
  //insert B
  //insert C
  // 'COMMIT'
} catch (Exception $e) (
  //'ROLLBACK'
}

 

You can write a simple static wrapper class around the mysql transaction calls to make this very easy to use.  Thus if any of the inserts fail, the entire transaction will be rolled back, guaranteeing your db will be in a consistent state, even though your code remains procedural (in your PHP code).

Link to comment
Share on other sites

Whatever id they are using is fine.  Sometimes they will use some sort of hash value or guid for an id.  Generally this is to facilitate sharding, but for a small site, sounds like overkill.  Regardless, whatever the primary key of the table is, is what you should use to relate a user to other tables you create.

Link to comment
Share on other sites

Relating tables just means, having a column or set of columns that allows you to join one table to another.  These are typically the primary and foreign keys in a table.  The valid relationships you can have are 1-1 or 1 to many.  There is a logical many to many relationship that you can resolve in an rdbms with a table between the two tables that have the logical many to many, each which provides a 1-Many. 

 

Look into these terms, and hopefully you'll get an idea. 

 

This tutorial from the mysql site might help as well:  http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

 

Link to comment
Share on other sites

thanks for the awsome info guys.

 

("SELECT username, clan_tag FROM user_clans WHERE username=" . $session->username . "")

 

the above mysql query gives me and Unknown column 'admin' in 'where clause' any ideas why

i dunno if i can ask a question of topic if not let me no and ill post it some where else

Link to comment
Share on other sites

have you added single quotes either side of the username? it looks like you are escaping the query using the double quotes to use the $session->username variable but you need single quotes if so either side of those

 

' ".$session->username " '  (without the spaces)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.