For each result, a new line in php

Question

I have a database where in the table users I have the columns:

+----------+------------+----------+------------+----------+------------+
| imp_cod1 | imp_local1 | imp_cod2 | imp_local2 | imp_cod3 | imp_local3 |
+----------+------------+----------+------------+----------+------------+
|       13 | RECEPÇÃO   |          |            |       18 | CEO        |
+----------+------------+----------+------------+----------+------------+

Note that the values of imp2 are all empty, for in this case only imp1 has data. In my html code I use the following format to display the information of imp1:

 <div class="panel-body">
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>#</th>
                <th>Impressora</th>
                <th>Modelo</th>
                <th>Contagem</th>
                <th>Situação</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <th scope="row"><?php echo $imp_cod1; ?></th>
                <td><b><?php echo $imp_local1; ?></b></td>
                <td><?php echo $imp_modelo1; ?></td>
                <td><?php echo $imp_contador1; ?></td>
                <td><button type="button" class="btn btn-round btn-success"><?php echo $imp_situacao1; ?></button></td>
            </tr>
        </tbody>
    </table>
</div>

Today, when I have a second die on imp2, imp3... i will manually and create a new field of results as the above mentioned example <tr>

I wonder if there’s any way I can make it automatic so I don’t have to keep editing PHP every time I insert or delete new data in the table.

Connection code with db (every page that requires the user to be logged in has a include for this page): http://pastebin.com/02WJFzEp

Code of my page where I am having difficulties to include new lines automatically: http://pastebin.com/GcTbPn3H (in line 251 has the first data fill, it ends in 257. Just below there are 7 more fills where I have already left ready.)

Now suppose the user only has the imp1 and all the others are empty, yet my system will search all the others and leave blank, and because the html code is already created, it leaves the created lines but blank as well. I would like to know if I can make this become automatic, in theory: if the imp field is empty do not create line, otherwise create line and fill in data...

If I am going the wrong way in logic I am able to change, I am beginner and was urgently at the end of this.

Follow photo of how the system gets even with blank data:

Answer 1

It is noticeable that your code sins by data normalization.

Although your problem is not the repetition of data between records, you end up creating repeated columns, which is practically impossible to have maintenance only by PHP code (there is always a gambiarra that solves). Your problem is fixed by 1FN

Basically you have a 1.* (one-to-Many or one-to-many) relationship. Being your unique record the user and the registration related to imp.

As a diagram, we can define the relationship as (I will only use the required fields):

This aggregation-type relationship allows you to have an infinite number of locations to associate with the user.

Another case can occur if you have a defined and limited list of locations. In this case, the relationship will change and will be considered a simple association:

Basically, the relationship above is for when you have, for example, a list of printers. And these printers can be shared among users, but there can be no printers other than those previously registered in the table imp.

As a curiosity, there is another case, which is an extension of the second example, is when you have a set amount of items (10 printers), but the printers cannot be shared. Whoever "gets" the printer will have booked it and will be accessible only to the linked user.

In the latter case, just add the column imp_id table user_has_imp as a unique key. This way, a printer cannot be shared between users and will continue with the possibility of a user having more than one printer.

Regardless of the approach chosen, both will give you the possibility and flexibility of how many associations create.

For code question, just return the association:

//prepared statement da PDO
$query = 'SELECT * FROM imp WHERE user_id = :userid';

or

//prepared statement da PDO
$query = 'SELECT * FROM imp WHERE id IN (SELECT imp_id FROM user_has_imp WHERE user_id = :userid)';

With the return of the data, simply loop the result:

$statement = $pdo->prepare('SELECT * FROM imp WHERE user_id = :userid');
$statement->bindValue(':userid' , $userId);
$statement->execute();

foreach($statement->fetch() as $row)
{
    var_dump($row);
}

Now suppose that the user only has the imp1 and all the others are empty, even so my system will search all the others and leave blank, and because the html code is already created, it leaves the created lines but also blank. I would like to know if I can make this become automatic, in theory: if the imp field is empty do not create line, otherwise create line and fill in data...

Just use the logic of foreach, above, to create the HTML. It will only be created for the results that exist.

PS.: All codes are just examples.

PS. 2: names created by Mysqlworkbench.