I’m busy building up a catalog site for a client of mine, and need to tweek the search a bit.
The catalog contains a whole bunch of products. Each product has the ability to contain a single, multiple and an interval of itemnumbers. To clarify that a bit I’ve listed a couple of examples beneath.
EXAMPLE 1)
multiple itemnumbers
itemnumber = 100, 105, 109, 200
EXAMPLE 2)
an interval of itemnumbers
itemnumber = 100 – 110
EXAMPLE 3)
A combination
itemnumber = 100 – 110, 220, 300 – 310, 400, 401
My question is therefore:
is there a syntax that allows me to check intervals between two
numbers separated with ‘ – ‘?If yes, any suggestions on how to build up a query that allows me
to implement.If no, any directions you would recommend?
Additional info
The site is build up in WordPress – where itemnumber is a custom meta field. Atm i’ve hooked into the pre_posts and added: – also pasted in pastebin for readability pastebin
$where .= " OR ID IN ( SELECT post_id FROM {$wpdb->postmeta} WHERE meta_value LIKE '%" . $wp_query->query_vars['s'] . "%' AND ( {$wpdb->posts}.ID=post_id AND {$wpdb->posts}.post_status!='inherit' AND ( {$wpdb->posts}.post_type='produkt' ) ) )";
The above code simply just checks rather the products meta fields contain the searched word, not specific enough.
Replace “-” with “AND” and use BETWEEN keyword to get the records:
Where Column_Name Between 100 AND 110
It’s impossible to use a relational logic on an intentionally denormalized database like evil “WordPress custom meta field” approach.
So, the best you can do is to perform 2 queries:
As a benefit you will get much faster execution
You should create your own function that will build the query using CREATE FUNCTION / CREATE PROCEDURE
Create Procedure docs:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
String functions docs:
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html