Temporary table in MYSQL and its use in PHP

Question

Temporary table in MYSQL and its use in PHP

Navigation

#1 by (2 votes)

2

I'm doing this code:

<?php 
$tempSQL = "CREATE TEMPORARY TABLE IF NOT EXISTS tempUsers
SELECT CONCAT(NOMBRE,' 'AP_PATERNO,' ',AP_MATERNO) AS NOMBRE,
EMP_ID, AREA FROM EMPLEADOS WHERE ESTADO = 1";
mysqli_query($conn,$tempSQL);
if(mysqli_affected_rows($conn)<=0)
  echo 'No fue posible concretar la información';
else
{
  $sql = "SELECT * FROM tempUsers WHERE EMP_ID = $num";
  $result = mysqli_query($conn,$sql);
  if(!$result)
    echo 'Ocurrió un error interno en la base de datos';
  else
  {
    if(mysqli_num_rows($result)<=0)
      echo 'No se encontraron coincidencias en la búsqueda';
    else
    {...}
  }
}

mysqli_query($conn,"DROP TEMPORARY TABLE IF EXISTS tempUsers");
 ?>

My question is, this code is executed with the Jquery event onkeyup I worry about the performance of the server and the creation and destruction of temporary tables constantly, believe that it would be better to do a stored procedure every hour that makes this operation or creating and destroying tables can not affect the database.

Thank you for your experience.

UPDATE WITH MORE INFORMATION This is the problem, I am doing a search by employee id, name or area, search by the 3 areas, the idea of making a temporary table is because there are employees that have their full name with surnames within the name field or separated by name ap_paternal and ap_maternal. What I have not found a way to find a search by name, Juan + Lopez searches for me because the fields are separated. The only way I came up with is to make a temporary table where to concatenate those names and make the query to that table, that way if it works. I tried to create the temporary table in the database and try to collect the data, but at the time of the connection from PHP it shows me that the table does not exist. And that's why I'm doing this experiment.

php mysqli

asked by Alberto Siurob 23.06.2017 в 02:26

source

1 answer

List index out of range Get the columns that are part of a PostgreSQL table

score 2 · Answer 1

My suggestion would be that you do not use temporary tables for this, especially if you are going to execute it every time the user presses a key on a form. The performance of your database server will be adversely affected with all the creation filled in and destruction of temporary tables ... all for a SELECT that is relatively simple.

Instead I would recommend that you use a view. That view, which you can call "people's view", will contain the columns you need: the concatenation of name, paternal surname and maternal surname, the id and the area. And then when you press a key, you would consult the view instead of using a complex method.

The creation code of the view would be simple (I have corrected a typo, missing a comma):

CREATE VIEW vistaPersonas
    AS SELECT CONCAT(nombre, ' ', ap_paterno, ' ', ap_materno) AS nombre, emp_id, area 
       FROM   personas 
       WHERE  estado = 1

And then, the request is considerably reduced when you press on the keyboard (I have changed it to avoid dynamic statements, it is safer to use prepared statements):

Note: I have not tested this code and may contain errors

<?php 

  $stmt = mysqli_prepare($conn, "SELECT * FROM vistaPersonas WHERE emp_id = ?"); 
  mysqli_stmt_bind_param($stmt, "i", $num);
  mysqli_stmt_execute($stmt);
  $result = mysqli_stmt_get_result($stmt);

  if(!$result) {
    echo 'Ocurrió un error interno en la base de datos';
  } else {
    if(mysqli_num_rows($result)<=0) {
      echo 'No se encontraron coincidencias en la búsqueda';
    } else {
      ...
    }
  }