Jump to content

Search numbers


dreamwest
 Share

Recommended Posts

my problem is ive got a list of rows that associate a video id

 

select * from video where word_id IN(1,3)

 

Which returns

 

word_id      vid_id

 

1 864144

3 864144

3 864145

1 864146

 

Basically from this set i only want to return rows that have both numbers in this case vid_id 864144, im just not sure how to go about it

Link to comment
Share on other sites

modify your REGEX statement to look like this:

"WHERE $column REGEXP '[[:<:]]" . $number . "[[:>:]]' = 1"

 

Using "[[:<:]]" makes the engine look for non alphanumeric characters as word boundaries. It's pretty darn fast, and it should tear into those comma delimited columns without a problem... especially if they are indexed.

Link to comment
Share on other sites

One more quick question: Is there an alternative for IN() using REGEX

 

Currently i have:

select * from video WHERE id IN(2,7,18,19)

 

Not to the best of my knowledge, but I do have a php query builder class that parses search strings like:

this_is_treated_as_an_OR_string "this is a quote" +this_is_something_that_has_to_be_in_the_results -i_dont_want_this wildca%rd -"I don't want this quote in my results"

 

and automatically converts them into a regex string. It's a module in what will be an open source searching class I'm building that I can send you. That would obviously have way more functionality than you require, but it's a nice little bit of code.

 

I just whipped up a really basic interface to it here:

http://fiftyoff.phpwebhosting.com/business/sql_search_query_builder.php?test

I can't guarantee that I'll have that up permanently.

 

I'd be happy to send you the code if you like. Otherwise, the style query that you are looking for is like this:

SELECT * FROM `table` WHERE `column` REGEXP '[[:<:]]or1[[:>:]]' = 1 OR `column` REGEXP '[[:<:]]or2[[:>:]]' = 1

Link to comment
Share on other sites

OK, because REGEX is doing the actual search i dont need to replace IN() because i can limit the amount to 30 items at a time. Its pretty fast on 50 Million rows it returning a set in 0.0006 secs

 

This is equivilant to "some words" in boolean

select id from video WHERE word_id REGEXP '[[:<:]]567,765[[:>:]]' = 1 limit 0, 30

 

This is equivilant to find rows that contain at least one of the two words in boolean

select id from video WHERE word_id REGEXP '[[:<:]](567|765)[[:>:]]' = 1 limit 0, 30

 

The final one i need is '+some +words' boolean equivilant aka match both numbers within the string, any suggestions?

 

Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.