Select Data From 1 Table Based On Data In Another Tablee

[swdev]

Hi

Environment
MySQL 5.1.30 (hosted account)
PHP 5.1.30
Apache 2.2.13

I have 2 tables - haystack and needles (see attachment for table layouts and some sample data, also included at end of question in case attachments fail)

The Haystack table is populated every minute by a scheduled process that finds URLs that I am interested in.

What I want do do is to find all those URLs in the haystack table that begin with one of the URLs in the needles table and insert them (URLs and dates) into a new table.

Note that there will be several thousand entries in the haystack table and several hundred entries in the needles table.

Once the data has been inserted into the new table, it can be removed from the original haystack table, together with all the rows that did not match.

The columns in the haystack and needles tables are not fixed - i.e. I can add / change columns to make things easier if required.

Given the test data, I need rows 7, 8, 9, 10, 15 and 17 from the haystack table.

Any ideas as to how to do this all in MySQL (as a stored procedure if necessary)

Many thanks

J

Haystack table

CREATE TABLE `haystack` (
  `haystack_url` varchar(512) NOT NULL,
  `haystack_read` date NOT NULL default '0000-00-00',
  UNIQUE KEY `url_read_idx` (`haystack_url`(332),`haystack_read`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample haystack data

INSERT INTO `haystack` VALUES ('http://adzquik.com/','2009-12-20'),('http://we-r-cashingin.com/adtrack/tracker.cgi?trucker','2009-12-20'),('http://we-r-cashingin.com/adtrack/tracker.cgi?text','2009-12-20'),('http://cashingin.creditsafelists.com','2009-12-20'),('http://we-r-cashingin.com/adtrack/tracker.cgi?iearnadsfree','2009-12-20'),('http://supers-r.us','2009-12-20'),('http://adzquik.com/bannerclicks1.php?id=17784','2009-12-20'),('http://adzquik.com/members/adframe.php?id=132673','2009-12-20'),('http://adzquik.com/members/adframe.php?id=133226','2009-12-20'),('http://adzquik.com/members/adframe.php?id=132773','2009-12-20'),('http://adzquik.com/terms.php','2009-12-20'),('http://adzquik.com/earnings.php','2009-12-20'),('http://adzquik.com/spam.php','2009-12-20'),('http://adzquik.com/privacy.php','2009-12-20'),('http://adzquik.com/bannerclicks1.php?id=12494','2009-12-20'),('http://escalating.biz/?pubudu40','2009-12-20'),('http://adzquik.com/members/adframe_html.php?id=94045','2009-12-20');

Needles table

CREATE TABLE `needles` (
  `needle_url` varchar(512) default NULL,
  UNIQUE KEY `needle_url_idx` (`needle_url`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample Needles data

INSERT INTO `needles` VALUES ('http://adzquik.com/bannerclicks1.php'),('http://adzquik.com/buttonclick1.php'),('http://adzquik.com/members/ptcadclick.php?url='),('http://adzquik.com/members/bannerclick.php?id='),('http://adzquik.com/members/adframe.php?id='),('http://adzquik.com/members/adframe_html.php?id=');

[r937]

What I want do do is to find all those URLs in the haystack table that begin with one of the URLs in the needles table and insert them (URLs and dates) into a new table.

INSERT
  INTO newtable
     ( url
     , readdate )
SELECT haystack.haystack_url
     , haystack.haystack_read
  FROM haystack
INNER
  JOIN needles
    ON haystack.haystack_url 
       LIKE CONCAT(needle_url,'%')

[swdev]

Wow

Thanks man!!

That worked a treat.

I never knew that you could have a LIKE CONCAT (...) in the ON section of a JOIN.

I learn something new every day.