[PHP] Mysql search query ignoring dots
Hi all,
I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH AGAINST.
But there is a problem. In this field there are company names that
contain dots, for istance I've PO.SE. srl and I want to find it if
the user search for: POSE or PO.SE or P.O.S.E. etc.
I googled in the web but I don't find any solution. I don't want to
add a new field with the cleaned version of my string because I would
like to solve with the query and I prefer that the mysql table not
become too big. But if I will not find a different solution, I will
use this escamotage.
I've find a post that is similar but the solution don't seem to solve
my situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
is indifferent and don't change my results.
My query, searching POSE, is:
select aziende.* from aziende where 10 AND
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
'')) order by aziende.ragione_sociale limit 0, 10
The alternative choice could be REGEXP but I've red that it make my
query slow in a table of millions of records and I don't know how to
exclude dots in the regular expression.
Can anyone help me?
Thanks in advance.
Barbara
--
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
http://www.microsrl.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql search query ignoring dots
-Original Message-
From: Barbara Picci [mailto:barbara.pi...@sardi.it]
Sent: Monday, January 24, 2011 4:51 AM
To: php-general@lists.php.net
Subject: [PHP] Mysql search query ignoring dots
Hi all,
I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH
AGAINST.
But there is a problem. In this field there are company names that contain
dots, for istance I've PO.SE. srl and I want to find it if the user
search for:
POSE or PO.SE or P.O.S.E. etc.
I googled in the web but I don't find any solution. I don't want to add a
new
field with the cleaned version of my string because I would like to solve
with the query and I prefer that the mysql table not become too big. But
if I
will not find a different solution, I will use this escamotage.
I've find a post that is similar but the solution don't seem to solve my
situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', ''); is
indifferent and don't change my results.
My query, searching POSE, is:
select aziende.* from aziende where 10 AND
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
'')) order by aziende.ragione_sociale limit 0, 10
The alternative choice could be REGEXP but I've red that it make my query
slow in a table of millions of records and I don't know how to exclude
dots
in the regular expression.
Can anyone help me?
Thanks in advance.
Barbara
--
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
http://www.microsrl.com
I don't see anything relevant regarding PHP. As for ' a table of millions
of records,' that sounds like questions for the DBA.
Regards,
Tommy
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search query ignoring dots
Hi,
Monday, January 24, 2011, 10:50:41 PM, you wrote:
BP Hi all,
BP I have to perform a mysql query in a table with millions of records.
BP I've full-text indexed my search field and I'm searching with MATCH AGAINST.
BP But there is a problem. In this field there are company names that
BP contain dots, for istance I've PO.SE. srl and I want to find it if
BP the user search for: POSE or PO.SE or P.O.S.E. etc.
BP I googled in the web but I don't find any solution. I don't want to
BP add a new field with the cleaned version of my string because I would
BP like to solve with the query and I prefer that the mysql table not
BP become too big. But if I will not find a different solution, I will
BP use this escamotage.
BP I've find a post that is similar but the solution don't seem to solve
BP my situation.
BP You can see it at the url:
BP http://forums.mysql.com/read.php?10,395557,395584#msg-395584
BP In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
BP is indifferent and don't change my results.
BP My query, searching POSE, is:
BP select aziende.* from aziende where 10 AND
BP (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
BP (replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
BP '')) order by aziende.ragione_sociale limit 0, 10
BP The alternative choice could be REGEXP but I've red that it make my
BP query slow in a table of millions of records and I don't know how to
BP exclude dots in the regular expression.
BP Can anyone help me?
BP Thanks in advance.
BP Barbara
BP --
BP
BP Barbara Picci
BP Micro srl
BP viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
BP http://www.microsrl.com
In the interest of speed it may be worth creating a table with just a
link id and the text to search which you can cleanup before inserting.
It will probably save you a headache in the future and will be quicker
than complicated queries.
The list of possible ids can then be tested against the full table for
any other criteria which if the table is indexed properly will be fast
too.
--
regards,
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql search html tags excluded
Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652880.2217.54.ca...@localhost... On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk Yes i can. You misunderestood me or i didn't express myself correctly. how can i get rid of the tags before entering the data into the second field created for search engine? Br Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 15:00 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652880.2217.54.ca...@localhost... On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk Yes i can. You misunderestood me or i didn't express myself correctly. how can i get rid of the tags before entering the data into the second field created for search engine? Br Tanel Ah right. Use strip_tags(). As long as the HTML is well-formed and doesn't contain any malformed tags it should remove them correctly. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
At 2:12 PM +0300 6/4/10, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Tanel: If your database has html tags in it, then it's pretty simple to grab the data from the db and preform strip_tags(). After which you can search what's left. If you want to have the db do the search, then look in to full text to do the searching for you. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
At 11:16 PM -0700 10/21/08, Ryan S wrote: clipp Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. Cheers, Rob. /clipp Did some searching based on your tip, got what i was looking for, just didnt know where to start.. and now feeling like the man who was taught how to fish :D Thanks! R I often feel like a fish given a matchbook. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
clipp Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. Cheers, Rob. /clipp Did some searching based on your tip, got what i was looking for, just didnt know where to start.. and now feeling like the man who was taught how to fish :D Thanks! R -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql search
Hey all, I have two columns in my DB title varchar(254) and jtext text which I would like to search, as the user might enter two or more words I am opting not to use LIKE %search_term% so started searching google, I came across this very promising class: http://code.activestate.com/recipes/125901/ but when i tried to run it I am just getting a blank page, no errors or anything. Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Thanks, Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search
On Tue, 2008-10-21 at 21:48 -0700, Ryan S wrote: Hey all, I have two columns in my DB title varchar(254) and jtext text which I would like to search, as the user might enter two or more words I am opting not to use LIKE %search_term% so started searching google, I came across this very promising class: http://code.activestate.com/recipes/125901/ but when i tried to run it I am just getting a blank page, no errors or anything. Am hoping someone out there can recommend a better script or maybe share some of your own code? Any help would be appreciated. Do it right... read up on MySQL's fulltext matching. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PhP/MySQL Search Results code
Newbie question - please excuse. I'm using Dreamweaver to generate most of
my PhP. I have created a search page and am trying to generate the results
page. The search form is using the GET function and the form field name is
manufacturer. The following code is generated by DW in the results
page...
?php
$vManufacturer_rs_RobesonResultsList = Reece;
if (isset(manufacturer)) {
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
manufacturer : addslashes(manufacturer);
}
mysql_select_db($database_VandyDivAddresses, $VandyDivAddresses);
$query_rs_RobesonResultsList = sprintf(SELECT robeson_inv.manufacturer,
robeson_inv.model, robeson_inv.specs, robeson_inv.qty FROM robeson_inv
WHERE robeson_inv.manufacturer = '%s' ORDER BY robeson_inv.model,
$vManufacturer_rs_RobesonResultsList);
$rs_RobesonResultsList = mysql_query($query_rs_RobesonResultsList,
$VandyDivAddresses) or die(mysql_error());
$row_rs_RobesonResultsList = mysql_fetch_assoc($rs_RobesonResultsList);
$totalRows_rs_RobesonResultsList = mysql_num_rows($rs_RobesonResultsList);
?
I'm always getting an error when testing the page. The error is...
Parse error: parse error, expecting `T_VARIABLE' or `'$'' in
/home/s/k/user992816/html/RobesonWeb/TMP4np9nf7zp.php on line 4
The line to which it refers is the line stating,
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
manufacturer : addslashes(manufacturer);
Anyone know what the problem may be?
Thanx in advance for any help provided,
Robb Kerr
Digital IGUANA
Helping Digital Artists Achieve their Dreams
http://www.digitaliguana.com
http://www.cancerreallysucks.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PhP/MySQL Search Results code
[snip]
if (isset(manufacturer)) {
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
manufacturer : addslashes(manufacturer);
}
Parse error: parse error, expecting `T_VARIABLE' or `'$'' in
/home/s/k/user992816/html/RobesonWeb/TMP4np9nf7zp.php on line 4
[/snip]
Looks like 'manufacturer' needs a $ in fromt of it to make it a variable
if (isset($manufacturer)) {
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
$manufacturer : addslashes($manufacturer);
}
HTH
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PhP/MySQL Search Results code
The line should be
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
$manufacturer : addslashes($manufacturer);
Robb Kerr wrote:
Newbie question - please excuse. I'm using Dreamweaver to generate most of
my PhP. I have created a search page and am trying to generate the results
page. The search form is using the GET function and the form field name is
manufacturer. The following code is generated by DW in the results
page...
?php
$vManufacturer_rs_RobesonResultsList = Reece;
if (isset(manufacturer)) {
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
manufacturer : addslashes(manufacturer);
}
mysql_select_db($database_VandyDivAddresses, $VandyDivAddresses);
$query_rs_RobesonResultsList = sprintf(SELECT robeson_inv.manufacturer,
robeson_inv.model, robeson_inv.specs, robeson_inv.qty FROM robeson_inv
WHERE robeson_inv.manufacturer = '%s' ORDER BY robeson_inv.model,
$vManufacturer_rs_RobesonResultsList);
$rs_RobesonResultsList = mysql_query($query_rs_RobesonResultsList,
$VandyDivAddresses) or die(mysql_error());
$row_rs_RobesonResultsList = mysql_fetch_assoc($rs_RobesonResultsList);
$totalRows_rs_RobesonResultsList = mysql_num_rows($rs_RobesonResultsList);
?
I'm always getting an error when testing the page. The error is...
Parse error: parse error, expecting `T_VARIABLE' or `'$'' in
/home/s/k/user992816/html/RobesonWeb/TMP4np9nf7zp.php on line 4
The line to which it refers is the line stating,
$vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ?
manufacturer : addslashes(manufacturer);
Anyone know what the problem may be?
Thanx in advance for any help provided,
Robb Kerr
Digital IGUANA
Helping Digital Artists Achieve their Dreams
http://www.digitaliguana.com
http://www.cancerreallysucks.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PhP/MySQL Search Results code
That was it! Thanx for the help. This board has proven invaluable to me. Robb -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PhP/MySQL Search Results code
* Thus wrote Robb Kerr ([EMAIL PROTECTED]): I'm always getting an error when testing the page. The error is... Parse error: parse error, expecting `T_VARIABLE' or `'$'' in /home/s/k/user992816/html/RobesonWeb/TMP4np9nf7zp.php on line 4 The line to which it refers is the line stating, $vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ? manufacturer : addslashes(manufacturer); hmm.. what part of line 4? take the php line and put it on 4 different lines $vManufacturer_rs_RobesonResultsList = (get_magic_quotes_gpc()) ? manufacturer : addslashes(manufacturer); What line is the error on now? line 6? ah.. so what exacly is manufacturer? A variable, constant, string or number? I'm going to make the assumption that it is a variable. So you need to prepend a $ to the word manufacturer ($manufacturer). If you test it now, you'll probaly get a notice/warning about line 7 (depending on php's error_reporting level). You'll probably want to make that manufacturer a variable also. Curt -- My PHP key is worn out PHP List stats since 1997: http://zirzow.dyndns.org/html/mlists/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and MySQL.
The site currently has a simple search engine that allows a shopper to type
in a search string that is stored in $search. For example, if a shopper
types in 1972 Ford Mustang
$string =1972 Ford Mustang
Using the following SQL statement:
SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search%
Records are returned that have this exact string and in this exact order
(I'm aware a wild card character is included on the front and back of the
string).
My desire is to be able to logically AND each token of the search together
independent or the order of the tokens.
I want to return all records that have Mustang AND 1972 AND Ford.
Since a shopper inputs the search string in advance I don't know how many
tokens will be used.
How about using OR and a ranked sorting so that the more words that match,
the better?
?php
if (!isset($start)){
$start = 0; # Or is it 1? MySQL/PostgreSQL do it differently. G.
}
$words = explode(' ', $search);
$query = select whatever, 0 ;
while (list(,$word) = each($words)){
if (trim($word)){
$query .= + whatevercolumn LIKE '%$word%' ;
}
}
$query .= as score ;
$query .= from sometable ;
$query .= where score 0 ;
$query .= order by score desc ;
$query .= limit $start, $limit ;
?
Since LIKE returns TRUE/FALSE, when you add them up they turn into 1/0,
and you get a one-point score for each matching word.
--
Like Music? http://l-i-e.com/artists.htm
I'm looking for a PRO QUALITY two-input sound card supported by Linux (any
major distro). Need to record live events (mixed already) to stereo
CD-quality. Soundcard Recommendations?
Software to handle the recording? Don't need fancy mixer stuff. Zero (0)
post-production time. Just raw PCM/WAV/AIFF 16+ bit, 44.1KHz, Stereo
audio-to-disk.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL Search Engine Query Question
Here is an idea
?
// make array out of words in search string
$search_array = explode(' ', $search);
// make regexp pattern '.*(this|or|that).*'
$string = .*( . implode('|', $search_array) . ).*;
$query = SELECT * FROM my_table WHERE body REGEXP '$string';
$result = mysql_query($query, $connection);
$res = mysql_num_rows($result);
if ($res 1)
die(no match for $search);
?
using this method car would match car, carwash, scar, scarred,
etc.
Since this result will contain the entire boy of text you could some more
matching or scoring for relevancy
?
while ( $row = mysql_fetch_assoc($result) )
{
$num = sizeof($search_array);
for ($i = 0; $i $num; $i++)
{
if ( preg_match(/.*\b$search_array[$i]/i, $row[body]) )
{
// it was found so score 25 to start
$score[$row[page_title_or_something]] += 25;
$body_size = strlen($row[body]);
// this is the first case-insensitive occurance of the word
$temp = @stristr($row[body], $search_array[$i]);
$pos = @strlen($row[body])-strlen($temp);
if ($pos == $body_size)
$pos = 0;
// score higher
$percent = ( ($pos / $body_size * 1000) / 10 );
$score[$row[page_title_or_something]] += ((100 -
number_format($percent)) / 2);
// this is the first occurance of the word by it's self
preg_match(/[^a-z0-9]?($search_array[$i])([^0-9a-z])?/i,
$row[body], $matches);
$temp = @stristr($row[body], trim($matches[0]));
$pos_clean = @strlen($row[body])-strlen($temp);
if ($pos_clean == $body_size)
$pos_clean = 0;
// score higher
$percent = ( ($pos_clean / $body_size * 1000) / 10 );
$score[$row[page_title_or_something]] += (100 -
number_format($percent));
// this is how many times it occured in total
$reps = substr_count($row[body], $search_array[$i]);
// score higher
$score[$row[page_title_or_something]] += ($reps * 5);
// this is how many times it occured by it's self
$rc = preg_grep(/[^a-z0-9]?($search_array[$i])([^0-9a-z])?/i,
explode( , $row[body]) );
$reps_clean = sizeof($rc);
// score higher
$score[$row[page_title_or_something]] += ($reps_clean * 10);
}
}
?
I had that code from a previous working project. I copied it and changed
some var names to make it more clear. I did not test it in this format but
it is a good example and you could certainly improve it or build on it.
Jim Grill
Support
Web-1 Hosting
http://www.web-1hosting.net
- Original Message -
From: Paul Maine [EMAIL PROTECTED]
To: PHP PHP [EMAIL PROTECTED]
Sent: Saturday, July 27, 2002 9:31 PM
Subject: [PHP] PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and
MySQL.
The site currently has a simple search engine that allows a shopper to
type
in a search string that is stored in $search. For example, if a shopper
types in 1972 Ford Mustang
$string =1972 Ford Mustang
Using the following SQL statement:
SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search%
Records are returned that have this exact string and in this exact order
(I'm aware a wild card character is included on the front and back of the
string).
My desire is to be able to logically AND each token of the search together
independent or the order of the tokens.
I want to return all records that have Mustang AND 1972 AND Ford.
Since a shopper inputs the search string in advance I don't know how many
tokens will be used.
I would appreciate any suggestions.
Regards,
Paul
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: php/mysql search. need help
There are various sounds like modules such as soundex and metaphone and suchlike. These can ameliorate bad spelling. Another option, if you have a limited number of types available, is to give the user a popup menu rather than free-form box to type in. In general, the fewer ways you can provide for the user to type incorrectly, the better. -- WARNING [EMAIL PROTECTED] address is an endangered species -- Use [EMAIL PROTECTED] Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm Volunteer a little time: http://chatmusic.com/volunteer.htm - Original Message - From: Lizlynch [EMAIL PROTECTED] Newsgroups: php.general To: [EMAIL PROTECTED] Sent: Friday, September 07, 2001 6:59 PM Subject: php/mysql search. need help i have a form where a user inputs a home service type and this results in a list of that value, eg a user inputs 'plumber' and a list of plumbers is displayed. if a user accidentally types plumbar no results are displayed. is there anyway of making php/mysql recognise the first three letters or give a closest match to the query. yours liz lynch -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] php/mysql search. need help
i have a form where a user inputs a home service type and this results in a list of that value, eg a user inputs 'plumber' and a list of plumbers is displayed. if a user accidentally types plumbar no results are displayed. is there anyway of making php/mysql recognise the first three letters or give a closest match to the query. yours liz lynch
[PHP] PHP MySQL Search Results
Does anyone know (or have) of a good example of how to create search results like a Yahoo or MSN search? for example: Say I do a search for "cars" and there are 10,000 records in my table that match "car"... I would like to show the results in blocks of (say) 25 at a time, therefore I will need a "Next Results", "Previous Results", "Total Records found that match my query", and perhaps, between the "Previous Results" and "Next Results" a set of numbers like so; Previous [1] [2] [3] [...] Next The numbers being "jump to" links, etc. Thanks. Jason -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP MySQL Search Results
In a MySQL query you can use the LIMIT keyword, like in phpmyadmin. Use limit 0,10 to get the first ten results in a query. Then do the query again but use 10,10 for the limit. The first number is the "offset", and the second one is the maximum results to return. That's the way most people do "pages". -- Plutarck Should be working on something... ...but forgot what it was. ""Jason Caldwell"" [EMAIL PROTECTED] wrote in message 9b8rgd$oot$[EMAIL PROTECTED]">news:9b8rgd$oot$[EMAIL PROTECTED]... Does anyone know (or have) of a good example of how to create search results like a Yahoo or MSN search? for example: Say I do a search for "cars" and there are 10,000 records in my table that match "car"... I would like to show the results in blocks of (say) 25 at a time, therefore I will need a "Next Results", "Previous Results", "Total Records found that match my query", and perhaps, between the "Previous Results" and "Next Results" a set of numbers like so; Previous [1] [2] [3] [...] Next The numbers being "jump to" links, etc. Thanks. Jason -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP MySQL Search Results
Hi Jason,
Say you have a search form that says search for "searchbox" and the search
box has a variable name of "search":
?
/* Include connection details, with a variable of $connection */
if (!$page) {
$page = 1;
}
$limit = $page * 25;
$limit = $limit - 25;
$sql = "SELECT FROM table WHERE searchdata LIKE '$search' ORDER BY
searchdata DESC LIMIT $limit,25";
$result = @mysql_query($sql, $connection)
or die (mysql_error());
$num = mysql_num_rows($result);
if ($num == 0) {
?
NO Valid search results.
?
} else {
while($row = mysql_fetch_array($result)) {
$url = $row['url'];
$name = $row['name'];
?
a href="? echo "$url"; ?"? echo "$name"; ?BR
? }
if ($page 1) {
echo "a
href=\"search.php?search=".$search."page=".(page -1)."\"Previous/a";
}
if (($num 25) (($limit + 25) $num)) {
echo "a href=\"search.php?search=".$search."page=".($page +
1)."\"Next/a.";
}
/* End the initial "Else" */
}
?
__
Here you will see that if no page is assigend as a variable, it is force
assigned to 1. The limit is the number of results the search is limited to.
If you do some mathematics (ouch) you will see that the limit works like
this:
if the page is equal to 1:
limit is 1 * 25 = 25.
limit = 25 - 25 = 0
The limit in the sql query limits 0,25 (first 25 matches).
if the page is equal to 2:
limit is 2 * 25 = 50
limit is 50 - 25 = 25
The limit in the sql query limits 25,25 (25 matches After the first 25
matches).
if the page is equal to 3:
limit is 3 * 25 = 75
limit = 75 - 50
The limit in the sql query is 50,25 (next 25 matches after the first 50).
Now, towards the bottom of the code I have supplied, I have next and
previous links:
if page is greater than 1, you can show a link to a previous page:
search.php?search=".$search."page=".($page - 1)
This links back to the search page with the searchg criteria, and the page -
1.
if there are more than 25 results ($num) and the (limit + 25) is less than
the total number of pages, we can add a next link.
If you wanted to add page numbers as links, you could do a while or for
loop, but I'd have to use my brain a little more to try and explain that to
you.
I hope that makes a little sense, I'm not very good at explaining these
things. There's a really good tutorial on this at devshed.com
James.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PHP | MySQL Search
Hya'll, I've got a little problem with coding some php/mysql, this is what I have : - Table Search - HouseID | FacilityID | SurroundingID | PriceID | TypeID | CountryID | - 1| 1 | 0 |3|4 | 2 | 1| 3 | 0 |3|4 | 2 | 1| 5 | 0 |3|4 | 2 | 1| 0 | 3 |3|4 | 2 | 2| 0 | 0 |3|5 | 2 | 2| 0 | 1 |3|5 | 2 | 2| 5 | 0 |3|5 | 2 | - Query 1 : (I only want result on houses which are in country 2 and in price 3 and facility 1 must be included on the spot) I've used : SELECT HouseID FROM Search WHERE CountryID='2' AND PriceID='3' AND (FacilityID='5') Result 1 : HouseID 1 and HouseID 2 this one is right. Query 2 : (I only want result on houses which are in country 2 and in price 3 and facility 1 and 5 must be included on the spot) I've used : SELECT HouseID FROM Search WHERE CountryID='2' AND PriceID='3' AND (FacilityID='1' OR FacilityID='5') Result 2 : again HouseID 1 and HouseID 2, the problem here is that HouseID 2 doesn't contain Facility 1. The problem here is just that when I give more criteria to the query the result is or equal or even bigger then when I give less criteria, it should be just the other way around, that is I want a more detailed result, does anyone have the solution for this problem ? A. Boontjes aka Tuin ([EMAIL PROTECTED]) Web-developer at : www.TomDeeDom.com www.Impression.nl -- Hi! I'm a .signature virus! Copy me into your ~/.signature to help me spread! -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP+MySQL SEARCH
if ($search_in == "1"){
$query1 = "SELECT * FROM FISH where ID LIKE '%$search_text%'
OR name LIKE '%$search_text%' OR Category LIKE '%$search_text%' OR Color
LIKE '%$search_text%' OR Size LIKE '%$search_text%'";
}
Looks good the way it is for now.
Things you *could* do:
1) Break up your query like this:
$query1 = "select * from fish ";
$query1 .= " where id like '%$search_text%' ";
$query1 .= "or name like '%$search_text%' ";
.
.
.
2) Calculate a "score" for your search:
$query1 = "select *, ";
$query1 .= " ( id like '%$search_text%' ";
$query1 .= " + name like '%$search_text%' ";
$query1 .= " + category like '%$search_text%' ";
.
.
.
$query1 .= " ) as score ";
$query1 .= " where score 0 ";
$query1 .= " order by score desc ";
So, here, a record gets a "point" for each match, and we add all the
"points" up as a "score" and now you have not only all the matches, but you
have a "score" for each to say how *good* the match is.
You could even give more "weight" to the name field by using:
$query1 .= "+ 3*(name like '%$search_text%') ";
Here, a "match" in the name field is 3X as important as in the category
field.
You could also try to build an "advanced search" form where users would pick
which fields to search in.
--
Visit the Zend Store at http://www.zend.com/store/
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] PHP+MySQL SEARCH
I got this from a book but given the fact that it would only search for
one type of information I modified it and it works great, but I wanted
to know what you think about it if anything. Could an array be maid to
trim it down?
if ($search_in == "1"){
$query1 = "SELECT * FROM FISH where ID LIKE '%$search_text%'
OR name LIKE '%$search_text%' OR Category LIKE '%$search_text%' OR Color
LIKE '%$search_text%' OR Size LIKE '%$search_text%'";
}
Any feedback would be a preciated.
--
Gerry Figueroa
-- - - - --*
War does not determine who is right, war determine who is left.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
