Why does this display only 1 result?

[TeddyKiller]

There isn't much to say.. basically.. the code below only shows 1 result. When there are more than 1 status in the database. (Under a friend ID)

 

How can I fix it?

Thanks

 

$query = mysql_query("select * from `friends` where `user_id`='$user->id'"); //Get the friends
while($row = mysql_fetch_assoc($query)) {
    $friend_ids[] = $row['friend_id']; //Put each friend id into an array
}
$query = mysql_query("select * from `user_status` where `user_id` = '".implode(', ', $friend_ids)."' order by `posted` desc");
while($row = mysql_fetch_assoc($query)) {
    $query = mysql_query("select * from `users` where `id` = '".$row['user_id']."'");
    $users = mysql_fetch_assoc($query);
    echo $users['username'] . '<br />' . $row['status'];
}

[Muddy_Funster]

give this a shot:

<?php
   $query = mysql_query("select friend_id from `friends` where `user_id`='$user->id'"); //Get the friends
while($row = mysql_fetch_assoc($query)) {
    $friend_ids = $row['friend_id'];
$query = mysql_query("select user_id from `user_status` where `user_id` = '".$friend_ids."' order by `posted` desc");
while($row = mysql_fetch_assoc($query)) {
    $query = mysql_query("select * from `users` where `id` = '".$row['user_id']."'");
    $users = mysql_fetch_assoc($query);
    echo $users['username'] . '<br />' . $row['status'];
}
}
?>

[Ken2k7]

Should be WHERE user_id IN ..., not WHERE user_id = ...

[ignace]

@Teddy avoid the use of * as much as you can and write the field names you which to use. This avoids that your memory is polluted with data that your program doesn't use.

[TeddyKiller]

@Teddy avoid the use of * as much as you can and write the field names you which to use. This avoids that your memory is polluted with data that your program doesn't use.

Usually I use * when I'm using more than 50% of the collum names. To save space of having 10 names in the query. Though thanks.

The problem was.. is that in the second while, I use $query again, and it gets over written.. so the while breaks.

Although.. that teaches me a lesson to be careful when using queries inside whiles.

[Psycho]

The problem was.. is that in the second while, I use $query again, and it gets over written.. so the while breaks.

Although.. that teaches me a lesson to be careful when using queries inside whiles.

The real lesson is you should NEVER do queries within loops. Doing so is a huge overhead on the sesrver. 99% of the time there is a way to get the same results with a single query. The other 1% you probably set up your database wrong to begin with. The following query should get you the same results as the multiple queries above gave you. I did this on-the-fly so I may have a typo or missed a table relation somewhere. But the logic is sound and should give you a place to start. I expect you will want to order the results in some manner.

 

SELECT u.*

FROM `users` u
JOIN `user_status` us ON u.id = us.user_id
JOIN `friends` f ON us.user_id = f.friend_id

WHERE f.user_id IN = '$user->id'

[ignace]

The other 1% you probably set up your database wrong to begin with.

 

Teddy, that's you

[TeddyKiller]

SELECT u.*

FROM `users` u
JOIN `user_status` us ON u.id = us.user_id
JOIN `friends` f ON us.user_id = f.friend_id

WHERE f.user_id IN = '$user->id'

Could you explain that code please. Where do the f, us, and u come from and what does it actually mean alltogether. I can't make sense of it.

 

Basically.. select all friends from table friends, where user_id = $user->id.

Check for status's for each friend_id.

For each row, so a while.. to put the  friend_id into the users query to get the relative information about that user. Username, etc. and then below that to display the the status.

 

I'm not quite sure what yours will do, could you perhaps rewrite my code, commenting on the query, so I can test it and make more sense from it so I can do methods like that in future.

 

Thanks!

[Ken2k7]

Question, what does WHERE f.user_id IN = '$user->id' mean? Never seen that before.

 

TeddyKiller, the f, us and u are aliases.

[roopurt18]

Aliases:

 

FROM <table_name> [AS <alias>]

 

The square brackets mean the AS <alias> is optional.  If you provide it, then you can give the table an alias, or alternative name.

 

FROM `users` AS u

Means that you don't have to repeatedly type `users` in your query.  Now you can just use u.

 

SELECT
  u.`id`, u.`name`, u.`email`
FROM `users` AS u
WHERE u.`active`=1

 

Aliases are barely useful when a query runs on just one table.  They are incredibly useful when multiple tables are involved in a query or when the same table is involved multiple times, such as during joins or sub-queries.

[TeddyKiller]

