In this procedure, it only returns numeric values if it exists, that is, if it exists, it returns a value true
+ 1
row, but it does not return the registered data from the database to ajax.
fetch.php code:
if(isset($_POST["employee_id"])) {
$stmt = $con->prepare("SELECT name,address,gender,designation,age FROM tbl_employee WHERE id = '".$_POST["employee_id"]."' ");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($name,$address,$gender,$designation,$age);
while ($row = $stmt->fetch()) {
echo json_encode($row);
}
}
Result:
As seen does not return any user data
Instead, with this procedure, it returns the registered data from the database.
fetch.php code:
if(isset($_POST["employee_id"])) {
$query = "SELECT * FROM tbl_employee WHERE id = '".$_POST["employee_id"]."'";
$result = mysqli_query($connect, $query);
$row = mysqli_fetch_array($result);
echo json_encode($row);
}
Result:
I carry out the var_dump
code from A. Cedano's answer.
var_dump($arrResultado);
Result ofvar_dump
[{"name":"1","address":"1","gender":"Male","designation":"1","age":1}]array(1) { [0]=> array(5) { ["name"]=> &string(1) "1" ["address"]=> &string(1) "1" ["gender"]=> &string(4) "Male" ["designation"]=> &string(1) "1" ["age"]=> &int(1) } }
Ya lo habia mencionado @A.Cedano, sobre el problema del driver
mysqlnd
aquella extensiónmysqlnd
(controlador nativo de MySQL) no esta siempre disponible en otros equipos.Pero si ese problema no fuera problema, se podría hacer eso con bastante facilidad, simplemente no puedes hacerlo con el
mysqli_stmt
objeto, tienes que extraer el subyacentemysqli_result
, y hacerlo simplemente llamandomysqli_stmt::get_result()
.También puedes observar más información en SO ingles, puedes observar algunas ventajas y desventajas.
Cómo lo dije anteriormente si el driver
mysqlnd
no sería un problema, la solución sería la siguiente:De lo contrario, puede consultar los elementos de la matriz directamente en
bind_result()
, en lugar de usar variables separadas.Now I understand.
I want to tell you a few things about your code, in order of importance.
1. Your code is vulnerable
You may have heard of SQL injection. Which is avoided by using prepared queries. But it turns out that you don't actually prepare anything by writing the query like this:
Well, the fact of passing this value directly in the query
$_POST["employee_id"]
is the real vulnerability of the code, since that value can be changed by a malicious user and you will have the injection.We will fix that vulnerability in the final code proposal.
If you want to know more about the risk of SQL Injection , you can read the following questions and their answers:
2. The problem of getting an associative array with mysqli and prepared queries
The MySQLi extension has its limitations. And one of them is that getting an associative array when using prepared queries can become a real headache, when you don't have the driver called
mysqlnd
. And even if you have it on your current machine and write code based on that driver, that code won't work for you if you run it on another computer that doesn't have the driver installed.I raised that problem here: How to get an associative array using prepared queries with mysqli? , and the accepted answer allowed us to implement a function in our code that will allow us to do what we want, without having to resort to a driver that may be installed on some computers and not on others. It's a better solution, because then the code won't crash. If it is a procedure that you are going to do very frequently, you can incorporate that function into a utility class to use it more easily, by creating instances of that class.
3. Proposed Solution
Taking into account what was said before, I propose this solution:
4. Example based on a real scenario
I share here a demo that I have made.
The code is sufficiently commented in case there is something you don't understand.
A.HTML/JAVASCRIPT code
You will notice that:
in the form, I have cleverly given each element an id that is equal to the column names of them in the table. That way, when reading the Ajax response, I'll be able to reference each element by reading the value of
key
it from the JSON, thus setting its value.there is a
div
dedicated one to present the possible errors that occur. (This is optional, although it is very useful for information).I have improved the code of the Ajax request, making it clearer, using
done
, since itsuccess
is deprecated since jQuery 3, and also incorporating a functionfail
to handle possible errors in the request.Everything else is commented out in the code.
b.PHP code
The PHP code uses the function I talked about in my answer to query the database. Also, it controls any eventuality that occurs and enters it in an array. When any error occurs, said array will have a called key
error
and a message with the reason for the error (you will see that later in the Ajax response it searches if there is a called keyerror
and if it is found, the corresponding message is printed in adiv
dedicated exclusively to those purposes.Result
The server-side output is a JSON array similar to this:
Los corchetes de apertura y de cierre significa que se trata de un Array JSON, que no es lo mismo que un Objeto JSON. Por eso en la respuesta de Ajax no he puesto el
dataType: "json"
, tengo que recibir el array y convertirlo a objeto JSON usandoparseJSON
.El formulario se llenará más o menos así al hacer clic en el botón:
Aquí tienes un demo del código que ejecuta la petición Ajax.
Te toca a ti adaptarlo, si quieres optar por esa vía de solución.
Espero te sirva.
Nota: Cabe destacar que gran parte de la solución a tu problema parece complicada debido al problema planteado en el punto 2 de la respuesta. Si estuviéramos usando PDO por ejemplo, éste cuenta con métodos adecuados para convertir nuestros resultados en un array asociativo que sería convertido posteriormente a JSON sin mayores complicaciones.