[RESOLVED] dynamically generated drop down list

[revez2002]

Hello,

I have been driving myself crazy trying to figure out what I think should be a simple thing to do.

I have two tables - one named HOBBIE_TYPES, the other named MY_HOBBIES. The HOBBIE_TYPES table contains records listing a wide variety of different hobbies. This table is used with a PHP script to dynamically generate a drop down menu on a form that users can use to select and specify their favorite hobbies. Once they do so, their selection is added to the MY_HOBBIES table.

However, if a user decides to add more hobbies to their profile, I want the drop down list to exclude any hobbies that they have already added in the MY_HOBBIES table.

The code that I am using is as follows, and it is not doing what I want:

  // Query the HOBBIE_TYPES and MY_HOBBIES tables to dynamically generate hobby list 
    $hobby_query = mysql_query("SELECT 
    HOBBIE_TYPES.hobby_ID, 
    HOBBIE_TYPES.hobby_name
    FROM HOBBIE_TYPES, MY_HOBBIES 
    WHERE HOBBIES.hobby_ID != MY_HOBBIES.hobby_ID
    AND MY_HOBBIES.username = '$username' 
    ORDER BY HOBBIES_TYPES.display_name");
    $hobby_result = mysql_num_rows($hobby_query);

I have been referencing various sites and manuals, but I am still unable to figure out what I am overlooking, and I know that this type of thing has to be possible.

revez2002

[void_function]

An easier way, and lighter on the database, is to load up the hobbies the user has in an array, then when you wish to show the drop down, select from HOBBIE_TYPES and show only the ones not present in the array.

Otherwise you can do it with a LEFT JOIN:

SELECT t1.hobby_ID, t1.hobby_name, t2.hobby_ID FROM HOBBIE_TYPES AS t1 LEFT JOIN MY_HOBBIES AS t2 ON t2.hobby_ID=t1.hobby_ID WHERE t2.hobby_ID=NULL ...

[revez2002]

Hi there void_function,

After reading your post, I decided to redo the script using your array suggestion. As you mentioned, the performance was far superior to the LEFT JOIN query, and it does exactly what I want it to do.

Thanks a bunch for the advice!

Regards,
revez2002