Need help to use LIMIT and Counting the rows

[neo2sxn]

<?php
$uname=$_GET['mid']; //getting username
$query = mysql_query("SELECT friends FROM users WHERE user_name='$uname'");
while($row = mysql_fetch_array($query)) {
if($row["friends"]=="")
{echo "<div style='margin:17px 0px 15px 35px;color:#ce0100;'>This user do not have any friends yet.</div>";}
$friends = unserialize($row["friends"]); //unserialize the records
if(isset($friends[0])) {
foreach($friends as $friend) {
$_query = mysql_query("SELECT * FROM users WHERE id = '" . $friend . "'");
$_row = mysql_fetch_array($_query);
$frname=$_row["user_name"];
$frpic=$_row["upic"];
?>
<a href="user.php?mid=<?php echo $frname;?>"><img src="img/<?php echo $frpic;?>" class="imag"></a>&nbsp;&nbsp;&nbsp;&nbsp;
<?php
}
}
}
?>


I have two problems .

1) I am not able to use mysql_num_rows from second query , i mean when this coding runs,it shows three records (three picture along whith hyperlink).But i also want to print it in number formate like - "3" .
2) I am not able to sort or limit the records because in "user" table the row "friends" have serialize records like "{i:0;s:1:"2";i:1;s:1:"4";i:2;s:1:"3";}", as you can see in coding where i am unserialize them. I tried to put ORDER BY and LIMIT ,but those not works...

Please guys help me out to sort this problem..

Thanks in advance..

[CoursesWeb]

Hi,
Try count and use the number of elements in if(isset$friends.

if(isset($friends[0])) {
 $nr = count($friends);
 foreach($friends as $friend) {
  // ... here add /use $nr
  $nr--;
 }
}

[rocknbil]

You won't be able to use order/limit because this is generally a bad idea when storing multiple reference records - the friends of this friend should be somehow marked in a separate table. Then you could do all sorts of stuff more efficiently.

user
id | fname | lname | email | etc.

user_friends
id |user_id| friend_id

select user_friends.friend_id, user.fname, user.lname where user_friends.friend_id=user.id and user_id=$your_target_user order by user.lname, user.fname

... on which you can output results and use mysql_num_rows.

As is, aside from the counting, you can use one of the many sort functions [php.net] on your friends array but it's not likely to give you the results you want as it appears all that's there are numeric id's, which will have nothing to do with sorting by name.

The only way to do it that comes to mind is to look up the names of the friends in that array, store it in yet another associative array, then execute one of the sort functions on that array to order by name (or whatever.) Something like

$tmp = array();
foreach($friends as $friend) {
// Don't use * unless you REALLY need everything, and
// numeric value selects do NOT need quotes
$_query = mysql_query("SELECT fname,lname FROM users WHERE id=$friend") or die (cannot get friend data");
$_row = mysql_fetch_array($_query);
$tmp[$friend] = $_row['lname'] . ', ' . $_row['fname'];
}
// now sort by value
$tmp = asort($tmp);
foreach ($tmp as $id => $name) { echo "<p>$name</p>"; }
}
?>

This is a really long way around the fence workaround, I'd just go back and structure the database correctly and fix it. The advantages will emerge in other areas as well - smaller databases (numeric as opposed to textual fields), enabling easier and better searches, faster database access, and a lot more intelligible programming.

[neo2sxn]

Thanks for reply rocknbil,


I have sent you a mail ...


Thanks in advance

[rocknbil]

For the benefit of others, the crux of the sticky is:

.... for each friend i will have to make 2 new rows, Suppose -

User A want to be a Fiend of user B then the table structure would be -

id----user-----friend
1 ---- A ----- B

It wouldn't make two rows. This table is only for storing the ID's of friends, and you wouldn't use letters, you'd use numeric id values.

User A = 1234
user B = 5678

Add a friend to user A

insert into friends (user_id, friend_id) values (1234,5678);

so for "user A" the entries might look like this.

id|user_id|friend_id
id|1234|5678
id|1234|26
id|1234|4589
id|1234|89
id|1234|4382

and returning to my original select statement (with some corrections, it was typed on the fly), you could pull the first five friends of user A like so

select user_friends.friend_id, user.fname, user.lname where user_friends.friend_id=user.id and user_friends.user_id=$your_target_user order by user.lname, user.fname limit 5

So if you want the friends of user A, the value of $your_target_user is 1234 in this example. It would give you these values.

26|Mary|Contrary
4589|Joe Dirt
89|Ronald|McDonald
5678|John|Smith

Note the ordering: by last name, first name, ascending.

If you're going to do something this robust, leverage all the power of mySQL you can, sorting in arrays is far more work and not efficient at all.

[neo2sxn]

Yep :) Thanks a lot, i am going to implement this ,and will ask you letter if i face any problem ...