Displaying PHP Data from Query Without Using a Loop

Question

I have a query that gets some data that I need to display on my webpage. Here is my query:

$sql1 = "SELECT CAST([Series] AS INT) AS Series
      ,[Master Supplier Title]
      ,[Fund Name]
      ,CAST([Agreement_ID] AS INT) AS Agreement_ID
      ,CAST([Tier_ID] AS INT) AS Tier_ID
      ,[Retro_to_1]
      ,CAST([Payments per Year] AS INT) AS [Payments per Year]
      ,[Condition Unit of Measure]
      ,CAST([Condition Minimum] AS INT) AS [Condition Minimum]
      ,CAST([Condition Maximum] AS INT) AS [Condition Maximum]
      ,CAST([Incentive Multiplier] AS DEC(5,4)) AS [Incentive Multiplier]
  FROM [Test].[dbo].[vExample]
  WHERE [Master Supplier Title] = '$supp' AND [Series] = 1 AND [Fund Name] = '400P' AND [Agreement_ID] = 2
  ORDER BY [Master Supplier Title]";

I am then using this chunk of code to display the needed results:

<?php foreach ($pdo->query($sql1) as $supp11) { ?>
            <label>Agreement ID:</label><input value="<?php echo $supp11['Agreement_ID'];?>" readonly><br><br><br>
            <table>
                <tr>
                <thead>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                </thead>
                </tr>
                <?php foreach ($pdo->query($sql1) as $supp22) { ?>
                <tr>
                <tbody>
                    <td><?php echo $supp22['Tier_ID'];?></td>
                    <td><?php echo $supp22['Incentive Multiplier'];?></td>
                    <td><?php echo $supp22['Condition Minimum'];?></td>
                    <td><?php echo $supp22['Condition Maximum'];?></td>
                    <td><?php echo $supp22['Condition Unit of Measure'];?></td>
                    <td><?php echo $supp22['Retro_to_1'];?></td>
                    <td><?php echo $supp22['Payments per Year'];?></td>
                </tbody>
                </tr>
                <?php } ?>
            </table>
            <?php } ?>

The variable $supp that you see is the value that I get from my dropdown selection that I have in some previous code.

Whenever, I make a dropdown selection, it displays the correct data. However, I only need it to display in a table one time. For example, a selection may be in multiple rows of the database. So whenever it is displaying the results in a table, it is displaying the results in a table the number of times that the dropdown selection is seen in the database. So, if the value in my $supp variable is seen 8 times in the database, I only need that info (8 rows) in a table ONE TIME. Not eight different times like I am currently getting.

How can I fix this problem?

If the value is the same all the time, simply display it outside your loop. — Dan Bracuk, Sep 28 '17 at 19:36

score 1 · Accepted Answer · answered Sep 28 '17 at 19:54

I think the problem is that you have a double loop going on, causing the duplication. If you want to get all the results back as a set to process, you should use a prepared statement.

<?php 
$stmt = $pdo->prepare($sql1);
$stmt->execute();
$results = $stmt->fetchAll();
?>
<label>Agreement ID:</label><input value="<?php echo $results[0]['Agreement_ID'];?>" readonly><br><br><br>
<table>
<tr>
<thead>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
</thead>
</tr>
<?php foreach ($results as $supp22) { ?>
<tr>
<tbody>
    <td><?php echo $supp22['Tier_ID'];?></td>
    <td><?php echo $supp22['Incentive Multiplier'];?></td>
    <td><?php echo $supp22['Condition Minimum'];?></td>
    <td><?php echo $supp22['Condition Maximum'];?></td>
    <td><?php echo $supp22['Condition Unit of Measure'];?></td>
    <td><?php echo $supp22['Retro_to_1'];?></td>
    <td><?php echo $supp22['Payments per Year'];?></td>
</tbody>
</tr>
<?php } ?>
</table>

The call to fetchAll() should return an array of results you can loop through. Looking into the 1st record at index 0 should let you get the Agreement_ID parameter for you top label.

the @OP didn't ask for this, but they should fixup their html too... https://stackoverflow.com/questions/5395228/html-tables-thead-vs-th — Wee Zel, Sep 28 '17 at 20:11

Typel · Answer 2 · 2017-09-28T19:51:33.773

You could use DISTINCT in your query to make sure that you only get one copy of each row, but that would probably be way too slow, depending on the database size and structure. I'd probably advocate just adding an IF statement around your <tbody> loop. For example:

<?php
$shown = array(); // keeps track of which values were already displayed
foreach ($pdo->query($sql1) as $supp22) { 
  // this just has to be a value (or combination of values) 
  // that you don't want repeated.
  // Since I don't know the exact structure of your tables, I've just 
  // appended all the variables together into one long string. If 'Tier_ID'
  // is a unique row identifier you'd want to exclude it from here.
  $dont_repeat = $supp22['Tier_ID'].$supp22['Incentive Multiplier'].$supp22['Condition Minimum'].$supp22['Condition Maximum'].$supp22['Condition Unit of Measure'].$supp22['Retro_to_1'].$supp22['Payments per Year'];
  // here we only display the row if it wasn't already shown
  if ( !in_array($dont_repeat, $shown) ) {
    // add it to our $shown array so we don't display the same data again
    $shown[] = $dont_repeat;
    ?>
    <tr>
    <tbody>
    <td><?php echo $supp22['Tier_ID'];?></td>
    <td><?php echo $supp22['Incentive Multiplier'];?></td>
    <td><?php echo $supp22['Condition Minimum'];?></td>
    <td><?php echo $supp22['Condition Maximum'];?></td>
    <td><?php echo $supp22['Condition Unit of Measure'];?></td>
    <td><?php echo $supp22['Retro_to_1'];?></td>
    <td><?php echo $supp22['Payments per Year'];?></td>
    </tbody>
    </tr>
    <?php 
    } 
  ?>
  </table>
  <?php 
  } 
/* be tidy and */ unset($shown, $dont_repeat);
?>

Yeah I dont think `DISTINCT` would work based on what I need. I used this piece of code in place of what I had and its giving me an internal server error now — Rataiczak24, Sep 28 '17 at 19:51
There's probably an unclosed bracket somewhere. If you want to be sure, check your php error log - it'll tell you exactly what is generating that error. Or display errors inline: [link](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) — Typel, Sep 28 '17 at 19:56

Displaying PHP Data from Query Without Using a Loop

2 Answers2