MySql error: CMS won't display or analyze raw data

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Using the tracking report to view/analyze the log data
2.
3.

What is the expected output? What do you see instead?

The expected output is the raw log data. What appears instead is the following 
error:

Notice: You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'AS record, 
smt2_cache.* AS cache, smt2_browsers.name AS browser, smt2_os.name AS' at line 
1 in C:\Program Files\xampp\htdocs\smt2\admin\sys\functions.db.php on line 32

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in 
C:\Program Files\xampp\htdocs\smt2\admin\sys\functions.db.php on line 49
Error: User log #28 was not found on database.

What version of the product are you using? On what operating system?

Operating System: Windows XP
Bundle: Xampp
Smt2: 2.0.2

Please provide any additional information below.

Original issue reported on code.google.com by arapakis...@gmail.com on 20 Mar 2012 at 5:02

[GoogleCodeExporter]

Are you using version 2.0.2 from SVN trunk?

It seems that you are calling `db_select($column, $table, $condition)` instead 
of `db_select($table, $column, $condition)` in functions.db.php. 

Please verify, as this issue should not happen in the trunk version:
http://code.google.com/p/smt2/source/browse/trunk/admin/sys/functions.db.php
http://code.google.com/p/smt2/source/browse/trunk/admin/ext/tracking-report/anal
yze.php 

Original comment by luis.lei...@gmail.com on 20 Mar 2012 at 6:10

[GoogleCodeExporter]

I am using 2.0.2 from SVN trunk. It appears that the problem is caused in 
analyze.php and sql.php, in the sections that the sql query is constructed. 
Apparently, associating an alias with multiple columns is causing the error:

=== Original code ===
$log = db_select($r." LEFT JOIN ".$c." ON ".$r.".cache_id = ".$c.".id LEFT JOIN 
".$b." ON ".$r.".browser_id = ".$b.".id LEFT JOIN ".$o." ON ".$r.".os_id = 
".$o.".id", $r.".* AS record, ".$c.".* AS cache, ".$b.".name AS browser, 
".$o.".name AS os", TBL_PREFIX.TBL_RECORDS.".id = '".$id."'");
=====================

This one works..

=== Modified code ===
    $log = db_select($r." LEFT JOIN ".$c." ON ".$r.".cache_id = ".$c.".id LEFT JOIN ".$b." ON ".$r.".browser_id = ".$b.".id LEFT JOIN ".$o." ON ".$r.".os_id = ".$o.".id", $r.".*, ".$c.".*, ".$b.".name AS browser, ".$o.".name AS os", TBL_PREFIX.TBL_RECORDS.".id = '".$id."'");
=====================

Original comment by arapakis...@gmail.com on 20 Mar 2012 at 7:22

[GoogleCodeExporter]

The alias is used in each left join for convenience, to avoid collisions when 
various columns in various tables have the same name (e.g. all joined tables 
have an "id" column). However, in analyze.php $log['id'] is not needed, so 
there is no risk of collision and in effect you can safely remove the aliases.

I don't remember if there are other scripts that access the id column from 
other tables in the same SQL query, so right now I cannot remove those aliases 
from the code. BTW, you are the first one in reporting that issue.

I'll leave this issue open until the next version 2.1.0 is released.

Original comment by luis.lei...@gmail.com on 21 Mar 2012 at 9:14

[GoogleCodeExporter]

Eventually alias were removed, since they are no longer needed in other scripts.
Therefore I'm closing this issue, as it is solved in current version (2.1.0).

Original comment by luis.lei...@gmail.com on 31 Mar 2012 at 6:21

• Changed state: Verified