[SOLVED] fetching and wrapping problem

[catri3l]

Hello everyone. Im having a little problem trying to figure something out.

 

i'm using joovili (social network software) and im trying to create a friends feed for my site.

 

There is a table called Joovili_buddies, where it contains all the friends a user has. im trying to wrap the friends a user has to use them on the feed mod i came up with. The feed mod will post the user name and the action they did. im trying to fetch the friends to show the latest actions of a user's friend. i came up with this code.

 

<?php

$fetch_buddies = sql_query("SELECT * FROM joovili_buddies WHERE buddy_username = '".$_COOKIE['session_username']."'");

 

while ($buddies = mysql_fetch_array($fetch_buddies)){

 

$fetch_feed = sql_query("SELECT * FROM joovili_buddy_feed WHERE feed_username = '".$buddies['buddy_buddy']."' ORDER BY id DESC LIMIT 15");

 

while ($feed = mysql_fetch_array($fetch_feed)){

 

 

?>

 

This code works fine. however, it is fetching and ordering the feed by buddies and not by feed id. I know the buddy_buddy is affecting the code to order it by id. I want the feed to be displayed the newest on top and on a cascade mod.

 

Any help please?

[btherl]

Have you got the table definitions there?  It's possible this can all be done in a single mysql statement.

[catri3l]

Have you got the table definitions there?  It's possible this can all be done in a single mysql statement.

 

hey i hope this is what u need!

 

 

CREATE TABLE `joovili_buddies` (

  `buddy_id` int(11) NOT NULL auto_increment,

  `buddy_username` varchar(100) NOT NULL default '',

  `buddy_buddy` varchar(100) NOT NULL default '',

  `buddy_status` int(11) NOT NULL default '0',

  PRIMARY KEY  (`buddy_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

 

friend feed:

 

CREATE TABLE `joovili_buddy_feed` (

  `id` int(11) NOT NULL auto_increment,

  `feed_username` varchar(100) NOT NULL,

  `what_done` varchar(200) NOT NULL,

  `feed_date` varchar(200) NOT NULL default '0',

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

 

 

 

 

[catri3l]

By the way. thanks alot!

[btherl]

Ok try this query:

 

SELECT * FROM joovili_buddies jb
JOIN joovili_buddy_feed jbf ON (jb.buddy_buddy = jbf.feed_username)
WHERE jb.buddy_username = {$_COOKIE['session_username']}
ORDER BY jbf.id

 

If there's overlapping column names in those two tables you might need to explicitly rename them (eg, if both tables have an "id" column).

 

The basic idea is to match up the two tables using jb.buddy_buddy and jbf.feed_username.  Then just order the results and take out whatever data you want.

 

Btw, is the logged in username stored directly in a cookie?  Cookie data can be modified by the user.  If you can, use sessions.

[catri3l]

Hey there. i tried this code. but it doesn't seem to work. Am i doing something wrong?

 

<?php

 

 

$fetch_feed = sql_query("SELECT * FROM joovili_buddies jb

JOIN joovili_buddy_feed jbf ON (jb.buddy_buddy = jbf.feed_username)

WHERE jb.buddy_username = {$_COOKIE['session_username']}

ORDER BY jbf.id DESC LIMIT 15");

 

while ($feed = mysql_fetch_array($fetch_feed)){

 

 

?>  

 

[btherl]

Change it to this:

 

$query = "SELECT * FROM joovili_buddies jb
JOIN joovili_buddy_feed jbf ON (jb.buddy_buddy = jbf.feed_username)
WHERE jb.buddy_username = '{$_COOKIE['session_username']}'
ORDER BY jbf.id DESC LIMIT 15";
$fetch_feed = sql_query($query) or die("Error in $query: " . mysql_error());

 

That will tell you if there's any error in the query.  If sql_query() already does error checking, ignore that

 

Anyway, I have added missing quotes around the username.  That will probably have caused the query to fail.

[catri3l]

Hey, im still getting a problem. here is the whole file just incase the file is affecting something.

sorry for being so annoying, but its driving me crazy!

 

Thanks for your help.

 

<div align="center">

<table width="96%" border="0" cellpadding="4" cellspacing="1" class="table_1_css">

<tr>

<td colspan="2" class="text_5_css" align="center">Buddies Feed</td>

</tr>

<tr>

<td>

<?php

$fetch_buddies = sql_query("SELECT * FROM joovili_buddies WHERE buddy_username = '".$_COOKIE['session_username']."'");

 

while ($buddies = mysql_fetch_array($fetch_buddies)){

 

$fetch_feed = sql_query("SELECT * FROM joovili_buddy_feed WHERE feed_username = '".$buddies['buddy_buddy']."' ORDER BY feed_date LIMIT 10");

 

while ($feed = mysql_fetch_array($fetch_feed)){

 

?>

<table width="100%" border="0" cellpadding="4" cellspacing="1" class="table_1_css">

<tr>

<td width="49">

<td class="text_4_css"><a href="<?php echo $feed['feed_username'];?>" class="text_4_css"><?php echo $feed['feed_username'];?></a></td>

</tr>

<tr>

<td class="text_4_css"><?php echo $feed['what_done'];?></td>

</tr>

<tr>

<td class="text_4_css"><?php echo $feed['feed_date'];?></td>

</tr>

</table>

<?php }}?>

</td>

</tr>

</table>

</div>

<br>

 

[catri3l]

Should there be a "while" in the code?

[catri3l]

I GOT ITTTTT!!!!!! YESSSSS THANK YOU SO MUCH FOR YOUR HELP!!

 

YOU ARE THE BEST!