0

Correction below!

I search a fairly large table for matches with this simple SQL question:

$result = mysql_query("SELECT *, MATCH(foretag, stad) AGAINST('$query') AS r FROM tblforetag WHERE MATCH(foretag, stad) AGAINST('$query')");

...but mysql_num_rows($result) always returns 1. I tried the exact same query (with the same value as $query) in HeidiSQL and it returned 45 results. Any clues how to get rid of this problem?

Correction: As it turns out, the counting isn't the problem. This only returns one result on the PHP page, but not when executed directly in an MySQL prompt or in HeidiSQL (essentially the same thing).

Christian Lundahl
  • 2,000
  • 3
  • 18
  • 29
  • What does the query look like? What does the table look like? Are the data sets the same in HeidiSQL as they are in MySQL? – Colin M Jan 08 '13 at 14:59
  • This particular query looks like this: "SELECT *, MATCH(foretag, stad) AGAINST('Norrköping') AS r FROM foretag WHERE MATCH(foretag, stad) AGAINST('Norrköping')". Yes, the data sets are the same. – Christian Lundahl Jan 08 '13 at 15:01
  • Whilst this does not answer your question, please please please look into using PDO instead of the `mysql_*` functions. These are now deprecated in the latest versions of PHP and therefore use of them is hugely discouraged. See http://php.net/manual/en/book.pdo.php – Ben Carey Jan 08 '13 at 15:01
  • @BenCarey: I am aware of that. Thank you for pointing it out, though. I guess my mind just tells me "this works, don't learn anything new, that's ridiculous." – Christian Lundahl Jan 08 '13 at 15:02
  • The table looks like this: CREATE TABLE IF NOT EXISTS `tblforetag` ( `foretag` varchar(50) NOT NULL, `www` varchar(100) NOT NULL, `tele` varchar(20) NOT NULL, `adress` varchar(200) NOT NULL, `postnummer` varchar(10) NOT NULL, `stad` varchar(30) NOT NULL, `info_kort` varchar(200) NOT NULL, `info_long` text NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), FULLTEXT KEY `foretag` (`foretag`,`stad`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; – Christian Lundahl Jan 08 '13 at 15:05
  • @Perplexor You might not want to switch to PDO, but it has a lot of advantages, one of which is making sure that you don't inject variables into your query (by using prepared statements). Have you used `mysql_real_escape_string` on `$query`? – h2ooooooo Jan 08 '13 at 15:20
  • I always do that, not only with SQL queries, but with all data that can me modified by the user. I'm gonna look into PDO. It sounds neat. – Christian Lundahl Jan 08 '13 at 15:23
  • Even if you don't switch to PDO, the `mysqli` functions are supported, and are pretty similar to the old `mysql` funcs, so switching to them is pretty straightforward. PDO will give you better gains in the long term, but switching to mysqli is a quick-win alternative if you just want to stop using the deprecated/obsolete/insecure mysql_xx() funcs. – SDC Jan 08 '13 at 15:32
  • Well, isn't that funny. It turns out that PDO was really simple. Thank you for your advise! – Christian Lundahl Jan 08 '13 at 16:05

1 Answers1

0

The problem was the encoding. I just realized this since I got a match on "ping". I solved this issue by using utf8_decode().

Christian Lundahl
  • 2,000
  • 3
  • 18
  • 29