MySQL, Multiple Tables

[nightfox1405241487 0]

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

[Vyoma 0]

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.

SELECT c.contentid, c.contenttext, u.nameFROM content c, users uWHERE c.contentid = $cntntidAND c.userid = u.userid

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.

[vhortex 1]

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.

[yordan 10]

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

[Quatrux 4]

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

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.

[nightfox1405241487 0]

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

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

[N]F

[Chesso 0]

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.

[vhortex 1]

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

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

[nightfox1405241487 0]

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:

<?php$username = "root";$password = "";$hostname = "localhost";	$database = "join-test";$dbh = mysql_connect($hostname, $username, $password) 	or die("Unable to connect to MySQL");mysql_select_db($database)	or die("Unable to connect to database. Check connection settings.");// #############################################$active_user = $_GET['id'];// #############################################$sql = "SELECT * FROM `users, content` WHERE `access_name`='".$active_user."' AND `users.id`=`content.id`";$row = mysql_fetch_array(mysql_query($sql));if($row['id']!="") {?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://forums.xisto.com/no_longer_exists/ http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><title>Untitled Document</title></head><body><?phpecho("User's profile:<br>".$row["content"]."");?></body></html><?php}else{die("<h1>Invalid ID or General Error</h1>");}?><?phpmysql_close($dbh);?>

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

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\mysql-joins\test.php on line 14Invalid ID or General Error

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

[Chesso 0]

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

Edited August 4, 2006 by Chesso (see edit history)

[nightfox1405241487 0]

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

 

Didn't work....

 

I don't even know how it works... I'm going off the pointless examples that don't make any sense... I've been googling & looking through my PHP & MySQL books... so based on these confusing examples, I revised my code to this:

$sql = "SELECT * " .	   "FROM users u " .	   "JOIN content c " .	   "WHERE access_name='$active_user' AND id = 'id'";$row = mysql_fetch_array(mysql_query($sql));

and for some reason, it's complaining about this line:

$row = mysql_fetch_array(mysql_query($sql));

 

It's always that line whenever I work with MySQL... that's why I hate working with it so much...

 

[N]F

[Chesso 0]

Yeah because of the mysql_fetch_array, it errors because the syntax is incorrect for the SQL or your'e not asking for information exists.

[nightfox1405241487 0]

ok,ok... I took an example from my other php book and now, I replaced

$row = mysql_fetch_array(mysql_query($sql));

to

$result = mysql_query($sql)

or die(mysql_error());

 

and now, I got a different error message:

Column 'id' in where clause is ambiguous

 

At least it is better than working with Microsoft errors which tell you nothing... I can partly understand what that means...

 

Edit: Ok, I re-did the code and it's now this:

$sql = "SELECT * " .	   "FROM users u " .	   "JOIN content c " .	   "WHERE u.access_name='$active_user' AND u.id = 'c.cid'";$row = mysql_fetch_array(mysql_query($sql));

not producing any errors, but now I see my "Invalid ID or General Error" message even when I try to execute the script with a VALID access name (test.php?id=access_name)

 

[N]F

Edited August 4, 2006 by nightfox (see edit history)

[vhortex 1]

Edit: Ok, I re-did the code and it's now this:

$sql = "SELECT * " .	   "FROM users u " .	   "JOIN content c " .	   "WHERE u.access_name='$active_user' AND u.id = 'c.cid'";$row = mysql_fetch_array(mysql_query($sql));

not producing any errors, but now I see my "Invalid ID or General Error" message even when I try to execute the script with a VALID access name (test.php?id=access_name)

 

[N]F

 

Please recode that to this one..

you have problems with the quotes.. use ` when you want to say table name or column name.

 

avoid using ' since mySQL dont understand that well.. " is use for table names..

$sql = "SELECT * " .	   "FROM users u " .	   "JOIN content c " .	   "WHERE `u`.`access_name` = `"  . $active_user. "` AND `u`.`id` = `c`.`cid`";$row = mysql_fetch_array(mysql_query($sql));

I hope this solves the problem.

 

 

***********

 

about the $active_user

where do you get its value? it seems that you are using this variable but it was never been given a value.

 

please use this for visual test after the declaration of $sql

echo $sql;

if the "name =" part have nothing in its rigth side then, you gave fail to assign $active_user a value.
Edited August 4, 2006 by vhortex (see edit history)

[nightfox1405241487 0]

$sql = "SELECT * " .	   "FROM users u " .	   "JOIN content c " .	   "WHERE `u`.`access_name` = `"  . $active_user. "` AND `u`.`id` = `c`.`cid`";$row = mysql_fetch_array(mysql_query($sql));

I hope this solves the problem.
***********

Nope, didn't work... it's still complaining about $row ...

Visual reference of the $sql:
SELECT * FROM users u JOIN content c WHERE `u`.`access_name` = `silly_george` AND `u`.`id` = `c`.`cid`

about the $active_userwhere do you get its value? it seems that you are using this variable but it was never been given a value.

It has a value... well, it's from the URL...

my original code:

// #############################################$active_user = $_GET['id'];// #############################################

so basically, if there's a valid access name that comes after the ?id= in the URL, then fetch that user's content for their profile out of the content table.

[N]F