[SOLVED] Mysql Query Help

[venom792001]

Im having trouble getting this query to execute im using mysql 5 and this code was created 3 years ago.

 

            $this->count_sites = $this->base->db->fetch_id('SELECT COUNT(s.id) FROM @sites AS s, @users as u, @emails as e WHERE s.user = u.id && s.email = e.id && u.blocked = 0 && s.active = 1 && s.admin_validate = 1 && e.validated = 1'.$where);

            // If this query were a person it would be Rik Waller             
            $this->db_resource = $this->base->db->query('
                SELECT s.*,COUNT(DISTINCT c.id) as comment_count, AVG(v.value) as rating,COUNT(DISTINCT v.id) as num_votes'.$additional_select.'
                FROM @sites AS s, @users AS u, @emails AS e
                '.$votes_join.'
                LEFT JOIN @comments AS c ON c.site = s.id && c.admin_validate = 1
                LEFT JOIN @votes AS v ON v.active = 1 && v.site = s.id
                WHERE s.user = u.id && s.email = e.id && u.blocked = 0 && s.active = 1 && s.admin_validate = 1 && e.validated = 1'.$where.' 
                GROUP BY s.id
                ORDER BY '.$order_by_parameter.'
                LIMIT ' . $this->offset . ',' . $this->search_limit
            );   
        }

 

and it gives me this error.

 

Unknown column 's.id' in 'on clause'

Query Executed: SELECT s.*,COUNT(DISTINCT c.id) as comment_count, AVG(v.value) as rating,COUNT(DISTINCT v.id) as num_votes, 0 as already_voted FROM tsl_sites AS s, tsl_users AS u, tsl_emails AS e INNER JOIN tsl_comments AS c ON c.site = s.id && c.admin_validate = 1 INNER JOIN tsl_votes AS v ON v.active = 1 && v.site = s.id WHERE s.user = u.id && s.email = e.id && u.blocked = 0 && s.active = 1 && s.admin_validate = 1 && e.validated = 1 GROUP BY s.id ORDER BY in_count DESC, rating DESC, out_count DESC, hit_count DESC, name DESC LIMIT 0,20

 

Any idea on what i need to fix in the query im assuming its not valid for mysql 5 and im pretty new to php/mysql and learning more each day but im stumped.

 

Thanks to anyone who can help.

[derwert]

It's saying the column id doesn't exist in your tsl_sites table.

[venom792001]

Yeah thats the first thing i checked and its in the tsl_sites table, i know it works in mysql 4 because i installed mysql 4  on my localhost and it works fine

 

I made sure the column "id" was in the table before posting so i wouldnt look like an idiot lol

 

this is the query

 

CREATE TABLE `tsl_sites` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `user` mediumint( unsigned NOT NULL default '0',
  `email` mediumint( unsigned NOT NULL default '0',
  `guest_email` tinyint(1) NOT NULL default '1',
  `show_email` tinyint(1) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `category` smallint(5) unsigned NOT NULL default '0',
  `category_validated` tinyint(1) NOT NULL default '1',
  `last_category` smallint(5) unsigned NOT NULL default '0',
  `webring` tinyint(1) NOT NULL default '0',
  `banner` varchar(255) default NULL,
  `banner_size` varchar(10) NOT NULL default '',
  `in_count` mediumint( unsigned NOT NULL default '0',
  `out_count` mediumint( unsigned NOT NULL default '0',
  `out_total` mediumint( unsigned NOT NULL default '0',
  `hit_count` mediumint( unsigned NOT NULL default '0',
  `archive_in` mediumint( unsigned NOT NULL default '0',
  `archive_out` mediumint( unsigned NOT NULL default '0',
  `archive_hits` mediumint( unsigned NOT NULL default '0',
  `vote_count` mediumint( unsigned NOT NULL default '0',
  `archive_votes` mediumint(9) NOT NULL default '0',
  `admin_validate` tinyint(1) NOT NULL default '0',
  `active` tinyint(1) NOT NULL default '0',
  `last_position` mediumint( unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category` (`category`),
  KEY `user` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

 

and it was successfull and id was there not sure what the correct mysql 5 query would be

 

Thanks for replying!

[derwert]

I found the cause, in MySQL version 5.0.12 they made some changes to make the syntax more SQL2003 compatible; in turn they've broke numerous queries that once worked in previous versions.

 

See the section titled "Join Processing Changes in MySQL 5.0.12"

http://dev.mysql.com/doc/refman/5.0/en/join.html#id2841428

[fenway]

I found the cause, in MySQL version 5.0.12 they made some changes to make the syntax more SQL2003 compatible; in turn they've broke numerous queries that once worked in previous versions.

 

See the section titled "Join Processing Changes in MySQL 5.0.12"

http://dev.mysql.com/doc/refman/5.0/en/join.html#id2841428

They didn't "break it" -- the comma operator was always a bad idea