SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Threaded View

  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


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
  •