Chap 9 - problems using MySQL versions prior to version 5.

kenj · September 25th, 2010, 07:26 PM

If you are using versions of MySQL prior to
version 5, you will have a problem. The problem is that
the rows "Unique Visitors" and "Top 10 IP Addresses"
will be empty.

The download code for report.php is using
aggregate functions in the ORDER BY clauses.
MySQL does not allow this in versions prior
to MySQL version 5.

You can workaround the problem by aliasing the aggregate in
the select statement.

Code:

Select  count(IP_ADDRESS) as theCount, ....
....
ORDER BY theCount ...

The result will be that you can use this for all versions of MySQL.

You will need to change this in 2 places.

1. Chapter 9, page 257, lines 2-18
2. Chapter 9, page 257, 4th line up from the
bottom, it has about 16 lines for the query statement.

THE DETAILS

The changed code for the first one is as follows. The
second one is similar. Note, my code is slightly different
in that I don't use DB_TBL_PREFIX.

Code:

$query = sprintf('
    SELECT
        COUNT(IP_ADDRESS) AS theCount,
        INET_NTOA(IP_ADDRESS) AS IP_ADDRESS
    FROM
        SITE_ACCESS SA
    WHERE
        DATE(ACCESS_TIME) BETWEEN
            "%d-%02d-01" AND
            "%d-%02d-01" + INTERVAL 1 MONTH - INTERVAL 1 DAY
    GROUP BY
        SA.IP_ADDRESS
    ORDER BY
        theCount DESC',
    $full_year,
    $num_month,
    $full_year,
    $num_month);

MY CONFIGURATION

I am using Wrox download code
MySQL server version 4.1.22
UNIX: FreeBSD 4.10