Cara menggunakan ntu mysql

Code:

CREATE TABLE IF NOT EXISTS `point` (

  `id` int(11) NOT NULL,

  `name` varchar(255) NOT NULL,

  `id_challange` int(11) NOT NULL,

  `point` varchar(255) NOT NULL

);


INSERT INTO `point` (`id`, `name`, `id_challange`, `point`) VALUES

 ('1', 'Budi', '1', '50'),

 ('2', 'Joko', '1', '55'),

 ('3', 'Budi', '2', '30'),

 ('4', 'Joko', '2', '35'),

 ('5', 'Budi', '3', '70');

Code:

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'MAX(IF(`id_challange` = ''',

`id_challange`,

''', point, NULL)) AS ',

CONCAT('`',`id_challange`,'`')

)

) INTO @sql

FROM point;


SET @sql = CONCAT('SELECT id

, name

, ', @sql, '

FROM point

GROUP BY name');


PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

lalu untuk membuat procedure di MySQL, Anda tinggal eksekusi code ini:

Code:

DROP PROCEDURE IF EXISTS `point_proc`;

DELIMITER //

CREATE DEFINER=`user`@`%` PROCEDURE `point_proc`()

BEGIN

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'MAX(IF(`id_challange` = ''',

`id_challange`,

''', point, NULL)) AS ',

CONCAT('`',`id_challange`,'`')

)

) INTO @sql

FROM point;


SET @sql = CONCAT('SELECT id

, name

, ', @sql, '

FROM point

GROUP BY name');


PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END//

DELIMITER ;

upss..
ini cara panggil di php..

Code:

$mysqli = new mysqli("localhost", "user", "pass");

if(!$mysqli) die('Could not connect: ' . mysql_error());

mysqli_select_db($mysqli, "dbname");

if(!$mysqli) die('Could not connect to DB: ' . mysql_error()); 

 
$result = $mysqli->query("CALL point_proc()");

if(!$result) die("CALL failed: (" . $mysqli->errno . ") " . $mysqli->error);

$rows = [];

if($result->num_rows > 0) 

{

    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))

    {

        $sid = $row["id"];

        $name = $row["name"];

        $a = $row["1"];

        $b = $row["2"];

        $c = $row["3"];

//atau

  $rows[] = $row;

    }

 print_r($rows);//ini hasilnya

}

else {

    echo "no row";

}