MySQL Query Search using Multiple Rows

Firstly I'd like to start by apologizing for the potentially miss-leading title... I am finding it difficult to describe what I am trying to do here.

With the current project I'm working on, we have setup a 'dynamic' database structure with MySQL that looks something like this.

item_details ( Describes the item_data )

fieldID  |  fieldValue  |  fieldCaption

    1    |     addr1   |    Address Line 1

    2    |     country |    Country

item_data

itemID   |  fieldID  |  fieldValue

12345    |     1     |  Some Random Address

12345    |     2     |  United Kingdom

So as you can see, if for example I wanted to lookup the address for the item 12345 I would simply do the statement.

SELECT fieldValue FROM item_data WHERE fieldID=1 and itemID=12345;

But here is where I am stuck... the database is relatively large with around ~80k rows and I am trying to create a set of search functions within PHP.

I would like to be able to开发者_Python百科 perform a query on the result set of a query as quickly as possible...

For example, Search an address name within a certain country... ie: Search for the fieldValue of the results with the same itemID's as the results from the query:

'SELECT itemID from item_data WHERE fieldID=2 and fieldValue='United Kingdom'..

Sorry If I am unclear, I have been struggling with this for the past couple of days...

Cheers

You can do this in a couple of ways. One is to use multiple joins to the item_data table with the fieldID limited to whatever it is you want to get.

SELECT * 
FROM 
Item i
INNER JOIN item_data country
ON i.itemID = country.itemID
  and fieldid = 2
INNER JOIN item_data address
ON i.itemID = country.itemID
  and fieldid = 1
WHERE
    country.fieldValue= 'United Kingdom'
    and address.fieldValue= 'Whatever'

As an aside this structure is often referred to as an Entry Attribute Value or EAV database

Sorry in advance if this sounds patronizing, but (as you suggested) I'm not quite clear what you are asking for.

If you are looking for one query to do the whole thing, you could simply nest them. For your example, pretend there is a table named CACHED with the results of your UK query, and write the query you want against that, but replace CACHED with your UK query.

If the idea is that you have ALREADY done this UK query and want to (re-)use its results, you could save the results to a table in the DB (which may not be practical if there are a large number of queries executed), or save the list of IDs as text and paste that into the subsequent query (...WHERE ID in (...) ... ), which might be OK if your 'cached' query gives you a manageable fraction of the original table.