Page 1 of 2
Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 9:54 am
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:00 am
by Celauran
Where is $row->title defined? Have you echoed the query to see what's actually being sent to the SQL server?
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:11 am
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:17 am
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%'";
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:23 am
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??
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:34 am
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%'
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:39 am
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?
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:41 am
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:44 am
by simonmlewis
Sorry what first option?
I also thought "Match Against" would work, but can never get that right.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:47 am
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 10:49 am
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 12:02 pm
by mikosiko
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 12:07 pm
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.
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 12:47 pm
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?
Re: Basic query not working: where A is LIKE '%B%'...
Posted: Tue Nov 08, 2011 1:02 pm
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))