Viewing current month birthday in Fullcalendar

Question

I have a database with a date in the field start, would like to display only the current month’s birthday fullcalendar, but I’m having two difficulties:

• select only the current month birthday

• show birthdays in the corresponding month in the current calendar year, not in the original field year.

I currently have this code:

<?php

//Database
$data = array();


$link = mysqli_connect("localhost", "root", "", "agenda");

mysqli_set_charset($link, 'utf8');

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

$query = "SELECT * FROM clientes";

if ($result = $link->query($query)) {

    /* fetch object array */
    while ($obj = $result->fetch_object()) {
        $data[] = array(
            'id' => $obj->id,
            'title'=> $obj->title,
            'start'=> $obj->start
        );
    }

    /* free result set */
    $result->close();
}

mysqli_close($link);

?>

<script>

    $(document).ready(function() {

        $('#clientes').fullCalendar({
            header: {
                left: 'prev,next today',
                center: 'title',
                right: 'month,basicWeek,basicDay'
            },
            defaultDate: '<?php echo date('Y-m-d');?>',
            editable: true,
            eventLimit: true, // allow "more" link when too many events
            events : <?php echo json_encode($data);?>
        });

    });

</script>

And the show is like this:

Answer 1

The first part of the problem is solved with the function MONTH of Mysql itself:

SELECT * FROM clientes WHERE MONTH( start ) = MONTH( CURRENT_DATE );

• MONTH() returns only the month of a date ( DAY and YEAR return the day and the year).

• CURRENT_DATE always returns the current date.

Then, to display the data as a birthday, we have to exchange the original year for the current year. One of the simplest ways is to make the change in SELECT itself as well.

Taking advantage, let’s eliminate the * of SELECT and specify exactly what we want:

SELECT
       id,
       CONCAT_WS( '-', YEAR( CURRENT_DATE ), MONTH( start ), DAY( start ) ) AS aniversario,
       titulo
FROM 
       clientes
WHERE
       MONTH( start ) = MONTH( CURRENT_DATE );

Thus, a date like 2010-05-30 will be returned as 2016-05-30, appearing in the current month on fullcalendar.

How do we recreate the date using functions, the AS aniversário served to give a name in the returned column, so we finished with this adjustment:

while ($obj = $result->fetch_object()) {
    $data[] = array(
        'id' => $obj->id,
        'title'=> $obj->title,
        'start'=> $obj->aniversario
    );
}

If at any time you want to display the whole year calendar, just take the WHERE MONTH( start ) = MONTH( CURRENT_DATE ) of query, for the CONCAT allied with the fullcalendar You’re already going to separate everything by month naturally.

_{This response took some good suggestions from @Randrade at chat room of Sopt}