Blocking when using AJAX making multiple queries to MySQL

Question

Blocking when using AJAX making multiple queries to MySQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I could not determine a good title for my problem but it is the following one, I am doing a small page to make queries to a database, the idea is that while the user writes in an input the results are shown in a table , for that I am implemented AJAX, a remote MySQL server and PHP, running the page from my PC runs very well, at the time of writing it looks for the data in the remote server and shows them at the moment, the problem is that I uploaded this page to a paid hosting and there at the time of writing, after doing a few queries is blocked and no longer looking for anything, I guess it has to be speed or something, but I can not find a way to optimize my code, I hope you can help me . I attached my page to do the tests: www.oaxaqueando.com just look for names of people and notice that it is blocked and no longer looking, the code I put it below:

function buscar_datos(consulta)
{
$.ajax({
	url: 'buscar.php',
	type: 'POST',
	dataType: 'html',
	data: {consulta: consulta},
})

.done(function(respuesta) {
	$("#datos").html(respuesta);
})
}

$(document).on('keydown','#caja_busqueda',function(){
   var valor =  $(this).val();
         buscar_datos(valor);
});

<!DOCTYPE html>
<html lang="es">
    <head>
        <meta charset="UTF-8">
        <title>Document</title>
    </head>
    <body>

        <section>
            <div class="form">
                <input type="text" name="caja_busqueda" id="caja_busqueda">                              
            </div>
            <div id="datos">              
            </div>
        </section>

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script src="main.js"></script>
                  
    </body>
</html>

<?php

$buscar = str_replace(" ", "%", $_POST['consulta']);
$salida = "";

if ($buscar != "") {

$mysqli = new mysqli("servidor", "usuario", "contraseña", "bd");
mysqli_set_charset($mysqli, 'utf8');

$query = "SELECT * FROM bd_secjo.contactos where concat(nombres, ' ', apellido_paterno, ' ', apellido_materno) like '%" . $buscar . "%' limit 10;";

$resultado = $mysqli->query($query);

if ($resultado->num_rows > 0) {

    $salida = "<table class='tabla_datos'>
       <thead>
            <tr>
                 <td></td>
                 <td></td>
                 <td></td>
            </tr>
        </thead>
            <tbody>";

    while($fila=$resultado->fetch_assoc()){
        $salida = $salida . "<tr>" .
                "<td>". $fila['Nombres'] . "</td>" .
                "<td>". $fila['Apellido_Paterno'] . "</td>" .
                "<td>". $fila['Apellido_Materno'] . "</td>" ;
    }

    $salida = $salida . "</tbody></table>";

} else {
    $salida = "Sin resultados";
}

$mysqli->close();

}


echo $salida;

I use Windows 7, MySQL, Godaddy Hosting of 1 CPU, 512mb of RAM

mysql javascript html php ajax

asked by Carlos Daniel Zárate Ramírez 28.08.2018 в 17:52

source

2 answers

How to accommodate the fields of a form with bootstrap? Operator overload + in C ++

score 1 · Answer 1

I'm not an ax in optimization and I have not set up a test scenario to test your example, but the biggest problem I'm seeing in your code is that you relegate too much work in the PHP you call for AJAX.

Notice that for every time a key is pressed, the call is launched and the system waits for the PHP to build the response, in this case it goes through what it recovers from the database and builds a table . And he thinks that while he is doing this, the user will press another key and it is possible that he has not finished building the response of the previous request by then.

Personally, I like to return JSON objects in my AJAX calls and that it is the client side that "paint" the HTML. In your code, with that you would free the server a lot of work, that being a poor host, it is not going as fast as a person can write in your search box.

Greetings and I hope it helps you.

score 1 · Answer 2

Look at my site www.glosarioit.com I use JQuery and especially the Autocomplete driver. I also use PHP and AJAX, but I add JQuery.

As you can see when you want to search for a term in the search field it is shown according to the match. I have more than 6000 definitions loaded in MySQL and no delay occurs. I hope it serves you, above all, as a reference.