Results 1 to 10 of 10
March 19th, 2009, 04:27 PM #1Using an array from a mysql field in a select statement
I thought I had my head wrapped around this, but apparently not.
I have a mysql table called merchants. The table has fields like name, network and keywords.
Name network keywords
Sears gan appliances, electronics, clothing, garden
Vanns sas appliances, electronics
ChecksUnlimited sas checks, checkbooks,deposit slips
Best Buy cj appliances, electronics, music,video games
4checks sas checks, checkbooks, stuffed animals
For example, if the merchant is Sears, the second select would find Vanns and Best Buy as they share one or more keywords, but would not return Checksunlimited or 4checks as they don't have any keywords in common.
I thought exploding the field keyword based on the comma would create a usable array for a select query, but then I found an example somewhere that said you have to implode the array with a comma separator to make it readable by to select query.
What I've got at this point
From the first select that gets the featured merchant
$relatedmerchants = $query_data['keyword'];
Here is the current query, I don't get an error and when I try to var_dump $relatedmerchants it doesn't show anything. Could I be losing the data from the 'keyword' field somewhere along the way?
$query_related = "select name FROM merchants WHERE keywords IN('$relatedmerchants')";
March 19th, 2009, 04:52 PM #2
First off, if you use single quotes inside your mysql statement, it's going to treat everything inside those quotes as flat text rather than using the value of the variable.
You may want to try something like this:
$query_related = "select name FROM merchants WHERE keywords IN('".$relatedmerchants."')";
See if that helps.
March 19th, 2009, 05:35 PM #3
No luck with that format, which I think I had tried previously. And you're right that is the way it should be.
I did a var_dump of $query_related and it looks like the query is forming correctly for 4checks as this example.
string(94) "select * FROM merchants WHERE keywords IN('checks',' plush',' stuffed animals',' blank checks')"
March 19th, 2009, 05:53 PM #4
Try removing the spaces...
$relatedmerchants = str_replace(" " , "" , $relatedmerchants);
March 19th, 2009, 06:03 PM #5
Thanks, that didn't solve the problem although it's a good idea to add it. I made a change to it by adding a single quote " ' " to avoid it collapsing multi-word keywords like 'stuffed animals' into 'stuffedanimals'.
March 19th, 2009, 06:20 PM #6
Don't know. It should work. Try the same query in phpmyadmin to see what happens.
March 19th, 2009, 06:37 PM #7Originally Posted by SeymourButts
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 sec)
March 19th, 2009, 07:22 PM #8
I got it working using the like operator and imploding with "%' or '%" using the query like '%$relatedmerchants%'. It's not the most elegant solution, so if anyone has a better way I'm still open. But at least it works and I can move on to the next part of development.
Thanks SB and Eric for your input and help in thinking this through.
March 19th, 2009, 07:37 PM #9
You won't find matches that way because when you use "where keywords in(1, 2, 3)" you're asking for a row where the keywords value is exactly 1, 2 or 3. Since your keywords fields are never exactly 1, 2 or 3 you'll never get a match. What you want to do is use a fulltext index on your keywords field and do something like this:
select *, match(keywords) against('$relatedmerchants') as Relevance from table where match(keywords) against('$relatedmerchants') order by Relevance desc
- ScottHatred stirs up strife, But love covers all transgressions.
May 9th, 2010, 10:16 PM #10
Using the percent sign is the wrong way. It looks like it works, but it doesn't. You put quotes with your commas so sql knows to treat it as text, not numbers (or worse). You can make your life easier with a bit of debugging. Write down what you want to get to, such as
select * from fruit where type in ('apples', 'oranges', 'pears')
that's a correct sql statement, looking for a series of string/text values that are in the column 'fruit'.
Then, work backwards. Building a sql string from concatenation, you'll do this:
sql = "select * from fruit where type in (" +
"'apples', 'oranges', 'pears'" +
-- pay attention to the use of single and double quotes here--
sql = "select * from fruit where type in (" +
You'll have to make sure you format $myvariable with proper quotes and commas. You're on the right track. Don't forget the leading and trailing quotes if you just focus on exploding/imploding on the commas.
After you think its right, echo the sql back out to the screen and paste it into your sql editor for testing.
By Kevin in forum Programming / Datafeeds / ToolsReplies: 7Last Post: January 27th, 2009, 11:21 PM
By Uncle Rico in forum Programming / Datafeeds / ToolsReplies: 3Last Post: December 12th, 2008, 11:37 AM
By sweetiowa in forum WebMerge (Fourthworld.com)Replies: 3Last Post: October 13th, 2007, 08:30 PM
By suzie250 in forum Programming / Datafeeds / ToolsReplies: 5Last Post: January 17th, 2006, 09:37 PM
By Doug247 in forum Programming / Datafeeds / ToolsReplies: 1Last Post: July 30th, 2004, 12:01 PM