horizontal vertical data table php mysql

Question

I Have code to show table horizontal.

i want can show table Table like this.

                12/15/2014 || 12/16/2014
Rumah ATM ...........| YA..................||YA.............. etc

</tr>
 <?php 
$nip= $_REQUEST["txtcar"];
include ("../connect.php");
$query1 = "SELECT ambilmk.tgl,ambilmk.nim,ambilmk.kodeMK,ambilmk.nilai,list.kom,list.id_check 
FROM ambilmk 
LEFT JOIN list
ON ambilmk.kodemk=list.id_check
where nim='1'
 ";
$hasil1 = mysql_query($query1);
$no = 1;
while ($row1 = mysql_fetch_array($hasil1))
{?>
  <tr align="center">
    <td><?php echo $row1["tgl"]; ?></td>
    <td><?php echo $row1["kom"]; ?></td>
    <td><?php echo $row1["nilai"]; ?></td>
  </tr>
<?php } ?>

</table>

Can You help to give code. display table vertical horizontal

So you want to basically do a pivot table from the data you are pulling in the query? — Fluffeh, Dec 05 '14 at 07:44
Why not simply get the data and display it the way you want using PHP ? MySQL is not on presentation layer, and in fact not so great at pivoting. — xlecoustillier, Dec 05 '14 at 07:52
yes... i want show horizontally $row[tgl].based on vertically $row[kom] — Ade putra tio aldino, Dec 05 '14 at 08:14

score 0 · Answer 1 · edited May 23 '17 at 12:23

As in HTML-Table you need to code a ROW first then a second row AND you have a ROW but want to display in different rows VERTICALLY... So you need to store the rows in variables.

Let me show you how. Assume you have MySQL Row with 3 columns (as in your code), Then:

 $row_1='<tr align="center">';
 $row_2='<tr align="center">';
 $row_3='<tr align="center">';
 while ($row1 = mysql_fetch_array($hasil1))
 {
     $row_1.="<td>".$row1["tgl"]."</td>";
     $row_2.="<td>".$row1["kom"]."</td>";
     $row_3.="<td>".$row1["nilai"]."</td>";
 }
 echo $row_1."</tr>";
 echo $row_1."</tr>";
 echo $row_1."</tr>";

EDIT: AS OP need Pivot

[Reason of incorrectness for above answer:] If group by is required then its not possible by above code (I m leaving it for future users) & PIVOT is not supported in FREE MySQL...

AND

By static queries you can not do that UNLESS values of tgl are static/fixed. So you need to use dynamic query AND second option is to create stored procedure.

Both method are explained in this question: Pivot table in MySQL - convert to pivot table with values in varchar format

EDIT 2: Got the way to do it by PHP after analyzing the above link:

 $result = mysql_query("SELECT DISTINCT tgl FROM ambilmk WHERE nim='1';");
 $tgls_col="";
 while($row = mysql_fetch_array($result)){
      $tgls_col.=",MAX(CASE WHEN ambilmk.tgl = '".$row[0]."' THEN ambilmk.nilai END) `".$row[0]."`";
 }

 $query1 = "SELECT list.kom as Kom ".$tgls_col." FROM ambilmk LEFT JOIN list 
      ON ambilmk.kodemk=list.id_check where nim='1' GROUP BY ambilmk.tgl,list.kom;";
 $hasil1 = mysql_query($query1);

 $fields=mysql_fetch_fields($hasil1);
 echo '<tr>';
 foreach ($fields as $col) {
     echo '<th>'.$col->name.'</th>';
 }
 echo '</tr>';
 $fields=count($fields);
 while($row=mysql_fetch_array($hasil1)){
      echo '<tr>';
      for($I=0;$I<$fields;I++){
           echo "<td>".$row[$I].'</td>';
      }
      echo '</tr>';
 }

HERE you can switch between list.kom and ambilmk.nilai AND can change aggregation function MAX to any other you want like COUNT etc. ALSO toggle the order of columns in GROUP BY caluse to get desire result. As your column names are in different language, that's why cant understands whats in those, so you need to do these suggested variations. AND ALSO NOT ABLE TO TEST AS DONT HAVE YOUR TABLE STRUCTURE.

i dont understand. i just want Pivot table.where header group by tgl. — Ade putra tio aldino, Dec 05 '14 at 08:23
@Adeputratioaldino: If group by is required then its not possible by my answer & PIVOT is not supported in FREE MySQL... | By static queries you can not do that UNLESS values of `tgl` are static/fixed. AND second option is to create stored procedure. Look the edited answer for link to help you. :) — Adarsh Rajput, Dec 05 '14 at 08:54
@Adeputratioaldino: You didn't understand coz in your mind/need `PIVOT` was there. Now see the edited answer. [AND INITIALLY, I just explained that MySQL returns rows horizontally, SO... to display those vertically you need to store in variables first then display] — Adarsh Rajput, Dec 12 '14 at 13:07

horizontal vertical data table php mysql

1 Answers1