Hi,
I querying my database using a script in PHP and returning the result as a JSON (so that it can be retrieved in Javascript).
However I notice that querying the database (sending the query to the database) takes about half the time if takes PHP to retrieve the rows (row by row using monetdb_fetch_object).
In the documentation I see that the MAPI interface has a fetch_all_rows function, however I can't find this function in the PHP library. In the Python example I see that you are also using a cursor.fetchall() procedure.
What is the best way to approach this problem > querying the database and returning all rows as a JSON object Is PHP the way to go (it is the easiest for me, since I know that), if it is, what is the best way or should I consider another language like Python or maybe Java to implement this function?
Below I list the PHP code I see that send the query ($q_statement) to the database and formulating the JSON:
// Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds error_log($execution_time.'sec - Formulate query'); $time_start = microtime(true); $db = monetdb_connect($lang = "sql", $host = "127.0.0.1", $port = "50000" , $username = "monetdb", $password = "monetdb", $database = "social" ) or die(monetdb_last_error()); $res = monetdb_query($db, monetdb_escape_string($q_statement));
// Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds error_log($execution_time.'sec - Query database'); $time_start = microtime(true); /* Iterate over the result set returning rows as objects */ while ( $row = monetdb_fetch_object($res) ) { $rows[] = $row; }
echo json_encode($rows);
// Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds $total_execution_time = ($time_end - $initial_start); error_log($execution_time.'sec - Return records'); error_log($total_execution_time.'sec - Total time'); $time_start = microtime(true);
thanks in advance,
Richard