MySQL Query Search using Multiple Rows

Question

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 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

Conrad Frix · Accepted Answer · 2011-03-29 19:12:09Z

1

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

answered Mar 29, 2011 at 19:12

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eddie Over a year ago

Aha just what I needed! Is there a way to only select a specific field on the INNER JOIN? Much appreciated.. Cheers!

Conrad Frix Over a year ago

@Eddie you could use an inline view if you wanted to. This can have a positive or negative impact your perf so be sure to test

Eddie Over a year ago

I'll give it a shot, but as per previous experience it didn't help with optimization... Cheers

Scott Hunter · Accepted Answer · 2011-03-29 19:43:11Z

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.

Collectives™ on Stack Overflow

MySQL Query Search using Multiple Rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related