[RESOLVED] trim strings issue

scrypte

I am trying to pull information from one table, but the your id can be in two different rows depending on who requested you as a friend, but if only one is in a table its screwing me big time

$sql = "SELECT * FROM friends WHERE user_1=$id && status=2";
$result = mysql_query($sql);
$total_friends = mysql_num_rows($result);
$str = '';
while ($row = mysql_fetch_array($result)) {
    $sql2 = "SELECT * FROM users WHERE userid = " . $row['user_2'];
    $query2 = mysql_query($sql2);
    $arr2 = mysql_fetch_array($query2);
    $str .= '<a href=?op=viewprofile&id='. $arr2['userid'] .'>'. $arr2['username'] . '</a>, ';
}

echo trim($str, ', ');



$sql3 = "SELECT * FROM friends WHERE user_2=$id && status=2";
$result3 = mysql_query($sql3);
$str2 = '';
while ($row = mysql_fetch_array($result3)) {
    $sql4 = "SELECT * FROM users WHERE userid = " . $row['user_1'];
    $query4 = mysql_query($sql4);
    $arr4 = mysql_fetch_array($query4);
    $str2 .= '<a href=?op=viewprofile&id='. $arr4['userid'] .'>'. $arr4['username'] . '</a>, ';
}

echo trim($str2, ', ');

if you have any ideas I would greatly appreciated it. Example can be found at http://syc0tests.com/index.php?op=viewprofile&id=1 where Bittybop and sh0x are two different users but joined together

Installer

What happens if you remove the first "echo trim()" statement?

Jack_McSlay

seriously, this code could use a serious revamp, doing several queries like this is not a good idea at all, it severely decrease your website's perfoemance. if an user has, like, 300 friends things will become sloppy.

$sql = "
	SELECT us.username, us.userid
	FROM friends fr, username us
	WHERE (fr.user_1=$id && status=2 && us.userid = fr.user_2) 
	OR (fr.user_2=$id && status=2 && us.userid = fr.user_1)
";
$result = mysql_query($sql);
$total_friends = mysql_num_rows($result);
$str = '';
while ($row = mysql_fetch_assoc($result)) {
   $str .= '<a href=?op=viewprofile&id='. $row['userid'] .'>'. $row['username'] . '</a>, ';
}

echo trim($str, ', ');

I havent tested this code but If there are no errors it will severely speed up your page.

either way, here's a fool-proof solution, but I higly recomment you use the one above

$sql = "SELECT * FROM friends WHERE user_1=$id && status=2";
$result = mysql_query($sql);
$total_friends = mysql_num_rows($result);
$str = '';
while ($row = mysql_fetch_array($result)) {
    $sql2 = "SELECT * FROM users WHERE userid = " . $row['user_2'];
    $query2 = mysql_query($sql2);
    $arr2 = mysql_fetch_array($query2);
    $str .= '<a href=?op=viewprofile&id='. $arr2['userid'] .'>'. $arr2['username'] . '</a>, ';
}

$sql3 = "SELECT * FROM friends WHERE user_2=$id && status=2";
$result3 = mysql_query($sql3);
$str2 = '';
while ($row = mysql_fetch_array($result3)) {
    $sql4 = "SELECT * FROM users WHERE userid = " . $row['user_1'];
    $query4 = mysql_query($sql4);
    $arr4 = mysql_fetch_array($query4);
    $str2 .= '<a href=?op=viewprofile&id='. $arr4['userid'] .'>'. $arr4['username'] . '</a>, ';
}

echo trim($str, ', ');

scrypte

that doesnt even match my table structure

Jack_McSlay

you don't know much SQL, do you? I've just checked my phpmyadmin for syntax, and if your code is correct, so should be the SQL. let me explain it:

    SELECT us.username, us.userid

that means I'm only selecting the username and userid from the user table because that's the only info I need

    FROM friends fr, username us

for this query I'm going to need data from the user and friends table, and i'm calling the friends table 'fr' for reference as well as calling the user table 'us'

    WHERE (fr.user_1=$id && status=2 && us.userid = fr.user_2) 
    OR (fr.user_2=$id && status=2 && us.userid = fr.user_1)

I'm selecting the user which id is the same as the user_2 when user_1 is the user you're selecting from, or the user which id is the same as the user_1 when user_2 is the user you're selecting from

scrypte

Ok there was an error in your coding, here is what worked for me. users is the table not username, thanks for your help

 $sql = "
    SELECT us.username, us.userid
    FROM friends fr, users us
    WHERE (fr.user_1=$id && status=2 && us.userid = fr.user_2)
    OR (fr.user_2=$id && status=2 && us.userid = fr.user_1)
";
$result = mysql_query($sql);
$str = '';
while ($row = mysql_fetch_array($result)) {
   $str .= '<a href=?op=viewprofile&id='. $row['userid'] .'>'. $row['username'] . '</a>, ';
}

echo trim($str, ', ');