How to insert data from a table by selecting data from another mysql table

Question

How to insert data from a table by selecting data from another mysql table

Navigation

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

3

I have the following code:

if($_POST['id']=="id")
{ 

$nomb_idm=$_POST['nombre'];
$n_idm=$_POST['id'];
$nivel_ide=$_POST['nivel'];
$pass_ide=$_POST['pass'];
$correo_idm= $_POST['correo']; 
$usuario_ide= $_POST['usuario']; 

$idfto=$_POST['id'];
$ide_consult = "SELECT * FROM usuarios WHERE id=".$idfto."" ;
$ide_result = $conexion->query($ide_consult);
while( $ide_fila = $ide_result->fetch_array() )
 { $idm=$ide_fila['id_foto'];}

if($idfto===$idm){
    $msj="El usuario existe , crear con un numero de registro diferente o borrar el existente";
    header("Location: index.php?msj=".$msj."");

}else{


$ide_permisos = "INSERT INTO permisos(id, nivel, smenu, status) 
SELECT permisos_default.nivel,permisos_default.smenu,permisos_default.status 
FROM permisos_default 
WHERE permisos_default.nivel LIKE '%{$nivel_ide}%'";
$ide_p_result = $conexion->query($ide_permisos);




$msj="Usuario Creado con Exito";
header("Location: index.php?msj=".$msj.""); 

}


}

With this code what I do is that when I send the user id I use the same one to sign permissions to a table that I call permissions, but taking from the table permissions_default by means of the level of the user.

The code inserts the user but does not insert the permissions in the permissions table.

My problem affects this code:

$ide_permisos = "INSERT INTO permisos(id, nivel, smenu, status) 
SELECT permisos_default.nivel,permisos_default.smenu,permisos_default.status 
FROM permisos_default 
WHERE permisos_default.nivel LIKE '%{$nivel_ide}%'";
$ide_p_result = $conexion->query($ide_permisos);

Any suggestions or help please

Thank you very much!!

mysql php

asked by Alexander Quiroz 11.08.2017 в 01:50

source

2 answers

0

Let's look at each instruction separately:

INSERT INTO permisos(
      id, 
      nivel, 
      smenu, 
      status) 

SELECT 
      permisos_default.nivel,
      permisos_default.smenu,
      permisos_default.status 

FROM permisos_default 
WHERE permisos_default.nivel LIKE '%{$nivel_ide}%'

The INSERT has four columns indicated, while in the SELECT you are selecting only three . The query will not work that way. If id is of type AUTO_INCREMENT or SERIAL you can omit it in INSERT .

It is also important to bear in mind the following:

There should be as many columns in SELECT as in INSERT
The data types of each column must match
The code could fail in cases of INSERT attempts that create duplicate rows
The INSERT will not be made if there is nothing in SELECT , that is, if the conditions of WHERE are not met.

These are things to take into account.

Note:

If the variable you pass in the LIKE comes from an external source (form or another), your code is vulnerable to SQL injection. It is strongly recommended to use prepared queries to avoid it.

answered by 11.08.2017 в 04:40

Query database with SQLAlchemy Get IDS with Drag & Drop

score 2 · Accepted Answer

The problem you suffer is that you are not adding the id of the user to the query:

$ide_permisos = "
  INSERT INTO permisos (
    id,
    nivel,
    smenu,
    status
  )
  SELECT
    {$idfto} id,
    nivel,
    smenu,
    status
  FROM permisos_default 
  WHERE nivel LIKE '%{$nivel_ide}%'
";

This way, each element of the template of the level you have in permisos_default in permisos will be copied using the id of the desired user. The alias id is not necessary, but it will be useful to debug where each value should go or what that number refers to.

Online example: link