I have a Table like below
CREATE TABLE Statistics(Stat_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Period VARCHAR(55),
Location VARCHAR(255),
Rate_per_SqFt INT)
INSERT INTO Statistics(Period, Location, Rate_per_SqFt)
VALUES('June', 'Location A', 2500),
('June', 'Location B', 2740),
('June', 'Location C', 3200),
('July', 'Location A', 2650),
('July', 'Location B', 2800),
('July', 'Location C', 3250),
('August', 'Location A', 2750),
('August', 'Location B', 2950),
('August', 'Location C', 3230),
('October', 'Location A', 2950),
('October', 'Location B', 3950),
('October', 'Location C', 3530);
I Used the below proc
DROP PROCEDURE IF EXISTS test.stats_report;
CREATE PROCEDURE test.`stats_report`()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN Location = ''',
Location,
''' then Rate_per_SqFt ELSE 0 end) AS ',
CONCAT('`',Location,'`')
)
) INTO @sql
FROM statistics;
SET @sql = CONCAT('SELECT Period, ', @sql, '
FROM Statistics
GROUP BY Period');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
It brings output as below
Period Location A Location B Location C
June 2500 2740 3200
July 2650 2800 3250
August 2750 2950 3230
October 2950 3950 3530
Since I dynamically generate columns in MySQL Procedure I dont know how to get the rows from the result set in PHP by using MySQLi Bind Method or any thing else.
How to get the rows from Result set in which the number of columns is not know.
Please note I want Location A, Location B and Location C which i specified as Column in Result set.
Now when I add New Location D its going to come as new column.
You can have look at the MySQL Table here
You can always work with the rows from a result set as an array and use foreach to loop through them. You didn't include your PHP code so we don't know whether this is via PDO or MySQLi.. so this may be a bit off the mark.. but..
Information about the resultset is available from the metadata . For MySQLi start with:
/* get resultset for metadata */
$result = $stmt->result_metadata();
Then use this to call fetch_fields as such:
/* Get field information for all columns */
$fields = $result->fetch_fields();
foreach ($fields as $field) {}
PDO has similar functions.