loop problem

[proctk]

below is code that I'm trying to use to get data from the listed tables and then pull the data and display some of ithe information in a table.

background

I have a table where people add people to there buddy list. buddies or registered users add information to their profile and when someone adds a user to  their buddy list then they can see certain pieces of information.  my biggest problem is looping through the buddy list and using each buddy id to search through the tables with their information attached.


The code below echoes the the value but repeats the same value many times.

thank you for any help

complete code


[code=php:0]
      <?php   
$get_buddies_id = mysql_query("SELECT * FROM buddylink WHERE owner_id ='$user_id'") or die ("Error: getting buddies user_id" .mysql_error());

while ($id_row = mysql_fetch_assoc($get_buddies_id)){
$buddy_id = $id_row['buddy_id']; 

  $buddy_calendar = mysql_query("
SELECT *
  FROM `users` AS u
INNER JOIN `calendar` AS c ON u.user_id = c.userid
INNER JOIN `children` AS C ON u.user_id = C.owner_id
INNER JOIN `parent`  AS p ON u.user_id = p.owner_id
INNER JOIN `sibling`  AS s ON u.user_id = s.owner_id
WHERE u.user_id = '{$buddy_id}'
ORDER by u.user_id;
") or die ("Error: getting buddies user_id:<br>\n" .mysql_error());

while ($row_buddy_info = mysql_fetch_array($buddy_calendar))
echo $row_buddy_info['first_name'];
}
}

?>
[/code]

[jsladek]

Maybe a SELECT DISTINCT first_name

Just a guess

-John

[proctk]

thanks for the reply. there will be many columns from which I need information  and I'm not sure what DISTINCT is, I'll have to do some seaching,

I'm 90% confident that the proplem lys with getting the buddy_id and how it looks throygh the query,

[proctk]

I just did some digging on what DISTINCT does. thank you for shating that, I can probably use it else where but will not help my problem.

is it possible to assign the results of my while statement to an array value that can be used out side the apprentices

[jsladek]

Sorry, I'm not the best at SQL... Yours makes my brain hurt. I don't think join is what you are looking for though ( unless you know that works) how about a UNION instead.

-John

[proctk]

This problem has been hanging me up for two days. I changed my codee to

[code=php:0]
      <?php   

  $buddy_calendar = mysql_query("SELECT * FROM buddylink b, users u, calendar c, children C, parent p, sibling s WHERE b.owner_id = '$user_id' AND u.user_id = b.buddy_id AND c.userid = b.buddy_id AND C.owner_id = b.buddy_id AND p.owner_id = b.buddy_id AND s.owner_id = b.buddy_id ORDER by u.user_id;") or die ("Error: getting buddies user_id:<br>\n" .mysql_error());

while($row_buddy_info = mysql_fetch_assoc($buddy_calendar)){

echo $row_buddy_info['first_name'];

}
?>
[/code]

nothing really changes it repeats the value over and over but less then in the first code that I posted

[jsladek]

That sql makes more sense to me only thing I'm not sure about is the feilds in the table. I'm guessing that users, children, parent & sibling may all have the same structure so I would not expect a JOIN on them.  I am not too sure about the calendar table but I was thinking that maybe some hybrid of a UNION and a JOIN. A UNION on the users, children. parent & sibling and then join that with the calendar table.

As I mentioned before, I'm not the best with SQL and I usually have to get a tool to help me build a complex SQL (phpMyAdmin or even Access). If I were stuck on something like this I would probably justs query each table one at a time and maybe a nested query if the calendar information is unique to each user/child/parent/sibling.

I know that none of that probably helps but it might get you thinking.

Regards,
John Sladek

[proctk]

Thank you for the help. I have tried doing a seperate query for each table, however I',m having a hard time gettng ech budd_id to loop through each table correctly

How i have structed this
[code=php:0]
$get_buddy_id =mysql_query(SELECT * FROM buddylinks WHERE owner_id = '$user_id');

while($row = mysql_fetch_assoc($get_buddy_id){

//Code to query each database goes hear and code to create table

}
[/code]
the problem is this creates multiple tables and makes a mess of the dispaly.  The issue is the while loop getting the buddy_ids.  I have not figured out how to fixed that. this will be day three toying with this for me

thank you for any help (what I',m trying to do seems easy, I'm probley not doing a good job explaining it

:)

[proctk]

ops

[phil.t]

Let me see if I understand correctly what it is you're trying to do. You have users that have profiles, and buddy lists.  The buddy lists are lists of other users.  And for a particular user, you want to retrieve and display information from the profiles of the users in their buddy list.  Is that correct?

A little more information about the structure of your database would probably be helpful.  My general advice would be to test the SQL independently (using phpMyAdmin or Access, as jsladek suggested... or even running the query directly at the mysql prompt if you can), and unless you're sure that the query returns the values you expect, make them simpler and start from there.

For each buddy on the buddy list, retrieve the information from the tables containing profile information separately (one SQL statement for each table -- calendar, children, parent, etc.)  See if that works for getting the information that you need.  From there if you really need to consolidate your SQL into one statement you can work from the simple to the more complex, and revert back if the changes didn't have the desired result.

Good luck.