Trying to insert array values into an SQL query for a friend's list application

[dxdolar]

I'm programming a friend's list feature using a linking table structured like this;

m_id  | f_id

1  |  2

1  |  3

1  |  4

 

I need to select f_id column from all the rows where mf_id (my id = me ) then set the f_id's to a variable, then insert them to an SQL query which gets all the names of my friends from the main user table. For you pros out there it's a pretty simple structure but I can't seem to get the array of f_id's into the SQL, I don't even know if my SQL syntax is right and it's killing me. here's a snippet of my PHP.

 

[pre]if (isset ($_POST['submitted'])) { //check if form is submitted

 

$errors = array (); //initialize error array

$q = "SELECT * FROM friends_junction WHERE myf_id='$_SESSION['user_id'];"; //select both columns from a friend's junction table

$r = mysqli_query ($dbc, $q);

if (mysqli_num_rows ($r) >= 1)) { //as long as there are records

 

$row = mysqli_fetch_array ($r, MYSQLI_ASSOC)); //create the array of all the f_user_id's taken from the junction table

$friend =

 

$q = "SELECT user_id, first_name, last_name FROM users WHERE user_id=('$friend')";

$r = mysqli_query ($dbc, $q);

if (mysqli_num_rows ($r) >= 1)) { // as long as there are results

while ($f = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {

echo '' . $f['first_name'] . ' ' . $f['last_name'] . ' is your friend!';

 

} else {

 

echo '<p class="error">The following error(s) occurred:';

foreach ($errors as $msg) { // Print each error.

echo " - $msg<br />\n";

 

} // end of while

} // end of as long as there are results

}// end of if user has any friends

} //end of if submitted[/pre]

 

 

Any help in getting it to work would be great, I've been working on this all day and I can't get it to work.  (I've only been php coding for about 2 weeks unfortunately)

 

Thanks much!

[unkwntech]

Sub queries are better for this.. somthing like:

$sql = "SELECT name FROM friends WHERE id=(SELECT f_id FROM users WHERE m_id='$id')";

What ever is returned from the second query(SELECT f_id...) will be used as id for the first.

ALWAYS ALWAYS ALWAYS let MySQL do the grunt of the work instead of doing it in code, this is what it is designed for.

For more info on subqueries see -> http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

This may not be perfect but it will get you started.

[dxdolar]

I tried the above method but I think it only works if I return a single value.

 

I'm trying to return multiple values so I can get the first and last name of everyone that's your "friend"

 

here's a look at the new code that I was trying to use.

if (isset ($_POST['submitted'])) { //check if form is submitted

$id = $_POST['mid'];

$q = "SELECT first_name, last_name FROM users WHERE user_id=(SELECT f_id FROM friends_junction WHERE m_id='$id');"; //subselect query
$r = mysqli_query ($dbc, $q); 
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) { //as long as there are records
	echo '' . $row['first_name'] . ' ' . $row['last_name'] . ' is your friend!';
	}//end of while	
} //end of if submitted


echo '<div>
<form action ="test_friends.php" method="post">
<input type="text" size="15" maxlength="20" name="mid" value="" />
<input type="submit" name="submit" value="SUBMIT" />
<input type="hidden" name="submitted" value="TRUE" /> 
</form>
</div>';

include ('includes/footer.php');
?>

 

my friend_junction table looks like this

m_id | f_id

3  |  4

3  |  5

3  |  6

 

I read the SQ 5.0 reference manual but get completely lost on this part

[pre] Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:

 

DELETE FROM t1

WHERE s11 > ANY

(SELECT COUNT(*) /* no hint */ FROM t2

  WHERE NOT EXISTS

  (SELECT * FROM t3

    WHERE ROW(5*t2.s1,77)=

    (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM

      (SELECT * FROM t5) AS t5)));

 

A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections. [/pre]

 

I think the answer is in there somewhere...but I can't wrap my head around it. Thanks a bunch!

[unkwntech]

It should work if more then 1 value is returned I think.

[samshel]

Try

$sql = "SELECT name FROM friends WHERE id IN (SELECT f_id FROM users WHERE m_id='$id')";

 

OR you can use joins

 

$sql = "SELECT name FROM friends, users WHERE friends.id = users.f_id and users.m_id='".$id."'";

 

hth