How to save SELECT output?

Asked

Viewed 623 times

1

I need to enter data in an A table, but I have an unknown data and need to search it in table B.

Thus, I need to make a SELECT in table B and save it for later insertion in table A.

I got the result I expected, but it’s a palliative solution:

$cod = $_POST['descricao']; 
$produto = $_POST['codigoproduto'];
$quant = $_POST['quantidade'];

$sqlcode = mysql_query("INSERT INTO tabelaA(codigo, codigoproduto, quantidade) SELECT codigo, 9212, codigo FROM tabelaB WHERE descricao='$cod'");
$sqlcode1 = mysql_query("UPDATE tabelaA SET codigoproduto='$produto', quantidade='$quant' WHERE codigoproduto=9212");

How to do it properly?

php mysql sql query
  • 1

    The problem is getting the query return?

  • 2

    Which field do you need to pick from table B? It’s the field codigo?

  • @I have exactly the field codigo

  • how about $sqlcode = mysql_query("INSERT INTO tabelaA(codigo, codigoproduto, quantidade) SELECT codigo, '$produto', '$quant' FROM tabelaB WHERE descricao='$cod'");?

  • @Bacco I need to know the value of the field codigo, in table WHERE descricao='$cod' then use the value of codigo in tableA. I understood? :(

2 answers

1

You can use a subquery, which is a query inside the other, like this:

$cod = $_POST['descricao']; 
$produto = $_POST['codigoproduto'];
$quant = $_POST['quantidade'];

// aconselho a usar o LIMIT 1 para agilizar a consulta
$selectCodigo = "SELECT codigo FROM tabelaB WHERE descricao LIKE $cod LIMIT 1";

// não esqueça dos parênteses entre a variável $selectCodigo
// os parênteses são necessários para separar a subquery da query principal
$sqlcode = mysql_query("INSERT INTO tabelaA (codigo, codigoproduto, quantidade) VALUES (($selectCodigo), $produto, $quant)");

1


As you have noticed when using the constant 9212 in the INSERT..SELECT, you can fix some of the values returned by SELECT - I mean, instead of getting the table value, you set a constant to represent the field value.

So, in your INSERT command, you can get a table value and the other values you get from your variables:

$sqlcode = mysql_query("INSERT INTO tabelaA(codigo, codigoproduto, quantidade) SELECT codigo, '$produto', '$quant' FROM tabelaB WHERE descricao='$cod'");

Just remembering, as usual, that you are relying on user information and sending it straight to your database, which is a security flaw (see "SQL Injection").

  • Thank you, it worked perfectly. What do you mean by "user information"? In my case, all these variables come from an application, that is, the user only informs the amount

  • 1

    @Renesá Whenever you concatenate in your query a value that was obtained from outside of your application (that comes from another application, or from a configuration file, or that has been entered by the user) you are subject to SQL Injection. If the user-informed "amount" is what you concatenate in your query, you are subject to SQL Injection. Getting rid of this risk and still possibly gain performance is simple: take a look at how to use parameters in SQL commands instead of concatenating them in the query. Success there!

Browser other questions tagged php mysql sql query

You are not signed in. Login or sign up in order to post.