如何在php中调用mysql程序创建动态表

Question

This is not a duplicate question its different. I have stored procedure that I created in MySQL which transpose(pivote) rows to column and there are n numbers of columns. I want PHP to call that stored procedure and display in table form..

Here is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `daily`()
BEGIN SET SESSION group_concat_max_len = (7 * 1024);
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(daily_present.course = ''',
      course,
      ''', daily_present.p, NULL)) AS ',
      course
    )
  ) INTO @sql
FROM daily_present;

SET @sql = CONCAT('SELECT daily_present.date  
                    , ', @sql, ' 
                   FROM daily_present
                   group by daily_present.date
                  order by daily_present.date desc');

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

i have searched and wrote a code below

<?php 

  //connect to database
  $connection = mysqli_connect("localhost", "root", "", "smartcard");

  //run the store proc
  $result = mysqli_query($connection, 
     "CALL daily") or die("Query fail: " . mysqli_error());

  //loop the result set
  while ($row = mysqli_fetch_array($result)){   
      echo $row[0] . " - " . + $row[1]; 
  }

?>

this give me this output but not what i want.

2014-06-28 - 522014-06-07 - 522014-06-06 - 502014-06-05 - 522014-06-04 - 492014-06-03 - 532014-06-02 - 47

please help me.......

this the raw data of table daily_present

date            course      p 
28/06/2014  BBAII           52/2
28/06/2014  BCOM2nd_Year    109/4
07/06/2014  BBAII           52/2
06/06/2014  BBAII           50/4
05/06/2014  BBAII           52/2
05/06/2014  BCOM2nd_Year    104/9
04/06/2014  BBAII           49/5
04/06/2014  BCOM2nd_Year    104/9
03/06/2014  BBAII           53/1
03/06/2014  BCOM2nd_Year    106/7
02/06/2014  BBAII           47/7
02/06/2014  BCOM2nd_Year    109/4

i want result like this in html table format

date            BBAII   BCOM2nd_Year
28/06/2014  52/2    109/4
07/06/2014  52/2    NULL
06/06/2014  50/4    NULL
05/06/2014  52/2    104/9
04/06/2014  49/5    104/9
03/06/2014  53/1    106/7
02/06/2014  47/7    109/4

Answer 1

It looks like your only problem is with your echo statement at the very end. You aren't printing out all the values stored in $row, just the first two--and I often forget to add line-breaks with php echo, don't forget the <br> :) What happens if you use

while($row = mysqli_fetch_array($result)){

echo $row[0] . " " . $row[1] . "/" . $row[2] . " " . $row[3] . "<br>"; }

What is your database schema? I think I guessed it right but you may have to tweak it to make it work.

~~ I'm putting more info up here so we don't explode the comments: If you want your data in a table, you can use:

  echo "<table><tr><td>colname</td><td>col2</td><td>col3</td></tr>";
  while (loops) { //aka for each row
      echo "<tr><td>".$row[0]."</td><td>".$row[1]."/".$row[2]."</td><td>".$row[3]."</td></tr>";
  }
  echo "</table>"; //don't forget to close the tag

Echo sends strings to your HTML file. You use php to decide what and when to output, but only what's echoed will show up on the browser.

Building one with a dynamic number of columns will be harder but it's still the same idea with more loops. I didn't see any array where you had the colNames for your database table already--you can use the query SHOW COLUMNS yourTableName