I need help building a query

[DBallerz01]

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:

$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

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

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

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.

[r937]

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

[DBallerz01]

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.