SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Stuck with displaying accessories

    hi all

    There is field name "model" for all products in products table.

    there is one category "ACCESSORIES" in which "model" field has values like "e71,e72,e73,e74....." all together in one field, because some accessories are compatible with many phones.

    when e71 phone is displayed on the page, then i want to display accessories whose "model" field has values e71.

    here is what i have tried

    PHP Code:

    /* query to select all models from accessory table */

    $qry_model="select * from product_table where status='Y'and category_id=4";
    $qry_model_result=mysql_query($qry_model);
    while(
    $qry_model_row=mysql_fetch_array($qry_model_result))
    {
    $desc_model_all $qry_model_row['model'];
    $desc_model_all explode(','$desc_model_all); 
    }

    /* query to display accessories  */

    $qryc="select * from product_table where model LIKE '$desc_model_all[0]' and status='Y' and category_id=4"
    i m stuck with the limitation of my display query.

    because i will have to write
    PHP Code:
    '$desc_model_all[0]' '$desc_model_all[1]' and so on.... 
    vineet

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    So you want to find items where model contains e71 ?
    PHP Code:
    "SELECT .....  WHERE model LIKE '%e71%' AND status......" 
    NOTE: This will also find anything that contains models e710, or he71 or.... anything that contains the three characters 'e' '7' and '1' back to back.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    then what would i have to do so that it finds and display result for "e71" only

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    WHERE model = 'e71' OR model LIKE 'e71,%' OR model LIKE '%,e71' OR model LIKE '%,e71,%'
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    i m stuck with the limitation of my display query.
    You are plumbing the depths of what you can with arrays kept in a table, so perhaps you can now better understand the purpose of normalizing data in a database.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    WHERE model = 'e71' OR model LIKE 'e71,%' OR model LIKE '%,e71' OR model LIKE '%,e71,%'
    this is quite clumsy, yes?

    better to use the FIELD() function

    however, far better would be to normalize the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is quite clumsy, yes?

    better to use the FIELD() function

    however, far better would be to normalize the data
    Field works with column names? I was trying to do it with a single query rather than pulling all results and then filtering in a separate one. Yes, it's very clumsy, which IS why the data should be normalized.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


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
  •