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(' ',' ',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(' ',' ',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.

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.