Basic query not working: where A is LIKE '%B%'...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

Code: Select all

$result = mysql_query("SELECT * FROM relate WHERE keyword LIKE '%$row->title%'");
while ($row = mysql_fetch_object($result))
{
echo "$row->keyword";
}	mysql_free_result($result);
Here's the scenario.
We run a backend DB Table where we put all manner of keywords relating to products on another web site.
ie, car, van, ford.

Consumer goes to a product page, and finds a Ford Truck.
At the bottom of this page, it should say "find keywords in Relate, where they are like the Product Title on this page.

So since the title is "Ford Truck", it should have found the "ford" entry.

The query above isn't doing that.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Basic query not working: where A is LIKE '%B%'...

Post by Celauran »

Where is $row->title defined? Have you echoed the query to see what's actually being sent to the SQL server?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

That's defined by the product page it is sitting in. It's an embedded query.
I don't know how to echo the query, sorry.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Basic query not working: where A is LIKE '%B%'...

Post by Celauran »

simonmlewis wrote:I don't know how to echo the query, sorry.

Code: Select all

echo "SELECT * FROM relate WHERE keyword LIKE '%$row->title%'";
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

Yes I can see part of the fault, but I think the query is the wrong way around.

The keyword of "truck" will never be LIKE '%Ford Truck%'.
It's the other way around = 'Ford Truck' can be LIKE '%ford%'.

But can you run that query the other way around??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Basic query not working: where A is LIKE '%B%'...

Post by Celauran »

Depending how your database is set up and what your aim is, I can see a few options. You could get a list of tags associated with the title product, then return a list of other products containing the same tags. You could also break the title into individual words and run your query against each.

Code: Select all

SELECT * FROM relate WHERE keyword LIKE '%Ford%' OR keyword LIKE '%Truck%'
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

How would that work? The $title could be "Ford F150 Truck Automatic in Black".
How would you split that up, and dynamically use it in the query?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Basic query not working: where A is LIKE '%B%'...

Post by Celauran »

Given the "Ford Truck" example, I had thought of perhaps using explode() with space as a delimiter. With "Ford F150 Truck Automatic in Black" it becomes a little more complex. The first option might be a better choice in this case.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

Sorry what first option?
I also thought "Match Against" would work, but can never get that right.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Basic query not working: where A is LIKE '%B%'...

Post by Celauran »

Celauran wrote:You could get a list of tags associated with the title product, then return a list of other products containing the same tags.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

Sorry I am really not with you.
I have one table with multiple rows of keywords. These are associated with another web site.
How do I make $title check to see if any of the words in that title, are in the "keyword" rows of the other table?

It sounds so simple - but doesn't seem to be.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Basic query not working: where A is LIKE '%B%'...

Post by mikosiko »

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

Not quite sure how this helps, as that is looking for a position.
I just want the query to looking for words in the Database, that are the same as words in the $title. Then to echo those words from the db.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Basic query not working: where A is LIKE '%B%'...

Post by mikosiko »

$title = "Ford F150 Truck Automatic in Black"
keyword = "Ford"

Code: Select all

SELECT * 
  FROM relate 
   WHERE INSTR($row->title, keyword) > 0   // > 0 means that the keyword 'Ford' is present at least 1 time in the $title
is that what you want or I misunderstood your goal?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic query not working: where A is LIKE '%B%'...

Post by simonmlewis »

How does it know the keyword is Ford? Or does that go through a loop till it finds something that is used at least once in title?
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\phpMyAdmin\site\includes\product.inc on line 437
Line 437 is:

Code: Select all

while ($row = mysql_fetch_object($result))
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply