hi guys,

want to say thank you in advanced for any replies and help!

my problem is im trying to create a 'friend' relationship
which is based off a user bring a friend with another
therefore in my database ive just got the user entity.
my first thought was 'easy i'll just create a seperate entity that will sort the many to many relation out.
e.g id have a friend entity
with a key friend_id
and then user_id (from user entity, to set the user that is friends with --->)
user_id (this user)
this way i would have a list of all the people who are linked to each other and i can query this table to friends.
but therein lies the problem, sql wont allow me to use the same.ive been thinking for awhile and im not sure how i can solve this...
any tips?

thanks again

james

CREATE TABLE friends
( user1 INTEGER NOT NULL
, user2 INTEGER NOT NULL
, PRIMARY KEY ( user1, user2 )
, INDEX reversi ( user2, user1 )
, FOREIGN KEY ( user1 ) REFERENCES users ( id )
, FOREIGN KEY ( user2 ) REFERENCES users ( id )
);

hi thanks for you reply thats solved my problem, although i would like to ask why you've created the index reversi?
forgive me for being thick i don't quite understand how this will speed up a table search?

thanks again

the friends application is a bit tricky, because it's not at all certain whether the link is unidirectional or bidirectional

i have seen people do the friends app where user1 links to user2 (as a friend request) and then if user2 accepts, a second row is inserted where user2 links to user1, so that they are only actual friends if both rows exist

i have also seen people do this app where user1 links to user2 and there is only ever a single row for this pair of users, and the relationship is assumed to be the same in both directions (the way to store it is to simply put the lower userid first)

if you think of the many-to-many relationship, using instead the order-orderitem-item model, the orderitem table looks very similar -- it has two foreign keys, right?

so if you want to find all items on a particular order, the (orderid,itemid) index is used, but if you want to find all orders with a particular item, you need the (itemid,orderid) index, otherwise you get a table scan

so that is why i make (a,b) the PK, but also add the "reversi" index on (b,a)

how this relates to your specific friends app, i dunno, but i figured i'd give you a head start by declaring the additional index


helps?

ooooh i seee ingenius!
i understand what your saying and it helps very much thank you!

im stuck again!
do you know anything about mysql and easyphp or wamps?

ive installed easyphp 2.0.0
and im trying to create a webdatabase
ive uploaded my database into the myphpadmin thing and it seems to be working.
my problem is that i seem to be having trouble connecting to the database ive connected.
ive not changed any of the settings and im using this code to try connect ot the database and print out a short query of the data in photo
heres abit of my sql code



CREATE DATABASE snapshare;

USE snapshare;
CREATE TABLE photo (
photo_id int(10) NOT NULL auto_increment,
description varchar(500),
dateuploaded date,
format varchar(30),
price decimal(4,2),
user_id1 int(10),
photo blob,
PRIMARY KEY(photo_id),
FOREIGN KEY (user_id1) REFERENCES user(user_id)
) type=MyISAM;

INSERT INTO photo VALUES (1, 'photo1', '2003-12-12', 'jpeg', '01.01', '1', NULL);
INSERT INTO photo VALUES (4, 'photo4', '2003-12-12', 'jpeg', '01.04', '1', NULL);
INSERT INTO photo VALUES (7, 'photo7', '2003-12-12', 'jpeg', '01.07', '1', NULL);
INSERT INTO photo VALUES (2, 'photo2', '2004-12-29', 'jpeg', '02.00', '2', NULL);
INSERT INTO photo VALUES (5, 'photo5', '2004-12-29', 'jpeg', '02.05', '2', NULL);
INSERT INTO photo VALUES (8, 'photo8', '2004-12-29', 'jpeg', '02.08', '2', NULL);
INSERT INTO photo VALUES (3, 'photo3', '2004-10-10', 'jpeg', '03.00', '3', NULL);
INSERT INTO photo VALUES (6, 'photo6', '2004-10-10', 'jpeg', '03.06', '3', NULL);
INSERT INTO photo VALUES (9, 'photo9', '2004-10-10', 'jpeg', '03.09', '3', NULL);

and heres the php code im using to try connect to the database

<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>photos</title>
</head>
<body>
<pre>
<?php
// (1) Open the database connection
$connection = mysql_connect("localhost","root","");

// (2) Select the winestore database
mysql_select_db("snapshare", $connection);

// (3) Run the query on the winestore through the connection
$result = mysql_query ("SELECT * FROM
photo", $connection);

// (4) While there are still rows in the result set, fetch the current
// row into the array $row
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
// (5) Print out each element in $row, that is, print the values of
// the attributes
foreach ($row as $attribute)
print "{$attribute} ";

// Print a carriage return to neaten the output
print "\n";
}
?>
</pre>
</body>


but its not returning anything
please help!

james

Sorry but I can't see the problem...
The code you supplied returned:
Personally I would change two of the lines you supplied
But this is only to help you figure out what is wrong.

I think therefore that you have either setup your wamp wrong or your root account on MySQL is password protected.

I'm sorry if this sounds patronising but where have you saved your php file and what address are you trying to access it from?