Searching shopping cart

Someone referred me to:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Does this look like I am on the right track?

Ron

SELECT MATCH(shopping_cart_product.product_description,
shopping_cart_product.product_name) AGAINST ('$keyword') as Relevance
FROM ( shopping_cart_category INNER JOIN shopping_cart_product ON
shopping_cart_category.reference =
shopping_cart_product.category_reference ) INNER JOIN
shopping_cart_product_image ON
shopping_cart_product_image.product_reference =
shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
shopping_cart_inventory.product_reference =
shopping_cart_product.reference WHERE MATCH
(shopping_cart_product.product_description,
shopping_cart_product.product_name) AGAINST('$keyword' IN BOOLEAN MODE)
HAVING Relevance > 0.2 ORDER
BY Relevance DESC


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

Ron Piggott wrote:
> Someone referred me to:
>
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
>
> Does this look like I am on the right track?
>
> Ron
>
> SELECT MATCH(shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST ('$keyword') as Relevance
> FROM ( shopping_cart_category INNER JOIN shopping_cart_product ON
> shopping_cart_category.reference =
> shopping_cart_product.category_reference ) INNER JOIN
> shopping_cart_product_image ON
> shopping_cart_product_image.product_reference =
> shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
> shopping_cart_inventory.product_reference =
> shopping_cart_product.reference WHERE MATCH
> (shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST('$keyword' IN BOOLEAN MODE)
> HAVING Relevance > 0.2 ORDER
> BY Relevance DESC

My first suggestion :) Use table aliases.

Instead of:

shopping_cart_category inner join shopping_cart_product

do

shopping_cart_category cat inner join shopping_cart_product prod

and so on (give them all a short name), so then your query becomes a
little easier to read:

select match(prod.product_description, prod.product_name) ....

http://dev.mysql.com/doc/refman/5.0/en/join.html


Secondly - do you need to do all of those joins to do a keyword search?
If you're searching a particular category, then include that category
limitation. If you're not, don't include it - the database will do less
work. Same for images etc.

It looks like you're on the right track though.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Searching shopping cart

I am getting the following error when running the query through php my
admin:

MySQL said: Documentation

#1191 - Can't find FULLTEXT index matching the column list

I am new to this; Thank you for the help.

Ron

On Wed, 2008-05-28 at 22:01 -0400, Ron Piggott wrote:
> Someone referred me to:
>
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
>
> Does this look like I am on the right track?
>
> Ron
>
> SELECT MATCH(shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST ('$keyword') as Relevance
> FROM ( shopping_cart_category INNER JOIN shopping_cart_product ON
> shopping_cart_category.reference =
> shopping_cart_product.category_reference ) INNER JOIN
> shopping_cart_product_image ON
> shopping_cart_product_image.product_reference =
> shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
> shopping_cart_inventory.product_reference =
> shopping_cart_product.reference WHERE MATCH
> (shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST('$keyword' IN BOOLEAN MODE)
> HAVING Relevance > 0.2 ORDER
> BY Relevance DESC
--
ron.piggott@actsministries.org
www.actsministrieschristianevangelism.org

Acts Ministries Christian Evangelism
"Where People Matter"
12 Burton Street
Belleville, Ontario, Canada K8P 1E6

In Belleville Phone : (613) 967-0032
In North America Call Toll Free : (866) ACTS-MIN
Fax: (613) 967-9963


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

Ron Piggott wrote:
> I am getting the following error when running the query through php my
> admin:
>
> MySQL said: Documentation
>
> #1191 - Can't find FULLTEXT index matching the column list

Grr - stupid manual didn't have a link on how to create the index.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

create fulltext index on table(field1, field2);

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

I don't understand what is going wrong:

CREATE FULLTEXT INDEX
ON ï»=BFshopping_cart_product(ï»=BFproduct_name, ï»=BFproduct_d=
escription)=20

gives me this error:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'ON shopping_cart_product(pr oduct_=
name,
product_description)' at line 1

My table is named: shopping_cart_product

I have columns:=20

product_name varchar(50)
product_description longtext

Ron

On Thu, 2008-05-29 at 12:48 +1000, Chris wrote:
> Ron Piggott wrote:
> > I am getting the following error when running the query through php my
> > admin:
> >=20
> > MySQL said: Documentation
> >=20
> > #1191 - Can't find FULLTEXT index matching the column list
>=20
> Grr - stupid manual didn't have a link on how to create the index.
>=20
> http://dev.mysql.com/doc/refman/5.0/en/create-index.html
>=20
> create fulltext index on table(field1, field2);



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

------=_Part_3743_10796233.1212199138111
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: base64
Content-Disposition: inline

T24gNS8zMC8wOCwgUm9uIFBpZ2dvdHQgPHJvbi5waHBAYWN0c21pbmlzdHJp ZXMub3JnPiB3cm90
ZToKPgo+Cj4gSSBkb24ndCB1bmRlcnN0YW5kIHdoYXQgaXMgZ29pbmcgd3Jv bmc6Cj4KPiBDUkVB
VEUgRlVMTFRFWFQgSU5ERVgKPiBPTiDvu79zaG9wcGluZ19jYXJ0X3Byb2R1 Y3Qo77u/cHJvZHVj
dF9uYW1lLCDvu79wcm9kdWN0X2Rlc2NyaXB0aW9uKQo+Cj4gZ2l2ZXMgbWUg dGhpcyBlcnJvcjoK
Pgo+ICMxMDY0IC0gWW91IGhhdmUgYW4gZXJyb3IgaW4geW91ciBTUUwgc3lu dGF4OyBjaGVjayB0
aGUgbWFudWFsIHRoYXQKPiBjb3JyZXNwb25kcyB0byB5b3VyIE15U1FMIHNl cnZlciB2ZXJzaW9u
IGZvciB0aGUgcmlnaHQgc3ludGF4IHRvIHVzZQo+IG5lYXIgJ09OIMOvwrvC v3Nob3BwaW5nX2Nh
cnRfcHJvZHVjdCjDr8K7wr9wcm9kdWN0X25hbWUsCj4gw6/Cu8K/cHJvZHVj dF9kZXNjcmlwdGlv
biknIGF0IGxpbmUgMQo+Cj4gTXkgdGFibGUgaXMgbmFtZWQ6IHNob3BwaW5n X2NhcnRfcHJvZHVj
dAo+Cj4gSSBoYXZlIGNvbHVtbnM6Cj4KPiBwcm9kdWN0X25hbWUgdmFyY2hh cig1MCkKPiBwcm9k
dWN0X2Rlc2NyaXB0aW9uIGxvbmd0ZXh0Cj4KPiBSb24KPgo+IE9uIFRodSwg MjAwOC0wNS0yOSBh
dCAxMjo0OCArMTAwMCwgQ2hyaXMgd3JvdGU6Cj4gPiBSb24gUGlnZ290dCB3 cm90ZToKPiA+ID4g
SSBhbSBnZXR0aW5nIHRoZSBmb2xsb3dpbmcgZXJyb3Igd2hlbiBydW5uaW5n IHRoZSBxdWVyeSB0
aHJvdWdoIHBocCBteQo+ID4gPiBhZG1pbjoKPiA+ID4KPiA+ID4gTXlTUUwg c2FpZDogRG9jdW1l
bnRhdGlvbgo+ID4gPgo+ID4gPiAjMTE5MSAtIENhbid0IGZpbmQgRlVMTFRF WFQgaW5kZXggbWF0
Y2hpbmcgdGhlIGNvbHVtbiBsaXN0Cj4gPgo+ID4gR3JyIC0gc3R1cGlkIG1h bnVhbCBkaWRuJ3Qg
aGF2ZSBhIGxpbmsgb24gaG93IHRvIGNyZWF0ZSB0aGUgaW5kZXguCj4gPgo+ ID4gaHR0cDovL2Rl
di5teXNxbC5jb20vZG9jL3JlZm1hbi81LjAvZW4vY3JlYXRlLWluZGV4Lmh0 bWwKPiA+Cj4gPiBj
cmVhdGUgZnVsbHRleHQgaW5kZXggb24gdGFibGUoZmllbGQxLCBmaWVsZDIp Owo+Cj4KPgo+IC0t
Cj4gUEhQIERhdGFiYXNlIE1haWxpbmcgTGlzdCAoaHR0cDovL3d3dy5waHAu bmV0LykKPiBUbyB1
bnN1YnNjcmliZSwgdmlzaXQ6IGh0dHA6Ly93d3cucGhwLm5ldC91bnN1Yi5w aHAKPgo+IFJvbiwK
CllvdSBjYW4ndCBjcmVhdGUgYSBtdWx0aS1jb2wgZnVsbCB0ZXh0IGluZGV4 LiBZb3Ugc2hvdWxk
IG9ubHkgY3JlYXRlIGl0IG9uCnRoZSBsb25nIHRleHQgZmllbGQKCgoKCi0t IAoKQmFzdGllbgoK
Q2F0LCB0aGUgb3RoZXIgb3RoZXIgd2hpdGUgbWVhdAo=
------=_Part_3743_10796233.1212199138111--

Re: Searching shopping cart

Ok. I have the index created. =20

ALTER TABLE `shopping_cart_product` ADD FULLTEXT
( `product_description`,`product_name` )=20

is what worked --- I think the web site
http://dev.mysql.com/doc/refman/5.0/en/create-index.html was assuming
the creation of a new table.

I am able to have both columns in the index. My index is named
product_description_search

Would someone show me a sample query from the table? I have tried to
get this to work during the past half hour and I don't understand this
type of query yet. =20

Where I am at with my search query that doesn't work is:

SELECT MATCH ( 'product_description_search') AGAINST ( 'Jesus') FROM
shopping_cart_product

Thanks, Ron

On Fri, 2008-05-30 at 21:58 -0400, Bastien Koert wrote:
>=20
>=20
> On 5/30/08, Ron Piggott wrote:=20
> =20
> I don't understand what is going wrong:
> =20
> CREATE FULLTEXT INDEX
> ON ï»=BFshopping_cart_product(ï»=BFproduct_name, ï»=
=BFproduct_description)
> =20
> gives me this error:
> =20
> #1064 - You have an error in your SQL syntax; check the manual
> that
> corresponds to your MySQL server version for the right syntax
> to use
> near 'ON shopping_cart_product(ï»=C2=
=BFproduct_name,
> product_description)' at line 1
> =20
> My table is named: shopping_cart_product
> =20
> I have columns:
> =20
> product_name varchar(50)
> product_description longtext
> =20
> Ron
> =20
> On Thu, 2008-05-29 at 12:48 +1000, Chris wrote:
> > Ron Piggott wrote:
> > > I am getting the following error when running the query
> through php my
> > > admin:
> > >
> > > MySQL said: Documentation
> > >
> > > #1191 - Can't find FULLTEXT index matching the column list
> >
> > Grr - stupid manual didn't have a link on how to create the
> index.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/create-index.html
> >
> > create fulltext index on table(field1, field2);
> =20
> =20
> =20
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> =20
> Ron,
> =20
> You can't create a multi-col full text index. You should only create
> it on the long text field
> =20
>=20
>=20
>=20
> --=20
>=20
> Bastien
>=20
> Cat, the other other white meat=20


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

> You can't create a multi-col full text index. You should only create it
> on the long text field

'Course you can.

mysql> create table a(a int, b varchar(50), c text);
Query OK, 0 rows affected (0.01 sec)

mysql> create fulltext index fulltext_bc on a(b,c);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Just the syntax of the create index statement wasn't right.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Searching shopping cart

> Would someone show me a sample query from the table? I have tried to
> get this to work during the past half hour and I don't understand this
> type of query yet.
>
> Where I am at with my search query that doesn't work is:
>
> SELECT MATCH ( 'product_description_search') AGAINST ( 'Jesus') FROM
> shopping_cart_product

You don't use the index name in the match, you use the columns you want
to search.

select match(col1, col2) against('keyword') from tablename;

As mentioned in the mysql manual:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php