php mysql display the records in table

Question

I'm doing an availability system. I need to display the records in a table.

My system is basically about real estate which tracks if a property is available or not. Sample is I have 20 floors in condominium each floor has 10 units so I need to loop the number of floors and also display each unit under a specific floor.

1st flr | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 2nd flr | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

This is what I've tried so far:

<table class="table table-bordered">
            <?php
                for($i=0; $row3 = $stmt3->fetch(); $i++){
                  $floor = $row3['floor'];
            ?>
          <tr>
            <td><?php echo $floor; ?></td>
            <?php
                for($i=0; $row4 = $stmt4->fetch(); $i++){
                  $unit_code = $row4['unit_code'];
            ?>
            <td><?php echo $unit_code; ?></td>
            <?php 
              }
            ?>

          </tr>
          <?php 
            }
          ?>
        </table>

But it's just display all the records in the first floor.

This is what actually happened:

1st flr| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 
2nd flr

What could be the correct way to achieve my needs? Any ideas? I would gladly appreciate your help. Thanks.

UPDATE:

  $stmt3 = $conn->prepare( "SELECT DISTINCT floor
  FROM tblunitsmaster 
  WHERE project_code = :code" );
  $stmt3->execute(array(':code' => $code));


  $stmt4 = $conn->prepare( "SELECT unit_code
  FROM tblunitsmaster 
  WHERE project_code = :code
  AND floor = :floor
  AND sub_project_code = 'SUB-AX0001'" );

You need to add your db structure and the sql calls you're making. — Anna Billstrom
– Anna Billstrom, Commented Feb 25, 2014 at 7:31
This is happening because your inner loop is getting executed first i.e. all your unit_code is printed before the floor can change. One way to solve this is to change your query to fetch unit_code by floor. — AyB
– AyB, Commented Feb 25, 2014 at 7:32

Community · Accepted Answer · 2017-05-23 11:57:48Z

I don't know if you have already solved it, but Satish Sharma code has a typo on line 6. The line should be

$floor = $row3['floor'];

This will solve your problem. I really should comment on the answer but don't have enough points.

Updated code below

UPDATE: Latest update for each floor with its own set of units.

<?php
$code = 'so';
$stmt3 = $pdo->prepare( "SELECT DISTINCT floor
FROM tblunitsmaster 
WHERE project_code = :code" );
$stmt3->execute(array(':code' => $code));


$stmt4 = $pdo2->prepare( "SELECT unit_code
FROM tblunitsmaster 
WHERE project_code = :code
AND floor = :floor
AND sub_project_code = 'SUB-AX0001'" );

?>
<table border="1px" class="table table-bordered">
    <?php
    $rows3 = $stmt3->fetchAll();

    foreach ($rows3 as $row3) {
        $floor = $row3['floor'];
        ?>
        <tr>
            <td><?php echo $floor; ?></td>
            <?php
            $stmt4->execute(array(':code' => $code, ':floor' => $floor));
            $rows4 = $stmt4->fetchAll();            
            foreach ($rows4 as $row4) {
                $unit_code = $row4['unit_code'];
                ?>
                <td><?php echo $unit_code; ?></td>
                <?php
            }
            ?>

        </tr>
        <?php
    }
    ?>
</table>

OUTPUT:

1st flr 1   2   3   4   5   6   7   8   9   10
2nd flr 1   2   3   4   5   6   7   8   9   10
3rd flr 1   2   3   4   5   6   7   8   9   10

Yes I updated his code, but I'm still not getting the correct records displayed in each floor
@Dunkey - It worked for me. Of course I have used my own SQL, but I guessed all floors will have the same units (say 1-10). If this is not the case then the logic is wrong, let me know. I have posted the whole code I worked on. Please see if you can work with this. Also you may share the output you got from this code with your own SQL.
Okay I'll just give you the query I have. My stmt4 needs to $stmt4 = $conn->prepare( "SELECT unit_code FROM tblunitsmaster WHERE project_code = :code AND floor = :floor AND sub_project_code = 'SUB-AX0001'" );
I see! In this SQL, the units may differ for each floor. So, the logic will be a little different. We will execute stmt4 inside the foreach loop of every floor. I am now updating my post with a working code.

Irdrah · Accepted Answer · 2014-05-16 13:13:16Z

I think this might solve your problem:

<table class="table table-bordered">
<?php
    $stmt3 = $conn->prepare("SELECT DISTINCT `floor` FROM `tblunitsmaster` WHERE `project_code` = :code")->execute(array(':code' => $code))->fetchAll(PDO::FETCH_ASSOC);
    $stmt4 = $conn->prepare("SELECT `unit_code` FROM `tblunitsmaster` WHERE `project_code` = :code AND `floor` = :floor AND `sub_project_code` = 'SUB-AX0001");

    foreach ($stmt3 as $row3){
        $floor = $row3['floor'];

        echo '<tr></td>'.$floor.'</td>';

        if ($stmt4->execute(array(':code' => $code, ':floor' => $floor))){
            foreach ($stmt4->fetchAll(PDO::FETCH_ASSOC) as $row4){
                echo '<td>'.$row4['unit_code'].'</td>';
            }
        }

        echo '</tr>';
    }
?>
</table>

Community · Accepted Answer · 2023-11-14 22:27:54Z

0

for($i=0; $row3 = $stmt3->fetch(); $i++)

What is in $stmt3?

I think you are using the FOR loop wrong. The second part, your $row3 = $stmt3->fetch(), should be an evaluation and not a setting of a variable.

You are probably looking for something like: for($i=0; $stmt3->fetch() <> null; $i++){ $row3 = $stmt3->fetch()

Same for the second FOR loop.

I could be a little wrong with the answer.

edited Nov 14, 2023 at 22:27

CommunityBot

11 silver badge

answered Feb 25, 2014 at 7:50

Wesley De Keirsmaeker

1,0251 gold badge9 silver badges29 bronze badges

1 Comment

Dunkey Over a year ago

I added the stmt3 in my post.

Satish Sharma · Accepted Answer · 2014-02-25 08:13:45Z

0

you need to reset your result for $stmt4-

try this

<table class="table table-bordered">
            <?php
                $rows3 = $stmt3->fetchAll();
                $rows4 = $stmt4->fetchAll();
                foreach($rows3 as $row3){
                  $floor = $row['floor'];
            ?>
          <tr>
            <td><?php echo $floor; ?></td>
            <?php
                foreach($rows4 as $row4){
                  $unit_code = $row4['unit_code'];
            ?>
            <td><?php echo $unit_code; ?></td>
            <?php 
              }

            ?>

          </tr>
          <?php 
            }
          ?>
        </table>

edited Feb 25, 2014 at 8:13

answered Feb 25, 2014 at 7:31

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

7 Comments

Dunkey Over a year ago

What is the data_seek(0) ?

Dunkey Over a year ago

Yes but I'm getting error, Fatal error: Call to undefined method PDOStatement::data_seek() on line 171, Line 171 is: $stmt4->data_seek(0); // use this to reseet your result

Dunkey Over a year ago

I'm getting this Parse error: syntax error, unexpected 'as' (T_AS), expecting ';' on line 161

Satish Sharma Over a year ago

what is on your line no 161

Satish Sharma Over a year ago

ok i got that you need to use foreach instead of for. i have updated it. try now

|

Anna Billstrom · Accepted Answer · 2014-02-25 20:52:25Z

0

It's really not a good idea to put a sql call in a loop. Instead, structure your db call to group the data properly - "select floor, condo_unit group by floor, condo_unit". Then, iterate through the result set.

answered Feb 25, 2014 at 20:52

Anna Billstrom

2,50226 silver badges33 bronze badges

Collectives™ on Stack Overflow

php mysql display the records in table

5 Answers 5

5 Comments

Comments

1 Comment

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

1 Comment

7 Comments

Comments

Related