Retrieving database entries

[ueon]

$query = mysql_query("SELECT a.*, b.*
         FROM friendlist a
 INNER JOIN friendlist b
 ON (a.friendemail=b.friendemail)
 INNER JOIN users c
 ON (b.friendemail = c.EmailAddress)
 WHERE a.email = 'asdf@gmail.com'
 AND c.Username LIKE '%carol%'
 GROUP BY a.id
 ORDER BY count(*) DESC");

 

while ($showfriends = mysql_fetch_array($query)) {
     echo $showfriends['Username'];
}

 

and I would get nothing. It produces the correct number of <div> so i know it's getting through, but it's having trouble displaying the entries?

[AyKay47]

You aren't outputting any divs in your loop, so how would you determine that your query is correct by the number of divs?

[ueon]

You aren't outputting any divs in your loop, so how would you determine that your query is correct by the number of divs?

 

the query is copy and pasted from my project. in the project, it does have divs

[AyKay47]

You aren't outputting any divs in your loop, so how would you determine that your query is correct by the number of divs?

 

the query is copy and pasted from my project. in the project, it does have divs

Show all of the relevant code, if you leave stuff out that makes us have to guess.

[ueon]

You aren't outputting any divs in your loop, so how would you determine that your query is correct by the number of divs?

 

the query is copy and pasted from my project. in the project, it does have divs

Show all of the relevant code, if you leave stuff out that makes us have to guess.

 

how would you output the `Username` from table c? c.Username? in the simplest form?

[AyKay47]

If username is coming from table c, you will need to specify that in your SQL, right now you are grabbing a.* and b.* in your code, add c.Username to that list. Shouldnt have to change anything else.

[ueon]

wow, can't believe i didn't see that. That's all i needed, thanks!

[Zane]

you should be joining on IDs/inedexes... not a varchar field.  Not saying it won't work, but there is definitely more room for errors if you join on varchar.  For instance, a single space could keep it from working whereas an id is simply a number.

[AyKay47]

you should be joining on IDs/inedexes... not a varchar field.  Not saying it won't work, but there is definitely more room for errors if you join on varchar.  For instance, a single space could keep it from working whereas an id is simply a number.

To back Zane here, since I did overlook that in your code, joins are most efficient when they join tables by indexes, the purpose of an index in MySQL is to link two tables by an indexed field and possibly restrict certain actions to be done if the indices of two linked tables do not match. Yes it will work, but as Zane stated, all it would take is a space or something small by the user if the query is dependent on user data to throw off the query.