Using arrays to populate a table

Question

I am trying to count the number of images submitted to photo competitions. I am using an array (called $frequencies) to store my values generated from my query. I can create a table with both the competition name and count of entries, but cannot see how I can include the id associated with each of the competitions. In short, I'd like to have the id, competition name, and count - I can get all three of these but not so that the id can be used in a link to direct users to the specific competition).

I get this (everything works as it should, but no id):

...but want this (I cannot think how to get id into the table):

See my query and array below.

<?php
  $con = mysqli_connect( DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE );
  if ( mysqli_connect_errno() ) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error( $con );
  }
  $frequencies = array();
  $cnt_result = "SELECT entered_images.id AS compID,
competitions.name AS compName, 
COUNT(entered_images.id) AS Freq 
FROM entered_images 
INNER JOIN competitions 
ON competitions.id = entered_images.id 
GROUP BY competitions.name";
  $cnt_Recordset1 = mysqli_query( $con, $cnt_result )or die( mysqli_error( $con ) );
  $row_Recordset1 = mysqli_num_rows( $cnt_Recordset1 );
  if ( $cnt_Recordset1 === false ) {
    trigger_error( mysqli_error( $GLOBALS[ 'con' ] ) );
  }
  while ( $row = mysqli_fetch_row( $cnt_Recordset1 ) ) {
    $frequencies[$row[1]] = $row[2];
  }
  ?>

This works as required. Here is how I get the values into my table:

<table width="95%" border="1" align="center">
  <tbody>
    <tr>
        <td align="center" bgcolor="#F8F2C3"><strong>Competition Name</strong></td>
      <td align="center" bgcolor="#F8F2C3"><strong>Count</strong></td>
    </tr>
    <?php foreach ($frequencies as $name => $count) {?>
    <tr>
      <td><?php echo $name?></td>
      <td align="center"><?php echo $count; ?></td>
    </tr>
    <?php
    }
    ?>
  </tbody>
</table>

It is a very bad idea to use `die(mysqli_error($$conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Dec 17 '20 at 19:27

NoobishPro · Accepted Answer · 2020-12-16T03:54:29.583

-1

Should be as easy as simply adding the ID to the table then, no? I would recommend using a foreach loop on the result directly to avoid double looping.

$sQuery = "SELECT entered_images.id AS compID,
competitions.name AS compName, 
COUNT(entered_images.id) AS Freq 
FROM entered_images 
INNER JOIN competitions 
ON competitions.id = entered_images.id 
GROUP BY competitions.name";

$result = mysqli_query( $con, $cnt_result )or die( mysqli_error( $con ) );

<table width="95%" border="1" align="center">
  <thead>
    <tr>
        <th align="center" bgcolor="#F8F2C3"><strong>ID</strong></th>
        <th align="center" bgcolor="#F8F2C3"><strong>Competition Name</strong></th>
      <th align="center" bgcolor="#F8F2C3"><strong>Count</strong></th>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($result as $row) {?>
    <tr>
      <td><?php echo $row['compID'] ?></td> 
      <td><?php echo $row['compName']?></td>
      <td align="center"><?php echo $row['Freq']; ?></td>
    </tr>
    <?php
    }
    ?>
  </tbody>
</table>

edited Dec 16 '20 at 03:54

answered Dec 16 '20 at 00:33

NoobishPro

2,539
1
12
23

Thanks for the reply. I have changed the question - apologies. Yes, images.id is the relationship value to competitions.id. I am getting an error on foreach result. I'll try working trough this myself. Also, I need 3 columns in my table: id, name, and count. – John Twiname Dec 16 '20 at 01:12
I have updated my answer. Try keeping it simple and see if this works. If it does, then re-add the checks you want to have in there. I still feel weird about you wanting the same ID to display as you're counting, though. Something about that seems off to me... – NoobishPro Dec 16 '20 at 03:56
Yes, you are right. Got it working with a few very minor tweaks. What was I thinking!! I was over-thinking it. Thank you...it was so simple..I couldn't see the trees for the forest. – John Twiname Dec 16 '20 at 06:22

Using arrays to populate a table

1 Answers1