MySql error: CMS won't display or analyze raw data
Closed this issue · 4 comments
GoogleCodeExporter commented
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 commented
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 commented
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 commented
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 commented
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