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_stats.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
