0

Okay, so have a wonderful script that checks the "base" url up to a certain number of slashes, but realized what I really need is checking the "ending" going backwards to the first non-escaped URL slash, and providing the prefixes are identical, display them.

I.e., let's say this is my data:

CREATE TABLE `testtable` (
  `field1` int(11) NOT NULL,
  `field2` datetime NOT NULL,
  `urlTest` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `testtable` (`field1`, `field2`, `urlTest`) VALUES
(1, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1'),
(2, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/'),
(3, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata'),
(4, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata/dada'),
(5, '2010-01-01', 'http://test1.com/singleprefix/randomdata'),
(6, '2012-02-02', 'http://test1.com/newscript/something'),
(7, '2013-02-02', 'http://test2.com/newscript/something'),
(8, '2014-02-02', 'http://test3.com/newscript/something'),
(9, '2014-02-02', 'http://test3.com/');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`field1`);

so - the script should find identical "base" urls with different endings, based on the last "slash" in the URL.

So in this case,

http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1
http://test1.com/identicalprefix/anotherprefix/
http://test1.com/identicalprefix/anotherprefix/randomdata

would both be listed, because they have the same "base" URL (http://test1.com/identicalprefix/anotherprefix/), just with different suffixes, and same dates.

and obviously, "test3.com" would not display, because even though it shares a common base url, and common date - it is not the "same" entire base URL? (ie., "http://test3.com/" versus "http://test3.com/newscript/"

1 Answer 1

0
SELECT DISTINCT t1.*
FROM testtable t1, testtable t2
WHERE t1.field2 = t2.field2
  AND t1.urlTest != t2.urlTest
  AND SUBSTRING(REVERSE(t1.urlTest) FROM LOCATE('/', REVERSE(t1.urlTest)))
     =SUBSTRING(REVERSE(t2.urlTest) FROM LOCATE('/', REVERSE(t2.urlTest)))

fiddle

1
  • thanks! that is super fantastic and exactly what I needed! :) Sep 27, 2019 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.