At the end of the month start new table

[Stefce]

How can i get the end of the month or the beggining of the month to create a new table for records ? i have this code but it gets the current month...

<?php
$user = getUserData('users', 'UserUsername');
$sql = "SELECT `ticket_id`, `Uplata`, `Dobivka`, `Date` FROM `kladilnica` WHERE `Username`='$user'";
$result = $conn->query($sql);
$count = 0;
$uplata_total = 0;

if($result === false) {
    echo "<b>Could not connect to database.</b>";
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
    if($result->num_rows == 0) {
        echo "<b>You do not have any tickets.</b>";
    } else if($result->num_rows >= 1) {
        $date = date('F Y');
        ?>
<div class="col-md-5 col-md-pull">
    <div class="panel panel-default" id="wrapper">
        <table class="table table-hover">
            <thead>
                <tr>
                    <th colspan='5'><center><?= $date ?></center></th>
                </tr>
                <tr style="background-color: lavender;">
                    <th>
                        <td><b>Ticket ID</b></td>
                        <td><b>Bet</b></td>
                        <td><b>Gain</b></td>
                        <td><b>Date</b></td>
                    </th>
                </tr>
            </thead>
        <?php
        while($row = $result->fetch_assoc()) {
            $count++;
            $ticket_date = htmlentities($row["Date"]);
            $ticket_id = htmlentities($row["ticket_id"]);
            $uplata = htmlentities($row["Uplata"]);
            $dobivka = htmlentities($row["Dobivka"]);

            $uplata_total = $uplata_total + $row["Uplata"];

            ?>
            <tbody>
                <tr>
                    <td><?= $count ?></td>
                    <td><?= $ticket_id ?></td>
                    <td><?= $uplata ?></td>
                    <td><?= $dobivka ?></td>
                    <td><?= $ticket_date ?></td>
                </tr>
            <?php
        }
        ?>
                <tr style="background-color: lightgreen;">
                    <th colspan='4'>Total Bet: <?= $uplata_total ?></th>
                    <th colspan='4'>Total Win: <?= $dobivka ?></th>
                </tr>
            </tbody>
        </table>
    </div>
</div>
            <?php
    }
}
$conn->close();
?>

[Stefce]

Someone who can help ?

[jkon]

To make it clear , do you need two methods that will take a timestamp and the first one will return the beginning of that month timestamp and the second one the ending in timestamps ?

[Stefce]

No, i guess i need one method just when month will start 02.01.2016 for example automaticly to create new table and insert there the new items and calculate them like in the example above.

[jkon]

Do you mean that what you need is a job (a cron job apparently) that will run once a month (at the beginning of the month) ? If so , what is the question ? How to create a cron job or how to create new tables and insert them data ?

Two easy approaches - have 2 calculated fields in the SQL:

SELECT `ticket_id`, `Uplata`, `Dobivka`, `Date`, MONTH(`Date`) AS mnth, YEAR(`Date`) AS yr FROM `kladilnica` WHERE `Username`='$user' ORDER BY `Date`";

In your loop, check the value of mnth and yr for changes from the previous row - if different, finish the previous table (if it exists - i.e. not the very first row) and create a new table (with or without a html heading, e.g. <h3>{$row['mnth']}-{$row['yr']}</h3>). Add an 'end table' after the loop.

Otherwise you can do this on the Date field itself - check the month and year using a DateTime object and do similar to the above.

However, you may want a different method - grouping via column:

$sql = "SELECT CONCAT_WS('-',MONTH(`Date`),YEAR(`Date`)) AS mnthyr, `ticket_id`, `Uplata`, `Dobivka`, `Date` FROM `kladilnica` WHERE `Username`= ? ORDER BY `Date`"
$stmt = $pdo->prepare($sql);
$stmt->execute([$user]);
$data = $stmt->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);

This will give you an array or arrays for each month-year combo. So this makes it much easier to create a table for each combo - no need to check on each iteration of the loop. I think this is how I'd do it.

However, your table format is unclear - not sure why you have 4 heading columns and 5 data columns. The count is beyond me as is the running total.

[Stefce]

But Date is when the ticket is created not the table, do i need to create new value in the database to check the table_date or create a new db table for ex. history and insert there the all values something like this:

$sql = "SELECT `ticket_id`, `Uplata`, `Dobivka`, `Date` FROM `kladilnica` WHERE `Username`='".$user."' 
                    INSERT INTO `history` VALUES ('".$user."', ticket_ID, Uplata, Dobivka, `Date`, table_date_create)";

You've completely lost me now. You suddenly throw in a new DB table. No idea how this relates to the HTML tables you were creating. Is this the same issue or a different one? Getting a nosebleed!

[Stefce]

I found a solution

<?php
function remove_day($date) {
        return substr($date, 0, strrpos($date, "-"));
    }
$user = getUserData('users', 'UserUsername');
                    $sql = "SELECT `ticket_id`, `Uplata`, `Dobivka`, `Date` FROM `kladilnica` WHERE `Username`='$user'";
                    $result = $conn->query($sql);
                    $count = 0;
                    $uplata_total = 0;

                    if($result === false) {
                        echo "<b>Could not connect to database.</b>";
                        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
                    } else {
                        if($result->num_rows == 0) {
                            echo "<b>You do not have any tickets.</b>";
                        } else if($result->num_rows >= 1) {
                            $date = date('F Y');
                            $months = [];
                            while($row = $result->fetch_assoc()) {
                                $months[remove_day($row["Date"])][] = $row;
                            }
                            //var_dump($months);

                            foreach($months as $month => $rows) {
                                $count = 0;
                            ?>
                    <div class="col-md-5 col-md-pull">
                        <div class="panel panel-default" id="wrapper">
                            <table class="table table-hover">
                                <thead>
                                    <tr>
                                        <th colspan='5'><center><?= $month ?></center></th>
                                    </tr>
                                    <tr style="background-color: lavender;">
                                        <th>
                                            <td><b>Ticket ID</b></td>
                                            <td><b>Bet</b></td>
                                            <td><b>Gain</b></td>
                                            <td><b>Date</b></td>
                                        </th>
                                    </tr>
                                </thead>
                            <?php
                            foreach($rows as $row) {
                                $count++;
                                $ticket_date = htmlentities($row["Date"]);
                                $ticket_id = htmlentities($row["ticket_id"]);
                                $uplata = htmlentities($row["Uplata"]);
                                $dobivka = htmlentities($row["Dobivka"]);

                                $uplata_total = $uplata_total + $row["Uplata"];

                                ?>
                                <tbody>
                                    <tr>
                                        <td><?= $count ?></td>
                                        <td><?= $ticket_id ?></td>
                                        <td><?= $uplata ?></td>
                                        <td><?= $dobivka ?></td>
                                        <td><?= $ticket_date ?></td>
                                    </tr>
                                <?php
                            }
                            ?>
                                    <tr style="background-color: lightgreen;">
                                        <th colspan='4'>Total Bet: <?= $uplata_total ?></th>
                                        <th colspan='4'>Total Win: <?= $dobivka ?></th>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                                <?php
                            }
                        }
                    }
                    $conn->close();
                ?>