phpmyadmin Question posted in
The official documentation can be found here.

How do I solve this error caused from a Pivot? – Phpmyadmin

When trying to pivot my table – a dynamic one which I can’t define as entries my change can be added or deleted – I receive a fatal error. I used a checked answer from this question and changed it where it was necessary. You will see that I use AVG instead of SUM.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'AVG(IF(country = ''',
          country,
          ''', option_id, NULL)) AS ',
          country
        )
  ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;
SET @sql = CONCAT('SELECT gspa.question_id', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error I get is:

Fatal error: Uncaught TypeError: Argument 1 passed to PhpMyAdminSql::resultSetHasJustOneTable() must be of the type array, boolean given, called in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php on line 1908 and defined in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php:143 Stack trace: #0 /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php(1908): PhpMyAdminSql->resultSetHasJustOneTable(false) #1 /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php(2252): PhpMyAdminSql->getQueryResponseForResultsReturned(true, Array, '1812apmy', 'gs_poll_answers', NULL, NULL, Object(PhpMyAdminDisplayResults), './themes/pmahom...', '1', 0, NULL, NULL, NULL, NULL, NULL, 'SELECTrntGROUP_...', NULL) #2 /usr/local/dh/web/phpmyadmin/import.php(736): PhpMyAdminSql->executeQueryAndGetQueryResponse(Array, false, '1812apmy', 'gs_poll_answers', NULL, NULL, NULL, NULL, NULL, NULL, 'tbl_sql.php', './themes/pmahom...', NULL, NULL, NULL, 'SELECTrntGROUP_...', NULL, NULL) #3 {main} thrown in /usr/local/dh/web/phpmyadmin/libraries/classes/Sql.php on line 143

For the moment I have no idea what the problem is, except of the idea that it can’t handle the Join in the “From”.

Is my code the problem, is it the server or anything else?

EDIT:
Create Tables for skill_cv_test

CREATE TABLE `skill_cv_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `usr_id` int(32) NOT NULL,
 `category` int(16) NOT NULL COMMENT 'school_or_job',
 `comp_id` int(32) NOT NULL,
 `position` varchar(64) NOT NULL,
 `description` text,
 `country` varchar(16) NOT NULL,
 `state` varchar(16) NOT NULL,
 `city` varchar(64) NOT NULL,
 `start_date` date NOT NULL,
 `end_date` date DEFAULT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8

and gs_poll_answers

CREATE TABLE `gs_poll_answers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(64) NOT NULL,
 `poll_id` int(64) NOT NULL,
 `question_id` int(64) NOT NULL,
 `option_id` int(64) NOT NULL,
 `vote_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I have found a solution. It's not exactly a solution on the pivot-issue and the error, but a way to the desired solution. By querying all occurring countries in table skill_cv_test I can create the part of the required code that will remain dynamic through a for-loop in PHP.

    Like "SELECT country FROM skill_cv_test WHERE comp_id = 1". With a loop I then can create the following lines: $qry .= "AVG(IF(sct.country = '.$row["country"].',option_id,NULL)) AS '.$row["country"].'". Through this approach I can achieve the same result.


  2. 0

    AS i suspected, it has nothing to do with your php.

    your query is a little bit wrong.

    A missing comma When you collect all strings together and i used double quotes, mysql doesn’t like the three ”’ at all

    SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'AVG(IF(country = '"',
          country,
          '"', option_id, NULL)) AS "',
          country,'"'
        )
  ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;
SET @sql = CONCAT('SELECT gspa.question_id,', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    Login or Signup to reply.
  3. 0

    Try to escape single quotes when concatenating text. Example based on your code:

    Note: in any case add an IF statement to avoid execution in the case of @sql being NULL

    SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('AVG(IF(sct.country = '',sctable.country,'',', option_id,', 0)) AS ',sctable.country)
    ) INTO @sql
FROM
  (SELECT gspa.question_id AS question_id, sct.country AS country, gspa.option_id AS option_id
    FROM gs_poll_answers gspa 
    LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id
    WHERE gspa.poll_id = 1) sctable;

// at this point @sql should not be null    
IF @sql IS NOT NULL THEN

SET @sql = CONCAT('SELECT gspa.question_id AS question_id,', @sql, ' FROM gs_poll_answers gspa LEFT JOIN skill_cv_test sct ON sct.usr_id = gspa.user_id WHERE gspa.poll_id = 1 GROUP BY gspa.question_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search