I would try something like this... SELECT COUNT(tececo_stats.id) as num_hits, meta_data.title FROM tececo_stats, meta_data WHERE meta_data.id = tececo_stats.page_id GROUP BY tececo_stats.page_id ORDER BY num_hits DESC
/Joakim > -----Original Message----- > From: JJ Harrison [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 09, 2002 11:07 AM > To: [EMAIL PROTECTED] > Subject: Re: [PHP] Grr SQL syntax error silghtly OT > > > table structure for tececo stats(Stores information about the > visitors to my > site.visited is either 0 or 1 depending on whether or not the > stats cookie > has been set): > > id int(11) unsigned NOT NULL auto_increment, > page_id int(11) NOT NULL default '0', > visited int(11) NOT NULL default '0', > time int(11) NOT NULL default '0', > remote_dns varchar(100) NOT NULL default '', > remote_ip varchar(15) NOT NULL default '', > referer varchar(200) NOT NULL default '', > browser varchar(100) NOT NULL default '', > system varchar(100) NOT NULL default '', > PRIMARY KEY (id), > KEY page_id (page_id,time) > > table structure for meta_data(contains information about the > pages in my > website): > > id int(11) unsigned NOT NULL auto_increment, > pid int(11) unsigned NOT NULL default '0', > title varchar(200) NOT NULL default '', > page_name varchar(75) NOT NULL default '', > description text NOT NULL, > keywords text NOT NULL, > PRIMARY KEY (id), > KEY pid (pid) > > main file(required.php automaticly does a DB connect and is > used elsewhere > so I know is not the problem). > <? > include "includes/required.php"; > do_html_header('Page Detail Statistics'); > > $query = "select > count(tececo_stats.*) as tececo_stats.views, meta_data.title > from > meta_data, tececo_stats > where > meta_data.id = tececo_stats.id > order by meta_data.id > group by meta_data.id"; > $result = mysql_query($query) or die("Query failed: $query<br>" . > mysql_error()); > $num_results = mysql_num_rows($result); > ?> > <table width="500"> > <tr style="black_row"><td width="200">Page Name</td><td>Tota;Number of > Sessions</td></tr> > <? > for ($i=0; $i < $num_results; $i++) > { > $row = mysql_fetch_array($result); > echo > '<tr><td>'.$row['meta_data.title'].'</td><td>'.$row['tececo_st > ats.views'].'< > /td></tr>'; > } > ?> > </table> > <? > do_html_footer(); > ?> > > error message: > Query failed: select count(tececo_stats.*) as tececo_stats.views, > meta_data.title from meta_data and tececo_stats where meta_data.id = > tececo_stats.id order by meta_data.id > You have an error in your SQL syntax near '*) as tececo_stats.views, > meta_data.title from meta_data and tececo_stats whe' at line 2 > > What I want to do: This is essentially a script for a stats > program that I > am writting that returns the number of hits for each page. Instead of > looping a query I decieded to try and join the two tables to > make it more > efficiant. I am trying to get it to work with hits now than > make it sessions > later. > > I hope this is enough info and thank you for your help. > > > -- > JJ Harrison > [EMAIL PROTECTED] > www.tececo.com > > > "Joakim Andersson" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > I assume that 2count should really be count and 2 is just > the line-number > > you added in this post... > > > > tececo_stats.views is (probably) the name of a column and > cannot be used > as > > an alias. Change it to something else. > > change count(*) to count(tececo_stats.*) (I think that's > what you want) > > And you probably need a group by-statement at the end: GROUP BY > > whatever_you_need_to_group_by > > > > It's really difficult to answer your questions without the > table designs, > > the error message and what you expect this query to do. > And, it's not > > slightly OT. It's totally OT. :-) > > > > Regards > > Joakim Andersson > > > > > > > -----Original Message----- > > > From: JJ Harrison [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, July 09, 2002 10:10 AM > > > To: [EMAIL PROTECTED] > > > Subject: [PHP] Grr SQL syntax error silghtly OT > > > > > > > > > Sorry :} > > > > > > I get a SQL syntax error at line two of the query: > > > > > > $query = "select > > > 2count(*) as tececo_stats.views, meta_data.title > > > from > > > meta_data, tececo_stats > > > where > > > meta_data.id = tececo_stats.id"; > > > > > > I have stared at this 'till i felt dizzy. can someone tell me > > > what I am > > > doing wrong so that I can learn from it? > > > > > > Thanks in advance > > > > > > > > > -- > > > JJ Harrison > > > [EMAIL PROTECTED] > > > www.tececo.com > > > > > > > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
