errors when searching a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

errors when searching a table

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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);
    }
  }
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try using 'searching' instead?
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

no one has any ideas? I've tried the same query from the terminal with the same results (none).
Stewsburntmonkey
Forum Commoner
Posts: 44
Joined: Wed Aug 24, 2005 2:09 pm

Post 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? :)
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
Post Reply