2 Replies Latest reply on Sep 15, 2010 8:06 PM by TheHillbillyGeek

    Filtering results from one table with a value from a second table

    TheHillbillyGeek

      Using Dreamweaver CS3 & PHP & MySQL.

      Let's say I have a bunch of cars from different dealers in one table, with dealer-id being one of the values.

       

      Another table holds the dealer's values, including whether they are active/inactive, with dealer-id being the primary key.

       

      I want to search the car records, but only return the results from active dealers.

       

      I can build an array of active dealers, and filter the results as they are produced from a standard search, but this is not optimum.

      It gives me a number for total results that includes the results rom inactive dealers, and also screws with pagination of results.

       

      Dreamweaver puts the recordset code in the header of the page, but....

       

      One solution I see is to do a do { } while for all the active dealers that includes a query and a do { } while for results for just that dealer, right in the body code, but is there a more elegant one?

      One problem I see is that results are not going to be able to be broken up into paginated groups...

       

      I don't suppose there's a way to do this at the database level with a column that pulls a live value from another table during the query, is there?

      Any MySQL gurus out there? Bueler...?

      My copy of  phpMyAdmin says: "The additional features for working with linked tables have been deactivated." this sounds hopeful, but I get bogged down in the documentation. Can someone give me a boost?

       

      Thanks in advance.