CODEWITHSUNDEEP
phpmysqlhtml-table
Acelasi Eu
Acelasi Eu

Reputation: 914

How to output mysql query to a html table?

need your help again.

So I have a query, with multiple variables which I have to output to a table.

Here is the query: 

SELECT DISTINCT
          zi.zile,
          ore.ora AS ore,
          materii.materie
        FROM
          zi LEFT JOIN  orar ON zi.id = orar.id_zi 
          LEFT JOIN  ore ON ore.id = orar.id_ora 
          LEFT JOIN  nume_scoli ON nume_scoli.id = orar.id_scoala 
          LEFT JOIN  materii_pe_clase ON materii_pe_clase.id_scoala = nume_scoli.id 
          LEFT JOIN  clase ON materii_pe_clase.id_clasa = clase.id AND orar.id_clasa = clase.id
          LEFT JOIN  elevi ON elevi.id_clasa = materii_pe_clase.id_clasa 
          LEFT JOIN  materii ON materii.id = orar.id_materie 
                WHERE clase.`id`=1

            ORDER BY zi.`zile`, ore.`id` ASC

and the result of this query looks like this :

image

Now this is what I`ve done in php: 

  $oraru = "the query from up this page ";
    $gaseste_oraru = mysql_query($oraru);
    $numar_orar = mysql_num_rows($oraru);
  if($numar_orar==0)
    {
      echo "Orarul nu este disponibil momentan.";
    }
  else 
    {
    while($randorar=mysql_fetch_array($gaseste_oraru))
    {
        $ziorar = $randorar['zile'];
        $oraorar = $randorar['ore'];
        $materieorar = $randorar['materie'];
}
}

This is the table I want to populate with data from the database:

table

Here you have the code of the table: 

<div class="CSSTableGenerator" >
                <table >
                    <tr>
                        <td>
                           Orar
                        </td>
                        <td>
                           Luni
                        </td>
                        <td >
                            Marti
                        </td>
                        <td>
                            Miercuri
                        </td>
                        <td>
                            Joi
                        </td>
                        <td> 
                            Vineri
                        </td>
                    </tr>
                    <tr>
                        <td >
                            8:00 - 9:00
                        </td>
                        <td>
                           <select>
  <option>Mate</option>
  <option>Romana</option>
  <option>Geogra</option>
</select>

                        </td>
                        <td>
                            <select>
  <option>Mate</option>
  <option>Romana</option>
  <option>Geogra</option>
</select>
                        </td>
                        <td>
                            <select>
  <option>Mate</option>
  <option>Romana</option>
  <option>Geogra</option>
</select>
                        </td>
                        <td>
                           <select>
  <option>Mate</option>
  <option>Romana</option>
  <option>Geogra</option>
</select>
                        </td>

                             <td>
                            rand 1
                        </td>
                    </tr>
                    <tr>
                        <td >
                            9:00 - 10:00
                        </td>
                        <td>
                            rand 2
                        </td>
                        <td>
                            rand 2
                        </td>
                        <td>
                            rand 1
                        </td>
                        <td>
                            rand 1
                        </td>
                             <td>
                            rand 1
                        </td>
                    </tr>
                    <tr>
                        <td >
                            10:00 - 11:00
                        </td>
                        <td>
                            rand 2
                        </td>
                        <td>
                            rand 2
                        </td>
                        <td>
                            rand 1
                        </td>
                        <td>
                            rand 1
                        </td>
                             <td>
                            rand 1
                        </td>
                    </tr>
                    <tr>
                        <td >
                            11:00 - 12:00
                        </td>
                        <td>
                            rand 3
                        </td>
                        <td>
                            rand 3
                        </td>
                        <td>
                            rand 1
                        </td>
                        <td>
                            rand 1
                        </td>
                       <td>
                            rand 1
                        </td>
                </table>
            </div>

NEVERMIND THE DROPDOWNS, I WON`T USE THEM!

Do you have any idea how to populate it, BUT if nothing exists for an interval (e.g Luni 8:00-9:00), then write nothing in the table?

EDIT

Here is what I tried : 

