How can I achieve this?

[chris.cavage]

I am trying to create a search results page that has a table on it:

Manufacturer Name, Make Name, Model, Desc., Price.

In order to retrieve the above information I use the following SQL (after a search is done from a dropdown box selection):

SELECT *
FROM prod_items  INNER JOIN prod_make ON prod_items.make_ID= prod_make.make_ID INNER JOIN prod_model ON prod_items.model_ID= prod_model.model_ID  INNER JOIN prod_man ON prod_items.man_ID= prod_man.man_ID

I have a table for man, one for make, one for model, a final for items being sold. (I inner join them to display certain info)

I would like to change my table to be this:

Manufacturer Name, Make Name, Available Models, Desc., Price.

So when the search page results comes up, all available models will be present in that cell. *My initial plan is to have the same price for all models from that manufacturer*

How can I change my SQL to list all available models from the items table?!

[kows]

well, since you're matching model numbers in your query, if you want to show all available models then I'd think you would need to get rid of that. Now, I'm not sure how you would determine which models were available -- but I'm sure you do. so, create a WHERE clause in your query to reflect that.

[chris.cavage]

Here's where I am:

I have all the tables I just talked about, and they all come together in the items table (all the foreign keys are there with their price).

I want to make an attractive way of displaying the information.

I can filter the items table by model_name (use inner join to join with the items table on the same mode_id). This way a query result of available models might return this:

1 5 (5 = model name 5)
1 6 (6 = model name 6)
1 7 (7 = model name 7)

... but I want to have this on my page:

Man 1, Make1, Available Models 5,6,7

I really want to combine results but not repeat the same information.

So I might even have this:

Man1, Make 1, Model 1, $200
Man 1, Make 1, Model 2, $200

.. but display:
Man1, Make 1, Model 1,2, $200

Or if this is in my recordset:

Man1, Make 1, Model1, $200
Man1, Make1, Model3, $400

to have this displayed:

Man1, Make1, Model1,3 , $200, $400


does that make sense?

I wonder how to do such things.

[kows]

I think you might need to do a second query to return all of the prices and model numbers, if you're wanting to do that with just SQL. this could make everything very slow, though.

with php, though, it's possible to keep track of what record you're on and what the values of make and man are, and if they're the same as the last values of make/man, then you can keep displaying information as if it were the same row. if they change, you simply make a new row and start displaying the basic information again, and keep track of that make/man. do you understand what I'm getting at? I don't have any time to write anything down right now.

[chris.cavage]

Thanks kows.

I got the jist of your response.

I am going to seriously sit down again tonight and think about the best seach method for my products.

Sometimes searching by too many fields is not the best for the user, either.

I spent all evening reading php at the bookstore, and my brain is fried now.

Better rest it... thanks again for now.