CODEWITHSUNDEEP
phphtmlmysql
RedZ
RedZ

Reputation: 408

display mysql table with a field as the main header

I have a mysql data table that I am displaying on a php page. It works but I would like them be grouped under the departments they belong to. Also to remove all other field headers such as Name,Phone Email.

I tried using SORT BY but nothing happened.

eg. This is how I would like it to look like

                              Vehicle Department 
                    Bob        3234234     [email protected]
                    Hanna      3434323     [email protected]
                              Workshop Department
                    Andrew     45454523    [email protected]

This is how it currently looks:

ID    Name       Phone       Email               Department
1     Bob        3234234     [email protected]     Vehicle Department
2     Hanna      3434323     [email protected]   Workshop Department

my current code:

<?php

    $db_host = 'localhost';
    $db_user = 'root';
    $db_pwd = '*****';

    $database = 'list';
    $table = 'users';

    if (!mysql_connect($db_host, $db_user, $db_pwd))
        die("Can't connect to database");

    if (!mysql_select_db($database))
        die("Can't select database");

    // sending query
    $result = mysql_query("SELECT * FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }

    $fields_num = mysql_num_fields($result);

    echo "<table border='1'><tr>";
    // printing table headers
    for($i=0; $i<$fields_num; $i++)
    {
        $field = mysql_fetch_field($result);
        echo "<td>{$field->name}</td>";
    }
    echo "</tr>\n";
    // printing table rows
    $temp = "";

    while($row = mysql_fetch_row($result))
    {
        echo "<tr>";

        if ($row['department'] != $temp){
        echo "<td colspan=\"3\">" . $row['department'] . "</td></tr>\n<tr>";
        $temp = $row['department'];
    }

    echo "<td>" . $row['name'] . "</td><td>" . $row['phone'] . "</td><td>" . $row['email'] . "</td>";

    echo "</tr>\n";
    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
    }
    mysql_free_result($result);
?>

Upvotes: 1

Views: 1998

Answers (2)

MrDarkLynx
MrDarkLynx

Reputation: 686

If you use

"SELECT name, phone, email, department FROM {$table} ORDER BY department"

as your query, it will return only your desired columns, ordered by the department column.
Then you can arrange them in your table depending on their department.

Note that you might have to adjust the column names.

Try this code:

<?php
    $db_host = 'localhost';
    $db_user = 'root';
    $db_pwd = '*****';

    $database = 'list';
    $table = 'users';

    $conn = mysqli_connect($db_host, $db_user, $db_pwd) or die("Connecting to database failed");

    mysqli_select_db($conn, $database) or die("Can't select database");

    // sending query
    $result = mysqli_query($conn, "SELECT name, phone, email, department FROM {$table} ORDER BY department");
    if (!$result) {
        die("Query to show fields from table failed");
    }

    echo "<table border='1'><tr>";

    // printing table rows
    $temp = "";

    while($row = mysqli_fetch_array($result))
    {
        echo "<tr>";

        if ($row['department'] != $temp){
            echo "<td colspan=\"3\" align=\"center\">" . $row['department'] . "</td></tr>\n<tr>";
            $temp = $row['department'];
        }

        echo "<td>" . $row['name'] . "</td><td>" . $row['phone'] . "</td><td>" . $row['email'] . "</td>";

        echo "</tr>\n";
    }
    mysqli_free_result($result);
    echo "</table>"
?>

Upvotes: 2

Parantap Parashar
Parantap Parashar

Reputation: 2010

First, you have to process the data to arrange in a structure you want for it. 

<?php

$db_host = 'localhost';
$db_user = 'root';
$db_pwd = '*****';

$database = 'list';
$table = 'users';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<table border='1'><tr>";
// printing table headers
for ($i = 0; $i < $fields_num; $i++) {
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

//variable to store rearranged results;
$resultsModified = [];

// printing table rows
while ($row = mysql_fetch_row($result)) {
    if (!isset($resultsModified[$result['department']]))
        $resultsModified[$result['department']] = [];

    $resultsModified[$result['department']][] = $row;
}

foreach ($resultsModified as $daptName => $results) {
    echo "<caption> $daptName </caption>";
    foreach ($results as $row) {
        echo "<tr>";

// $row is array... foreach( .. ) puts every element
// of $row to $cell variable
        foreach ($row as $cell)
            echo "<td>$cell</td>";

        echo "</tr>\n";
    }
}

mysql_free_result($result);

However I have not tested it but this should work.

Upvotes: 0

Related Questions