Private Message

[Illasera]

Hey all, Here is the deal, I am using MySQL 5.0.77, phpMYAdmin 2.8.2.4 (Versions maybe necessary) with Storage Engine of MyIASM.I want to give users the ability to store private messages.Now i have a table for users that stores all the users data in it, But i cant store private messages inside it, Since i need a full table for it as well.Now to create a new table i have no problems with, The problem is to find out is "What private message belong to which user (The recipient)"So i need to link the private message table to the user table, And link the private message itself to the User ID, Now i have read about Foreign key, But there is no support for it AT THE MOMENT, in MyIASM,Any ideas?

[Illasera]

Hey all, Here is the deal, I am using MySQL 5.0.77, phpMYAdmin 2.8.2.4 (Versions maybe necessary) with Storage Engine of MyIASM.I want to give users the ability to store private messages.Now i have a table for users that stores all the users data in it, But i cant store private messages inside it, Since i need a full table for it as well.Now to create a new table i have no problems with, The problem is to find out is "What private message belong to which user (The recipient)"So i need to link the private message table to the user table, And link the private message itself to the User ID, Now i have read about Foreign key, But there is no support for it AT THE MOMENT, in MyIASM,Any ideas?

Ok , I understand that i have no choice but to use InnoDB, But how do i execute the code in php?How do i call one table and asks its key for another table? Do i query data from 2 tables all togheter?Thanks in Advance.

[birbal]

How do i call one table and asks its key for another table? Do i query data from 2 tables all togheter?

you have to use join in this case to get the tables data all together i think.

[Illasera]

you have to use join in this case to get the tables data all together i think.

Thanks mate, I have read all about it, but the following questions are :1.)Does it work with MySql as well? I noticed it worked well with SQL.2.)What`s the difference between JOIN and Foreign key?3.)Does join work with MyIASM storage engine (I assume that it does but still).Thanks in advance.

[birbal]

1) yes its works with mysql. i guess join works with all sql i think.2)

2.)What`s the difference between JOIN and Foreign key?

foreign keys are use to give a relationship beetwen two or more columns. foreign key is a constraint. it ensure that child table's value, contains in parents table (which column will be in relation). it reduces human error and it do automatic database update and deletion. (ON DELETE UPDATE ON DELETE CASCADE). you dont need to manage it manually. mysql do it for you.join collect data from more the one tables depending on the joining condition.(DATA QUERY). it is not must that you have to use foreign key to use join. (but genaraly people join in relational column and it is the way)3)as far i know join works on myisam also.

[Illasera]

Thank you, Hope this will work Gonna test the JOINing it soon.

[Fmdpa]

foreign keys are use to give a relationship beetwen two or more columns.

Yes, and AFAIK, the foreign key clause in MySQL actually does nothing other than verbally establish the relationship between two tables. Like a comment.

[birbal]

i think foreighn key is not for only for comments. its significance in (ON UPDATE and ON DELETE)...how the child table will react if the change (update or delete) has been made in parent table. cause childs are dependent to their parent tables. whereas change in child table will not affect parent tables.foreign will stop any redundancy of data flow in realtional databse.

[Fmdpa]

Ok, that would make sense.

[Illasera]

yay, thank you all

[Illasera]

Behind the scane : I wish to know the technical behind the JOIN part, Is JOIN actually append one table to another?Does it actually perform 2 queries when i JOIN a table to another (one from Primary table, and one from secondary)?Does it Resize the primary table according to the JOINed table?in short, How does the server handle the JOIN operation behind the scanes.Thanks in advance.

[justsomeguy]

It creates a new temporary table and populates it with values from the joined tables according to the join conditions. If you just do a join without any conditions, or with only conditions in the WHERE, then the temporary table is a cartesian product of the two joined tables, or it has every combination of rows in the two tables. If each table has 10 rows, and the join does not have any conditions, then the joined table has 100 rows. If each table has 100, then the joined table has 10000 rows. It doesn't alter the original tables though. It's much more efficient to put your conditions in the join to limit the size of the joined table than it is to put your conditions in the WHERE clause only.

[birbal]

It creates a new temporary table and populates it with values from the joined tables according to the join conditions. If you just do a join without any conditions, or with only conditions in the WHERE, then the temporary table is a cartesian product of the two joined tables, or it has every combination of rows in the two tables. If each table has 10 rows, and the join does not have any conditions, then the joined table has 100 rows. If each table has 100, then the joined table has 10000 rows. It doesn't alter the original tables though. It's much more efficient to put your conditions in the join to limit the size of the joined table than it is to put your conditions in the WHERE clause only.

thanks for the clarification. now i am geting why you were telling that using condition in ON is more efficient.

[Illasera]

yay cheers