Page 1 of 1

tough mysql query = php

Posted: Sun Jun 15, 2008 11:44 am
by Patricio88
Hello,
I got a really big problem which I can't seem to solve. However, I'm not really a genius in coding. So, I am trying to code kind of a Social Networking Site. OK, I have a Database called friends_real with two columns.

friends_real

own_name friend_name

patrick philip
philip george
george lennie
lennie patrick
kurt philip
patrick kurt
bob george

(just as an example right now)

This is kind of a who is a friend with whom thingy. Let's focuse on patrick (highlighted in blue) As seen patrick is friend with: philip, lennie and kurt.

Now I want to output on my site with whom MY friends philip, lennie and kurt are friends with. So, if the code works it should ouput:

philip is a friend with patrick
philip is a friend with george
philip is a friend with kurt
lennie is a friend with patrick
lennie is a friend with george
kurt is a friend with philip
kurt is a friend with patrick
(I hope i got it right)


I mean I kind of got the idea how to do it, but I am still failing. Below there is one of my tries, it is probably more complicated than it has to be and it still doesn't work and things are failing, but it is a try.


$username is the user who is logged in! (in our example patrick)

Code: Select all

 
$query = mysql_query ("SELECT * from friends_real where own_name = '$username' OR friend_name = '$username';");
 
echo "News-Feed:<p>";
 
 
while ($output_mysql = mysql_fetch_assoc ($query))
{
      if ($output_mysql['own_name'] == "$username")
      {
    
        $query_1 = mysql_query ("SELECT * from friends_real where friend_name = '$output_mysql[friend_name]';");
        while ($query_2 = mysql_fetch_assoc ($query_1))
        {
        echo $query_2['own_name'] . "&nbsp;is now a friend with&nbsp;" . $query_2['friend_name'] . "<p>";
        }
 
      }
 
    else
    {
 
 
        $query_7 = mysql_query ("SELECT * from friends_real where own_name = '$output_mysql[own_name]';");
        while ($query_8 = mysql_fetch_assoc ($query_7))
        {
        echo $query_8['own_name'] . "&nbsp;is now a friend with&nbsp;" . $query_8['friend_name'] . "<p>";
        }   
    }
 
}
 
I'd really appreciate if somebody can help me.
Thanks a lot!

Re: tough mysql query = php

Posted: Sun Jun 15, 2008 2:19 pm
by WebbieDave
Can you let us know how it is failing? Such as what errors you are getting, if any.

What I noticed off the bat is:

Code: Select all

"SELECT * from friends_real where friend_name = '$output_mysql[friend_name]';"
You can't stick an array in a string that way.

Try surrounding the array in braces:

Code: Select all

"SELECT * from friends_real where friend_name = '{$output_mysql['friend_name']}"
Or use concatenation:

Code: Select all

"SELECT * from friends_real where friend_name = '" . $output_mysql['friend_name']

Note: If the subject stays on PHP related issues rather than MySQL, we'll want to move over to the PHP - Code forum.

Re: tough mysql query = php

Posted: Sun Jun 15, 2008 2:34 pm
by Patricio88
Thank you for your advice. My code doesn't output any errors, but it simply doesn't work because it doesn't display the correct names how they should be displayed. For example "kurt is friend with lennie" would be displayed two times. The code is good, without errors but it doesn't do what I want it to do, it is a logical problem on my side.

I think we should start by scratch. So, I hope somebody could tell me how to do that ..
What I did is just a mess ...
Thanks so much!

Topic could be moved to PHP, sorry.

Re: tough mysql query = php

Posted: Sun Jun 15, 2008 3:11 pm
by WebbieDave
Since you're willing to start from scratch, may I suggest you start with a db schema along the lines of:

CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL
);

CREATE TABLE `friends` (
`user_id` int(10) unsigned NOT NULL,
`friend_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`friend_id`)
);

Let's say users contains three rows:
1, John
2, Paul
3, George

Let's say friends contains six rows:
1,2
1,3
2,1
2,3
3,1
3,2

To get a list of John's friends the SQL would be:

SELECT friends.friend_id, users.name
FROM friends
LEFT JOIN users ON users.id = friends.friend_id
WHERE friends.user_id = 1

OK, that's more code/SQL than I give anyone on these boards! I'm done for now but take this and run with it, study it, or discard it altogether! It's up to you! Keep programming, keep reading programming books and best of luck to you!

Re: tough mysql query = php

Posted: Sun Jun 15, 2008 3:33 pm
by Patricio88
Thanks a lot. I don't understand it on the first sight, but I'll study it tomorrow. I hope I can implant that in my site how I'd like to have it. Thank you so much!!!

Re: tough mysql query = php

Posted: Mon Jun 16, 2008 1:24 am
by Benjamin
Those should be friend id's from the auto_increment column in your members table. I'm hoping they are? If not I'd change it now.

Re: tough mysql query = php

Posted: Mon Jun 16, 2008 1:10 pm
by califdon
Patricio, you're trying to reinvent relational databases, something that was settled by E. F. Codd in 1970. Do yourself a huge favor and read up on how relational databases are structured. WebbieDave furnished you with an example, but I urge you to learn what a relational database is. I would suggest the following resources:
http://www.kirupa.com/developer/php/rel ... design.htm
http://cisnet.baruch.cuny.edu/holowczak ... ssall.html
http://www.tekstenuitleg.net/en/article ... tutorial/3
http://jegsworks.com/Lessons/lesson1-2/ ... tabase.htm
http://www.quackit.com/database/tutoria ... design.cfm