Jump to content

Private Message


Recommended Posts

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?

Link to post
Share on other sites
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.
Link to post
Share on other sites
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.
Link to post
Share on other sites
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.
Link to post
Share on other sites

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. Edited by birbal
Link to post
Share on other sites
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.
Link to post
Share on other sites

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.

Edited by birbal
Link to post
Share on other sites

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.

Edited by Illasera
Link to post
Share on other sites

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.

Link to post
Share on other sites
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.
Link to post
Share on other sites

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.   Paste as plain text instead

  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.

Loading...
×
×
  • Create New...