SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: using LIKE

  1. #1
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using LIKE

    Hi,

    I'm working on a query that searches for similar IDs of different products.

    The problem is, the similar IDs are different in length for different brands.

    For instance...

    Some Brother Carts all start LC01, others start LC02, so I'm doing a LIKE search on the first 4 characters. HP carts all start C87, others start C48, so I'm doing a LIKE search on the first 3 characters.

    I'm hoping this is possible to work around instead of having to identify the brand first, then set up the number of characters to read in to based on the brand...

    Here's my SQL

    To work correctly for Brother (though this is the same for other carts)
    Code MySQL:
    SELECT product_ID, product_Name, product_ShortDescription, prdctImage_FileName FROM tbl_products as p LEFT JOIN tbl_prdtimages as img ON img.prdctImage_ProductID = p.product_ID WHERE product_MerchantProductID LIKE '%LC01%' AND product_ID != '5' GROUP BY product_ID

    To work correctly for HP (though this is also the same for other carts)
    Code MySQL:
    SELECT product_ID, product_Name, product_ShortDescription, prdctImage_FileName FROM tbl_products as p LEFT JOIN tbl_prdtimages as img ON img.prdctImage_ProductID = p.product_ID WHERE product_MerchantProductID LIKE '%C87%' AND product_ID != '18' GROUP BY product_ID

    Thanks for any help on this.
    No, I REALLY dislike having to use Joomla.

  2. #2
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's the problem with doing it the way you are? Are you returning products that aren't from that brand? If so, what are the product ids of the products?

    If you remove the first % sign, MySQL will force a match for the start of the product id (and also use indexes if possible I think).
    Code:
    LIKE 'C48%'
    MySQL v5.1.58
    PHP v5.3.6

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
    	product_ID,
    	-- Is second char numeric? Yes (HP), No (Brother)
    	(CASE
    		WHEN MID(product_ID, 2, 1) REGEXP('[0-9]')
    		THEN product_ID
    	END) AS hpbrandid,
    	(CASE
    		WHEN MID(product_ID, 2, 1) REGEXP('[a-z]')
    		THEN product_ID
    	END) AS brotherbrandid
    FROM tbl_products AS product
    LEFT JOIN tbl_prdtimages AS image
      ON(image.prdctImage_ProductID = product.product_ID)
    WHERE product_ID != 5

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Select Brother:
    Code:
    SELECT product_ID
         , product_Name
         , product_ShortDescription
         , prdctImage_FileName
      FROM tbl_products as p 
      LEFT
      JOIN tbl_prdtimages as img
        ON img.prdctImage_ProductID = p.product_ID
     WHERE (
               product_MerchantProductID LIKE 'LC01%'
            OR product_MerchantProductID LIKE 'LC02%'
           )
       AND product_ID != '5'
     GROUP
        BY product_ID
    Select HP:
    Code:
    SELECT product_ID
         , product_Name
         , product_ShortDescription
         , prdctImage_FileName
      FROM tbl_products as p 
      LEFT
      JOIN tbl_prdtimages as img
        ON img.prdctImage_ProductID = p.product_ID
     WHERE (
               product_MerchantProductID LIKE 'C87%'
            OR product_MerchantProductID LIKE 'C48%'
           )
       AND product_ID != '5'
     GROUP
        BY product_ID
    MySQL v5.1.58
    PHP v5.3.6

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brandon, he wants to identify the product ID by brand type, HP or Brother.

  6. #6
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    First, thank you for your replies.

    I've found a way to relate similar cartridges without having to do custom SQL.
    No, I REALLY dislike having to use Joomla.


Bookmarks

Posting Permissions

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