SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast PlayStone's Avatar
    Join Date
    Jan 2004
    Location
    Canada
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data sorting based on multiple db columns.

    Hi,

    I am working on a search engine for my site, and I came to a halt at this point.
    This is my table structure.

    Code:
    TABLE `sites` (
      `ID` int(11) NOT NULL auto_increment,
      `site_name` varchar(100) default '',
      `site_description` varchar(255) default '',
      `type1` varchar(100) default '',
      `type2` varchar(100) default '',
      `type3` varchar(100) default '',
      `type4` varchar(100) default '',
      `type5` varchar(100) default '',
      `type6` varchar(100) default '',
      `type7` varchar(100) default '',
      `type8` varchar(100) default '',
      `type1_rating` int(4) default '0',
      `type2_rating` int(4) default '0',
      `type3_rating` int(4) default '0',
      `type4_rating` int(4) default '0',
      `type5_rating` int(4) default '0',
      `type6_rating` int(4) default '0',
      `type7_rating` int(4) default '0',
      `type8_rating` int(4) default '0',
      PRIMARY KEY  (`ID`),
      FULLTEXT KEY `forsearch` (`site_name`,`site_description`,`type1`,`type2`,`type3`,`type4`,`type5`,`type6`,`type7`,`type8`)
    ) TYPE=MyISAM AUTO_INCREMENT=483 ;
    What my site does is ranking of sites, so each site can be ranked on up to 8 parameters, and each parameter gets a rating, as specified in my database.
    I can already do a FULLTEXT search and it works well. Now I have to handle my sorting and this is where the problems begin.

    This is my main query:
    $results = mysql_query("SELECT * FROM $db_tbl WHERE MATCH(site_name, site_description, type1, type1, type1, type1, type1, type1, type1, type1, type1, ) AGAINST ('$query') LIMIT $page, $limit"); }

    How do I create a sorting method based on my type ratings? So if I searched for "racing", any records on my database with "racing" on any of the types would be sorted based on the matched type rating??

    Thanks a lot.

  2. #2
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Milano
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all let me say that you may consider an alternative form of storing your datas, more flexible and customizable (only if your project is still in the project phase ).
    So my suggestion is to separete your table in 3 tables related.
    Code:
    SITE table
    id
    site_name
    site_description
    
    TYPE_RATING table
    id
    type_rating
    
    RATING
    site_id
    type_rating_id
    rating
    Your query should be quite easy:
    Code:
    SELECT site_name, site_description, type_rating, rating FROM site LEFT JOIN rating ON site.id = rating.site_id LEFT JOIN type_rating ON rating.type_rating_id = type_rating.id WHERE MATCH (type_rating) AGAINST ('$mystring') ORDER BY type_rating, rating DESC LIMIT $page, $limit ;
    I know that I've not answered directly to your question but I think that the solution described is good to be considered.

  3. #3
    SitePoint Enthusiast PlayStone's Avatar
    Join Date
    Jan 2004
    Location
    Canada
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Ponticelli, for the suggestion. But I want to make sure you know what I am trying to do.

    Let's say that my table fields that the following values. I'm just using some of the fields to keep it simple.

    Code:
    SITE1
    site_name='mysite'
    type1='car'
    type2='aircraft'
    type3='construction'
    type1_rating='23'
    type2_rating='40'
    type3_rating='34'
    
    SITE2
    site_name='coolsite'
    type1='jet'
    type2='bus'
    type3='car'
    type1_rating='35'
    type2_rating='22'
    type3_rating='66'
    When I search for "car" it will find that the two sites match the query. That's fine. But how do I sort the results is the value found is in different fields?
    On SITE1 I will use type1_rating and on SITE2 I will use type3_rating to sort!

    I really appreciate any help on this.
    Thanks.

  4. #4
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester, UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Playstone,

    From what I've read I think ponticelli has solved your problem.

    The key to it is the:

    "ORDER BY type_rating, rating DESC LIMIT $page, $limit ", at the end of the select statement.

    If you read the order function in the mysql documentation / manual you can understand how it works?

    I'm working on something similar - where you pull out of mysql your table and if you click on the table headings it will resort the data with the order function.

    I've made this work, but what I can't get to work is where your table results from a search. Which is set to a set variable Eg $search. When you click on the column headings it just brings up an error message saying I haven't declared what $search is.

    Does anybody know how I can do this?

    Thanks

    t

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    timbo, when you have a new question, it's okay to start a new thread instead of tacking it onto a old thread

    your question isn't really a mysql question, i suggest you ask it in the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Manchester, UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OOOPs
    soz -
    I'm a newbie - 'scuse my ignorance.

    T

    p.s. still can't work out how to start a new thread! Doh

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it's okay, everybody was new once

    go to the php forum, and look for the New Thread button part way down the left side

    or use the Post a New Thread option in the Forum Tools dropdown
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •