Roku Developer Program

[lbell]

Does anyone know how I'm supposed connect to a database? Am I supposed to use php to handle the request and how would brightscript communicate with the database or php file to send the search request and receive the results...I know I'm supposed to use rourltransfer but I'm lost on how this is all supposed to work together...nothing seems to be working right.
I'm very new at this so some example code would help a lot.

[Romans_I_XVI]

Are you using a template? The Video Player template includes urlUtils.brs . This will make your life a lot easier if you use this. I have been playing around with getting data from a SQL database using PHP and turning the result into a XML format which is very easy for Roku to parse and get data from. Here is an example (note- this uses functions from urlUtils.brs)

   
 http = NewHttp("The URL to my php file")
 rsp = http.GetToStringWithRetry() 
 xml=CreateObject("roXMLElement")
 if not xml.Parse(rsp) then
        print "Can't parse feed"
 else
        print "working"
 endif

I don't know your experience with PHP and SQL, I don't have much myself, but I was able to tell the PHP file to access the SQL database and echo the table in a XML format. It's working great for me. Just make sure to have a look at the Video Player example. It includes most of what you are wanting.

Note - I don't know if this is the official way of accessing a SQL database, or if there even is an official way. But again, it is working fine for me.

[lbell]

Thanks Romans(:
Yeah, I combined a couple of them, I'll take a look at the urlutils.
But I need to search the database not just get all the results..how would I send the search request to a php file to use to search the database? If that's even possible.. I haven't ever used php so I don't what I'm doing..if there's an easier way to do it that would be awesome, cause the main goal is to have a search function I don't really care how I get ther.

[greubel]

You could construct the encoded URL with a query string appended to the CGI request.

http://www.mysite.com/env.cgi?type=movie&title=%22Some%20Movie%22

%22 = quote, %20 = space
Notice the "?" Every thing after becomes the query. Then it's up to the server to parse and do the SQL.

ENV data available to the script at the server

DOCUMENT_ROOT = /home/me/mysite.com
GATEWAY_INTERFACE = CGI/1.1
HTTP_ACCEPT = text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_ACCEPT_ENCODING = gzip, deflate
HTTP_ACCEPT_LANGUAGE = en-us
HTTP_CONNECTION = keep-alive
HTTP_DNT = 1
HTTP_HOST = www.mysite.com
HTTP_USER_AGENT = Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.1.25 (KHTML, like Gecko) Version/8.0 Safari/600.1.25
PATH = /usr/local/bin:/usr/bin:/bin
QUERY_STRING = type=movie&title=%22Some%20Movie%22
REMOTE_ADDR = xxx.xxx.xxx.xxx
REMOTE_PORT = 49733
REQUEST_METHOD = GET
REQUEST_URI = /env.cgi?type=movie&title=%22Some%20Movie%22
SCRIPT_FILENAME = /home/me/mysite.com/env.cgi
SCRIPT_NAME = /env.cgi
SCRIPT_URI = http://www.mysite.com/env.cgi
SCRIPT_URL = /env.cgi
SERVER_ADDR = xxx.xxx.xxx.xxx
SERVER_ADMIN = webmaster@mysite.com
SERVER_NAME = www.mysite.com
SERVER_PORT = 80
SERVER_PROTOCOL = HTTP/1.1
SERVER_SIGNATURE = 
SERVER_SOFTWARE = Apache
UNIQUE_ID = VHyhV0BvfxsAAAyZGpIAAAAD

[lbell]

I could but how would I display the results on the roku channel?

[Romans_I_XVI]

This is an example of a PHP file you would use to search a database. The URL would look something like this...

http://yourserveraddress.com/search.php?table=movies&search=Rocky

This would serch the "movies" table for "Rocky" in the column ,title, and return any results in a XML structure for Roku. You could have as many columns as you want but for the sake of this example we will just have the columns "Title" and "Description". Idealy you would have a column for each Content-Meta Data item necessary for Roku.
http://sdkdocs.roku.com/display/sdkdoc/ ... +Meta-Data

And use the functions I listed in my other post to get it in to a roXMLElement object in Roku

<?php 
$servername = ""; //This is your server address
$username = ""; //This is the username to access the database
$password = "";  //This is the password to access the database
$dbname = ""; //This is the name of the database

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM `".$_GET['table']."` WHERE `Title` REGEXP CONVERT( _utf8 '".$_GET['search']."' USING latin1 ) COLLATE latin1_swedish_ci LIMIT 0 , 30 ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // echo the XML declaration
    echo '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
    //echo the XML section
    echo '<movies>';
    while($row = $result->fetch_assoc()) {
        echo "<item><title>".$row["Title"]."</title><description>". $row["Description"]."</description></item>";
    }
    echo '</movies>';
} else {
    echo "0 results";
}
$conn->close();
?> 

[lbell]

Thanks Romans (: