SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Data From 1 Table Based On Data In Another Tablee

    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
    Code MySQL:
    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
    Code MySQL:
    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
    Code MySQL:
    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
    Code MySQL:
    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=');
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swdev View Post
    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.
    Code:
    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,'%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •