Results 1 to 10 of 10
  1. #1
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Using 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.
    i.e.

    PHP Code:
    Name                  network               keywords
    Sears                    gan      appliances
    electronicsclothinggarden
    Vanns                   sas      appliances
    electronics
    ChecksUnlimited         sas      checks
    checkbooks,deposit slips
    Best Buy                cj        appliances
    electronicsmusic,video games
    4checks                sas     checks
    checkbooksstuffed animals 
    I've got the query that selects a row and gets all the information for the merchant. I'm trying to take the field 'keywords' and use it in another select that will find other merchants with one or more of the same keywords.

    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
    PHP Code:
    $relatedmerchants $query_data['keyword']; 
    Then I do this preparing for the second select query
    PHP Code:
    $relatedmerchants=explode(",",$relatedmerchants);
    $relatedmerchants=implode("','",$relatedmerchants); 
    You'll notice on the implode I've added a single quote around the comma, not sure why, but that is what was in the example code I found.

    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?

    PHP Code:
    $query_related "select name FROM merchants WHERE keywords IN('$relatedmerchants')"
    I know that is a lot of code to go through. But at this point, I'm lost. I think it should work but it isn't. Any examples of how to make this second query would be much appreciated.
    Someday starts today
    Military Discounts

  2. #2
    Affiliate Manager EricTyler's Avatar
    Join Date
    September 11th, 2008
    Posts
    94
    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.

  3. #3
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Eric,
    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.
    PHP Code:
    string(94"select * FROM merchants WHERE keywords IN('checks',' plush',' stuffed animals',' blank checks')" 
    Looking at the result, perhaps the problem is the other records... spaces between the keywords and commas? Like checks , blank checks, that space between checks and the comma might not match in the query search... any way of combining LIKE with the IN search?
    Someday starts today
    Military Discounts

  4. #4
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Try removing the spaces...

    Code:
    $relatedmerchants = str_replace(" " , "" , $relatedmerchants);

  5. #5
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Seymour,
    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'.
    Someday starts today
    Military Discounts

  6. #6
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Don't know. It should work. Try the same query in phpmyadmin to see what happens.

  7. #7
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Quote Originally Posted by SeymourButts
    Don't know. It should work. Try the same query in phpmyadmin to see what happens.
    Excellent idea, It show that it returns an empty result.
    PHP Code:
    MySQL returned an empty result set (i.ezero rows). (Query took 0.0001 sec
    So the query is working, but it's not finding matches in the keyword field of other merchant records. If I change the query to search for only 1 keyword and not the entire set using like '%keyword%' it returns the merchants correctly. I think I need to find a way to use like and query the array one keyword at a time... although that seems very db intensive and wastes a lot of queries.
    Someday starts today
    Military Discounts

  8. #8
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    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.
    Someday starts today
    Military Discounts

  9. #9
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    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:

    Code:
    select *, match(keywords) against('$relatedmerchants') as Relevance from table where match(keywords) against('$relatedmerchants') order by Relevance desc
    This is nice because the closest matches with the most matching keywords will be returned first.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  10. #10
    Newbie Dave123's Avatar
    Join Date
    February 5th, 2010
    Location
    Seattle, WA
    Posts
    30
    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--
    and then

    sql = "select * from fruit where type in (" +
    $myvariable +
    ")"

    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.

    - Dave

  11. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Using PHP to pull a piece of a MySQL field
    By Kevin in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: January 27th, 2009, 11:21 PM
  2. Mysql Problem on Select
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: December 12th, 2008, 11:37 AM
  3. show select products on index page with IF statement
    By sweetiowa in forum WebMerge (Fourthworld.com)
    Replies: 3
    Last Post: October 13th, 2007, 08:30 PM
  4. LIKE '%$categoryid%' works = doesn't in select statement
    By suzie250 in forum Programming / Datafeeds / Tools
    Replies: 5
    Last Post: January 17th, 2006, 09:37 PM
  5. Help with PHP Select statement
    By Doug247 in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: July 30th, 2004, 12:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •