Retrieve data into a variable with php and mysqli

Question

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

@mickmackusa He doesn't need to show all rows, there's just one row. — Barmar, Oct 25 '18 at 20:59
The second answer there shows how to **Fetch a single record**. There are many other duplicates if you'd like to close with another. — mickmackusa, Oct 25 '18 at 21:03
i dont mind the questiong being marked as a duplicate, but i gotta say, Barmar answer was way better than the answer in the link above, anyway, thanks for the replies — Kouhei, Oct 26 '18 at 16:32

Barmar · Accepted Answer · 2018-10-25T21:04:00.680

0

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.

edited Oct 25 '18 at 21:04

answered Oct 25 '18 at 20:43

Barmar

741,623
53
500
612

hey, thanks a lot, it worked and now i can echo the variable, just one more thing, fetching in the only way? – Kouhei Oct 25 '18 at 20:49
I've updated the answer to show how you can insert directly from one table into another. – Barmar Oct 25 '18 at 20:51
Isn't fetching shown in every example in all the documentation and tutorials? I don't know how you could have missed it when you were learning how to use MySQL from PHP. – Barmar Oct 25 '18 at 20:53
i tried a few codes that used fetching too, but i could not manage to get the data inside another table, thats why i tought it was wrong – Kouhei Oct 25 '18 at 20:54
i got a little confused with the second code to make insert with only one query, however, ill study this until i get how it works, anyway, thanks for the answer, you helped a lot – Kouhei Oct 25 '18 at 20:59
See the update I just added about `LAST_INSERT_ID()`, it might be useful. – Barmar Oct 25 '18 at 21:06

Retrieve data into a variable with php and mysqli

1 Answers1