How can this be done?

[Mancent]

<?php
include "connect.php";
$username = $_GET['username'];
$friend = $_POST['friend'];
$sql = mysql_query("SELECT * FROM user_comments WHERE username = '$username'");

$sql2 = mysql_query("SELECT * FROM user_friends_list WHERE username = '$username'");
$user_xml = "<?xml version=\"1.0\"?>\n";
$user_xml .= "<myroots>\n";
$user_xml .= "<profileinfo>\n";

//Check the friendslist table where all users frinds are.
  while ($row3 = mysql_fetch_array($sql2, MYSQL_NUM))
  {
      
     
    
     $user_xml .= "<friend>" . $row3['2'] . "</friend>\n";
     $user_xml .= "<status>" . $row3['3'] . "</status>\n";
     //CHECK IF THE USER HAS ANY FRIENDS 1= IS FRIEND 0= IS NOT FRIEND YET
    if(  $row3['3'] == "1")
    {
      //CHECK ALL THE COMMENTS WHERE FRIEND HAS POSTED
       while ($row = mysql_fetch_array($sql, MYSQL_NUM))
       {
       //PROBLEM I SEE IS THIS $user_xml .= "<username>" .  $row['1'] . "</username>\n";
       // .  $row['1'] . = THE DEFUALT USERNAME BUT IT NEEDS TO CHANGE TO ALL FRIENDS USERNAMES
       $user_xml .= "<username>" .  $row['1'] . "</username>\n";
        $user_xml .= "<comments>" . $row['3'] . "</comments>\n";
       }
     
    }
  }
$user_xml .= "</profileinfo>\n";
$user_xml .= "</myroots>\n";
echo $user_xml;  
?> 

 

I trying to be able toread all friends and all comments from friends

[trq]

You'll want to look into using an SQL JOIN. There is a tutorial on the main site that covers there usage.

[Mancent]

Ya im not getting any where with this. this is some hard crap.

 

heres what I been doing trying to get it to work right but i still con not pull the correct user from the comment table its just looping the one thing i sade for all friends.

 

 

<?phpinclude "connect.php";$username = $_GET['username'];$friend = $_GET['friend'];$user_friends_list = mysql_query("SELECT * FROM user_friends_list WHERE username = '$username'");$user_comments = mysql_query("SELECT * FROM user_comments WHERE username = '$username'"); //$user_comments= mysql_query("SELECT * FROM user_comments JOIN ( SELECT * FROM user_friends_list WHERE username = '$username')");$user_xml = "<?xml version=\"1.0\"?>\n"; $user_xml .= "<myroots>\n";$user_xml .= "<profileinfo>\n";  while ($user_friends_list_row = mysql_fetch_assoc($user_friends_list))   { //$user_xml .= "<friend>" . $user_friends_list_row['friend'] . "</friend>\n";     // $user_xml .= "<status>" . $user_friends_list_row['status'] . "</status>\n"; if(  $user_friends_list_row['status'] == "1"){   while ($user_comments_row = mysql_fetch_assoc($user_comments))        {    	    while ($user_friends_list_newrow = mysql_fetch_array($user_friends_list, MYSQL_NUM))         //for($x = 0 ; $x < mysql_num_rows($user_friends_list) ; $x++)        {//THIS IS WORKING I AM GETTING ALL MY CORRECT FRIENDS        $user_xml .= "<username>" . $user_friends_list_newrow['2'] . "</username>\n";//THIS IS NOT WORKING I AM NOT GETTING MY FRIENDS COMMENTS I AM GETTING MY COMMNET //AND IT IS LOPPING LIKE ALL MY FRIENDS SAID IT.        $user_xml .= "<comments>" . $user_comments_row['comment'] . "</comments>\n";        }	}	  	     }   }$user_xml .= "</profileinfo>\n";$user_xml .= "</myroots>\n"; echo $user_xml;  ?>

 

 

HERE IS A EXAMPLE TYOU CAN SEE

http://wiistream.net/flash/getfriends.php?username=Mancent

[coupe-r]

How are those 2 tables related?  Meaning, what column do they share (Some sort of ID).

[jcbones]

Post your table structures, and we can get it sorted.

[Mancent]

Ya i dont think i have ever learned how to make them related.

This is the user comments table.

 

SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `user_comments`-- ----------------------------DROP TABLE IF EXISTS `user_comments`;CREATE TABLE `user_comments` (  `id` int(255) NOT NULL auto_increment,  `username` varchar(255) NOT NULL,  `password` varchar(255) NOT NULL,  `comment` longtext NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user_comments-- ----------------------------INSERT INTO `user_comments` VALUES ('1', 'Aucti9', 'Openowok', 'Hello World');INSERT INTO `user_comments` VALUES ('2', 'Mancent', 'Openowok', 'THIS IS NOT WORKING ITS LOOPING WHAT I SAID LIKE ALL MY FRIENDS SAID THE SAME THING WTF');INSERT INTO `user_comments` VALUES ('3', 'Martinezlucas11', 'l420420m', 'hi');INSERT INTO `user_comments` VALUES ('4', 'Aucti9', 'Openowok', 'Nothing so stop bugging me fag');INSERT INTO `user_comments` VALUES ('5', 'Aucti9', 'Openowok', 'Hello Can some one help me make this work correctly');INSERT INTO `user_comments` VALUES ('6', 'Aucti9', 'Openowok', 'Whats on my mind? Comment test');INSERT INTO `user_comments` VALUES ('7', 'Aucti9', 'Openowok', 'Next step todo user friends list then update code to show all friends comments if friend has accepted request from user to be a friend. Testing longtext mysql injection. How much can we write at one time? How knows the truth about anything? Well i for one have no clue what the hell im doing. Im just trying to make some thing for the hell of it.');INSERT INTO `user_comments` VALUES ('8', 'Aucti9', 'Openowok', 'Testing loop');INSERT INTO `user_comments` VALUES ('9', 'Aucti9', 'Openowok', 'Loop works cool\r');INSERT INTO `user_comments` VALUES ('10', 'Aucti9', 'Openowok', 'I just wish the image would load into the dam movie clip stupid thing I wish I knew the answer already so i can move on,');INSERT INTO `user_comments` VALUES ('11', 'Aucti9', 'Openowok', 'jyjsht nnu6w  ');INSERT INTO `user_comments` VALUES ('12', 'Martinezlucas11', 'l420420m', 'tesdtdsfhsdjfhsdfsdlkhsdklsdkljhfjksdhfjksdhfkjdsghasf');INSERT INTO `user_comments` VALUES ('13', 'Martinezlucas11', 'l420420m', 'Welceom graphicsbe');INSERT INTO `user_comments` VALUES ('14', 'Aucti9', 'Openowok123456', 'Hello Will this work?');INSERT INTO `user_comments` VALUES ('15', 'Aucti9', 'Openowok123456', 'Nothing much whats on your mind');INSERT INTO `user_comments` VALUES ('16', 'Aucti9', 'Openowok', 'Still no way to get this image to load correctly WTF');INSERT INTO `user_comments` VALUES ('17', 'Aucti9', 'Openowok', 'Hi world can i get some help please? can some one sove the problem with the image loading correctly\r');INSERT INTO `user_comments` VALUES ('18', 'Aucti9', 'Openowok', 'Well just posted this on facebook to get feed back from other users.\r');INSERT INTO `user_comments` VALUES ('19', 'Aucti9', 'Openowok', 'hi kyle');INSERT INTO `user_comments` VALUES ('20', 'Aucti9', 'Openowok', 'Im so happy becasue It works. The picture stays inside the frame and thats what it should do. Im glade becasue I can now move on to the next step. Friends list.');INSERT INTO `user_comments` VALUES ('21', 'Aucti9', 'Openowok', 'edhkrfsofh\r');INSERT INTO `user_comments` VALUES ('22', 'Aucti9', 'Openowok', 'nothing');INSERT INTO `user_comments` VALUES ('23', 'Martinezlucas11', 'l420420m', 'Welcome martinezlucas11');

 

 

This is the friendslist table

 

SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `user_friends_list`-- ----------------------------DROP TABLE IF EXISTS `user_friends_list`;CREATE TABLE `user_friends_list` (  `id` int(255) NOT NULL auto_increment,  `username` varchar(255) NOT NULL,  `friend` varchar(255) NOT NULL,  `status` int(255) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user_friends_list-- ----------------------------INSERT INTO `user_friends_list` VALUES ('1', 'Mancent', 'Mancent', '1');INSERT INTO `user_friends_list` VALUES ('2', 'martinezlucas11', 'martinezlucas11', '1');INSERT INTO `user_friends_list` VALUES ('4', 'Aucti9', 'Mancent', '1');INSERT INTO `user_friends_list` VALUES ('3', 'Aucti9', 'Aucti9', '1');INSERT INTO `user_friends_list` VALUES ('5', 'Mancent', 'Aucti9', '1');INSERT INTO `user_friends_list` VALUES ('6', 'Mancent', 'martinezlucas11', '1');INSERT INTO `user_friends_list` VALUES ('7', 'martinezlucas11', 'Mancent', '1');INSERT INTO `user_friends_list` VALUES ('8', 'Mancent', 'Mancent', '1');

 

[Mancent]

How are those 2 tables related?  Meaning, what column do they share (Some sort of ID).

 

See i thought the column would be username

should i give them both a user_id field that can be the related column

[jcbones]

Try this as a TEST.

<?php
include "connect.php";
$username = mysql_real_escape_string($_GET['username']);
//If friend is set, then query for it, otherwise return all of the friends comments.
$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;
$sql = "SELECT a.username,a.comment,b.friend FROM user_comments as a,`user_friends_list` as b WHERE (a.username = b.friend AND b.status = '1') AND (b.username = '$username' $friend) ORDER BY a.id DESC";
$result = mysql_query($sql);

$user_xml = "<?xml version=\"1.0\"?>\n"; 
$user_xml .= "<myroots>\n";
$user_xml .= "<profileinfo>\n";

if(mysql_num_rows($result) > 0) {
  while ($row = mysql_fetch_assoc($result)) {


        $user_xml .= "<username>" . $row['username'] . "</username>\n";


        $user_xml .= "<comments>" . $row['comment'] . "</comments>\n";
}
}

$user_xml .= "</profileinfo>\n";
$user_xml .= "</myroots>\n"; 
echo $user_xml;  
?>

[Mancent]

Dude that is so cool you did it. that sucks too becasue i been trying for days. Im so glad i found this website.

I hate to ask but I do have one last problem with this. see theres is sopst to be a avatar in this as well im going to study your code and see if i can do it but i have to pull the avatar from the account table .

 

becasue see this is the xml file that i use for my flash site

 

http://wiistream.net/flash/getprofileusercomments.php?username=Mancent

 

you can see there is a avatar link in there but this is the new working php file the one you just made

 

http://wiistream.net/flash/getfriends.php?username=Mancent

 

 

So i have to add the avatar and boom its done

you help me make a twetter effect with avatar like facebook inside my flash site.

here is a test of my flash project to see what i mean

http://wiistream.net/WiiStream.exe

 

Username and password required so make a account to test

[Mancent]

here is the table for accounts i need to pull the avatar back aswell can you help?

 

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `accounts`
-- ----------------------------
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',
  `username` varchar(255) collate utf8_unicode_ci NOT NULL,
  `firstname` varchar(32) collate utf8_unicode_ci NOT NULL COMMENT 'Login username',
  `lastname` varchar(32) collate utf8_unicode_ci NOT NULL,
  `password` varchar(32) collate utf8_unicode_ci NOT NULL COMMENT 'Login password',
  `youremail` varchar(64) collate utf8_unicode_ci NOT NULL default '' COMMENT 'Contact e-mail address',
  `accesslevel` varchar(64) collate utf8_unicode_ci NOT NULL,
  `avatar` varchar(255) collate utf8_unicode_ci NOT NULL default 'http://www.pickworld.com/images/questionmark.jpg',
  `website` varchar(255) collate utf8_unicode_ci NOT NULL,
  `age` int(2) NOT NULL,
  `male` int(1) NOT NULL,
  `female` int(1) NOT NULL,
  `birthday` date NOT NULL,
  `lookingfor` varchar(32) collate utf8_unicode_ci NOT NULL,
  `city` varchar(32) collate utf8_unicode_ci NOT NULL,
  `hometown` varchar(64) collate utf8_unicode_ci NOT NULL,
  `politicalviews` varchar(255) collate utf8_unicode_ci NOT NULL,
  `bio` longtext collate utf8_unicode_ci NOT NULL,
  `mobilephone` text collate utf8_unicode_ci NOT NULL,
  `otherphone` text collate utf8_unicode_ci NOT NULL,
  `address` varchar(255) collate utf8_unicode_ci NOT NULL,
  `background` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `a` (`youremail`),
  UNIQUE KEY `b` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Account Information';

 

<?php
include "connect.php";
$username = mysql_real_escape_string($_GET['username']);
//If friend is set, then query for it, otherwise return all of the friends comments.
$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;$sql = "SELECT a.username,a.comment,b.friend FROM user_comments as a,`user_friends_list` as b WHERE (a.username = b.friend AND b.status = '1') AND (b.username = '$username' $friend) ORDER BY a.id DESC";
$result = mysql_query($sql);
$user_xml = "<?xml version=\"1.0\"?>\n"; 
$user_xml .= "<myroots>\n";
$user_xml .= "<profileinfo>\n"; 
if(mysql_num_rows($result) > 0) 
{  
while ($row = mysql_fetch_assoc($result)) 
{        
$user_xml .= "<username>" . $row['username'] . "</username>\n"; 
$user_xml .= "<myavatar>" . $row['avatar'] . "</myavatar>\n";       
$user_xml .= "<comments>" . $row['comment'] . "</comments>\n";	
}
}
$user_xml .= "</profileinfo>\n";
$user_xml .= "</myroots>\n"; 
echo $user_xml; 

?>

[Mancent]

Its so close to working i dont know how to do it i got the avatars to get pulled back but they are all wrong wrong avatar for this user or that user. can any one help?

 

<?php
include "connect.php";
$username = mysql_real_escape_string($_GET['username']);
//If friend is set, then query for it, otherwise return all of the friends comments.
$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;$sql = "SELECT a.username,a.comment,b.friend,c.avatar FROM user_comments as a,`user_friends_list` as b,`accounts` as c WHERE (a.username = b.friend AND b.status = '1') AND (b.username = '$username' $friend) ORDER BY a.id DESC";
$result = mysql_query($sql);
$user_xml = "<?xml version=\"1.0\"?>\n"; 
if(mysql_num_rows($result) > 0) 
{  
while ($row = mysql_fetch_assoc($result)) 
{   
$user_xml .= "<comment>\n";
$user_xml .= "<image>" . $row['avatar'] . "</image>\n";
$user_xml .= "<name>" . $row['username'] . "</name>\n";
$user_xml .=	"<text>\n";
$user_xml .=	"<![CDATA[ ". $row['comment'] ."]]>\n"; 
$user_xml .=	 "</text>\n";
$user_xml .= "</comment>\n";     

}
}
echo $user_xml; 

?>

[jcbones]

This would work better if you used ID's to make the table connections.  Since you are using Username as the connection, you need to do two things.

1. Make sure all of the Collations are the same.

2. Make sure all  of the spellings are the same (including case, Even though the collation may be case insensitive, it still will not work if the case is different). <<<Dunno why, had me stumped for a few minutes.

 

$sql = "SELECT a.username,a.comment,b.id,c.avatar 
FROM (user_comments as a JOIN accounts as c USING(username))
JOIN `user_friends_list` as b ON (b.friend = a.username)
WHERE (b.username = '$username' $friend AND b.status = 1) ORDER BY a.id DESC";

 

Edit:  If you want to group the messages by author, change the ORDER BY clause to "b.id".

[Mancent]

Ok this just is not working out for me. its so close but yet not perfect. The problem is the avatars the script is pulling random avatars that do not go with the user. here you can see from here

http://wiistream.net/flash/getprofileusercomments.php?username=Mancent

and its lopping the same messages more then it should.

 

if the code is this way it works perfect other then the avatars dont work at all.

 

<?phpinclude "connect.php";$username = mysql_real_escape_string($_GET['username']);//If friend is set, then query for it, otherwise return all of the friends comments.$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;$sql = "SELECT b.username,b.comment,c.friend FROM user_comments as b,`user_friends_list` as c  WHERE (b.username = c.friend AND c.status = '1') AND (c.username = '$username' $friend) ORDER BY b.id DESC";$result = mysql_query($sql);$user_xml = "<?xml version=\"1.0\"?>\n"; $user_xml .= "<root>\n";$user_xml .= "<get>\n";if(mysql_num_rows($result) > 0){ while ($row = mysql_fetch_assoc($result)) {  $user_xml .= "<comment>\n";//$user_xml .= "<image>" . $row['avatar'] . "</image>\n";$user_xml .= "<name>" . $row['username'] . "</name>\n";$user_xml .=	"<text>\n";$user_xml .=	"<![CDATA[ ". $row['comment'] ."]]>\n"; $user_xml .=	 "</text>\n";$user_xml .= "</comment>\n";     }}//}$user_xml .= "</get>\n"; $user_xml .= "</root>\n"; echo $user_xml; ?>

 

there is only 2 names on there each one a test account. and as you see its pulling 3 different avatars the default avatar and the 2 set avatars and they get place randomly.

 

 

<?phpinclude "connect.php";$username = mysql_real_escape_string($_GET['username']);//If friend is set, then query for it, otherwise return all of the friends comments.$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;$sql = "SELECT a.avatar,b.username,b.comment,c.friend FROM accounts as a, user_comments as b,`user_friends_list` as c  WHERE (b.username = c.friend AND c.status = '1') AND (c.username = '$username' $friend) ORDER BY b.id DESC";$result = mysql_query($sql);$user_xml = "<?xml version=\"1.0\"?>\n"; $user_xml .= "<root>\n";$user_xml .= "<get>\n";if(mysql_num_rows($result) > 0){ while ($row = mysql_fetch_assoc($result)) {  $user_xml .= "<comment>\n";$user_xml .= "<image>" . $row['avatar'] . "</image>\n";$user_xml .= "<name>" . $row['username'] . "</name>\n";$user_xml .=	"<text>\n";$user_xml .=	"<![CDATA[ ". $row['comment'] ."]]>\n"; $user_xml .=	 "</text>\n";$user_xml .= "</comment>\n";     }}//}$user_xml .= "</get>\n"; $user_xml .= "</root>\n"; echo $user_xml; ?>

 

[jcbones]

Did you try my query posted above?  REPLACE the SQL variable in your last script you posted with this one.

$sql = "SELECT a.username,a.comment,b.id,c.avatar 
FROM (user_comments as a JOIN accounts as c USING(username))
JOIN `user_friends_list` as b ON (b.friend = a.username)
WHERE (b.username = '$username' $friend AND b.status = 1) ORDER BY a.id DESC";

 

It has been tested (with the data you provided in your dump), and it does work.  It returns the right avatar, for the right username on the right comment.

 

[Mancent]

 

yes when i use that code i get this error.

 

you can see the error here

http://wiistream.net/flash/getprofileusercomments.php?username=Mancent

 

 

The XML page cannot be displayed 
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. 


--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'http://wiistream.net/flash/getprofileu...

<b>Warning</b>:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in <b>/home/cont...

 

 

Idont know why it has a problem with USING(username)

becasue if i take that out it works the same way as wehat we got. with random avatars

 

<?php
include "connect.php";
$username = mysql_real_escape_string($_GET['username']);
//If friend is set, then query for it, otherwise return all of the friends comments.
$friend = (isset($_GET['friend'])) ? ' AND b.friend = \'' . mysql_real_escape_string($_GET['friend']) . '\' ' : NULL;

$sql = "SELECT a.username,a.comment,b.id,c.avatar FROM (user_comments as a JOIN accounts as c USING(username))JOIN `user_friends_list` as b ON (b.friend = a.username)WHERE (b.username = '$username' $friend AND b.status = 1) ORDER BY a.id DESC";

$result = mysql_query($sql)
;$user_xml = "<?xml version=\"1.0\"?>\n"; 
$user_xml .= "<myroots>\n";
$user_xml .= "<profileinfo>\n"; 
if(mysql_num_rows($result) > 0) 
{  
while ($row = mysql_fetch_assoc($result)) 
{        
$user_xml .= "<comment>\n";
$user_xml .= "<image>" . $row['avatar'] . "</image>\n";

$user_xml .= "<name>" . $row['username'] . "</name>\n";
$user_xml .=	"<text>\n";
$user_xml .=	"<![CDATA[ ". $row['comment'] ."]]>\n"; 
$user_xml .=	 "</text>\n";
$user_xml .= "</comment>\n";  
}
} 
$user_xml .= "</profileinfo>\n"; 
$user_xml .= "</myroots>\n";  
echo $user_xml;  
?>

 

do you see a error in here?

[Mancent]

see this is what you are helping me create.

http://wiistream.net/user_comment_test.swf

 

but right now itsjust not working right

[Mancent]

I hope today is the day where this is solved.

[jcbones]

There is an answer to your problem.

1. Make sure all of the Collations are the same.

2. Make sure all  of the spellings are the same (including case, Even though the collation may be case insensitive, it still will not work if the case is different). <<<Dunno why, had me stumped for a few minutes.

 

You are using different collations for your account table.  Even after setting these collations to the same as the others, I had to make sure the names were the same case, even though the table is case-insensitive.  You cannot compare values between two (username) columns, if the collation on them is different.  Fix the table, and solve the problem.

[Mancent]

lol I didnt even know what that was untill now.. you should be a teacher lol