The problems in your code:
Problem 1:
It should be:
echo "<td>" . $response['latitude'] . "</td>";
echo "<td>" . $response['longitude'] . "</td>";
or:
echo "<td>" . $row['ponto_trajeto_latitude'] . "</td>";
echo "<td>" . $row['ponto_trajeto_longitude'] . "</td>";
or:
echo "<td>" . $row[0] . "</td>";
echo "<td>" . $row[1] . "</td>";
Problem 2:
It should be $response instead of $responde overall. Or $responde instead of $response. But not both.
A code proposal:
Don't worry, the comments are long, but the actual code is short.
Feel free to ask anything.
Good luck.
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'dbname');
define('USERNAME', 'user');
define('PASSWORD', 'pass');
// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); // SET IT TO 0 ON A LIVE SERVER!
/**
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* See: http://php.net/manual/en/class.mysqli-driver.php
* See: http://php.net/manual/en/mysqli-driver.report-mode.php
* See: http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/**
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
$sql = 'SELECT
ponto_trajeto_latitude,
ponto_trajeto_longitude
FROM tb_pontos_trajeto';
/*
* Prepare the SQL statement for execution - ONLY ONCE.
*
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* See: http://php.net/manual/en/mysqli-stmt.execute.php
*/
$executed = $statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* See:
* http://php.net/manual/en/mysqli-stmt.get-result.php
* https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch data - all at once - and save it into $fetchedData array (associative array).
* It looks like this. Each array item represents a record in db table.
*
* Array
* (
* [0] => Array
* (
* [ponto_trajeto_latitude] => 50.43
* [ponto_trajeto_longitude] => 8.368
* )
*
* [1] => Array
* (
* [ponto_trajeto_latitude] => 48.34
* [ponto_trajeto_longitude] => 8.23476
* )
*
* )
*
* See: http://php.net/manual/en/mysqli-result.fetch-array.php
*/
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);
// ... OR fetch one row at a time, instead of using fetch_all().
// while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
// $fetchedData[] = $row;
// }
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* See: http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* See: http://php.net/manual/en/mysqli-stmt.close.php
*/
$statementClosed = $statement->close();
// Just for testing (delete it): Display fetched data.
echo '<pre>' . print_r($fetchedData, TRUE) . '</pre>';
echo json_encode($fetchedData);
Code differences:
The main difference:
As you probably know, each input value coming from the user (e.g. browser) is a potential threat to your system. The process of passing such harmful values into the data access layer is named "SQL injection". So they should/must be validated, filtered, sanitized, escaped, (encoded if needed).
mysqli_query can not "secure" the input values passed to the sql statement. So, each value must be treated separately from this point(s) of view, before being passed into the sql statement. Especially the escaping mechanism is important in avoiding eventual harmful effects.
In comparison, by preparing an sql statement using prepare() (followed by bind_param() and execute()), all potentially harmful input values are automatically escaped by the PHP engine, and are therefore transformed in harmless values.
That's the big picture. For more details regarding the SQL injection see the (accepted) answers from here and here, and read How to use mysqli properly, for example.
In your code you didn't pass any input values into the sql statement. E.g: you have, for example, no WHERE clause. So, you could have further used mysqli_query() without any security issues. But, if you would have needed something like ... WHERE id = $pontoTrajetoId ..., then mysqli_query would not have been able to protect your system from an eventually harmful value of the variable $pontoTrajetoId. Instead, prepare() would have done a great job on this part.
The second difference:
The 2nd difference relies in the use of fetch_all() in my code, compared with the need of a while($row = mysqli_fetch_array($result)) {...} loop in your code. If you read my code, under fetch_all() you'll discover the same while loop as an alternative (commented). The difference is just, that fetch_all() fetches all records in an array, at once. On the other hand, fetch_array() reads only one record (at a time).
The third difference:
The difference resides in the "activation" of error reporting - in my code, with two-three lines of code.
By default, the PHP engine triggers an error every time a failure occurs in any PHP function. Please note that there are multiple error types/levels in PHP: notices, warnings, fatal errors, etc. By using
error_reporting(E_ALL);
ini_set('display_errors', 1); // SET IT TO 0 ON A LIVE SERVER!
I am just telling PHP that I need it to display all errors, e.g. of any level, on screen.
Because all mysqli functions are part of PHP, they throw anyway errors (of type warning) on failure. But, by using
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
I am telling PHP to treat and throw them as exceptions. Read this.
Now, here you'll have to be very careful, to NOT display any data from the original error on the screen when you are deploying your website on the live server. That's why I commented like this:
ini_set('display_errors', 1); // SET IT TO 0 ON A LIVE SERVER!
Why? Because many errors are described by a lot of sensitive details (error message, error number, the file and line in which the error was raised, etc), which can provide too much informations regarding your sytem to an unknown user. The proper way to go with this would be to activate the logging of the errors in log files and either display a general, user-friendly error message to the user, or show a user-friendly html page containing a general error message in it - like "Unfortunately an error occurred during your request. Please try again or contact our support team".
The logging and the error/exception page/message display should be handled inside two functions of your choice. Condition is, that the names of the two functions are to be passed as arguments to the following functions: set_error_handler() and set_exception_handler(). If needed, you can involve a third function too. Its name should be then passed to register_shutdown_function(), which "* Registers a callback to be executed after script execution finishes or exit() is called.*".
In order to perfectly understand how you should correctly proceed with the error reporting, read this and this. It's easy to understand and follow.
Bottom line, instead of validating the result of a mysqli_query(), or checking the presence of a connection error by calling mysqli_error() - as you did in your code, I just let the PHP engine do his job: to throw an error/exception by itself and to display it on screen. Again, read the error reporting links that I gave you.
Well, I hope I could guide your next steps and, if you have more questions, just ask. I'll be happy to answer.