Ok roopurt18 I understand. I commented the query mjdamato gave. (Ignace, my database design is pretty poor.. I'm bount to have to redesign it.. but we live and learn)

 

SELECT u.*

FROM `users` AS u {This gets all rows from users table, and defines `users` as u}
JOIN `user_status` AS us ON u.id = us.user_id {Select the rows from users table where `users`"ID" = `user_status`"user_id" and defines user_status as us}
JOIN `friends` AS f ON us.user_id = f.friend_id {Select the rows from user_status where `user_status`"user_id" = `friends`"friend_id" and defines friends as f}

WHERE f.user_id IN = '$user->id' {select friends where user_id = $user->id}

The comments for each line is written between the curly braces.

Let me know if i've got it right, and as for the last line..

IN = - Should it be just IN, or just =, I'm not quite sure on the difference between them?

[Ken2k7]

Not every line is a select.

[roopurt18]

SELECT u.*  -- select these rows from these tables
FROM `users` AS u --use table users, alias it as u
JOIN `user_status` AS us ON u.id = us.user_id --use table user_status, alias it as us, include only rows where u.id=us.user_id
JOIN `friends` AS f ON us.user_id = f.friend_id --use table friends, alias it as f, include only rows where us.user_id=f.friend_id
-- I left out your WHERE, not because you don't need it, but because I don't feel like interpreting it

[Muddy_Funster]

WHERE f.user_id IN = '$user->id' { = Limit results to instances where the user_id in f is the dame as the variable given }

[TeddyKiller]

Ok. Thanks, so using this..

$query = mysql_query("
   SELECT u.*
   FROM `users` AS u
   JOIN `user_status` AS us ON u.id = us.user_id
   JOIN `friends` AS f ON us.user_id = f.friend_id 

   WHERE f.user_id IN = '$user->id'
");

How would I do ORDER BY. Would it go after the WHERE clause or somewhere else?

 

Also.. doing a while. relating to that.

while($row = mysql_fetch_array($query)) {

$row['id'] could mean the ID from users table, friends table, or from the user_status table... how could I do it so its specific to what it is?

[Muddy_Funster]

order by comes at the end, after the where. and MySQL will not let you return a field name that is ambiguous.  Using the joins is not the same as selecting.

[TeddyKiller]

Ok thanks.. so what should I do. I'm in need of the ID from the users table.

 

Here is the echo in which gets displayed in the while. Sorry it's a bit messy. I don't know how to rewrite the variables, based on that query given..

echo '<div class="statusfeed">' .

'<div style="float:left; width:65px; height:65px;;">' .
	'<a href="/profile.php?uid='.$users['id'].'">
		<img src="/resize_image.php?file='.$users['avatar'].'&size=65" title="" border="0" />
	</a>' .
'</div>' .

'<div style="margin-left:65px;">' .

	'<div style="text-align:left; padding:5px;">' .
		'<span>
			<a href="/profile.php?uid='.$users['id'].'">' . ucwords($users['username']) . '</a> ' . $row['status'] . '
		</span>' .
	'</div>' .

	'<div style="text-align:left; padding:0px 5px 5px 5px;">' .
		'<span style="font-size:10px;">Posted on: ' . time_since($row['posted']) . '</span>' .
	'</div>' .

	'<div style="padding:5px;">' .

		'<div style="margin-left:105px; text-align:right;">' .
			'<a href="#" onclick="showComments(\'comment'.$row['id'].'\')">View Comments(0)</a>' . 
			' - ' .
			'<a href="/profile.php?uid='.$users['id'].'">View Profile</a>' .
		'</div>' .

	'</div>' .

'</div>' .

'</div>' .

'<div id="comment'.$row['id'].'" style="border-left:1px solid #000; border-right:1px solid #000; border-bottom:1px solid #000; display:none; width:350px">' . ' COMMENTSSSS ' . '</div>';

[roopurt18]

SELECT u.`id`, ...
FROM `users` AS u
...

 

If you have that in your query, then the id field returned is that from `users`.

 

This is one reason we use aliases.  When a query contains multiple tables and the tables have columns with the same name, we want to identify which table the column we want comes from.

 

As for your ORDER BY question, why not consult the MySQL manual?

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

It clearly shows ORDER BY coming after WHERE.

[Muddy_Funster]

if you examin your select statement

SELECT u.* FROM `users` AS u

it shows that you are selecting information from all the fields in the users table, so

$qry = 'SELECT u.*
   FROM `users` AS u
   JOIN `user_status` AS us ON u.id = us.user_id
   JOIN `friends` AS f ON us.user_id = f.friend_id 
   WHERE f.user_id = '.$user->id    
$result = mysql_query($qry) or die (mysql_error());
WHILE ($users = mysql_fetch_assoc($result)){ 
echo '<div class="statusfeed">' .
'<div style="float:left; width:65px; height:65px;;">' .
'<a href="/profile.php?uid='.$users['id'].'">
<img src="/resize_image.php?file='.$users['avatar'].'&size=65" title="" border="0" />
</a>' .
'</div>' .
'<div style="margin-left:65px;">' .
'<div style="text-align:left; padding:5px;">' .
'<span>
<a href="/profile.php?uid='.$users['id'].'">' . ucwords($users['username']) . '</a> ' . $row['status'] . '
</span>' .
'</div>' .
'<div style="text-align:left; padding:0px 5px 5px 5px;">' .
'<span style="font-size:10px;">Posted on: ' . time_since($row['posted']) . '</span>' .
'</div>' .
'<div style="padding:5px;">' .
'<div style="margin-left:105px; text-align:right;">' .
'<a href="#" onclick="showComments(\'comment'.$row['id'].'\')">View Comments(0)</a>' . 
' - ' .
'<a href="/profile.php?uid='.$users['id'].'">View Profile</a>' .
'</div>' .
'</div>' .
'</div>' .
'</div>' .
'<div id="comment'.$row['id'].'" style="border-left:1px solid #000; border-right:1px solid #000; border-bottom:1px solid #000; display:none; width:350px">' . ' COMMENTSSSS ' . '</div>';
}

should work just fine.  By using the the JOINs you are afactivly telling the select query that there is a relationship between the two or more tables that you are joining, and that it must respect the rules of that relationship when it is selecting the information from any one of them.

 

hope this helps

[TeddyKiller]

They're are a few $row[''] in there too. So it's not all just $users. Thanks for there reply.

 

The following code, when put into a while, should allow me to use;

From users table

$row['username'] - Username,

$row['avatar'] - Avatar,

From status table

$row['id'] - Status ID

$row['user_id'] - Status poster, in origional echo it is $users['id'] which probably wouldn't be the best method.

$row['status'] - The status

$row['posted'] - Date of status post

<?php
$query = mysql_query("
SELECT users.username, users.avatar, user_status.*
FROM `users` AS u
JOIN `user_status` AS us ON u.id = us.user_id
JOIN `friends` AS f ON us.user_id = f.friend_id 

WHERE f.user_id IN = '$user->id'
ORDER BY us.posted DESC
");
?>

Am I correct?

[Muddy_Funster]

I have never tied a tableName.* select I don't have a clue if it will work or not.  But that won't work regardless as you have reffered to the actual table name in the select, and then aliased it.  Either use

aliasName.field

or remove th

AS xx

, and please please please don't use * unless you absoloutly have to

[TeddyKiller]

I have never tied a tableName.* select I don't have a clue if it will work or not.  But that won't work regardless as you have reffered to the actual table name in the select, and then aliased it.  Either use

aliasName.field

or remove th

AS xx

, and please please please don't use * unless you absoloutly have to

I thought you had to use the table name, as it wasn't alias'd until further in the code.. Oh well.. I'll change that.

 

In the code statement I supplied... * is used for user_status, all fields are used in this instance. It's already been mentioned not to use * unless abolustely having to..

[Muddy_Funster]

it's generaly a matter of prefference as to whether you alias or not.  I only alias when I am working with somone elses databases and they have done something crazy like put spaces in the table names.  I find it easier not having to remember what letter goes with what table and so forth.  let me know how that tableName.* works out.

[roopurt18]

In the code statement I supplied... * is used for user_status, all fields are used in this instance. It's already been mentioned not to use * unless abolustely having to..

You should still type out the fields you are using explicitly.  If the table has more columns added to it later, then they will also be returned by the *.  If one of those column names collides with another table in your query, then your page that was working will now be broken with MySQL errors complaining about ambiguous column names.

 

Stop being lazy and type the column names you expect.

[Psycho]

Also, stop putting "IN =".

WHERE f.user_id IN = '$user->id'

 

Use "=" when wanting to test against a single value, use "IN" when wanting to test against multiple values. You should just be using "=" here.

 

Here is the last query you posted (with corrections)

	SELECT u.username, u.avatar, us.*
FROM `users` AS u
JOIN `user_status` AS us ON u.id = us.user_id
JOIN `friends` AS f ON us.user_id = f.friend_id 

WHERE f.user_id = '$user->id'
ORDER BY us.posted DESC

Do you really need ALL the fields from the user_status table?

 

I would suggest you try running that code to see what results you have. I notice you have no error handling on your query command. How do you expect to find and fix any errors?

 

Although you should implement full-featured error handling that can exist in a prodduction environment, this will suffice for now:

 

$query = "SELECT u.username, u.avatar, us.*
          FROM `users` AS u
          JOIN `user_status` AS us ON u.id = us.user_id
          JOIN `friends` AS f ON us.user_id = f.friend_id 
          WHERE f.user_id = '$user->id'
          ORDER BY us.posted DESC";
$result = mysql_query($query) or DIE("Query:<br />$query<br />Error:<br />".mysql_error());

$first_record = true;
echo "<table>\n";
while($row = mysql_fetch_assoc($result))
{
    if($first_record)
    {
        echo "<tr>\n";
        foreach($row as $header=>$value)
        {
            echo "<th>{$header}</th>\n";
        }
        echo "</tr>\n";
        $first_record = false;
    }
    echo "<tr>\n";
    foreach($row as $value)
    {
        echo "<td>{$value}</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";