SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help me build rating query to add rating

    looking for an sql injection where I can display the total sum of each star that a item has been rated by users to better explain it I will post a pictures so you can see. I will also post a database dump so you guys have an idea of how the database is built.




    database dump

    --
    -- Database: `menu`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `rating`
    --

    CREATE TABLE IF NOT EXISTS `rating` (
    `item_name` varchar(100) NOT NULL,
    `ip_address` varchar(15) NOT NULL,
    `rating` tinyint(1) NOT NULL,
    `date_rated` date NOT NULL,
    PRIMARY KEY (`item_name`,`ip_address`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    The rating field is a number from 1 to 5 is recorded in there and it will vary according to the user rate. and that's the field I want to retrieve and sum all the 1 rating the 2 rating 3 ratings of item-name "X"

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    You should probably handle this in the application language just to make things easier.

    Select all the rows for a given item then loop through and increment the star based the rating value for each.

    PHP Code:
    $ratings array_fill(1,5,0);
    $rowsCount count($rows); // assume $rows is the result array

    for($i=0;$i<$rowsCount;$i++) {
        
    $ratings[$rows[$i]['rating']]++;


  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by co.ador View Post
    The rating field is a number from 1 to 5 is recorded in there and it will vary according to the user rate. and that's the field I want to retrieve and sum all the 1 rating the 2 rating 3 ratings of item-name "X"
    that's a very simple query, and you do ~not~ need to do this in your application code

    bringing all the detail rows into php and counting them there is very inefficient
    Code:
    SELECT rating
         , COUNT(*) AS rating_counts
      FROM rating
     WHERE item_name = 'X'
    GROUP
        BY rating
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    ^ correct – I had a brain fart there.

  5. #5
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $query 
    ="SELECT rating , COUNT(*) AS rating_counts FROM rating
     WHERE item_name = 'Nike Air Jordan' GROUP BY rating"
    ;

    $result mysql_query($query) or die ("Error in query: $query. ".mysql_error()); 

    // see if any rows were returned 
    if (mysql_num_rows($result) > 0) { 

    echo 
    '<h3>1 Star Active</h3>
    <ul class="rating onestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">['
    ,$result[2],']</li>
    </ul>
    <h3>2 Stars Active</h3>
    <ul class="rating twostar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>

        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[40]</li>
    </ul>
    <h3>3 Stars Active</h3>
    <ul class="rating threestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>

        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[60]</li>
    </ul>
    <h3>4 Stars Active</h3>
    <ul class="rating fourstar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>

        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[80]</li>
    </ul>
    <h3>5 Stars Active</h3>
    <ul class="rating fivestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>

        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[100]</li>
    </ul>'
    ;
    }
    ?>
    I have this so far but I don't know if I will have to repeat a query for each ul or will have to use an script like oddz i am lost.

    The index [2] is where the column of rating is and the index [0] is where the item_name is but i don't have any idea right now give a hand guys
    Last edited by co.ador; Jul 26, 2009 at 07:34.

  6. #6
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this thread has been moved I guess it needs some php script from now and on Help please the query is done.

  7. #7
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far i have tried to put both of your suggestion. I know i need an variable in item_name coming from the url, and a value for $rows n the for loop
    don't have any idea.
    PHP Code:
     <?php
    $query 
    ="SELECT rating , COUNT(*) AS rating_counts FROM rating
     WHERE item_name = 
    $shoename GROUP BY rating";

    $result mysql_query($query) or die ("Error in query: $query. ".mysql_error()); 

    // see if any rows were returned 
    if (mysql_num_rows($result) > 0) { 
    $ratings array_fill(1,5,0);
    $rowsCount count($result); // assume $rows is the result array

    for($i=0;$i<$rowsCount;$i++) {
        
    $ratings[$result[$i]['rating']]++;


    echo 
    '<h3>1 Star Active</h3>
    <ul class="rating onestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">['
    ,$result[2],']</li>
    </ul>
    <h3>2 Stars Active</h3>
    <ul class="rating twostar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>

        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[40]</li>
    </ul>
    <h3>3 Stars Active</h3>
    <ul class="rating threestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>

        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[60]</li>
    </ul>
    <h3>4 Stars Active</h3>
    <ul class="rating fourstar">
        <li class="one"><a href="#" title="1 Star">1</a></li>
        <li class="two"><a href="#" title="2 Stars">2</a></li>

        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[80]</li>
    </ul>
    <h3>5 Stars Active</h3>
    <ul class="rating fivestar">
        <li class="one"><a href="#" title="1 Star">1</a></li>

        <li class="two"><a href="#" title="2 Stars">2</a></li>
        <li class="three"><a href="#" title="3 Stars">3</a></li>
        <li class="four"><a href="#" title="4 Stars">4</a></li>
        <li class="five"><a href="#" title="5 Stars">5</a></li>
        <li class="total">[100]</li>
    </ul>'
    ;
    }

    ?>


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
  •