using LIKE in SQL query with activerecord

[eluser]mandril[/eluser]
Hi everyone, i have a problem with LIKE.. it seems to not be working properly...

Im developing an e-commerce and need to get the related products, for that reason the table product has a field called : tags.

Here is my method... that returns nothing, and if i remove the p1.id != p2.id it returns the product itself.. quite logic.

Code:

public function getRelatedProducts($idProd)
{

$query = $this->db->query("SELECT p1.id, p1.name FROM product p1, product p2 WHERE p1.id='".$idProd."'  AND p1.id != p2.id AND p1.tags LIKE p2.tags");

return $query;

}

Ok help me analyze step by step..

I need to grab the name and the id of the product so...

SELECT p1.id, p1.name seems fine

I need to compare a product with other products..

FROM product p1, product p2 seems fine

Now i need that one of the product is the one the customer is viewing .. that variable is $idProd

WHERE p1.id='".$idProd."' AND seems fine

I dont need to get the name of the product itself as a related product so..

p1.id != p2.id seems fine

I have to get similar tags.. so i use LIKE

AND p1.tags LIKE p2.tags is this working ?

a product has a tag like this: cellphone sony ericsson cam

and i put a similar tag to other product: cellphone motorola cam

AND I GET NO RESULT....

Thnx for your help

[eluser]mandril[/eluser]
anyone plis ?

[eluser]the_namdeeW[/eluser]
When using a LIKE in a SQL query I think you need to wrap your variable within %%.
Try it like this <code>LIKE %p2.tags%</code>

[eluser]xzela[/eluser]
You've got some interesting SQL there. I can help you make the SQL statement run better by not concatenating tables.

Try something like this:

SQL:

Code:

SELECT  
  p1.id,
  p1.name
FROM
  product_p1,
WHERE
  p1.id='".$idProd."'
  AND p1.id NOT IN (SELECT id FROM p2)
  AND p1.tags = p2.tags --//the like is unnecessary here (you're comparing two columns directly)

You may not be able to like two column like this.
Active Record:

Code:

$this->db->select('p1.id, p1.name');
$this->db->from('product_p1');
$this->db->where('p1.id', $idProd);
$this->db->where('p1.id', 'NOT IN (SELECT id FROM p2', FALSE);
$this->db->where('p1.tags', 'p2.tags');
$query = $this->db->get();

[eluser]mandril[/eluser]

Code:

AND p1.tags = p2.tags --//the like is unnecessary here (you're comparing two columns directly)

The like is necessary because i don't want to compare the fields.. i want to know if the have tags in common..

PRODUCT 1 has tags: cellphone apple touchscreen iphone
PRODUCT 2 has tags: music ipod touchscreen

so it will relate them with the touchscreen tag.. the field call tags in the product table have lots of tags separated by blank spaces.

I will check your solution, thnx for responding

[eluser]xzela[/eluser]
OK,
Just a heads up, creating a column to store multi-strings of data is not a best way to go about it. As now you are forced to create methods of extracting that data (for loops, if statements, etc...) in your PHP code. It will be very difficult to create a SQL statement to do the same thing. Plus, the next guy who has to maintain your code will hate you forever!

You should really create two tables for this:
tag_table:
-*tag_id //primary_key
-tag_name

product_tag_table:
-*p_tag_id //primary_key
-p_id
-tag_id
(this table actually doesn't need a primary key, but for this example we'll just leave it here)

The product_tag_table holds the relation ship between the products and the tags.

If you have this type of database structure you can use a simple select to get the information you want:

Code:

SELECT
  product_table.name,
  tag_table.tag_name
FROM product_tag_table
LEFT JOIN product_table ON product_tag_table.p_id = product_table.id
LEFT JOIN tag_table ON product_tag_table.tag_id = tag_table.tag_id
WHERE
  product_table.id = 9328;

This will return all of the tags that have a relationship with this product.

Anyway, just a suggestion.

good luck on your site.

[eluser]mandril[/eluser]
yeah i do that with more relevant information, maybe i should do it for tags too.. i know that relational data bases definition is to do what i didn't (in case of the tags) .. i did do that for the phones:

user_id, name , bla bla..

user_id, phone1, phone2, phone3

I understand your point and thank you for the advice, i just thought i could handle it as if it was a product name... you know.. Motorola v3 im not going to make a table for motorola to then get the v3 value so i thought a tag field for 3 or 4 tags it could work.

I also think my SQL statment(the first i post) is right.. and LIKE is not working even if i add % % =( ... maybe i end up doing another table for the tags.

Sorry if i sound roud or bad i speak spanish but i appreciate your help a lot and your dedication :-D

Just one more thing.. image you have to relate products by their name and the product does have multi strings, like this

"iphone 3G 8GB"
"toshiba satellite pro 324234"
"logitech mouse evolution 2008"

how would you do it ? i guess you are not going to create a table and store the first part of the product name in one and the rest in another.. :S

Thnx for everything