Trying to make a transaction history

Question

Hi guys im trying to make a transaction history getting the details of course form the database.

The details show perfect when user have and active subscription but when the suscription expire do not show anything and just show 1 row.

I want to show in history obviosly all transactions active or expired. Here is my code.

Active =1 expired =0 just for your knowledge

<?php
$query = "SELECT * FROM `subscriptions` WHERE `username` = '$username'";
$result = mysqli_query($con, $query) or die(mysqli_error($con));

while ($row = mysqli_fetch_array($result))
{
     if ($row['active'] == 1)
     {
         $status    = "Active";
         $transid   = $row['txn_id'];
         $transdate = date('F j, Y',strtotime($row['date']));
         $subid     = $row['id'];
         $payment   = $row['payment'];
     }
     else 
     {
         $status    = "Expired";
         $transid   = $row['txn_id'];
         $transdate = date('F j, Y',strtotime($row['date']));
         $subid     = $row['id'];
         $payment   = $row['payment'];
     }
}
?>

I use the if and else to show if active or expired in status.

My html code

<div class="table-responsive">
    <table class="table table-striped table-vcenter">
        <thead>
            <tr>
                <th class="text-center">ID</th>
                <th class="text-center">PRODUCT</th>
                <th class="text-center">STATUS</th>
                <th class="text-center">DATE</th>
                <th class="text-center">PAYMENT</th>
                <th class="text-center">TRANSACTION ID</th>
            </tr>
        </thead>


        <?php
        echo'    
        <tbody class="text-center">

            <tr>
                <td>'.$subid.'</td>
                <td>'.$package.'</td>
                <td>'.$status.'</td>
                <td>'.$transdate.'</td>
                <td>'.$payment.'</td>
                <td>'.$transid.'</td>
            </tr>
        </tbody>';
        ?>
    </table>
</div>

Some caps here for example i want to show this always the subscription still active or not because obviously it's a history

active subscription the problem is that if the user subscription expire do not show in history and only show 1 the last

here when expire expired subscription and

Answer 1

You can initially put the database values to an array

PHP CODE

<?php
$query = "SELECT * FROM subscriptions sub LEFT JOIN package pack ON (pack.id=sub.package) WHERE sub.username = '$username'";
$result = mysqli_query($con, $query) or die(mysqli_error($con));
$tableArray = array();
$counter = 0;
while ($row = mysqli_fetch_array($result))
{
     $tableArray[$counter]['status'] = $row['active'] == 1 ? 'Active' : 'Expired';
     $tableArray[$counter]['transid'] = $row['txn_id'];
     $tableArray[$counter]['transdate'] = date('F j, Y',strtotime($row['date']));
     $tableArray[$counter]['subid'] = $row['id'];
     $tableArray[$counter]['payment'] = $row['payment'];
     $counter++;
}
?>

Now access this array into the html part & iterate over it via foreach

HTML

 <div class="table-responsive">
    <table class="table table-striped table-vcenter">
        <thead>
            <tr>
                <th class="text-center">ID</th>
                <th class="text-center">PRODUCT</th>
                <th class="text-center">STATUS</th>
                <th class="text-center">DATE</th>
                <th class="text-center">PAYMENT</th>
                <th class="text-center">TRANSACTION ID</th>
            </tr>
        </thead>
        <tbody class="text-center">
            <?php foreach($tableArray as $row) {?>
            <tr>
                <td> <?php echo $row['id'] ?></td>
                <td><?php echo $row['package'] ?></td>
                <td><?php echo $row['status'] ?></td>
                <td><?php echo $row['transdate'] ?></td>
                <td><?php echo $row['payment'] ?></td>
                <td><?php echo $row['transid'] ?></td>
            </tr>
            <?php } ?>
        </tbody>
        ?>
    </table>
</div>

In the HTML, $row['package'] will be undefined, you need to fetch it from database as well if that is the field in your database. Your question didn't have this field. Please include it as well

Answer 2

My version :)

PHP

$query = "SELECT * FROM `subscriptions` WHERE `username` = ?";
$queryStmt = $con->prepare( $query );

if ( $queryStmt )
{
    $queryStmt->bind_param( "s", $username ); // <-- This prevents SQL injection and may be handy for executing the query several time more efficiently
    $queryStmt->execute();
    $queryStmt->bind_result( $active, $transid, $date, $subid, $payment, $package ); // <-- the order must match the order of the fields in the $query

    $rows = []; // <-- load here the content fo the DB rows
    while( $queryStmt->fetch() )
    {
        // Transform needed variables

        $status = ( $active == 1 ) ? "Active" : "Expired";
        $transdate = date( 'F j, Y', strtotime( $date ) );

        $rows[] = compact( 'satus', 'transid', 'transdate', 'subid', 'payment', 'package' );
    }

    $queryStmt->close();
}

HTML

    <div class="table-responsive">
    <table class="table table-striped table-vcenter">
        <thead>
        <tr>
            <th class="text-center">ID</th>
            <th class="text-center">PRODUCT</th>
            <th class="text-center">STATUS</th>
            <th class="text-center">DATE</th>
            <th class="text-center">PAYMENT</th>
            <th class="text-center">TRANSACTION ID</th>
        </tr>
        </thead>
        <tbody class="text-center">
        <?php foreach( $rows as $row ) : ?>
            <tr>
                <td><?php echo $row['subid'] ?></td>
                <td><?php echo $row['package'] ?></td>
                <td><?php echo $row['status'] ?></td>
                <td><?php echo $row['transdate'] ?></td>
                <td><?php echo $row['payment'] ?></td>
                <td><?php echo $row['transid'] ?></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
        ?>
    </table>
</div>