30 replies to this topic

[nightfox]

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]

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.name
FROM content c, users u
WHERE c.contentid = $cntntid
AND 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]

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]

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

[Quatrux]

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.

[nightfox]

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]

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]

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

[nightfox]

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://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<?php
echo("User's profile:<br>".$row["content"]."");?>
</body>
</html>
<?php
}else{
die("<h1>Invalid ID or General Error</h1>");
}
?>
<?php
mysql_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 14
Invalid 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]

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 by Chesso, 04 August 2006 - 02:02 AM.

[nightfox]

$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]

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.

[nightfox]

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 by nightfox, 04 August 2006 - 12:46 PM.

[vhortex]

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 by vhortex, 04 August 2006 - 05:49 PM.

[nightfox]

$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_user
where 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

[vhortex]

please try this one instead..

SELECT *
FROM users u
JOIN content c
WHERE `u`.`access_name` = \""  . $active_user. "\"
AND `u`.`id` = `c`.`id`

the changes are in the quotes..

\"" . $active_user. "\"

plus this typo

`c`.`id`

i hope this will work now

[nightfox]

grrr...

now it is giving me Parse error: parse error, unexpected T_VARIABLE for line 18... guess which line that is

as for the code, I'm assuming it is supposed to be like this:

$sql = "SELECT *
	   FROM users u
	   JOIN content c
	   WHERE `u`.`access_name` = \""  . $active_user. "\"
	   AND `u`.`id` = `c`.`id`"

I think that section just needs to be tweaked and then it should work... this script is really starting to get annoying and I'm tempted to put it all into one table...

UPDATE: I GOT IT TO WORK!!!

Well, I cheated... I took the semi-long-and-not-nearly-effective way to do it... here's what I did:

$sql = "SELECT * FROM users WHERE `access_name` = \""  .$active_user. "\"";
$row = mysql_fetch_array(mysql_query($sql));
$user_id = $row['id'];
$sql2 = "SELECT * FROM content WHERE `cid` = \""  .$user_id. "\"";
$row2 = mysql_fetch_array(mysql_query($sql2));

As you can see, since I called up one query which has the User ID number in it and then turned that number into a variable to link the two databases... now I can get on with my life. But still, I'd like to take the old code and get that to work.

[N]F

Edited by nightfox, 04 August 2006 - 11:55 PM.

[Chesso]

Also have you considered using $_REQUEST? Instead of $_POST or $_GET, I'm pretty sure it handles both and I have been using it for some time without fail

[Quatrux]

Also have you considered using $_REQUEST? Instead of $_POST or $_GET, I'm pretty sure it handles both and I have been using it for some time without fail

I personally avoid to use $_REQUEST; superglobal, but I know quite a lot of people who like to use it, so I guess it is alright, but it can make problems in your script, because I never trust user coming information into my script, so knowing through which method it came is quite alright, especially if you use both methods, but for some time now I really like to use QUERY_STRING with which you get the string index.php?string and even better PATH_INFO, with which you can get the string which comes from /index.php/string, but eventually I don't have anything against $_REQUEST; I just avoid it

[vhortex]

as for the code, I'm assuming it is supposed to be like this:

$sql = "SELECT *
	   FROM users u
	   JOIN content c
	   WHERE `u`.`access_name` = \""  . $active_user. "\"
	   AND `u`.`id` = `c`.`id`"

yeah, you are rigth.. i just posted the query..
i was so sleepy when i do that and lazy tooo...

$sql = "SELECT *
	   FROM users u
	   JOIN content c
	   WHERE `u`.`access_name` = \""  . $active_user. "\"
	   AND `u`.`id` = `c`.`id`";

there is a semicolon at the end.. and the parse error is that it is suppose to inside a quote string variable..

--------

They told me this when I started..
Good programmers -- modular / Object oriented approach of programming..
Good programmers -- reuses well crafted codes as libraries..
Good programmers -- have less work to do..
Good programmers -- turns into lazy people..

I guess i put much more focus on the lazt stuff. maybe if i was lazy enough i can be a good programmer..