Turn PHP MySQL query output into variable

Question

I have a problem that is giving me a headache. I'm fairly new to PHP and MySQL interacting, and coding in general (about 3 months since I first dived into it), so I'm still learning the ropes, so to speak.

The thing is, I have a page that receives data through $_POST from another. All is fine for well. The page receives the data, which is an ID number, and echoes the item's characteristics available in the database corresponding to that ID.

This part of the code works just fine.

$sql = "SELECT nome, preco, `status` FROM produtos WHERE id = $_POST[id]";
            $result =  mysqli_query($conn, $sql);
            if (mysqli_num_rows($result) > 0){
                while($row = mysqli_fetch_assoc($result)){
                    echo "Nome: "."<span style='color: red;'>".$row["nome"].
                    "</span>".
                    "<br>"."Preço: "."<span style='color: red;'>".
                    "R$".$row["preco"]."</span>"."<br><br>".$row["status"]; 
                }
            }

What I want, is to turn the values echoed into variables, so that I can set them as default value in another form and send that one to another page. Apparently, $row['nome'], for example, isn't available for re-use outside of the instance above.

<form method="post" action="?p=venda">
        <input type="text" name="nome" value="<?php echo $row["nome"]; ?>">
        <input type="text" name="preco" value="<?php echo $row["preco"]; ?>">

        <input type="submit" name="change" value="Efetuar">
    </form>

I know this code is prone to SQL injection, but I'm not looking into it right now. This will be a sort of offline program to help me with organizing some of my stuff, so, for now, I don't have to worry about security (not that I'll keep ignoring these issues).

Thanks in advance.

Answer 1

here is my table example if I was going to list the whole db table in a scrollable list, and submit it to a file called detail.php

     <?php
    $sql = "SELECT nome, preco, status FROM produtos";
    $result = $conn->query($sql);  

    echo "<table align=center bgcolor=e3fab5 ><td>";

    echo '<div style="width: 500px; height: 450px; overflow: auto; border 5px dashed black; background color: #ccc;">';
    echo "<table align=center bgcolor=fff2e2 border=1>\n";
     while ($data = $result->fetch_row()) {
  echo '<tr><th>Nome</th><th>Preco</th><th>Status</th></tr>';
   echo '<tr>';
      for ($m=0; $m<$result->field_count; $m++) {
        if ($m==0){
        $nome='';
        $nome=$data[$m];
        echo '<td bgcolor="#ff0000"><form action="detail.php" method="post"><input type="submit" name="id" value="'.$nome.'"></td>';
         } else if ($m==1){
        $preco='';
         $preco=$data[$m];
        echo '<td bgcolor="#ff0000">'.$preco.'<input type="hidden" name="preco" value="'.$preco.'"></td>';
          }else if ($m==2){
         $status='';
         $status=$data[$m];
        echo '<td bgcolor="#ffffff">'.$status.'<input type="hidden" name="status" value="'.$status.'"></td>';
           }
        }

        echo  "</form></tr>";

          }
          echo "</table>";
          echo "</div></table>";
    ?>

Answer 2

OK I'll tell you this, its actually unnecessary to sanitize every post. Its only necessary if your authenticated users are web users and there is a blank that they type code in.

In closed production environments, where your users are, you can safe guard the environment in several ways, but this is off topic here.

anyways, you have a DB chart that has a an Id column, the item name, and I am guessing stock or production status.

your display, I don't use spans, but I'll show you how I do it with tables. So lets make your query for your colums: nome, preco, status Here are the two methods, the first one is called looped result method which is mostly used for more than one row in the db table:

     <?php
   //load it in a variable and trim the outside spaces away if you are entering this from a blank form
    $id=trim($_POST[id]);

   ///this should look familiar to you
 $sql = "SELECT nome, preco, status FROM produtos WHERE id ='".$id."'";
    ///but I use the shorthand method here to get my results.
      $result = $conn->query($sql);  
     ///Now we loop and shift colum information into variables
     /// in a incremental loop, the colums are numbered starting with 0

   echo "<table align=center bgcolor=fff2e2 border=1>\n";
     while ($data = $result->fetch_row()) {
    ////I print my table header, and start the data row, if I want it as several ids I will reset here too (which I will do here if you want to play with this)

   echo '<tr><th>Nome</th><th>Preco</th><th>Status</th></tr>';
   echo '<tr>';
      for ($m=0; $m<$result->field_count; $m++) {
        if ($m==0){
        $nome='';
        $nome=$data[$m];
        echo '<td bgcolor="#ff0000">'.$nome.'</td>';
         } else if ($m==1){
        $preco='';
         $preco=$data[$m];
        echo '<td bgcolor="#ff0000">'.$preco.'</td>';
          }else if ($m==2){
         $status='';
         $status=$data[$m];
        echo '<td bgcolor="#ffffff">'.$status.'</td>';
           }
        }
     //////now if I was building a query chart with submit to another I would put my form and my hidden inputs here before i close the table row with /tr, and my submit button would be my first cell value 
        echo  "</tr>";

          }
          echo "</table>";

You could do the regular colum /row method since it is one ID, which would look like this I used the span format here so you can get the idea of how html is typically expressed in php:

               $id=trim($_POST[id]);
         $sql = "SELECT nome, preco, status FROM produtos WHERE id ='".$id."'";
        $result =  mysqli_query($conn, $sql);
        $row = mysqli_fetch_assoc($result);
        $nome=stripslashes($row['nome']);
        $preco=stripslashes($row['preco']);
        $status=stripslashes($row['status']);

         echo 'Nome: <span style="color: red;">'.$nome.'</span><br>Preço: <span style="color: red;">'.$preco.'</span><br><br>'.$status; 

///notice my quote usage above

Answer 3

Assign $row to a variable and treat it as an array() outside the while loop.

$sql = "SELECT nome, preco, `status` FROM produtos WHERE id = $_POST[id]";
$result =  mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0){
    while($row = mysqli_fetch_assoc($result)){
        $array = $row; // assign $row a variable.

        echo "Nome: "."<span style='color: red;'>".$row["nome"].
        "</span>".
        "<br>"."Preço: "."<span style='color: red;'>".
        "R$".$row["preco"]."</span>"."<br><br>".$row["status"]; 
    }
}

// example
echo($array['nome']);