Some MYSQL and php?

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by dharmil, Dec 16, 2007.

Thread Status:
Not open for further replies.
  1. dharmil

    dharmil New Member

    i get repeated results with this, dose any one know why

    PHP:
         $result mysql_query("SELECT albums.id AS albumsid, albums.name AS albumsname, users.id AS usersid, users.album AS usersalbum FROM albums, users");
        
            while(
    $row mysql_fetch_array($result))
                  {
                    echo 
    "<option ";
                    if(
    $row['albumsid'] == $row['usersalbum'] && $row['usersid'] == $_GET['id']){
                        echo 
    "selected=\"selected\"";
                    }
                    echo 
    " value=\"".$row['albumsid']."\">".$row['albumsname']."</option>";
                  }
    Last edited: Dec 16, 2007
  2. kajasweb

    kajasweb New Member

    Try filtering the result in SQL itself.

    Hint: use WHERE clause in SELECT Statement.
  3. flinx

    flinx New Member

    Because you made no link between the two tables. That means that all rows from the first table are being linked to all the rows in the second table. If you have 5 users, and 5 albums, then you'll get 5 x 5 = 25 rows.

    If there is a logical connection between the two tables (for example albums.id = users.album), then use the WHERE clause to tell MySQL which rows must be linked together:

    PHP:
    $result mysql_query("SELECT albums.id AS albumsid, albums.name AS albumsname, users.id AS usersid, users.album AS usersalbum FROM albums, users WHERE albums.id = users.album");
    If this still gives you repeated results, it means you've got some duplicate line in one of the two tables. Get rid of that line, or use DISTINCT in the select:

    PHP:
    $result mysql_query("SELECT DISTINCT albums.id AS albumsid, albums.name AS albumsname, users.id AS usersid, users.album AS usersalbum FROM albums, users WHERE albums.id = users.album");
  4. dharmil

    dharmil New Member

    Thanks IT works
Thread Status:
Not open for further replies.

Share This Page