How to write the sql command in mysql?

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.($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…

how do you know how much of the product name you want to take off the end in order to do a search with what’s left?

table : products which have fields: products_id , products_price ,products_status = 1( In Stock )

table : products_description which have fields: products_id(equals products_id in table products ), products_name

now , i want to shows five relative products name(in stock). which matches by some part of the products name.

1,you meaned there is no way to get this? could i truncate some part of the products then do the search?

Yes you can, if you can define a rule for the truncation.

if i truncate the first 20 characters of the given name $product_name as the match, how to write the sql command thank you.

SELECT p.products_id 
     , p.products_price 
     , p.products_status 
     , pd.products_name
  FROM products_description AS pd
INNER
  JOIN products AS p
    ON p.products_id = pd.products_id
 WHERE pd.products_name [COLOR="#0000FF"]LIKE CONCAT(LEFT('$product_name',20),'%')[/COLOR]   

some one told me using mysql fulltext search:

AND MATCH(`product_name`)
AGAINST (:product_name IN NATURAL LANGUAGE MODE)

does he right? thank you

what happened when you tested it? :slight_smile:

i don’t know how to enter the

 AGAINST (:product_name

part? when i put the code in, it shows an error,

AND MATCH('$products_name')
AGAINST (:product_name IN NATURAL LANGUAGE MODE).

what was the error?

1, your code contanins the matched item. how to exclude the current product

  1. the error shows :
    there is no anything shows, the page is blank. the where part
 WHERE  p.products_status = 1 and AND MATCH($products_name)
AGAINST (:pd.product_name IN NATURAL LANGUAGE MODE);

sorry, i don’t understand

eg:if i use the products name aaa as the match, the aaa also shows on the relative item part. i want to execlude it.

Add this to the WHERE to exclude the known product from the related items list

AND p.products_id <> $products_id

where $products_id contains the product ID of the known product