tough mysql query = php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Patricio88
Forum Newbie
Posts: 6
Joined: Thu Jun 12, 2008 8:41 am

tough mysql query = php

Post 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!
Last edited by Patricio88 on Sun Jun 15, 2008 2:38 pm, edited 1 time in total.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: tough mysql query = php

Post 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.
Patricio88
Forum Newbie
Posts: 6
Joined: Thu Jun 12, 2008 8:41 am

Re: tough mysql query = php

Post 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.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: tough mysql query = php

Post 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!
Patricio88
Forum Newbie
Posts: 6
Joined: Thu Jun 12, 2008 8:41 am

Re: tough mysql query = php

Post 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!!!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: tough mysql query = php

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: tough mysql query = php

Post 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
Post Reply