CODEWITHSUNDEEP
mysql
runeveryday
runeveryday

Reputation: 2799

what's error with my sql command?

the matched product name is: $products_name

$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 <> :p.products_id AND MATCH('products_name') AGAINST (:products_name IN NATURAL LANGUAGE MODE)";

1, why using " p, " not " p "

my IDE shows an alert of the command. i don't know how to correct it.

when run the query ,the error:

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 ':p.products_id AND MATCH('products_name') AGAINST (:products_name IN NATURAL LAN' at line 6

ps:

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 ':p.products_id AND MATCH(`products_name`) AGAINST (:products_name IN NATURA' at line 5
in:
[ SELECT p.products_id, pd.products_name, p.products_price FROM TABLE_PRODUCTS p INNER JOIN TABLE_PRODUCTS_DESCRIPTION pd ON (p.products_id = pd.products_id) WHERE p.products_status = 1 AND p.products_id <> :p.products_id AND MATCH(`products_name`) AGAINST (:products_name IN NATURAL LANGUAGE MODE)]

I Want to show the relative item under the product. which match according to some parts of the product name

Upvotes: 0

Views: 553

Answers (3)

yoprogramo
yoprogramo

Reputation: 1306

The final query you are sending the database is:

select p.products_id, pd.products_name, p.products_price from table1 p, table2 pd
where p.products_status = 1
and p.products_id = pd.products_id
and pd.language_id = 'lang'
and p.products_id <> :product_id AND MATCH('products_name') AGAINST (:products_name IN NATURAL LANGUAGE MODE);

And it is wrong.

The most approximate query to work on mysql is:

select p.products_id, pd.product_name, p.products_price from table1 p, table2 pd 
where p.products_status = 1 and p.products_id = pd.products_id 
and pd.language_id = 'lang' and p.products_id <> pd.products_id 
AND MATCH(products_name) AGAINST ('product_name' IN NATURAL LANGUAGE MODE);

But I'm not sure if it is what you want...

The "," is mandatory to separate the two tables in the from clause.

Upvotes: 0

Johan
Johan

Reputation: 76567

 SELECT p.products_id, pd.products_name, p.products_price
 FROM TABLE_PRODUCTS p
 INNER JOIN TABLE_PRODUCTS_DESCRIPTION pd ON (p.products_id = pd.products_id)
 WHERE p.products_status = 1
   AND pd.language_id = :lang_id   
   AND p.products_id <> :p.products_id 
   AND MATCH(products_name) AGAINST (:products_name IN NATURAL LANGUAGE MODE)
   --        ^^^^^^^^^^^^^ A         ^^^^^^^^^^^^^^ B
   --  A = column, don't quote  B = value, can be quoted.

You have a syntax error in the match part.
column names should not be quoted in single quotes ', but in backticks `.
However that's only needed for reserved words and such.

Other problems
You are using implicit join syntax. That's a bad idea better to use explicit joins.

If you are using PDO, do not inject $vars in, but just use PDO for everything, it is cleaner, will execute faster and you cannot make SQL-injection mistakes.

As per your question:

1, why using " p, " not " p "

The , syntax is an implicit cross join of two tables, which you transmogrify into an inner join in your where clause: ... AND p.products_id = pd.products_id ....
This is SQL '89 syntax, long since improved upon by the much cleaner SQL '92 syntax.
Better to use an explicit inner join instead.

Upvotes: 1

galchen
galchen

Reputation: 5290

MATCH() is expecting columns. you cannot put quotes inside since they are treated as strings.

use ` instead, or nothing

MATCH(products_name)
MATCH(`products_name`)

Upvotes: 0

Related Questions