MySQL, Multiple Tables

Ok, I'm coding a project which is a leap than what I'd normally do. Before, I've always learned ONE table... put EVERYTHING in one database table. I'm making a profile system so there needs to be at least two tables: 1 for users, 1 for content. My problem is, how do I link the two together? I could probably figure this out faster if someone explained and posted sample SQL code that shows how the two are linked together. Thanks!! [N]F

Comment/Reply (w/o sign-up)

There are multiple ways of linking two tables, and it would depend on what exactly do you want to link these two tables to choose between them.

For most of the cases a JOIN of the tables works and in some complex cases one would require to go for INNER QUERIES. Note that if you are planning on using MySQL as your database, as far as my knowledge goes, the present version does not support INNER QUERIES. (People, correct me if I am wrong here).

Now what are JOINs? Well, ther are many different types of JOINS: Self Joins, Natural Joins, Inner and Outer Joins. Explaining them would take a lot of verbiage and instead I will just show a sample of JOIN query between two tables. (You may extend it to multiple tables.

Now, I am assuming here that you have two table 'users' and 'content'.

I am putting here some of the columns that I think may be present in these tables:

users
-userid (PK)
-name
-password

content
-contentid (PK)
-userid (FK)
-contenttext

The above columns are minimal, and you may need to add more. But for the sake of explaining JOINs, these should be sufficient.

Now, assume that data if filled in both the tables. The ones in 'users' table is straight forward. The data is put in 'content' table with 'userid' being a foreign key. This means, the content's author is that particular user.
userid and contentid are Primary Keys (hence PK), and userid in 'content' table is a Foreign Key (FK).

Now, we will see the SQL of how to retrive a particular content specified by contentid, along with the author name.


Note that c.contentid = $cntid part in the WHERE clause is like you would use in retrieving data from one table. ($cntid is just a PHP variable - you may replace it by any value, that is hard code it or pass it by any other means).

Now, what compromises of a join? Yes, the c.userid = u.userid joins these two tables. The result of the query is that it will fetch content id and content text is picked up from 'content' table and author name is picked up from 'users' table.


I have assumed a lot many things above, but if you want something specific, please do ask.

 

 

 

Comment/Reply (w/o sign-up)

Vyoma is correct..

I know of atleast 30 ways to connect the data together and they work on different versions of mySQL.. some are version specific and some are not..

what the post above shows is the basic way of doing stuffs with connecting data.

--

if you cant understand how joins work.. i can teach you how to do the same thing without the join command but this will be a little longer query.

the sample table structure will remain the same since it is a good basic sample for your current needs.

Comment/Reply (w/o sign-up)

It seems that nightfox starts learning what is a relationnal database, and why using relations is more interesting than using single tables.

Comment/Reply (w/o sign-up)

Yeah, agreed, that is the "fun" way to create something like this, but for me sometimes it gives a big headache how to do it "the best way".. I used to do databases with text files making them work by directories and file names and text inside, so you can imagine how my things got a lot more easier when I started getting into MySQL.

Comment/Reply (w/o sign-up)

Wow, that's a lot! I'll have to find some time to read it all and absorb it! lol

[N]F

Comment/Reply (w/o sign-up)

I usually just get the necessary information first and check it when needed later on.....

Or with user things their username/password is registered using $_SESSION and sometimes other bits of data that I can verify their signed in and who they are and whether they are banned and such.

Comment/Reply (w/o sign-up)

on my first tries.. i completely rewritten and restructured my whole database..
i started on 1 table then jump to 5 tables..

since there is no connection, the database is easy..
when i consider space saving and verification system..

i need to link them together.. how painfull the headache was..

--

when i bump into transactional database.. the pain and suffering grows much more..

i do now most of the initial designs on paper and always takes 3 rechecks before
i create the actual database..

--
thanks to nested quries, i have manage to avoid joins.. =)

Comment/Reply (w/o sign-up)

OK... I'm stuck and getting frustrated... I have two tables:
-users
-content

The users table contains the following:
-id
-user
-pass
-access_name

The content table contains the following:
-id
-content

Here's my test file code:

which gives me the following error whenever I try to access it even WITH a VALID access name:


I think I joined the tables wrong with:
$sql = "SELECT * FROM `users, content` WHERE `access_name`='".$active_user."' AND `users.id`=`content.id`";
but I'm not sure how it should be. I've been googling for about an hour and I'm really confused on how this should be!

[N]F

Comment/Reply (w/o sign-up)

Ok well from what I know, when asking for something from two differen things, I *don't think* (not sure) that you should have `` around the two, maybe around both or not at all.

Also using WHERE `something` = `something`, I don't think that it is right, try surrounding = `something` in single ''s instead of ``. Same for normal variables like $my_var you can just do, `someval` = '$someval'.

Hope that helps, I'm really puzzled as to why you surrounded two things in one set of `'s but it could be something I haven't needed to use yet.

Here's what I would try your sql as:

$sql = "SELECT * FROM `users`, `content` WHERE `access_name`='$active_user' AND `users.id`= 'content.id'";

How does users.id and content.id work anyway. You are most likely getting the error on trying to fetch the array because the syntax is wrong or something you try to grab does not exist (meaning it could still mean wrong syntax).

Comment/Reply (w/o sign-up)