2. PHPRunner General questions
  3. php error in v9
This topic is locked

php error in v9

 		7/17/2016 1:33:26 PM
PHPRunner General questions
M
 merlingolden author

I have tested an application which execute the sql query correctly in v8.1 and shows php error in v9 there are two examples a list page, and a chart page below

///////////////////////////////
SQL

SELECT

@rownum:=@rownum+1 AS rnum,

t1.cid,

t1.syllabus_id,

t1.qid,

ROUND(t2.att_cnt*100/t1.tot_cnt, 2) AS tot_att,

ROUND(t2.crr_cnt*100/t2.att_cnt, 2) AS tot_crr,

100-ROUND(t2.crr_cnt*100/t2.att_cnt,2) AS tot_inc,

t2.rslt,

CONCAT(t2.my_marks, '/', t1.max_mark) AS my_marks,

t2.duration,

t1.did,

t2.my_marks AS my_mark,

t1.max_mark

FROM (SELECT

qr.rid,

qb.qid,

MAX(qb.syllabus_id) AS syllabus_id,

MAX(qb.cid) AS cid,

(SELECT

COUNT(r.rid)

FROM quiz_result AS r

WHERE r.quid = qz.quid

) AS tot_cnt,

qz.correct_score AS max_mark,

qb.did

FROM quiz AS qz

, qq_info AS qi

, qbank AS qb

, quiz_result AS qr

WHERE qz.quid = qr.quid AND qz.quid = qi.quid AND qi.qid = qb.qid

GROUP BY qr.rid, qb.qid

ORDER BY qb.cid, qb.qid

) AS t1

LEFT OUTER JOIN (

SELECT

tt2.rid,

tt1.qid,

tt1.att_cnt,

tt1.crr_cnt,

tt2.rslt,

tt2.my_marks,

tt2.duration

FROM (SELECT

qr.quid,

qi.qid,

COUNT(qra.qqid) AS att_cnt,

SUM(IF(qra.score=1, 1, 0)) AS crr_cnt

FROM quiz_result AS qr

, quiz_result_answer AS qra

, qq_info AS qi

WHERE qr.rid = qra.rid AND qra.qqid = qi.qqid

GROUP BY qr.quid, qi.qid

) AS tt1

LEFT OUTER JOIN (

SELECT

qr.quid,

qr.rid,

qi.qid,

IF(qra.score > 0, qra.score, IF(qra.score = 0, 0, -1)) AS rslt,

IF(qra.score = 1, qz.correct_score, IF(qra.score = 0, qz.incorrect_score, IFNULL(qra.score,0)*qz.correct_score)) AS my_marks,

qra.duration

FROM quiz_result AS qr

, quiz_result_answer AS qra

, quiz AS qz

, qq_info AS qi

WHERE qr.quid = qz.quid AND qr.rid = qra.rid AND qra.qqid = qi.qqid

) AS tt2 ON (tt1.quid = tt2.quid AND tt1.qid = tt2.qid)

) AS t2 ON (t1.rid = t2.rid AND t1.qid = t2.qid)

, (SELECT

@rownum:=0

FROM dumy

) AS r

ORDER BY rnum


Shows below error in V9
php error happened
Technical information

Error type 256

Error description 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 'LEFT OUTER JOIN ( SELECT tt2.rid, tt1.qid, tt1.att_cnt, tt1.crr_cnt' at line 3

URL localhost/Question_Report_list.php?rid=157

Error file H:\wamp\www\idea1_v9\output\connections\MySQLiConnection.php

Error line 142

SQL query select count(*) FROM (SELECT qr.rid, qb.qid, MAX(qb.syllabus_id) AS syllabus_id, MAX(qb.cid) AS cid, (SELECT COUNT(r.rid) FROM quiz_result AS r WHERE r.quid = qz.quid ) AS tot_cnt, qz.correct_score AS max_mark, qb.did FROM quiz AS qz , qq_info AS qi , qbank AS qb , quiz_result AS qr WHERE qz.quid = qr.quid AND qz.quid = qi.quid AND qi.qid = qb.qid GROUP BY qr.rid, qb.qid ORDER BY qb.cid, qb.qid ) AS t1 LEFT OUTER JOIN LEFT OUTER JOIN ( SELECT tt2.rid, tt1.qid, tt1.att_cnt, tt1.crr_cnt, tt2.rslt, tt2.my_marks, tt2.duration FROM (SELECT qr.quid, qi.qid, COUNT(qra.qqid) AS att_cnt, SUM(IF(qra.score=1, 1, 0)) AS crr_cnt FROM quiz_result AS qr , quiz_result_answer AS qra , qq_info AS qi WHERE qr.rid = qra.rid AND qra.qqid = qi.qqid GROUP BY qr.quid, qi.qid ) AS tt1 LEFT OUTER JOIN ( SELECT qr.quid, qr.rid, qi.qid, IF(qra.score > 0, qra.score, IF(qra.score = 0, 0, -1)) AS rslt, IF(qra.score = 1, qz.correct_score, IF(qra.score = 0, qz.incorrec
More info
Call stack

File: line Function Arguments

0. connections\MySQLiConnection.php:142 trigger_error 1. 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 'LEFT OUTER JOIN ( SELECT tt2.rid, tt1.qid, tt1.att_cn...;

  1. 256;

    1. connections\MySQLiConnection.php:142 MySQLiConnection->query 1. select count(*) FROM (SELECT qr.rid, qb.qid, MAX(qb.syllabus_id) AS syllabus_id, MAX(qb.cid) AS cid, (SELECT COUNT(r.rid) FROM quiz_result AS r WHERE r.quid = qz.quid ) AS tot_cnt, qz.cor...;

    2. connections\Connection.php:589 Connection->getFetchedRowsNumber 1. FROM (SELECT qr.rid, qb.qid, MAX(qb.syllabus_id) AS syllabus_id, MAX(qb.cid) AS cid, (SELECT COUNT(r.rid) FROM quiz_result AS r WHERE r.quid = qz.quid ) AS tot_cnt, qz.correct_score AS ma...;


  2. ;

    3. connections\MySQLiConnection.php:273 MySQLiConnection->getFetchedRowsNumber 1. FROM (SELECT qr.rid, qb.qid, MAX(qb.syllabus_id) AS syllabus_id, MAX(qb.cid) AS cid, (SELECT COUNT(r.rid) FROM quiz_result AS r WHERE r.quid = qz.quid ) AS tot_cnt, qz.correct_score AS ma...;


  3. ;

    4. classes\sql.php:1246 SQLQuery::gSQLRowCount_int 1. SELECT @rownum:=@rownum+1 as rnum, t1.cid, t1.syllabus_id, t1.qid, ROUND(t2.att_cnt100/t1.tot_cnt,2) as tot_att, ROUND(t2.crr_cnt100/t2.att_cnt,2) as tot_crr, 100-ROUND(t2.crr_cnt...;


  4. FROM (SELECT qr.rid, qb.qid, MAX(qb.syllabus_id) AS syllabus_id, MAX(qb.cid) AS cid, (SELECT COUNT(r.rid) FROM quiz_result AS r WHERE r.quid = qz.quid ) AS tot_cnt, qz.correct_score AS ma...;
  5. ;
  6. ;
  7. ;
  8. t1.rid = 157;
  9. ;
  10. MySQLiConnection;
  11. and;

    5. classes\listpage.php:1136 ListPage->buildSQL N/A

    6. classes\listpage.php:1900 ListPage->prepareForBuildPage N/A

    7. Question_Report_list.php:309 Global scope N/A


    ////////////////////////////////////
    [color="#FF0000"]2. Chart Page
    SQL



SELECT

t1.qno,

SUM(IFNULL(t2.duration, 0)) AS times

FROM (SELECT

@rownum:=@rownum+1 AS qno,

qb.qid

FROM qbank AS qb

, qq_info AS qi

, quiz_result AS qr

, (SELECT

@rownum:=0

FROM dumy

) AS r

WHERE qb.qid = qi.qid AND qi.quid = qr.quid AND qr.rid = 17

ORDER BY qb.cid, qb.qid

) AS t1

LEFT OUTER JOIN (SELECT

qi.qid,

qra.duration

FROM quiz_result_answer AS qra

, qq_info AS qi

WHERE qra.qqid = qi.qqid AND qra.rid = 17

) AS t2 ON (t1.qid = t2.qid)

GROUP BY t1.qno

ORDER BY t1.qno



php error happened
Technical information

Error type 256

Error description 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 'LEFT OUTER JOIN (SELECT qi.qid, qra.duration FROM quiz_result_answer AS qra ' at line 4

URL localhost/Times_chart.php?rid=157

Error file H:\wamp\www\idea1_v9\output\connections\MySQLiConnection.php

Error line 142

SQL query SELECT t1.qno, SUM(IFNULL(t2.duration,0)) as times FROM (SELECT @rownum:=@rownum+1 AS qno, qb.qid FROM qbank AS qb , qq_info AS qi , quiz_result AS qr , (SELECT @rownum:=0 FROM dumy ) AS r WHERE qb.qid = qi.qid AND qi.quid = qr.quid AND qr.rid = 17 ORDER BY qb.cid, qb.qid ) AS t1 LEFT OUTER JOIN LEFT OUTER JOIN (SELECT qi.qid, qra.duration FROM quiz_result_answer AS qra , qq_info AS qi WHERE qra.qqid = qi.qqid AND qra.rid = 17 ) AS t2 ON (t1.qid = t2.qid) ON (t1.qid = t2.qid) GROUP BY t1.qno LIMIT 1
More info
Call stack

File: line Function Arguments

0. connections\MySQLiConnection.php:142 trigger_error 1. 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 'LEFT OUTER JOIN (SELECT qi.qid, qra.duration FROM quizresult...;

  1. 256;

    1. connections\MySQLiConnection.php:142 MySQLiConnection->query 1. SELECT t1.qno, SUM(IFNULL(t2.duration,0)) as times FROM (SELECT @rownum:=@rownum+1 AS qno, qb.qid FROM qbank AS qb , qq_info AS qi , quiz_result AS qr , (SELECT @rownum:=0 FROM dumy...;

    2. classes\chartpage.php:94 ChartPage->getStartMasterKeys N/A

    3. classes\chartpage.php:18 ChartPage->__construct 1. Array ( [id] => 1 [xt] => XTempl Object ( [xt_vars] => Array ( [event] => Array ( [m...;

    4. Times_chart.php:179 Global scope N/A


    Probably a bug in v9 till latest built <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=24006&image=1&table=forumtopics' class='bbc_emoticon' alt=':unsure:' />

    Both the pages Works perfectly in V8.1 <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=24006&image=2&table=forumtopics' class='bbc_emoticon' alt=':rolleyes:' />
Sergey Kornilov admin 7/18/2016

I cannot tell what might be wrong with this SQL or what exactly changed between versions. As a word of advice - you can create a view in MySQL on the top of this SQL query and use this view as a datasource in PHPRunner. This will always work.
M
 		merlingolden author 7/19/2016

Dear admin

Unfortunately MySql is not allowing subqueries and variable, parameters in view. So there is no possibility of creating a mysql view for the above sql. but the PhpRunner view was able to hand the same in v8.1 and not in v9, I request you to look into this

regards



I cannot tell what might be wrong with this SQL or what exactly changed between versions. As a word of advice - you can create a view in MySQL on the top of this SQL query and use this view as a datasource in PHPRunner. This will always work.