...

View Full Version : Return results from two querys and compare



tspek
01-14-2010, 07:03 PM
...sort of

I have page that displays a member list of the entire site. The list is built by searching for all members in the "users" (MySQL)table. For each row returned, a (html)table row is generated and displayed.

The logged in user has the option of "following" any user displayed in this (html)table.

Everything so far works great.

The Problem is

I have another (MySQL)table that tracks who is following who. 3 rows (ID, user, following) where ID is an auto incrementing unique ID, user is the unique identifier from the users (MySQL)table and following is the unique identifier from the users (MySQL)table that the current user is following.

I want for the members list to display "unfollow" instead of "follow" for members that the user is already following. I'm not entirely sure how to go about doing this.

I know that I need to check the "following" table and fetch all of the current user's followed members, and then compare that to the the user's in the member list.

here is how the member list table is displayed


<table>
<tr>
<td><strong>Name</strong></td>
<td><strong>Last Race</strong></td>
<td><strong>XXXXX</strong></td>
<td><strong>License</strong></td>
<td><strong>Message</strong></td>
<td><strong>Follow</strong></td></tr>
<?php #returns results
for ($j = 0; $j < $rows ; ++$j)
{
$fname = mysql_result($result, $j, 'fname');
$lname = mysql_result($result, $j, 'lname');
$license = mysql_result($result, $j, 'license');

echo "<tr>
<td>$fname $lname</td>
<td>Last Race</td>
<td>Rank</td>
<td><a href=\"http://www.XXXXXX.XXX/results/index.php?compid=$license\">$license</a></td>
<td>Message</td>
";
if ($following == $license)
{
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
else {
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
}
?>
</table>

Rowsdower!
01-14-2010, 07:21 PM
I have another (MySQL)table that tracks who is following who. 3 rows (ID, user, following) where ID is an auto incrementing unique ID, user is the unique identifier from the users (MySQL)table and following is the unique identifier from the users (MySQL)table that the current user is following.

So does 'following' return an array or is it a CSV or what?

tspek
01-14-2010, 08:30 PM
So does 'following' return an array or is it a CSV or what?

This could be tricky with all the terminology being reused. I'm assuming you mean the variable $following?

The actual value of following is part of the issue. Each user is going to potentially have many different users that they are following, so my instinct is to approach that in the same manner that my member list is being populated.


$user is identified via a session cookie


$query2 = "SELECT follow FROM follow_list WHERE user='$user'";
$result2 = mysql_query($query2);
$rows2 = mysql_num_rows($result2);
for ($r = 0; $r < $rows2 ; ++$r)
{
$following = mysql_result($result2, $r, 'follow');

}



follow_list table looks like this:

[ID][user][following]


User and following are numbers that identifies the users in the users table.

Rowsdower!
01-14-2010, 11:13 PM
...User and following are numbers that identifies the users in the users table.

This is what I mean. When a user is following more than one other user the field 'following' would need to have more than one ID in it. So, these numbers held in the table row called "following," are they stored as comma separated values (CSV)? If not, how are they stored?

The answer to that will determine how exactly to go about creating an array of the users being followed.

Then, when you have your array of followed users, all you need to do is loop through your full list of users when printing. While in the loop if the user ID exists in the list of followed users you print a different link ("unfollow") than if the user ID does not exist in the list of followed users.

It would look something like this:

if(in_array($license,$following)){
//...print un-follow link here...
}
else {
//...print follow link here
}

Rebbu
01-15-2010, 12:25 AM
I assume that he will be inserting a row for each person he is following. As in, only the ID field is unique. For example, the following 3 records could be in the db:
ID User Following
1 1 2
2 1 3
3 2 3

Hence user 1 is following both users 2 and 3, and user 2 is following only user 3. User 3 is not following anyone.

If my assumption is correct, you could do the following:



$q1 = "SELECT * FROM users";
$userid = 1;//Use what ever method to grab userid (cookies or whatever) here.
while ($fetch = mysql_fetch_array($q1)){
//Echo your html table here
$followid = $fetch['id'];
$q2 = "SELECT * FROM followuser WHERE user = '$userid' && following = '$followid'";
if (mysql_num_rows($q2) == 0){
echo "<a href'#">Follow</a>";
}else{
echo "<a href'#">Unfollow</a>";
}
}



Also, you may not need the auto_inc ID field as the user and following fields together make a unique match.

tspek
01-15-2010, 12:55 AM
Exactly. Thank you i'll give that a shot.


I assume that he will be inserting a row for each person he is following. As in, only the ID field is unique. For example, the following 3 records could be in the db:
ID User Following
1 1 2
2 1 3
3 2 3

Hence user 1 is following both users 2 and 3, and user 2 is following only user 3. User 3 is not following anyone.

If my assumption is correct, you could do the following:



$q1 = "SELECT * FROM users";
$userid = 1;//Use what ever method to grab userid (cookies or whatever) here.
while ($fetch = mysql_fetch_array($q1)){
//Echo your html table here
$followid = $fetch['id'];
$q2 = "SELECT * FROM followuser WHERE user = '$userid' && following = '$followid'";
if (mysql_num_rows($q2) == 0){
echo "<a href'#">Follow</a>";
}else{
echo "<a href'#">Unfollow</a>";
}
}



Also, you may not need the auto_inc ID field as the user and following fields together make a unique match.

tspek
01-15-2010, 02:27 PM
I like what you're getting at here too. Thanks for the feedback.


This is what I mean. When a user is following more than one other user the field 'following' would need to have more than one ID in it. So, these numbers held in the table row called "following," are they stored as comma separated values (CSV)? If not, how are they stored?

The answer to that will determine how exactly to go about creating an array of the users being followed.

Then, when you have your array of followed users, all you need to do is loop through your full list of users when printing. While in the loop if the user ID exists in the list of followed users you print a different link ("unfollow") than if the user ID does not exist in the list of followed users.

It would look something like this:

if(in_array($license,$following)){
//...print un-follow link here...
}
else {
//...print follow link here
}

tspek
01-15-2010, 03:08 PM
I just had a tada moment...I'm not sure why this didn't occur to me earlier. I was over thinking this a bit. This works how I want it, is there any reason it's not an efficient way to do this?



<?php #returns results


for ($j = 0; $j < $rows ; ++$j)
{


$fname = mysql_result($result, $j, 'fname');
$lname = mysql_result($result, $j, 'lname');
$license = mysql_result($result, $j, 'license');

echo "<tr>
<td>$fname $lname</td>
<td>Last Race</td>
<td>Rank</td>
<td><a href=\"http://www.usacycling.org/results/index.php?compid=$license\">$license</a></td>
<td>Message</td>
";
#Finds Follow List
$query2 = "SELECT user, follow FROM follow_list WHERE user='$user' and follow='$license'";
$result2 = mysql_query($query2);
$rows2 = mysql_num_rows($result2);

if($rows2 == 1)
{
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
else
{
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
}

Rowsdower!
01-15-2010, 03:21 PM
It's probably not good to query your database that many times just to save on PHP code. You only need to find the data once so you should really only request it once.

tspek
01-15-2010, 04:30 PM
It's probably not good to query your database that many times just to save on PHP code. You only need to find the data once so you should really only request it once.

That occurred to me on my drive in to work.

tspek
01-15-2010, 10:04 PM
So the final result looks like this:



#sets array in case user not following anyone
$follow[0] = "0";

#stores results into array
for($i = 0; $i < $rows2; ++$i)
{
$followarr = mysql_result($result2, $i, 'follow');
$follow[$i] = "$followarr";
}

###############

#returns results
for ($j = 0; $j < $rows ; ++$j)
{
$fname = mysql_result($result, $j, 'fname');
$lname = mysql_result($result, $j, 'lname');
$license = mysql_result($result, $j, 'license');

echo "<tr>
<td>$fname $lname</td>
<td>Rank</td>
<td><a href=\"http://www.usacycling.org/results/index.php?compid=$license\">$license</a></td>
<td>Message</td>
";


if(in_array($license, $follow))
{
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/unfollow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
else
{
echo"
<td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>";
}
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum