SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot DBallerz01's Avatar
    Join Date
    Jan 2004
    Location
    CT
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I need help building a query

    I need to group 'votes' from one table against another table and then be able to get info on the links from each group. I can't figure out how to group the data for 'sum' then run it against the groups table and populate 3 columns. This is what I have so far:
    Code:
    $y = mysql_query("SELECT item_id, sum(star) AS sum from starsrate_votes group by item_id ORDER by sum desc") or die(mysql_error());
    while ($y1 = mysql_fetch_row($y)) {
    
    
    $b = mysql_query("SELECT url,title,id,category from links where category = '$a1[0]' AND approved = '1' LIMIT 3")or die(mysql_error());
    $b1 = mysql_fetch_row($b);
    $a = mysql_query("SELECT id,groupname, time from groups where approved = '1' AND category  order by time limit 3")or die(mysql_error());
    	
    
    while ($a1 = mysql_fetch_row($a)) {
    	
    $b = mysql_query("SELECT url,title,id from links where category = '$a1[0]' AND approved = '1' LIMIT 3")or die(mysql_error());
    
    echo "
    <div class=\"cell\">
    <div class=\"list_heading\" align=\"center\"><b>$a1[1]</b></div>
    <div class=\"list\">
    <ol>";
    
    
    while ($b1 = mysql_fetch_row($b)) {
    
    	echo "<li> <a href=\"$b1[1]\">$b1[0]</a> <a href=\"$b1[2]\"><img src=\"images/info.gif\"></a> <a onClick=\"return overlay(this, 'subcontent')\" href=\"$b1[2]\"></li>";
    	}
    The example is at http://websbests.com, it's the 1 row, 3 columns under the Top 3 category.


    starsrate_vote
    Code:
    CREATE TABLE starsrate_votes (
      id int(11) NOT NULL auto_increment,
      item_id int(11) default NULL,
      ip varchar(20) default NULL,
      dt datetime default NULL,
      star int(11) default NULL,
      PRIMARY KEY  (id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
    
    -- 
    -- Dumping data for table 'starsrate_votes'
    -- 
    
    INSERT INTO starsrate_votes VALUES (1, 1, '24.2.249.72', '2007-05-30 20:54:06', 3);
    INSERT INTO starsrate_votes VALUES (2, 1, '24.2.249.72', '2007-06-02 13:43:57', 5);
    INSERT INTO starsrate_votes VALUES (3, 2, '24.2.249.72', '2007-06-02 13:46:48', 5);
    INSERT INTO starsrate_votes VALUES (4, 2, '68.1.186.73', '2007-06-02 13:53:35', 5);

    links table

    Code:
    CREATE TABLE `links` (
      `id` int(11) NOT NULL auto_increment,
      `category` int(11) NOT NULL default '0',
      `title` varchar(255) NOT NULL default '',
      `url` varchar(255) NOT NULL default '',
      `approved` int(1) NOT NULL default '0',
      `added_by` int(11) NOT NULL default '0',
      `reason` text NOT NULL,
      `pros` text NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    -- 
    -- Dumping data for table `links`
    -- 
    
    INSERT INTO `links` VALUES (1, 1, 'http://www.mplonsky.com/photo/', 'Fine Art Photography', 1, 1, 'I decided to submit this website because of the beautiful angels of the photographs, it really takes my breath away.', '');
    INSERT INTO `links` VALUES (2, 1, 'http://flickr.mathewvp.com/', 'MathewVP', 1, 1, 'Very Interesting Flickr album.', '');
    INSERT INTO `links` VALUES (3, 1, 'http://www.timecatcher.com/', 'TimeCatcher', 1, 1, 'Very pretty photos of the earth we walk on.', '');
    and groups table
    Code:
    CREATE TABLE `groups` (
      `id` int(11) NOT NULL auto_increment,
      `groupname` varchar(255) NOT NULL default '',
      `groupdescription` text NOT NULL,
      `added_by` int(11) NOT NULL default '0',
      `approved` int(1) NOT NULL default '0',
      `pos_votes` int(11) NOT NULL default '0',
      `neg_votes` int(11) NOT NULL default '0',
      `time` int(11) NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;
    
    -- 
    -- Dumping data for table `groups`
    -- 
    
    INSERT INTO `groups` VALUES (1, 'Best Online Image', 'A Group describing the webs best images.', 2, 1, 0, 0, 1180293673);
    Any help is GREATLY appreciated. I have no idea how to construct this query.
    http://websbests.com-Top Rated Sites Of The Web Brought By YOU!
    (submit your site guys)

    http://howology.com-Explaining How To Do Stuff ...One Step A Time

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    is that a doubly nested loop with a SELECT at each of loop?

    for those of us who don't do php, could you say in words what your script does, and what's wrong with it? i mean, other than doing queries inside loops
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot DBallerz01's Avatar
    Join Date
    Jan 2004
    Location
    CT
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is that a doubly nested loop with a SELECT at each of loop?

    for those of us who don't do php, could you say in words what your script does, and what's wrong with it? i mean, other than doing queries inside loops
    The script displays three columns (groups) and then selects the links by the the 'group'. I need this script to be sorted by the amount of votes added up in each links and then display them sorted by group. Pretty confusing.
    http://websbests.com-Top Rated Sites Of The Web Brought By YOU!
    (submit your site guys)

    http://howology.com-Explaining How To Do Stuff ...One Step A Time


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
  •