how to write the sql command in mysql

Question

for example: the table named products there is a product name field in the table. i want to show some related items which matched by the knowed products's product name.

how to write the sql query command?

eg: if the knowed products's product name is

True Blood Season 1 DVD

i want to get all the product name which begins as True Blood Season..

if the knowed products's product name is

 24 Hours Season 7 DVD

i want to get all the product name which begins as 24 Hours Season..

the sql: what's error with the sql

$query ="select p.products_id, pd.products_name, p.products_price
                     from " . TABLE_PRODUCTS . " p " .
                             TABLE_PRODUCTS_DESCRIPTION . " pd
                     where p.products_status = 1
                     and p.products_id = pd.products_id
                     and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                    and p.products_id <> :product_id AND MATCH('products_name') AGAINST (:products_name IN NATURAL LANGUAGE MODE);

Sample code (e.g. table structure as CREATE TABLE statements) and data, along with the desired result for the sample data, would be most helpful. What's your exact criteria for determining what's related? — outis
– outis, Commented Oct 24, 2011 at 4:23

Phil · Accepted Answer · 2011-10-24 04:28:18Z

2

I'd look at using MySQL Full-Text search.

This should be able to take the title of your chosen product and use it to retrieve relevant matches, eg

SELECT * FROM `products`
WHERE `id` <> :product_id -- don't return current product
AND MATCH(`product_name`)
AGAINST (:product_name IN NATURAL LANGUAGE MODE)

This requires you to create a full-text index on your products.product_name column.

answered Oct 24, 2011 at 4:28

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

runeveryday Over a year ago

what's this line meaning AGAINST (:product_name IN NATURAL LANGUAGE MODE) thank you

Phil Over a year ago

@runeveryday Did you read the manual I linked to? If you're referring to :product_name, that's a parameter.

runeveryday Over a year ago

Phil, thank you, i have put your code in my sql command. but my IDE shows there is an error. how to correct it.thank you. i put the command on the question.

runeveryday Over a year ago

Phil,the matched product name is: $products_name how to put it in the command, thank you

cabanaboy · Accepted Answer · 2011-10-24 04:05:57Z

0

select * from products where name like 'True Blood Season%'

select * from products where name like '24 Hours Season%'

answered Oct 24, 2011 at 4:05

cabanaboy

714 bronze badges

Comments

Whetstone · Accepted Answer · 2011-10-24 04:22:15Z

-1

SELECT * FROM products WHERE product_name='title of show here';

It's pretty basic SQL, I'd really recommend reading up a bit before posting as this question is very entry level. I'm more than happy to help you get started though :).

edited Oct 24, 2011 at 4:22

answered Oct 24, 2011 at 4:05

Whetstone

1,2099 silver badges8 bronze badges

3 Comments

Whetstone Over a year ago

Downvotes don't help anyone (including me) unless explained. What's the issue here?

Phil Over a year ago

Not tagged PHP. Also, this does not satisfy the question requirements

Whetstone Over a year ago

You're right, edited so that it's not PHP. I've been fielding mostly PHP questions, oversight on my part, thanks for pointing that out :). I assume you just mean that I'm searching for the wrong product, I had it changed so that the asker would make the connection on what goes where when changing the title, aiding future learning.

Collectives™ on Stack Overflow

how to write the sql command in mysql

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related