Mysql search data Save to PHP variables

Question

I want to get the value of a query on mysql

SELECT lamp_estado, lamp_descricao FROM lampadas ORDER BY lamp_id

And write each value found in the search into a variable. I am using this command plus it ta recording only the last value of the search.

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $estado=$row["lamp_estado"];
        $descricao=$row["lamp_descricao"];
        $estado2=$row["lamp_estado"];
        $descricao2=$row["lamp_descricao"];
    }
}

Answer 1

This is because you are overwriting the information.

A simple variable cannot store separate information, to do this the variable has to be a array, that is, a list of variables.

When you do:

$estado = $row["lamp_estado"];

The variable $estado will receive the column value lamp_estado of the current line, if there is a value in the variable $estado, that value will be lost. You solve this information loss problem by creating a array(list) of states:

$estado = array();
$descricao = array();
$estado2 = array();
$descricao2 = array();
while ($row = $result->fetch_assoc()) {
    $estado.push($row["lamp_estado"]);
    $descricao.push($row["lamp_descricao"]);
    $estado2.push($row["lamp_estado"]);
    $descricao2.push($row["lamp_descricao"]);
}

The command push is responsible for adding an item to array, it makes the type variable array allocate one more memory space to store a new variable.

For you to write the name of the second state for example, you have to take the content of the second space of the variable $estado. As a array starts counting spaces in 0, the position of the second space of the variable $estado is 1:

echo $estado[1];

Answer 2

Store all query data in an array.

$array = array();

while($row = $result->fetch_assoc()) {
    $array[] = $row;
}

 //exemplo retornado
//Array ( [0] => Array ( [lamp_estado] => estado AAA [lamp_descricao] => lâmpadas LED ) [1] => Array ( [lamp_estado] =>estado BBB [lamp_descricao] => lâmpadas fluorescentes ) [2] => Array ( [lamp_estado] => estado CCC [lamp_descricao] => lâmpadas tubulares ) .....

And to access the array elements:

$estado = $estado2 = $array[1]['lamp_estado'];
echo $estado;   //estado BBB
echo $estado2; //estado BBB

$descricao = $descricao2 = $array[1]['lamp_descricao];
echo $descricao;  //lâmpadas fluorescentes
echo $descricao2; //lâmpadas fluorescentes

---

---

Clarifying this problem of ta gravando apenas o último valor da busca

Imagine a table whose column is called "lamps" and the arrow (->) is the pointer of the list.

-> lâmpadas LED
   lâmpadas fluorescentes
   lâmpadas tubulares

When we use the function $result->fetch_assoc(), it returns the first line:

   while($row = $result->fetch_assoc()) {
   $estado=$row["lamp_estado"];

At this time the variable $state takes on the value LED lamps and the pointer moves one position forward, and our list is as follows:

   lâmpadas LED
-> lâmpadas fluorescentes
   lâmpadas tubulares

At this time the variable $state takes on the value fluorescent lamps and the pointer moves one position forward, and so on.

So that’s why in your while in the variable $estado, as well as the other variables, only the last name appears.

When executing the while the value of the variable $estado is being overwritten because you are not concatenating the values.

You can for example do so to concatenate

$estado = $estado.",".$row["lamp_estado"];

which would result in a string with data separated by comma.