At the end of the month start new table

Question

Stefce 146 Posting Pro

10 Years Ago

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();
?>

php

Edited 10 Years Ago by Reverend Jim because: Changed "mont" to "month" in thread title.

3 Contributors
9 Replies
319 Views
4 Days Discussion Span
Latest Post 10 Years Ago Latest Post by Stefce

Dani AI

Generated 9 Months Ago

Good progress — the PHP grouping approach works, and the thread already has the two main directions: do everything on-read (group/format per month in SQL or in PHP) or move/archive rows on a schedule (cron). A few practical corrections and hardening tips that make the solution reliable in production.

The posted grouping still has three issues to fix: the per-month running total is not reset so totals will carry between months; the win total (Dobivka) is never summed (the code prints the last row’s value); and the query is built with a raw $user value (SQL injection risk). Also truncating the date string in PHP is brittle if the DB date format ever changes — prefer a date-format key (YYYY-MM) or SQL-side formatting.

Correct the totals and numeric handling (example simplified logic):

foreach ($months as $month => $rows) {
    $count = 0;
    $uplata_total = 0.0;
    $dobivka_total = 0.0;

    foreach ($rows as $r) {
        $count++;
        $uplata_total += (float) $r['Uplata'];
        $dobivka_total += (float) $r['Dobivka'];
        // output each row...
    }

    // output per-month totals, e.g. number_format($uplata_total,2)
}

Use a prepared statement to fetch rows (avoid embedding $user directly). With mysqli that looks like:

$stmt = $conn->prepare('SELECT ticket_id,Uplata,Dobivka,`Date` FROM kladilnica WHERE Username = ? ORDER BY `Date`');
$stmt->bind_param('s', $user);
$stmt->execute();
$result = $stmt->get_result();

If performance or long-term storage is the concern, avoid creating a separate physical table per month unless absolutely necessary. Alternatives: server-side grouping (DATE_FORMAT(Date, '%Y-%m') as a month key), a monthly summary/archive table that stores aggregates, or MySQL partitioning by month/year. If a cron/archive is chosen (as mentioned), use a scheduled script that moves rows into a well-named archive table (e.g., kladilnica_YYYY_MM) or writes compact monthly summaries; always keep indexes on Date and Username, and make Date a DATE/DATETIME column. ’s grouping ideas are on the right track — combining safe queries, correct per-month totals, and clear naming will make the solution robust.

All 9 Replies

jkon 738 Posting Whiz in Training

10 Years Ago

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 ?

jkon 738 Posting Whiz in Training

10 Years Ago

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 ?

Member #120589

10 Years Ago

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.

Edited 10 Years Ago by Member #120589

cereal commented: was thinking exactly to this solution +14

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Stefce 146 Posting Pro · Answer 1 · 2016-01-08T19:13:23+00:00

Stefce 146 Posting Pro

10 Years Ago

Someone who can help ?

Stefce 146 Posting Pro · Answer 2 · 2016-01-09T19:15:27+00:00

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.

Member #120589 · Answer 3 · 2016-01-09T23:36:35+00:00

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.

Stefce 146 Posting Pro · Answer 4 · 2016-01-11T15:52:52+00:00

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)";

Member #120589 · Answer 5 · 2016-01-11T18:52:54+00:00

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 146 Posting Pro · Answer 6 · 2016-01-11T23:08:51+00:00

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();
                ?>

At the end of the month start new table

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers