0

I have this MySQL table MONTHNAME generated by querying from the database using the following script using PHP. 

$query = 'SELECT monthname(`dateAdded`) as MONTHNAME,sum(`shift1PinCount`+`shift2PinCount`) from `supervisorupdate` WHERE YEAR(`dateAdded`) = YEAR(CURDATE()) group by monthname(`dateAdded`) ORDER BY monthname(`dateAdded`) DESC';
$queryExecute = mysqli_query($conn, $query);

I want to show this table as it is into my HTML page using PHP. As from I know normally we use

while($row = mysqli_fetch_array($queryExecute)){
   echo $row['someColumnName'];
}

In this case, I can't do that since I am doing some calculations and getting output as a table. Can someone guide me on how to show this table?

Edit 1

Here is a picture of the result that I am getting using phpMyadmin. So the HTML table should be similar to this

enter image description here

So for HTML side, it should show something like this

<table class="table">
  <thead>
    <tr>
      <th scope="col" id="month">Month</th>
      <th scope="col" id="sum">Sum</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th scope="row" id="jan">January</th>
      <td id="janPinCount">3456</td>
    </tr>
    <tr>
      <th scope="row" id="feb">February</th>
      <td id="febPinCount">443</td>
    </tr>

  </tbody>
</table>
Anu
  • 1,123
  • 2
  • 13
  • 42
  • Why downvote? Please tell the problem or what am I doing wrong here. This is a genuine question. At least someone can guide me instead of giving whole solution. – Anu Feb 27 '20 at 03:20
  • You're going to need to take this a step further. For example, what columns do you want to show? Can you at least sketch in the ``, `` and such? – tadman Feb 27 '20 at 03:22
  • 1
    @tadman Okay. I edited the question with HTML table added – Anu Feb 27 '20 at 03:34
  • You just need to show your mysql data in table , for that check [this](https://stackoverflow.com/questions/17902483/show-values-from-a-mysql-database-table-inside-a-html-table-on-a-webpage) post. – Swati Feb 27 '20 at 05:17
  • @Swati What you suggested is the one I mentioned in my question as well. I don't think it is possible since there is a table `MONTHNAME` is created by the query – Anu Feb 27 '20 at 05:50
  • Why this question is closed? The answers linked as duplicate to this question is a totally different question. – Anu Mar 11 '20 at 03:23

2 Answers2

1

Try this one, I added an alias on your sum(shift1PinCount+shift2PinCount) column as PinCount.

<?php
$query = 'SELECT monthname(`dateAdded`) as MONTHNAME, sum(`shift1PinCount`+`shift2PinCount`) as PinCount from `supervisorupdate` WHERE YEAR(`dateAdded`) = YEAR(CURDATE()) group by monthname(`dateAdded`) ORDER BY monthname(`dateAdded`) DESC';
$queryExecute = mysqli_query($conn, $query);
?>


<table class="table">
  <thead>
    <tr>
      <th scope="col" id="month">Month</th>
      <th scope="col" id="sum">Sum</th>
    </tr>
  </thead>
  <tbody>
    <?php while($row = mysqli_fetch_array($queryExecute)){ ?>
    <tr>
      <th scope="row" id="<?=$row['MONTHNAME']?>"><?=$row['MONTHNAME']?></th>
      <td id="<?=$row['MONTHNAME']?>PinCount"><?=$row['PinCount']?></td>
    </tr>
    <?php } ?>
  </tbody>
</table>
gilbertdim
  • 357
  • 2
  • 11
0

you can do this. put this after the table body

$row = mysqli_fetch_array($queryExecute);
do{
echo '   <tr>
      <th scope="row" id="jan">.$row["MONTHNAME"].</th>
      <td id="janPinCount">$row["SUM"]</td>
    </tr> '


}while($row = mysqli_fetch_array($queryExecute))
Alfredo Ramirez Jr.
  • 30
  • 8
  • I tried this way. But not working. Also `MONTHNAME` is a table name and not a column name. So I don't think it is possible to show by doing `$row['MONTHNAME']` – Anu Feb 27 '20 at 05:49
  • table of supervisorupdate dont have a month? i dont get your database. MONTHNAME and sum is different table why not join the two table make a column that can be equal to one another. – Alfredo Ramirez Jr. Feb 27 '20 at 05:58
  • `MONTHNAME` is a table created by query. `SUM` is the sum function using the query to sum different columns. Please check my query carefully. – Anu Feb 27 '20 at 06:04
  • oww i see i didn`t much pay attention to the query i just see the select. i think that is the problem why not make a new column that can inserted a month. its much easier i think and more precise. – Alfredo Ramirez Jr. Feb 27 '20 at 06:19
  • That is an existing database and all numbers are getting from various sources. Not easy to create a new column now. So I have to handle all those numbers addition and stuff with this query itself. – Anu Feb 27 '20 at 08:59
  • I am able to do it the hard way by running a query 12 times to fetch 12 months data. If no one can give a suggestion to do it an efficient way, I will post that as answer although that is not the method I want to solve this issue – Anu Feb 27 '20 at 09:02
  • The question I posted in fact perfectly fetching all the data with that one line of query in `phpMyadmin` and show as table. Only can't be done that way in HTML page – Anu Feb 27 '20 at 09:17