使用php和mysqli [duplicate]将数据检索到变量中

Question

This question already has an answer here:

• mysqli query results to show all rows 3 answers

i've been trying for quite some time already to get this working, but no success at all

I looked around many places, and by what i understood, the method im using isnt retrieving the data i want, but in fact, ALL the data inside the DB (despite there only 1 value to be returned)

This is my code right now:

$query = "SELECT id from produtos where tipo = 'Tubo' and inteiro_pedaco = '$tipo' and marca = '$marca' and comprimento = '$comprimento' and diaexterno = '$externo' and diainterno = '$interno'";
$result = $conec->query($query);

echo $result;
die;

At the code above im trying to retrieve ID from a table named produtos

And here is the table 'produtos' content:

id: 102 | tipo: Tubo | inteiro_pedaco: Inteiro | marca: Science | comprimento: 1000 | diaexterno: 1 | diainterno: 1 |
id: 103 | tipo: Whatever | inteiro_pedaco: Whatever | marca: Whatever | comprimento: Whatever | diaexterno: Whatever | diainterno: Whatever |

etc...

$result variable was supposed to retrieve "102"

After retrieving 102, i want to echo it just for tests purposes

However, if i can manage to make it work and echo "102", my next step is making an insert into ANOTHER table with $result content, which is "102"

I want to insert at entrada_produtos table some data with the following command:

mysqli_query($conec,"INSERT INTO entrada_produtos (fk_id, usuario, data_inclusao, qtd) VALUES ('$result', '$usuario', now(), '$qtd')");

Any help would be appreciated, plus, i dont want just some code working, i would like to understand how it works

If possible, try to explain any code posted bellow, it would be of great help, also, i want to make it as simple as possible, i dont wanna use like 10 lines of code just to retrieve some data into a variable (if its the only possible way, then there's nothing i can do, but go this way...)

Thanks in advance

</div>

Answer 1

You need to fetch the results:

$row = $result->fetch_assoc();
$id = $row['id'];
echo $id;

fetch_assoc() returns the next row of results as an associative array.

You can then use the $id variable in your INSERT query.

There's no need to use two queries to insert this into another table, you can do that with one query.

$stmt = $conec->prepare("
    INSERT INTO entrada_produtos (fk_id, usuario, data_inclusao, qtd)
    SELECT id, ?, now(), ?
    FROM produtos 
    where tipo = 'Tubo' 
    and inteiro_pedaco = ?   
    and marca = ? 
    and comprimento = ? 
    and diaexterno = ? 
    and diainterno = ?");
$stmt->bind_param("sssssss", $usuario, $qtd, $tipo, $marca, $comprimento, $externo, $interno);
$stmt->execute();

I've rewritten this as a prepared statement to prevent SQL injection. The ? in the query are placeholders, which are filled in with the variable values given in the call to bind_param().

BTW, if you're selecting the row that you just inserted into produtos, you can use the MySQL function LAST_INSERT_ID() or the PHP variable $conec->insert_id to get the auto-increment ID that was assigned, you don't need a query for that.