[ Date Index ][
Thread Index ]
[ <= Previous by date / thread ] [ Next by date / thread => ]
On Thursday 10 Jul 2003 9:00 am, peter walker wrote: > On 10 Jul 2003 at 0:14, Neil Williams wrote: > > What's the best way to compare two strings, each containing comma > > separated values, to see if ANY of the values in the first string > > match any of the values in the second string? > > > > Ideally, I'd like to do this within an existing SQL query, but PHP > > could be done (and therefore Perl as PHP can use perl regular > > expression pattern matching). > > You don't say what database you are using. But the following works for > MySQL. Otherwise it would translate to PERL or PHP easily (and wouldn't I use LAMP almost exclusively now (Thanks Adrian - I realised it is LAMP rather than PAM - Linux/Apache/MySQL/PHP), so the database I am looking at is MySQL. Actually, I had all the preparatory explode() and while(list(,$content... stuff in place for display reasons, so it was easy-peasy to add a few $regexp .= "|"; lines and ereg_replace() to escape the + to [+] and then to build the regexp from the previously isolated values - I didn't need the replace statements to be in SQL. That saved a lot of effort and made the SQL much more readable. It was the rlike statement that made all the difference. > look such a hack!). The SQL looks horrible and i can't comment on the > efficiency. Judging by performance during testing, the LAMP performance is blinding! I can't see any difference in loading times between this new report and similar, shorter, scripts like the main members area index page. The new report is now 15kb (450lines) as PHP and generates a 14kb HTML4 page (which is eerie). This is despite the SQL statements being long enough to fill about four lines of this size text. Test Platform: Linux Mandrake9.1, Apache2-2.0.44, MySQL Ver 12.18 Distrib 4.0.11a-gamma, PHP4 - mod_php-4.3.1, php-mysql-4.3.0, apache2-mod_php-2.0.44_4.3.1. (all default with Mdk9.1), Konqueror (KDE3.1). Test machine: P3, 700MHz, 128MB RAM, 1392.64 BogoMIPS (if bogomips mean anything anymore). Maximum load during testing: 0.15 Estimated loading time: <1second. Can't be bad! I was going to do some work optimising the SQL to eliminate redundant parts of the queries (I'm using other like and in statements) but it's so fast I can't tell if what I would do would actually speed it up. So all I am using, in essence, is: > SELECT "PHP, XML, GnuPG, standards, design" > RLIKE '$preparedstring' Many thanks Peter. The results are now available in the Members Area Meetings Report: http://www.dclug.org.uk/members/report.php (If anyone does find problems, please let me know.) -- Neil Williams ============= http://www.codehelp.co.uk http://www.dclug.org.uk http://www.wewantbroadband.co.uk/
Attachment:
pgp00028.pgp
Description: signature