There are a couple of ways you can access the data. The first is directly using ADO, a COM object built into Windows that can connect to databases. There are a few drawbacks to this method, the most important being that it's not very secure - your database username and password would have to be stored in plain text in the gadget's script file for anyone to see. This isn't quite as bad with limited permissions set.
Most developers would use a combination of PHP and XMLHttpRequest(). The PHP will fetch the data from the database on request, as you suggested yourself. The XMLHttpRequest is what is used for fetching the page contents. The only thing you need to decide is what format to output the data in; XML, JSON or something else. XML and JSON can be parsed by your gadget so either of those is a great choice. If you're running PHP 5.2 or later then JSON support is native. It's straightforward enough to get the data from the database as an associative array and then json_encode it and print the result.
XMLHttpRequest example
var xhr = new XMLHttpRequest();
xhr.open("GET", "http://mysite.com/test.php", true);
xhr.onreadystatechange = function ()
{
// readyState 4 = complete, status 200 = HTTP OK
if (xhr.readyState == 4 && xhr.status == 200)
{
parseData(JSON.parse(xhr.responseText)); // parse a JSON response
// parseData(xhr.responseXML); // parse an XML response
}
}
xhr.send();
Gadgets running on machines with IE8 installed can use JSON.parse() natively, pre IE8 will need to either eval() the data or run it through a safe parser (most of which eval after making sure the data is valid JSON).
More reading:
XMLHttpRequest Object (MSDN)
JSON in JavaScript