MySQL Table Relation and triggers

[stephend330]

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

 

[bh]

Hi,

 

If you dont wanna do from your PHP script, yes you can use MySql Triggers. The MySQL manual helps you.

[stephend330]

i have tried triggers before but after setting one it stops the  login script from working

[bh]

Maybe the script was wrong, still you have the trigger script?

[stephend330]

no i thought i had saved it but i havent could you post the syntax for a trigger

[fenway]

no i thought i had saved it but i havent could you post the syntax for a trigger

That's what the refman is for -- bh was even kind enough to link it for you.

[stephend330]

i ran this

CREATE TRIGGER ins_sum BEFORE INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO res (username) values (username);
END;
$$

and now my registration script will not add the user to the database but reports no errors

[gizmola]

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).

[gizmola]

Note that I left out the mysql_query() etc stuff, but the constants I provided are the queries you send to mysql to have it use transactions.

[stephend330]

the login system of my site was a system that i download of the web it has a user id field but it looks more like a encryted id iv tried adding an id field but script dosent work with it

 

ill try you solution later and let you knw the score

[gizmola]

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.

[stephend330]

ok thanks

 

is there a theard for the relation of tables becoz i cant get to grips with it or an example i have tried relating my tables but dunno if it has worked or not

 

[gizmola]

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

 

 

[stephend330]

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

[neller]

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)

[fenway]

Always echo the actual query.