SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

I am using MySQL 5.0.32 in Debian Etch. The application accessing the database is written in Perl.

I have a (FULLTEXT indexed) TEXT column in a table that will contain data similar to this:

code:

[url]http://www.google.com[/url]
[url]http://www.yahoo.com[/url]
[url]http://www.altavista.com[/url]

I have a string "$string" that will contain data in the same format as the above. I must discover if any line of $string exists in any row, for the column in question. I also must know which line of $string matched successfully.

So, given some row in the table like so:

code:

Apple
Orange
Pear

And a value of $string as such:

code:

Orange
Tomato

I want a "good" (MySQL) solution for discovering that "Orange" (from $string) exists somewhere in my table.

If I cannot do this "well", I will do a rather slow and lovely loop that will execute one query per line of $string. Doing this will make me feel like a bad programmer. Or, I could generate a query that would SELECT COUNT(something) ... MATCH ... AGAINST for each line and return a row that would let me pinpoint any line that matches. (SELECT (SELECT COUNT(...), (SELECT COUNT(...) ...).

Any suggestions are appreciated.

# ¿ Jan 3, 2008 09:56

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 18:22

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

Azzmodan posted:

That should give you every string that matched, with the number of matches

This seems to only match when myfield is exactly identical to 'Orange' or 'Tomato', whereas my 'myfield' will contain several unrelated terms separated by newlines. I must see if Orange or Tomato is on one of those lines, for any sql-row.

# ¿ Jan 3, 2008 21:40

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

deimos posted:

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE MATCH (myfield) AGAINST ('orange apple' IN BOOLEAN MODE);

Yes, but then I would not know specifically which of orange/apple matched

I suppose I could take the matching myfield and parse it myself, outside of MySQL. It would just be nice to have a pure MySQL solution.

# ¿ Jan 3, 2008 21:58

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

m5 posted:

You could dynamically build the query.

This is what I have done to solve the problem. It excludes input sanitation and application-specific terms. $Database is a reference to a custom DBI wrapper object that I made for convenience.

This code is in Perl, and I provide it for the benefit of anyone with a similar problem.

code:

my @things = split /\n/, $CGI->param('things');
my $query = "SELECT ";
for (@things) {
 # [b]this line is dumb because it breaks (SA forum) tables otherwise[/b]
 $query .= 
     "(SELECT COUNT(id) FROM table WHERE MATCH(column) AGAINST('$_' IN BOOLEAN MODE)),";
}  
chop($query); # Remove trailing ','

my @duplicates = @{$Database->fetch_row($query)};
for (my $i = 0; $i <= $#things; $i++) {
   if ($duplicates[$i] != 0) {
      print "$things[$i] is duplicated.<br/>";
   }
}

A bit less tidy than I would like, but it gets the job done.

Freezy fucked around with this message at 01:08 on Jan 4, 2008

# ¿ Jan 4, 2008 01:04

Freezy: Aug 26, 2005; I probably sucked Funky's large meaty cock for this account.

Triple Tech posted:

Edit^2: Well, "set rowcount N" and then "set rowcount 0" (to reset) works, but again, that feels wrong.

I have never used Sybase but perhaps this is useful:

http://forum.softpedia.com/lofiversion/index.php/t150162.html

quote:

SET ROWCOUNT is the simplest way to limit the number of rows in the result set. It only applies to your connection. Don't forget to SET ROWCOUNT 0 to reset the option to allow all rows.

There is no "rownum" by default in ASE, though another approach is to select the result set plus an identity column into a temp table and then use the identity column as a rownum to select a subset of the result set. Example:

select *, identity(9) as "rownum"
into #foobar
from sysindexes
select id, name from #foobar
where rownum between 22 and 42
drop table #foobar

quote:

SET ROWCOUNT is session based. Only valid on the connection you establish. Need to SET ROWCOUNT back to 0 as Bret suggests. Better to create a sp that runs the select as it may be easier to maintain.

Create Procedure sp_Test
As
Begin
SET ROWOUNT 10

select * from MyTable

SET ROWCOUNT 0

# ¿ Jan 4, 2008 19:20

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'