Join Query Counts Number of Users

[justlukeyou]

Hi,

 

I am logged in as 403. In the table 'follow' I have user 403 following 355 once. So the code below should echo 403 once to say that the user I am logged in is following user 355.

 

However the code below echoes 403 four times because I have 4 users in 'users' table.

 

I'm struggling to understand why the number of rows in the users table affects the number of times it echoes the user_id is echoed.

 

I want to display the user_id 403 once from table 'follow' and then join on the logo from table 'users'.

 

The $ID comes from the users table.

 

<?php

$query = mysql_query("SELECT user_id, logo FROM follow, users WHERE follow_user_id = $ID LIMIT 10");
while($row = mysql_fetch_array($query)) {

     ?>    

     <?php echo $row['user_id']; ?>

<?php echo $row['logo']; ?>

         <?php
       }
       ?>

Edited January 10, 2013 by justlukeyou

[Barand]

if you join 2 tables without specifying the join condition then you create a "cartesian join" which joins every record in table1 with every record in table2

 

So if table1 has 10 records and table2 has 50 records you will get 500 (50x10) rows returned.

 

In your case you get a row returned for every user

[justlukeyou]

if you join 2 tables without specifying the join condition then you create a "cartesian join" which joins every record in table1 with every record in table2

 

So if table1 has 10 records and table2 has 50 records you will get 500 (50x10) rows returned.

 

In your case you get a row returned for every user

 

 

 

 

I see. Thanks for the explanation.

 

I found this example, but its very complicated. http://stackoverflow.com/questions/4521182/how-to-use-join-and-multiple-where-condition

 

How would I add a join condition? I cant match the logo to anything. I can't say logo = ID.

 

Do I need to say "join users where logo = null". To say I am not querying the logo and just querying the follow table.

 

When I remove the join function it works but obviously does has no method of echoing the image.

Edited January 10, 2013 by justlukeyou

[Barand]

How would I a join condition?

 

By specifying which key field in table1 has the same value as a key field in table 2

 

eg

SELECT ...
FROM customer INNER JOIN invoice ON invoice.custID = customer.id

 

which says records should be matched where custID in the invoice table matches id in the customer table

 

see http://www.phpfreaks.com/tutorial/data-joins-unions

Edited January 11, 2013 by Barand

[justlukeyou]

Gosh this is getting complicated. I have as per the following but it will only echo 1 result at time.

 

I am trying to say only show the images which match the ID. This is the same number as the user_id.

 

I have tried around 10 variations so it must the code that is wrong.

 

 

<?php


$query = mysql_query("SELECT user_id, logo FROM follow INNER JOIN users ON users.id = follow_user_id WHERE follow_user_id = $ID LIMIT 10");
while($row = mysql_fetch_array($query)) {

  ?>	

  <?php echo $row['user_id']; ?>

<?php echo $row['logo']; ?>

  	<?php
	}
	?>

[haku]

Your problem lies in your use of LIMIT 10 at the end of your query. This will show you everything from the 10th record onward. I'm assuming you only have 10 or 11 items in your database, and you are seeing the last one.

 

You want to use: LIMIT 0 10

 

This means start at the zeroth record, and get 10 records. (0 - 9).

[Barand]

LIMIT 10 === LIMIT 0 10

 

LIMIT 10, 10 would give 10th record onwards.

[justlukeyou]

I thought LIMIT 10 just limits the maximum number of echoes to 10. Which is what I have found to be so far.

 

When I remove the LIMIT 10 this has no affect on what is being echoed. I only 3 records in the table.

[DavidAM]

Hi,

 

I am logged in as 403. In the table 'follow' I have user 403 following 355 once. So the code below should echo 403 once to say that the user I am logged in is following user 355.

 

However the code below echoes 403 four times because I have 4 users in 'users' table.

Gosh this is getting complicated. I have as per the following but it will only echo 1 result at time.

 

Your first post said you wanted to output the user only once. Your most recent post complains that you output the user only once. What is it that you actually want?

 

I'm a little confused about what you are attempting to do.

 

1) Is follow_user_id the ID of the user being followed or the user doing the following?

 

2) I would presume that the follow table has two user IDs in it -- the follower and the followee. What are the names of these columns and what does each represent?

 

3) What are you trying to list? The other users being followed by the logged-in user? Or the other users that are following the logged-in user?

[justlukeyou]

Hi,

 

I have one table called follow:

 

user_id - the person who is being followed (You view this persons profile)

follow_user_id - the person who is following the user_id

 

 

I have a second table called users:

 

id - the id of the user which is inserted into user_id or follow_user_id

logo - the logo of the user

 

I want to display the id and logo of the people following the profile ID and the opposite. The people the the profile ID is following.

 

 

[salathe]

Users followed by User 1

 

SELECT id, logo
FROM users
JOIN follow ON follow.user_id = users.id
WHERE follow.follow_user_id = 1

 

Users following User 1

SELECT id, logo
FROM users
JOIN follow ON follow.follow_user_id = users.id
WHERE follow.user_id = 1

Edited January 12, 2013 by salathe

[justlukeyou]

Nice one. Thats sorted it.

 

I can also use them next to another. My previous code kept creating an error on the second piece of code.

[DavidAM]

Soo much easier when the requirements are clearly stated.

 

Can we mark this thread solved?