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

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 ?

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.

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 Avatar
diafol

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.

Member Avatar
diafol

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.

commented: was thinking exactly to this solution +14

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 Avatar
diafol

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!

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