I want to display on the page all users who have an avatar installed.

The user record is stored in the user table, the avatar is gallery_foto ( avatar = 1 , which means there is an avatar)

I try to do it like this:

 $avatar = mysql_fetch_array(mysql_query("SELECT * FROM `gallery_foto` WHERE `avatar` = '1' LIMIT 1")); $koll = mysql_query("SELECT * FROM `user` WHERE pol <> '".$user['pol']."' AND '".$avatar['id']."' = 1 ORDER BY RAND() LIMIT 1"); while ($uss = mysql_fetch_assoc($koll)) { ///тут вывод рользователей }

but displayed with and without avatar.

Where is the mistake?

    1 answer 1

    If I understand the problem correctly, the correct SQL query should look like this:

     SELECT * FROM user u JOIN gallery_foto gf ON (u.user_id = gf.user_id AND gf.avatar = 1);

    Or so:

     SELECT * FROM user u, gallery_foto gf WHERE (u.user_id = gf.user_id AND gf.avatar = 1);

    (in both variants * if necessary, replace with the required fields)

    Note: user_id in this case is a user identifier (supposedly a common field for both tables must be present in order to correctly merge the tables): the question did not say anything about it, so you need to substitute the names appropriate for your case.

    An example based on these requests (with some changes)

    Read about using JOIN in SQL

    Based on this, the php code will be approximately as follows:

     $result = mysql_query("SELECT * FROM user u JOIN gallery_foto gf ON (u.user_id = gf.user_id AND gf.avatar = 1)"); while ($row = mysql_fetch_array($result)) { // вывод строк из результата SQL-запроса }

    Information about the mysql_fetch_array() function with good examples

    Extras: your code is defenseless against SQL injection . Screen information that you include in database queries.

    • Sorry, could you write the full request code based on my code? - iKey
    • @Denis, added the supposed php-code and link to the manual. - Oopscurity