HTML tag formatting php and MySQL

Question

I have this MySQL statement

Select type.type, color.color, ShotName, Item.name, Item.Item_id
  From type 
    Inner Join Item 
      On type.type_id =Item.type_id 
    Inner Join color 
      On color.color_id =Item.color_id 
  Where Item.state=0 And Item.offline = 0
  Group By color.color, Item.name, type.order_nr
  Order By type_D.order_nr, color.color, Item.name_d

How can do this with php to get a result formatted as the following HTML? I have managed to list the items but I am not able to put <ul> properly

<body>
  <h2> Type AA </h2>
  <h3> color black </h3>
  <ul>
    <li> ShortName 1 name 1</li>
    <li> ShortName 2 name 2</li>
    <li> ShortName 3 name 3</li>
    <li> ShortName 4 name 4</li>
    <li> ShortName 5 name 5</li>
  </ul>
  <h3> color green </h3>
  <ul>
    <li> ShortName 7 name 7</li>
    <li> ShortName 8 name 8</li>
    <li> ShortName 9 name 9</li>
    <li> ShortName 10 name 10</li>
    <li> ShortName 11 name 11</li>
  </ul>
  <h2> Type AB </h2>
  <h3> color black </h3>
  <ul>
    <li> ShortName 12 name 12</li>
    <li> ShortName 13 name 13</li>
    <li> ShortName 14 name 14</li>
    <li> ShortName 15name 15</li>
    <li> ShortName 16name 16</li>
  </ul>
  <h3> color green </h3>
  <ul>
    <li> ShortName 17 name 17</li>
    <li> ShortName 18 name 18</li>
    <li> ShortName 19 name 19</li>
    <li> ShortName 20 name 20</li>
    <li> ShortName 22 name 22</li>
  </ul>
</body>

Here is my PHP code

$myfile = mysql_query($query_myfile, $db) or die(mysql_error());
$totalRows_myfile = mysql_num_rows($myfile);

while ( $row_myfile = mysql_fetch_assoc($myfile) ) 
{
    if ( $type != $row_myfile[ 'type' ] ) 
    {

        $type = $row_myfile[ 'type' ];

        echo "<h2>$type</h2>";

    }
    if ( $color != $row_myfile[ 'color' ] ) 
    {

        $color = $row_myfile[ 'color' ];

        echo "<h3>$color</h3>";
    }

    echo "<li><a href=\"itemDetail.php?item_id=".$row_myfile['myfile_id']. "\">";
    echo $row_myfile['ShortName'].' ';
    echo $row_myfile['name']; ?></a></li>

thanx but the counter goes only to 5 ..i can have more or less — Mark
– Mark, Commented Jan 21, 2012 at 0:44
The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. Note that or die(mysql_error()) should never appear in production code, as die breaks HTML output and database error messages should never be revealed to non-admin users as it discloses too much information. A better approach would be to properly implement error handling. — outis
– outis, Commented Jan 21, 2012 at 19:27

rauschen · Accepted Answer · 2012-01-20 23:54:58Z

1

Add more things you want to display in the li-tag

$sql = "copy your sql";

$result_set = $result = mysql_query($sql);
echo "<body>";
$type = false;
$color = false;
$close_ul = false;
while ($row = mysql_fetch_assoc($result)) {
    if ($type != $row['type']) {
        if ($close_ul) { 
            echo "</ul>"; 
            $close_ul = false;
        }
        $type = $row['type'];
        echo "<h2>$type</h2>";
        $color = false;
    }
    if ($color != $row['color']) {
        if ($close_ul) { 
            echo "</ul>"; 
            $close_ul = false;
        }
        $color = $row['color'];
        echo "<h3>$color</h3>";
        echo "<ul>";
        $close_ul = true;
    }

    echo "<li>",$row['shortname'],"</li>";
}
if ($close_ul) { 
    echo "</ul>"; 
    $close_ul = false;
}
echo "</body>";

answered Jan 20, 2012 at 23:54

rauschen

4,0062 gold badges15 silver badges13 bronze badges

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

5 Comments

Mark Over a year ago

thanx a lot but i am getting this error syntax error, unexpected 'EOF'

rauschen Over a year ago

that means the there is a } or something missing, or a ' " .... maybe a copy and paste error ..

rauschen Over a year ago

can you post your complete file

Mark Over a year ago

corrected but now i am getting this </h3></ul><li> it should be rather </h3><ul><li>. all ul are closing, no opening <ul>

rauschen Over a year ago

is an opening <ul> echo "<h3>$color</h3>"; echo "<ul>";

Niklesh_Chauhan · Accepted Answer · 2017-01-02 09:54:17Z

contact your html properly. Here is the solution.

while ( $row_myfile = mysql_fetch_assoc($myfile) ){

                if ( $type != $row_myfile[ 'type' ] ) 
                {

                    $type = $row_myfile[ 'type' ];

                    echo "<h2>$type</h2>";

                }
                if ( $color != $row_myfile[ 'color' ] ) 
                {

                    $color = $row_myfile[ 'color' ];

                    echo "<h3>$color</h3>";
                }

                echo "<ul>"; 
                echo "<li>";                
                echo "<a href='itemDetail.php?item_id='$row_myfile['myfile_id']'>ShortName ".$row_myfile['ShortName']." Name " .$row_myfile['name']." </a>";               
                echo "</li>";
                echo "</ul>";
            }

Collectives™ on Stack Overflow

HTML tag formatting php and MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related