how can I dynamically create a new table (php) using data from a data base?

Question

first time asking so please be nice :)... im trying to dynamically create a new table, based on content bringed from my db. The table should have 3 columns, each with its own header (first case: "plants", second: "users" third:"email") and as many rows as the data bringed from the db. Also every data redirects to another php link. Can you please help me?

This is a kind of graphic of what the page should look like:

Example of the table im trying to do

here is my code:

<?php
    $cn = mysql_connect("localhost","pablokvitca","") or   
        die("No se pudo conectar: " . mysql_error());
    mysql_select_db("gaea");
    $searched=$_GET['search'];
    $sql1= "SELECT * FROM  Plantas WHERE Nombre LIKE '%$searched%'";
    $sql2= "SELECT * FROM Usuarios WHERE Username LIKE '%$searched%'";
    $sql3= "SELECT * FROM  Usuarios WHERE email LIKE '%$searched%'";

    $result1=mysql_query($sql1);
    $result2=mysql_query($sql2);
    $result3=mysql_query($sql3);

    $e1 = "";
    $e2 = "";
    $e3 = "";

    $e1.='<table align="center" align="top">';
    //$e1.='<th>Plantas</th>';
    //$e2.='<th>Usuarios</th>';
    //$e3.='<th>Mails</th>';
    while ($row1 = mysql_fetch_array($result1)) { //plants
        $e1 .= '<tr  align=left>';
        $pln = $row1['Nombre'];
        $idp = $row1['idPlanta'];
        $e1 .= '<td>';
        $e1 .= "<div style=color:black;margin-left:50px;font-size:25px;>";
        $e1 .= '<p> <a href="newPlanta.php?pltID='.$idp.'&viewer=true">Planta:'.$pln.'</a></p>';

        $e1 .= "</div>";
        $e1 .= '</td>';
        $e1 .= '</tr>';
    }
    echo $e1;
     //$e1.='</tr>';
    //echo $e1;
    while ($row2 = mysql_fetch_array($result2)) { //user
        $e2 .= '<tr align=left>';
        $usu = $row2['username'];
        $e2 .= '<td>';
        $e2 .= "<div style=color:black;margin-left:50px;font-size:25px;>";
        $e2 .= '<p> <a href="perfil.php?id='.$usu.'">Usuario:'.$usu.'</a></p>';
        $e2 .= "</div>";
        $e2 .= '</td>';
        $e2 .= '</tr>';

    }
    echo $e2;
    while ($row3 = mysql_fetch_array($result3)) { //email
        $ml = $row3['username'];
        $e3 .= '<tr align=left>';
        $e3 .= '<td>';
        $e3 .= "<div style=color:black;margin-left:50px;font-size:25px;>";
        $e3 .= '<p> <a href="perfil.php?id='.$ml.'">Email:'. $row3['email'].'</a></p>';
        $e3 .= "</div>";
        $e3 .= '</td>';
        $e3 .= '</tr>';
    }
        echo $e3;
     $e3.='</table>';
?>

Your code is open to SQL injection. You should be using PDO or mysqli with bound parameters. — Mike
– Mike, Commented Nov 18, 2015 at 23:19
If your tables all have the same structure, you should use the same table and add another column that would distinguish them. So if I understand you correctly, you would add a "plant" type, a "user" type and an "email" type. It is almost never necessary to dynamically add tables to the database. — Mike
– Mike, Commented Nov 18, 2015 at 23:22
Mike, thanks for the help, but maybe i didnt express myself correctly, i am not trying to add tables to the database, im triying to dynamically create a table (in the web page) based on data extracted from de database — Kevin Daich
– Kevin Daich, Commented Nov 18, 2015 at 23:55
Yeah, that totally was not clear. What's wrong with what you're doing now? — Mike
– Mike, Commented Nov 19, 2015 at 0:17
You're making one row per database record now. That's what <tr></tr> will do. If you want one column per database record, you'll probably want to load the results into an array and then output it. Also, <nbsp> is not an element! — miken32
– miken32, Commented Nov 19, 2015 at 0:27

Alan Quandt · Accepted Answer · 2015-11-19 17:59:28Z

1

While I must admit, I do not understand why you want to arrange this table this way. However, I cannot resist the challenge!

I took all the data and built an array using the same table structure you wanted. Then once the array is finished, we can run a foreach loop to build the table.

I have not tested this because I don't have your database to run off of. In my head, it works though.

<?php
  // Build an array holding all of the data from each table
  $rowNum = 0; // Start the count

  while ($row1 = mysql_fetch_array($result1)) { //plants
    $data[$rowNum]['col1']['Nombre'] = $row1['Nombre'];
    $data[$rowNum]['col1']['idPlanta'] = $row1['idPlanta'];
    $rowNum++;
  }

  $rowNum = 0; // Restart the row count
  while ($row2 = mysql_fetch_array($result2)) { //user
    $data[$rowNum]['col2'] = $row2['username'];
    $rowNum++;
  }

  $rowNum = 0; // Restart the row count
  while ($row3 = mysql_fetch_array($result3)) { //email
    $data[$rowNum]['col3']['username'] = $row3['username'];
    $data[$rowNum]['col3']['email'] = $row3['email'];
    $rowNum++;
  }

?>

<table align="center" align="top">
  <tr>
    <th>Plants</th>
    <th>Users</th>
    <th>Emails</th>
  </tr>
<?php
  // Run a foreach on the array to build out the table:
  foreach($data as $key => $row){?>

  <tr>
    <td>
      <?php 
        if(is_array($row['col1'])){
          echo '<div style="color:black;margin-left:50px;font-size:25px;">';
          echo '<p> <a href="newPlanta.php?pltID='.$row['col1']['idPlanta'].'&viewer=true">Planta:'.$row['col1']['Nombre'].'</a></p>';
          echo '</div>';
        }
      ?>
    </td>
    <td>
      <?php 
        if($row['col2']){
          echo '<div style="color:black;margin-left:50px;font-size:25px;">';
          echo '<p> <a href="perfil.php?id='.$row['col2'].'">Usuario:'.$row['col2'].'</a></p>';
          echo '</div>';         
        }
      ?>
    </td>
    <td>
      <?php 
        if(is_array($row['col3'])){
          echo '<div style="color:black;margin-left:50px;font-size:25px;">';
          echo '<p> <a href="perfil.php?id='.$row['col3']['username'].'">Usuario:'.$row['col3']['email'].'</a></p>';
          echo '</div>';        
        }
      ?>
    </td>    
  </tr>

<?php } ?>

</table>

edited Nov 19, 2015 at 17:59

answered Nov 19, 2015 at 5:48

Alan Quandt

965 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kevin Daich Over a year ago

hey @AlanQuandt thanks for the help, your code almost does what i need, but there are two problem: first (and more important) is that it only shows one result from each column (when it should be able to show as much results as the DB brings) second: the headers don't align with the results.

Alan Quandt Over a year ago

@KevinDaich I will try and mimic you database and give it a try. I wrote that above blindly.

Kevin Daich Over a year ago

thank you SO MUCH. I have to finish a hole proyect, including this, for monday and i couldn't really figure it out by myself how to do this part, so again, thank you very much for the collaboration. Looking forward to see if you can help me.

Alan Quandt Over a year ago

Try it now... I forgot to increment the $rowNum and mistakenly used the term $index after I decided to change it to $rowNum. This should work better.

Collectives™ on Stack Overflow

how can I dynamically create a new table (php) using data from a data base?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related