Page 1 of 1

errors when searching a table

Posted: Fri Aug 26, 2005 7:27 pm
by Skara

Code: Select all

$search = mysql_escape_string($_POST['search']);

  $res = mysql_query("SELECT * FROM posts WHERE MATCH (title,post) AGAINST ('{$search}')");

  if (!mysql_num_rows($res)) {
    print($sec_top.'<div style="padding: 6px;">No matches found.</div>'.$sec_bot);
  }

  else {
    // print each result
    while ($foo = mysql_fetch_array($res)) {
      $sdate = str_replace(' ','&nbsp;',date($date,$foo['time']));
      print($sec_top.$in1.$foo['title'].$in2.$sdate.$in3.$foo['post'].$in4.$sec_bot);
    }
  }
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/localhost/blog/search/index.php on line 10
and I always get "No matches found."

The table is set up so:
id = int(3)
time = int(10)
title = varchar(80)
post = text


I got this right from the MySQL manual, so what's the deal? Do I have a different version or something? I have version 4.0.24.

Posted: Fri Aug 26, 2005 7:30 pm
by feyd
you've got an error in your query syntax or logic. Try this to see what's up.

Code: Select all

$search = mysql_escape_string($_POST['search']);

  $res = mysql_query("SELECT * FROM posts WHERE MATCH (title,post) AGAINST ('{$search}')") or die(mysql_error());

  if (!mysql_num_rows($res)) {
    print($sec_top.'<div style="padding: 6px;">No matches found.</div>'.$sec_bot);
  }

  else {
    // print each result
    while ($foo = mysql_fetch_array($res)) {
      $sdate = str_replace(' ','&nbsp;',date($date,$foo['time']));
      print($sec_top.$in1.$foo['title'].$in2.$sdate.$in3.$foo['post'].$in4.$sec_bot);
    }
  }

Posted: Fri Aug 26, 2005 7:38 pm
by Skara
doh.. The problem was in my table. I had a fulltext for the title and a fulltext for the post, but they weren't joined. >.>
i.e.
FULLTEXT title
FULLTEXT post
instead of
FULLTEXT title,post

^^;

Never done a search page before.

Anyway, thanks. Must be one of those days. :P

Posted: Fri Aug 26, 2005 10:53 pm
by Skara
Ok, dammit. I know I didn't change anything, but it doesn't work again... I don't get any error messages, but I don't get any results.
The one thing I have done is add a giant post. Maybe 2k characters. *shrugs*

Code: Select all

$search = mysql_escape_string($_POST['search']);
  $res = mysql_query("SELECT * FROM posts WHERE MATCH (title,post) AGAINST ('{$search}') ORDER BY `time` DESC");
  if (!mysql_num_rows($res)) {
    print($sec_top.'<div style="padding: 6px;">No matches found.</div>'.$sec_bot);
  }
  else {
    // print matches
  }
and here's the table:

Code: Select all

CREATE TABLE `posts` (
  `id` int(3) NOT NULL auto_increment,
  `time` int(10) NOT NULL default '0',
  `title` varchar(80) NOT NULL default '',
  `post` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `time` (`time`),
  FULLTEXT KEY `searching` (`title`,`post`)
) TYPE=MyISAM ;
This:

Code: Select all

$res = mysql_query("SELECT * FROM posts WHERE MATCH (title,post) AGAINST ('{$search}') ORDER BY `time` DESC") or die(mysql_error());
gives me nothing.

Posted: Fri Aug 26, 2005 11:03 pm
by feyd
try using 'searching' instead?

Posted: Sat Aug 27, 2005 9:35 am
by Skara
That's what I thought. But I just get "Unknown column 'searching' in 'where clause'."
For natural-language full-text searches, it is a requirement that the columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table.

Posted: Sun Aug 28, 2005 10:53 am
by Skara
no one has any ideas? I've tried the same query from the terminal with the same results (none).

Posted: Sun Aug 28, 2005 3:55 pm
by Stewsburntmonkey
Have you checked to see what the SQL query is after variable replacement?

Also what happens if you remove the brackets in the AGAINST clause? :)

Posted: Thu Sep 01, 2005 9:24 pm
by Skara
Have you checked to see what the SQL query is after variable replacement?
As I said, I've tried running the query with multiple AGAINST clause values straight in the mysql terminal. Same result.
Also what happens if you remove the brackets in the AGAINST clause?
Nada.