How to retrieve info from database using ajax/json? (jQuery Week Calendar)

I'm trying to adapt the follo开发者_StackOverflow社区wing jQuery calendar module to integrate it with PHP and a MySQL database. The problem I'm having is that the code loads events as such:

  return {
     events : [
        {
           "id":1,
           "start": new Date(year, month, day, 12),
           "end": new Date(year, month, day, 13, 30),
           "title":"Lunch with Mike"
        },
        {
           "id":2,
           "start": new Date(year, month, day, 14),
           "end": new Date(year, month, day, 14, 45),
           "title":"Dev Meeting"
        },
        {
           "id":3,
           "start": new Date(year, month, day + 1, 17),
           "end": new Date(year, month, day + 1, 17, 45),
           "title":"Hair cut"
        }
     ]
  };

I want to change this to an AJAX query that retrieves the events from the database. But I can't figure out exactly how to do this. So far I thought of using JSON to get the information from a separate PHP page that calls the data, but how can I manipulate the data to return a proper object?

So far I have something like this:

  $.ajax({
        type: 'POST',
        url: 'test.php',
        dataType: 'json',
        cache: false,
        success: function(result) {
            //how can I copy the results to be able to return them?
        }
    });

And then in test.php I have something like

    $sql = $db->query("SELECT * FROM calendar;");

    $results = array();
    while($row = mysql_fetch_assoc($sql))
    {
       $results[] = array(
          'id' => $row['id'],
          'start' => $row['start'],
          'end' => $row['end'],
          'title' => $row['title']
       );
    }
    echo json_encode($results);

Keep in mind I also have to pass the start and end values through new Date().

Thank you for your time.

if your php page is working properly, it should return an array of events. So the variable result would be that array. You can then create the structure {events: result} and pass it to your calendar.

success(result){
    var myData = {events: result};//now you have your data in correct format.
}

The key problem most people have is that the data that's coming back isn't in the format your expecting.

Try this combination: JS File:

            $.ajax({
                type : 'POST',
                url : 'ajax_handle.php',
                dataType : 'json',
                data: { },
                success: function( jsondata ){

                    $.each( jsondata, function(i, item) {
                        alert( jsondata[i].start );
                    });

                }
            });

PHP File:

    $sql = $db->query("SELECT * FROM calendar;");

    $results = array();
    while($row = mysql_fetch_assoc($sql))
    {
       $results[] = array(
          'id' => $row['id'],
          'start' => $row['start'],
          'end' => $row['end'],
          'title' => $row['title']
       );
    }
    echo json_encode($results);

In the javascript set data to your URL

data: '/calendar/getData.php'

And returned JSON should be something similar to this:

[{"id":1,"start":"2012-09-01T13:15:00","end":"2012-09-01T13:45:00","title":"title"}]

$.ajax({
        type: 'POST',
        url: 'test.php',
        dataType: 'json',
        cache: false,
        success: function(result) {

        $("title").html(result.title);
        $("start").html(result.start);

        }
    });

1. Ajax Request

   var result;

   $.ajax({
       type: 'POST',
       url: 'Service/jsonService.aspx/GetItems',
       data: "{'userID':'" + userID + "'}",
       contentType: "application/json; charset=utf-8",
       dataType: "json",
       async: false,
       error: function (xhr, ajaxOptions, thrownError) {
       },
       success: function (msj) {
          result = JSON.parse(msj.d);
       }
   });
   

2.Week Calendar data :

on $calendar.weekCalendar({... section

data: result

3.Get data from db

var query = (from t in dc.Calendar
                         where t.Status== 1
                             && t.UserID== userID
                         select t).ToList();

sb.Append("[");

foreach (var item in query)
{
     sb.Append(" { ");
     sb.Append("\"id\": \"" + item.ID + "\", ");
     sb.Append("\"start\": \"" + item.DateItem.Year + "-" + item.DateItem.Month + "-" + item.DateItem.Day + "T" + item.StartTime+ "\", ");
                sb.Append("\"end\": \"" + item.DateItem.Year + "-" + item.DateItem.Month + "-" + item.DateItem.Day + "T" + item.EndTime+ "\", ");
     sb.Append("\"title\": \"" + item.Title + "\"");
     sb.Append(" }, ");
}

result = sb.ToString().Substring(0, sb.ToString().Length - 2) + "]";

return result;