SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
Thread: Help with PHP/MySQL Search
-
Jan 28, 2005, 11:50 #1
- Join Date
- Jan 2005
- Location
- Oxford
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with PHP/MySQL Search
Hi
I'm trying to build a a search facility for my site. I have found some good tutorials and developed a simple keyword search.
The problem is it only works for single keywords(not so good!).
Can anyone explain what I need to change to enable searching of several keywords with the following code:
PHP Code:if( $_POST['keyword'] )
{
/* Connect to the database: */
mysql_pconnect("localhost","username","password")
or die("ERROR: Could not connect to database!");
mysql_select_db("search");
/* Get timestamp before executing the query: */
$start_time = getmicrotime();
/* Set $keyword and $results, and use addslashes() to
* minimize the risk of executing unwanted SQL commands: */
$keyword = addslashes( $_POST['keyword'] );
$results = addslashes( $_POST['results'] );
/* Execute the query that performs the actual search in the DB:*/
$result = mysql_query(" SELECT p.page_url AS url,
COUNT(*) AS occurrences
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word = \"$keyword\"
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT $results" );
/* Get timestamp when the query is finished: */
$end_time = getmicrotime();
/* Present the search-results: */
print "<h2>Search results for '".$_POST['keyword[1]']."':</h2>\n";
for( $i = 1; $row = mysql_fetch_array($result); $i++ )
{
print "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";
print "(occurrences: ".$row['occurrences'].")<br><br>\n";
}
/* Present how long it took the execute the query: */
print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";
}
else
{
/* If no keyword is defined, present the search page instead: */
print "<form method='post'> Keyword:
<input type='text' size='20' name='keyword'>\n";
print "Results: <select name='results'><option value='5'>5</option>\n";
print "<option value='10'>10</option><option value='15'>15</option>\n";
print "<option value='20'>20</option></select>\n";
print "<input type='submit' value='Search'></form>\n";
}
print "</body></html>\n";
/* Simple function for retrieving the current timestamp in microseconds: */
function getmicrotime()
{
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
?>
I think it must be simple but i've spent two days reading up on SQL and every time I try to hack the code to enable multiple keywords I just turn it in to a BIG mess of errors.
Any help would be great
Thanks
-
Jan 28, 2005, 12:47 #2
- Join Date
- Apr 2004
- Location
- germany
- Posts
- 4,324
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So far I understand, you use occurrence(word_id, page_id) as an index table (populated elsewhere), right? I've used something like this in my own projects, although the same may be much simpler with mysql fulltext search.
For the sake of simplicity and speed I prefer to split the whole process into steps. First, let's translate words to their ids:
PHP Code:$words = array(list of keywords to search);
$word_list = implode(" ' , ' ", array_map('addslashes', $words));
$rc = mysql_query("SELECT word_id FROM word WHERE word_word IN ('$word_list')");
while($w = mysql_fetch_row($rc))
$word_ids[] = $w[0];
Now let's find pages with keywords. The sql statement will look like
PHP Code:$ids = implode(",", $word_ids);
$rc = mysql_query("
SELECT o.page_id, COUNT(o.page_id) AS relevance, p.url
FROM occurrence o, page p
WHERE word_id IN ($ids) AND o.page_id = p.page_id
GROUP BY o.page_id
ORDER BY relevance DESC");
while($w = mysql_fetch_array($rc))
print pages, ordered by relevance
HTH...
PS All code untested! Handle with care.
-
Jan 28, 2005, 12:59 #3
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Always
PHP Code:mysql_query(...) or die('...'.mysql_error().'....');
Code:SELECT p.page_url AS url, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = \"$keyword\" GROUP BY p.page_id ORDER BY occurrences DESC LIMIT $results
that COUNT(*), is it developed over all occurences X occurences X ...
Should that go on SQL forum?bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 13:13 #4
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi stereofrog,
We just collided but questions shoud be the same.
Just:
PHP Code:$word_list = implode(" ' , ' ", array_map('addslashes', $words));
do we need trailing spaces around words ?bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 13:30 #5
- Join Date
- Apr 2004
- Location
- germany
- Posts
- 4,324
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Well, normally this shoud look like
PHP Code:$sql = $db->prepare("SELECT word_id FROM word WHERE word_word IN (?)");
$res = $db->execute($sql, $word_list);
// etc
-
Jan 28, 2005, 14:19 #6
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
He really,
" IN (?) " meaninq all choices ?
Is it general SQL ?bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 15:22 #7
- Join Date
- Apr 2004
- Location
- germany
- Posts
- 4,324
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
? is an "sql placeholder", commonly used in sql libraries.
-
Jan 28, 2005, 15:28 #8
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ooops, I missed it was a 'prepare'
Then should $word_list be "'a','b','c'" not "' a ',' b ',' c '" ? Empty ?
That ? in my last sentence is not a "sql placeholder"
à+bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 17:53 #9
- Join Date
- Jan 2005
- Location
- Oxford
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with PHP/MySQL Search
Hi Guys,
I just popped out for something to eat and seamed to have bad timing!!
The tables for this project were created as follows:Code:CREATE TABLE page ( page_id int(10) unsigned NOT NULL auto_increment, page_url varchar(200) NOT NULL default '', PRIMARY KEY (page_id) ) TYPE=MyISAM; CREATE TABLE word ( word_id int(10) unsigned NOT NULL auto_increment, word_word varchar(50) NOT NULL default '', PRIMARY KEY (word_id) ) TYPE=MyISAM; CREATE TABLE occurrence ( occurrence_id int(10) unsigned NOT NULL auto_increment, word_id int(10) unsigned NOT NULL default '0', page_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (occurrence_id) ) TYPE=MyISAM;
The search function is as my first post.
As I explained before the scrip works but only for one keyword, i need more really.
I really appreciate the help.
Please be aware that I am not to hot with MySQL so simple terms are cool. Aslo I appologise if I should have posted this in MySQL forum I was unsure.
Thanks
-
Jan 28, 2005, 18:28 #10
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
PHP Code:$result = mysql_query(" SELECT p.page_url AS url,
COUNT(o.occurrence_id) AS occurrences // forgive *
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word IN (\"$word_list\") // as we disscussed as you got your foof ( ' or " )?
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT $results" );
bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 18:54 #11
- Join Date
- Jan 2005
- Location
- Oxford
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with PHP/MySQL Search
Okay I made the changes from the previos post and understand that I need to create a word_list array from the keywords entered in the FORM. But I don't understand how to do this.
Now when I run the search from the web page I get;
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
Code:for( $i = 1; $row = mysql_fetch_array($result); $i++ )
Thanks for working with me on this its driving me mad! So close yet Sooo far.
-
Jan 28, 2005, 19:11 #12
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Take your whole;
$result = mysql_query(" SELECT p.page_url AS url,
COUNT(o.occurrence_id) AS occurrences // forgive *
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word IN (\"$word_list\") // as we disscussed as you got your foof ( ' or " )?
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT $results" );
and copy it just before replacing
$result = mysql_query(...
by echo(....
So we can actually see what this SQL should eat !
Thus die on mysql_error()
à+bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 28, 2005, 19:51 #13
- Join Date
- Jan 2005
- Location
- Oxford
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with PHP/MySQL Search
I did as you said and I get error as in my last post, is the problem is with fetch _array? I don't know.
anyway its 2am here and I need some sleep i'll try some more in the morning.
Thanks for all your help.
-
Jan 28, 2005, 19:54 #14
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Bye, you're right better dreaming
bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
Bookmarks