How to query a column in the database and view filtered in 3 html columns?

Asked

Viewed 46 times

1

good morning. I have a question that I have never come across before with php and mysql. I have the following example table in mysql:

ITEM     :   DEP   :    QTD
=============================
 1       :  T01    :   1000
 2       :  T02    :   2000
 3       :  T03    :   3000
 4       :  T01    :   4000
 5       :  T03    :   5000
=============================


    Tendo esta tabela acima no mysql, eu preciso exibir uma página com uma
 tabela HTML dessa maneira abaixo:


TABELA HTML PARA EXIBIR
================================
ITEM : T01   :  T02  :  T03 
 1   : 1000  :   0   :   0
 2   :  0    : 2000  :   0
 3   :  0    :   0   : 3000
 4   : 4000  :   0   :   0
 5   :  0    :   0   : 5000
================================

Explaining: I need to display a query that shows me in separate columns the Qtd of each deposit.

I did some research, and I don’t know how to do that, and I don’t know where to go with the search. I tried to make a select for each deposit and while inside while and failed. Some way to do this?

Below is my script, only I have no idea how to do it, who has an idea of how to do it, or a direction for me to research I will be very grateful.

<?
$sql = mysql_query ("SELECT item, dep, qtd FROM estoque  ");
?>

 <table>
    <thead>
        <tr>                
            <th>Item</th>
            <th>T01</th>
            <th>T02</th>
            <th>T03</th>
        </tr>
    </thead>


    <tbody>
        <? while($r = mysql_fetch_array($sql)){ ?>
        <tr> 
            <td><? echo $r['item']; ?></td>
            <td></td>
            <td></td>
        </tr>
        <? } ?>

    </tbody>
 </table>
query select while

  • By the way you will have to do a table pivot. Have you tried this?

  • Pivot table, would be an auxiliary table for this case?

  • I found something like select case when then. I’m searching about this.

  • You are right, searching on pivot table, I see that this is the way. Thanks for the tip. As soon as I learn how to do it, I put it here as it was. Hug!

1 answer

0

I once had a similar problem, I would solve your problem as follows:

If any item is repeated in the stock table: - Create a table listing all items - I would make 2 selects as follows

 <table>
    <thead>
        <tr>                
            <th>Item</th>
            <th>T01</th>
            <th>T02</th>
            <th>T03</th>
        </tr>
    </thead>
    <tbody>
        <? 
        $sql = mysql_query ("SELECT nome FROM itens");
        while($r = mysql_fetch_array($sql)){ ?>
            <tr>
                <td>
                    <? $busca = mysql_query ("SELECT qtd FROM estoque WHERE item = $r[0] and dep = 'T01'");
                    if($i = mysql_fetch_array($busca)){ 
                        echo $i[0];
                    }
                    ?>
                </td>
                <td>
                    <? $busca = mysql_query ("SELECT qtd FROM estoque WHERE item = $r[0] and dep = 'T02'");
                    if($i = mysql_fetch_array($busca)){ 
                        echo $i[0];
                    }
                    ?>
                </td>
                <td>
                    <? $busca = mysql_query ("SELECT qtd FROM estoque WHERE item = $r[0] and dep = 'T03'");
                    if($i = mysql_fetch_array($busca)){ 
                        echo $i[0];
                    }
                    ?>
                </td>
            </tr>
        <? } ?>
    </tbody>
 </table>
If no item is repeated in the stock table, I would do as follows:

 <table>
    <thead>
        <tr>                
            <th>Item</th>
            <th>T01</th>
            <th>T02</th>
            <th>T03</th>
        </tr>
    </thead>
    <tbody>
        <? 
        $sql = mysql_query ("SELECT item, dep, qtd FROM estoque");
        while($r = mysql_fetch_array($sql)){ 
            echo '<tr>';
                if($r[1] == 'T01') {
                    echo '<td>'.$r[2].'</td>';
                    echo '<td>0</td>';
                    echo '<td>0</td>';
                } elseif(($r[1] == 'T02') {
                    echo '<td>0</td>';
                    echo '<td>'.$r[2].'</td>';
                    echo '<td>0</td>';
                } else {
                    echo '<td>0</td>';
                    echo '<td>0</td>';
                    echo '<td>'.$r[2].'</td>';
                }
            echo '</tr>';
        } ?>
    </tbody>
 </table>

  • I will try to adjust this script to my code. If it works, put here.Thanks

Browser other questions tagged query select while

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