Merging query results

waltherjt

Hello,

I am not really sure how to go about this, but it seems very possible.

I have two tables in the same database. In the first table I want to find all sellers in a given state. I know how to do this. Then I want to find all products for all the sellers that match that state. I can do this as well. The problem I am seeing is that I will have a separate array for each seller containing his products. I would like to put it into a single array so I can format the results in a table. I am thinking I may use array_merge(), but I have no idea how to use this.

Here is what I have going on.

connect to database.

select all sellers in a given state using while loop.

using a while loop again, select all products for that seller

merge the data into one array

format into a html table

The last two steps I am unsure of. I know how to format into the table, but not with the new array. Would I use a while loop again? Any help is appreciated.

Cheers!

snoopy13

you can make it with a join and one query:
SELECT * FROM table1, table2 WHERE table1.state='$state' AND table2.state='$state'

or did I understand your question wrong?

waltherjt

Snoopy,

I am not sure this will work.

Let's call table1 sellers and table2 products.

I will get the state to search for from a form. Let's say CA. so I will need to find all sellers from CA, then find all their products. In the products, I only have the sellers name, not any other info about them.

I only want to display the product info, and only for those products coming from a particular state. The two tables are joined by the seller's id. I hope this makes more sense.

What is a join query by the way? Is it what you displayed?

snoopy13

I still think it works..

You said that the tables are joined by the seller_ID - so you have not only got the sellers name in products but also his ID. I would do the fallowing:

$query=mysql_query("SELECT * FROM sellers, products WHERE sellers.state='$state' AND sellers.seller_ID=products.seller_ID");

Like that you get all product information and sellers information from both tables where the seller comes from the state (from the form) The AND makes that you only get product information from the state you want.
From this information you can then display whatever you need. This is a join - you can make it also different (with join or so). but like this it works on my system

any more doubts?

waltherjt

Snoopy,

Thanks, it worked. I have never used this method before, but I like it. One caution, you can't have the same column names in the two tables, it confused the result and I got an error.