Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Unanswered: using the same attribute twice in a table?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2008
    Posts
    5
    ooooh i seee ingenius!
    i understand what your saying and it helps very much thank you!

  6. #6
    Join Date
    Apr 2008
    Posts
    5
    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

  7. #7
    Join Date
    May 2008
    Posts
    5
    Sorry but I can't see the problem...
    The code you supplied returned:
    HTML Code:
    <!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>
    1 photo1 2003-12-12 jpeg 1.01 1  
    4 photo4 2003-12-12 jpeg 1.04 1  
    7 photo7 2003-12-12 jpeg 1.07 1  
    2 photo2 2004-12-29 jpeg 2.00 2  
    5 photo5 2004-12-29 jpeg 2.05 2  
    8 photo8 2004-12-29 jpeg 2.08 2  
    3 photo3 2004-10-10 jpeg 3.00 3  
    6 photo6 2004-10-10 jpeg 3.06 3  
    9 photo9 2004-10-10 jpeg 3.09 3  
    </pre>
    </body>
    Personally I would change two of the lines you supplied
    Code:
    // (1) Open the database connection
    $connection = @mysql_connect("localhost","root","") or die("Sorry could not connect to host, check your connection settings.");
    
    // (2) Select the winestore database
    @mysql_select_db("snapshare", $connection) or die("Sorry, the database you requested does not exist");
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •