DELIMITER //
 
DROP PROCEDURE IF EXISTS spLime_SurveyXcode //
 
CREATE PROCEDURE spLime_SurveyXcode(survey_code INT)
BEGIN
  /* return the set of question codes in SGQ format
    transcoded with group codes and question codes
  */
  select c.column_name
    , RIGHT(g.group_name, 5) codice_reato
    , q.Title codice_domanda
  from information_schema.columns c
  left join lime_groups g
    ON LOCATE(CONCAT('X', g.gid, 'X') , c.column_name) = 7
  left join lime_questions q
    ON g.gid = q.gid
    and LOCATE(CONCAT('X', q.qid, 'E'), CONCAT(c.column_name, 'E')) > 8
  where c.table_name = CONCAT('lime_survey_', CAST(survey_code AS CHAR(8)))   #869765'
    and left(c.column_name, 7) = CONCAT(CAST(survey_code AS CHAR(8)), 'X');  #'869765X';

 
END//
 
CALL `dbLime`.`spLime_SurveyXcode`(869765);

<?php
 
error_reporting(E_ALL &amp; ~E_NOTICE &amp; ~E_STRICT &amp; ~E_DEPRECATED);
 
$config['base_url'] = 'https://localhost/limesurvey/'; 
 
$config['db_host'] = 'localhost'; // Database host (e.g. localhost)
$config['db_name'] = 'dbLime'; // Database name
$config['db_username'] = 'root'; // Database username
$config['db_password'] = ''; // Database password
$config['survey_code'] = '869765';
 
 
function logMe($msg)
{
  // appends logs to a "message.log" file in the Lime's TMP directory
  if ($f=fopen('tmp/message.log', 'a'))
  {
    fputs($f, date("Y-m-d H:i:s")." $msg\n");
    fclose($f);
  }
 
}
 
// Open connection
$mysqli = new mysqli(
  $config['db_host']
  ,$config['db_username']
  ,$config['db_password']
  ,$config['db_name']
  ); 
 
// Check connection
if ($mysqli->connect_errno)
{
  logMe("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
  die($mysqli->connect_error);
}
 
 
// execute xcodeproc
$resx = $mysqli->query(sprintf('call spLime_SurveyXcode(%d)', $config['survey_code']), MYSQLI_STORE_RESULT);
 
if(!$resx) {
  logMe("CALL failed: (" . $mysqli->errno . ") " . $mysqli->error);
  die($mysqli->error);
}
 
 
// we build the union that traverse every SGQ-code column of the survey
// and pair the results with the actual codes of the group and of the question
while($obj = $resx->fetch_object()) {
 
  list($S, $G, $Q) = split('X', $obj->column_name);
 
  $the_unions[]  = sprintf("SELECT '%s' codice_reato \n
                                  , '%s' codice_domanda \n
                                  , %s codice_risposta \n
                                  , a.assessment_value \n
                            FROM lime_survey_%s AS s \n
                            LEFT JOIN lime_answers AS a \n
                              ON a.code = s.%s \n
                              AND a.qid = '%s' \n"
      , $obj->codice_reato
      , $obj->codice_domanda
      , $obj->column_name
      , $config['survey_code']
      , $obj->column_name
      , $Q);
}
$resx->close();
$mysqli->next_result();
 
$the_union = implode( "\nUNION ALL\n", $the_unions);
 
$the_union .= "\nORDER BY codice_reato, codice_domanda, codice_risposta, assessment_value;";
 
 
$res = $mysqli->query($the_union );
if($res) {
  while($row = $res->fetch_object()) {
    $results[] = $row;
  }
 
  // Free result set
  $res->close();
  $mysqli->next_result();
 
  //FINAL OUTPUT
  // array_values() removes the original keys and replaces
  // with plain consecutive numbers
  print json_encode(array_values($results));
 
}
else {
  logMe("UNION failed (" . $mysqli->errno . ") " . $mysqli->error);
}
 
 
// Close connection
logMe("Close connection.");
$mysqli->close();
 
?>