PHP/MySQL - Show the total # of records for each date on jquery calendar

Question

I'm trying to show the total number of records for a specific date on the jquery calendar like this:

For example, June 1st shows 6 on the calendar which means there's 6 records in the mysql table for June 1st

Here's what i have

Index.php

   <div id='calendar'></div>


    <script>
    $(function() { // document ready

      $('#calendar').fullCalendar({
        header: {
          left: 'prev,next today',
          center: 'title',
          right: 'month,agendaWeek,agendaDay'
        },

        editable: false,

        events: 'test.php',



        eventSources: [

            // your event source
            {
                events: [ // put the array in the `events` property


                    {
                        title  : 'Happy Thanksgiving',
                        start  : '2016-11-25',
                        end    : '2016-11-25',
                        imageurl:'img/holiday-icon.png'
                    },

                ],
                color: 'transparent',     // an option!
                textColor: 'black' // an option!
            }

            // any other event sources...

        ],


    eventRender: function(event, eventElement) {
        if (event.imageurl) {
            eventElement.find("div.fc-content").prepend("<img src='" + event.imageurl +"' width='15' height='15'>");
        }
    },



     });
    });


    </script>

test.php

                <?php
                error_reporting(E_ALL ^ E_DEPRECATED);





                $username = "root_jdc";

                $password = "password";

                $hostname = "localhost";

                $database = "table_customers";



                $conn = mysql_connect($hostname, $username, $password)

                or die("Connecting to MySQL failed");

                mysql_close($conn);

                if ($conn) {
                    if (isset($_GET["end"])) {
                        //this is calendar query. 
                        //form an array of events
                        $arr = array();
                        $from_date = htmlspecialchars($_GET["end"]);
                        $to_date   = htmlspecialchars($_GET["end"]);
                        $sql_string = "SELECT count(DISTINCT id) as title, date as ShipDate FROM players3 group by date";



                        $result = mysql_fetch_assoc($sql_string);


                        //odbc_result_all($result);
                        // Fetch rows:
                        while(mysql_fetch_array($result))
                        {
                            if (!mysql_result($result,2)) continue;
                            //collect results
                            $title=mysql_result($result,1);
                            $date=mysql_result($result,2);


                            $arr[] = array(
                                'title' => $title,
                                'url' => 'myfeed.php?dt='.substr($date, 0, 10),
                                'start' => substr($date, 0, 10)
                            );
                        }
                        echo json_encode($arr);
                    }
                }

                    ?>

can you point out at what part it's failing (and _how_!!)? – Jeff May 26 '16 at 21:39 — Jeff, May 26 '16 at 21:39

score 0 · Answer 1 · edited May 23 '17 at 12:15

First, you should know that DATE is a keyword in MySQL and probably other DB's aswell, you would do good to avoid using keywords in your column definitions as it could lead to unwanted headaches, or atleast use backticks (`).

What you need to do is to group the records, something like this:

SELECT 
  COUNT(id),
  YEAR(`date`), 
  MONTH(`date`), 
  DAY(`date`) 
FROM players3
GROUP BY 
  YEAR(`date`), 
  MONTH(`date`), 
  DAY(`date`);

This way you get number of records on each date.

To mod: Probably duplicate of MySQL Query GROUP BY day / month / year

PHP/MySQL - Show the total # of records for each date on jquery calendar

1 Answers1