Steps to Convert a SQL Query to a Data Service

With WSF/PHP Data Service library, you can convert a SQL query to a Data Service very easily in few steps.

1. Decide your SQL query first, For the query you may require some input parameters, and you have to decide what should be returned by the query, Say your query is

   $sql_query = "SELECT name, age, email FROM users where country = ?";

   Then the “country” is our input parameter and the name, age, email are our return values.

2. Define the input Format. For the above query it will be something like,

   $inputFormat = array("country" => "INT");

3. Define the output format. We are giving the name “Users” for the out most wrapper element and the name “user” for the wrapper element of each user. Here is how we define it,

   $outputFormat = array("resultElement" => "users",
                   "rowElement" => "user",
                   "elements" => array(
                               "name" => "name",
                               "age" => "age",
                               "email" => "email"));

   For this output format, our expected result payload would be like,

   <users>
     <user>
        <name>xxx</name>
        <age>23</age>
        <email>xxx@xxx.xx</email>
     </user>
     <user>
        <name>yyy</name>
        <age>23</age>
        <email>yyy@yyy.yy</email>
     </user>
     ....
   </users>

4. Define your operation using the sql query, input and output formats.

   $operations = array("getUsersByCountry" => array(
                                 "inputFormat" => $inputFormat,
                                 "sql" => $sql_query,
                                 "outputFormat" => $outputFormat));

5. Define your database configuration in an array like this,

   $config = array(
       "db" => "mysql", // your db engine
       "username" => "myname", // name & password for the db server
       "password" => "mypasswd",
       "dbname" => "db", // the db
       "dbhost" => "localhost");

6. Create a DataService instance using the database configuration and the operations we just created. And call DataServices reply method.

   $ds_service = new DataService(array(
                  "config" => $config,
                  "operations" => $operations));
   
   $ds_service->reply();

That is it. You just exposed your query as a web service. The PHP script URL will be the endpoint URL for the web service.