<div class="CSSTableGenerator" >
   <table >
                    <tr>
                        <td>
                           Orar
                        </td>
                        <?php
  {
      $oraru = "the query from up this page";
    $gaseste_oraru = mysql_query($oraru);

    while($randorar=mysql_fetch_array($gaseste_oraru))
    {
        $ziorar = $randorar['zile'];
        $oraorar = $randorar['ore'];
        $materieorar = $randorar['materie'];
        echo "<td>";
        echo $randorar['zile'];
        echo "</td>";
        echo "</tr>";
        echo "<tr>";
        echo "<td>";
        echo $randorar['ore'];
        echo "</td>";
        echo"<td>";
        echo $randorar['materie'];
        echo "</td>";
        echo "</tr>";
    }//sfarsit while

Here is the result:

bad

Any ideas?

Upvotes: 2

Views: 2119

Answers (3)

Acelasi Eu
Acelasi Eu

Reputation: 914

Ok, so this is the solution:

 $oraru = "
        SELECT DISTINCT
              zi.zile,
              ore.ora AS ore,
              materii.materie
            FROM zi 
              LEFT JOIN  orar ON zi.id = orar.id_zi 
              LEFT JOIN  ore ON ore.id = orar.id_ora 
              LEFT JOIN  nume_scoli ON nume_scoli.id = orar.id_scoala 
              LEFT JOIN  materii_pe_clase ON materii_pe_clase.id_scoala = nume_scoli.id 
              LEFT JOIN  clase ON materii_pe_clase.id_clasa = clase.id AND orar.id_clasa = clase.id
              LEFT JOIN  elevi ON elevi.id_clasa = materii_pe_clase.id_clasa 
              LEFT JOIN  materii ON materii.id = orar.id_materie 
              WHERE clase.`id`=1
            ORDER BY  ore.`id`, zi.`zile` ASC";
      $gaseste_oraru = mysql_query($oraru);
    $rows = array();
    while($randorar=mysql_fetch_array($gaseste_oraru))
    {        
        $ziorar = $randorar['zile'];
        $oraorar = $randorar['ore'];
        $materieorar = $randorar['materie'];
        if(!isset($rows[$oraorar])){
            $rows[$oraorar] = array();
        }
        $rows[$oraorar][$ziorar] = $materieorar;        
    }//sfarsit while

    ?>
<div class="CSSTableGenerator" >
    <table >
        <tr>
            <td>Orar</td><td>Luni</td><td>Marti</td><td>Mercuri</td><td>Joi</td><td>Vineri</td>
        </tr>
    <?php

        foreach ($rows as $key => $row) {
          if(!isset($row['Luni'])) $row['Luni'] = '&nbsp;';//incase no data from database
          if(!isset($row['Marti'])) $row['Marti'] = '&nbsp;';//incase no data from database
          if(!isset($row['Miercuri'])) $row['Miercuri'] = '&nbsp;';//incase no data from database
          if(!isset($row['Joi'])) $row['Joi'] = '&nbsp;';//incase no data from database
          if(!isset($row['Vineri'])) $row['Vineri'] = '&nbsp;';//incase no data from database
          echo "<tr>";
          echo "<td>".$key."</td>";
             foreach ($row as $day => $study) {
               echo "<td>$study</td>";    
             }
             echo "</tr>";
         } 
     ?>
    </table>
</div>

I figured it out with some help from a friend!

Thank you all anyway!

Upvotes: 0

Sylvain
Sylvain

Reputation: 158

What I would recommend is for you to ORDER BY ore.id, zi.zile.

Since you iterate row by row, and not column by column, you will be able to optimize your while loop.

Upvotes: 1

cleverfox
cleverfox

Reputation: 11

try to do something like this 

$xarr=Array();
$xcols=Array();
while($randorar=mysql_fetch_array($gaseste_oraru))
{
    $xcols[$randorar['zile']]++;
    $xarr[$randorar['ore']][$randorar['zile']] = $randorar['materie']
} 
echo "<tr><th>orar</th>";
foreach(array_keys($xcols) as $x)
   echo '<th>'.$x.'</th>';
echo "</tr>\n";

foreach($xarr as $key=>$y){
   echo '<tr><th>'.$key.'</th>';
   foreach(array_keys($xcols) as $x)
      echo '<td>'.$y[$x].'</td>';
   echo "</tr>\n";
}

Upvotes: 1

Related Questions