Drawing HTML Table With MySQL Data

[JDevOnline]

Hi,

 

I am trying to draw a HTML table with mysql data using loop. The code below is drawing html tables fine, but is not drawing multiple rows, showing just 1 row per table (image attached student.father.jpg). Can anyone please help me do what I am trying to do:

 

Mysql table structure:

id, sname, fname, foccup, fimp

id=id, sname=student's name, fname=father's name, foccup=father's occupation, fimp(Y/N)=father's occupation important

 

My Code:

$query = "select sname, fname, foccup from nroll where fimp = 'Y' group by foccup order by foccup asc";
$result = mysql_query($query);
$rows = mysql_num_rows($result);

while($rows = mysql_fetch_array($result)){
echo '<table border="1"><tr><td colspan="3">' . $rows[foccup] . '</td></tr>';
echo '<tr><td>' . $rows[sname] . '</td><td>' . $rows[fname] . '</td></tr>';
echo '</table><br />';
}

[Psycho]

 The code below is drawing html tables fine, but is not drawing multiple rows, showing just 1 row per table

 

Because the code is creating a new table in the loop for each record. Start the table before your loop. In the loop, output each row. Then after the loop, close the table. 

 

As this appears to be an assignment, I will leave it to you to make the modifications.

[JDevOnline]

Because the code is creating a new table in the loop for each record. Start the table before your loop. In the loop, output each row. Then after the loop, close the table. 

 

As this appears to be an assignment, I will leave it to you to make the modifications.

 

Hi, I in fact want to output multiple html tables each belonging to a different occupation and each with multiple rows of people belonging to the occupation of that table.

[Barand]

Either

• loop through the rows, opening a new table when the occupation changes, or
• store records in a 2 dimensional array by occupation then process that array
  foreach (data as occupation => records)
      open table for occupation
      foreach (records as row)
          write row
      endforeach
      close table
  endforeach

[JDevOnline]

I am a newbie, please post the real code, I am having difficulties with the mentioned example, thank.

[ginerjm]

One doesn't learn how to program without actually programming. This forum is here to help you learn, not to write it for you. What Barand took the time to explain to you is one of the first steps to learning programming and creating algorithms. Adding code to that understanding is the second step.

 

Take some time to educate yourself and then have some fun writing some code and then show us what you have difficulty with.

[ClipboardCode]

Here is a quick SELECT query function and example of creating an HTML table:

 

<?php

function selectDQ($query){

   $dataArray = Array();

   $connector = @new PDO('mysql:host=localhost', 'username', 'password');

   $comm = $connector -> prepare($query);

   $comm -> execute();

   $dataArray = $comm -> fetchAll(PDO::FETCH_ASSOC);

   $comm = null;

   $connector = null;

  return $dataArray;

}

 

$mydata = selectDQ('SELECT * from test.test');

echo "<table>";

echo "<tr><th>Client Name</th><th>Age</th></tr>";

foreach ($mydata as $row){

   echo "<tr>";

   echo "<td>".$row['clientname']."</td>";

   echo "<td>".$row['age']."</td>";

   echo "</tr>";

}

echo "</table>";

?>

 

EDIT : Product plug removed

Edited February 12, 2017 by Barand

[benanamen]

@Clipboardcode, You need to stop promoting your EXTREMELY dangerous app on this forum.

[ClipboardCode]

@Clipboardcode, You need to stop promoting your EXTREMELY dangerous app on this forum.

 

I added a note to the download page it is for testing only until I can get some help getting it secured. Thanks. Posting a new topic in a few minutes so we do not hijack this one.

[JDevOnline]

Hi, thank for your replies. Please review my code and suggest necessary amendments to produce the tables like in the image "table that i want.png" with multiple rows, thanks.

$query = "select sname, fname, foccup from nroll where fimp = 'Y' group by foccup";
$result = mysql_query($query);
$row = mysql_num_rows($result);

$index = 0;
while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
//$yourArray[$index] = $row;
    
echo "<table border=1>";
echo '<tr><th colspan="3">' . $row[foccup] . '</th></tr>';
echo "<tr><th>Student Name</th><th>Fathers Name</th><th>Occupation</th></tr>";

while($row = mysql_fetch_assoc($result)){
echo '<tr><td>' . $row[sname] . '</td><td>' . $row[fname] . '</td><td>' . $row[foccup] . '</td></tr>';
}
$index++;
echo "</table><br>";

$index++;
}

[Barand]

If your table headings show the occupation, why the occupation column in the table?

[JDevOnline]

I placed that within the table just to cross check, the column will be removed, when not required.

[Barand]

1 ) GROUP BY foccup will give you one row for each value of foccup. Use ORDER BY.

 

2 ) Functions beginning with mysql_ are obsolete and have been removed from PHP. Use PDO (or mysqli_ functions)

[JDevOnline]

1 ) GROUP BY foccup will give you one row for each value of foccup. Use ORDER BY.

 

2 ) Functions beginning with mysql_ are obsolete and have been removed from PHP. Use PDO (or mysqli_ functions)

Thanks for your reply. How is it possible to draw individual html table for people belonging to same occupation using only ORDER BY?

[Barand]

Two methods. Loop through the query results

 

1 ) When the occupation changes, start a new table (hence the order by occupation)

 

or

 

2 ) store the results in an array indexed by occupation

 

This is the second method (you may peep if you get stuck)

 

 

$sql = "select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y' 
        order by foccup, sname";
$res = $db->query($sql);

#
# store data in arrays by occupation
#
$data = [];
foreach ($res as $student) {
    $data[$student['foccup']][] = $student;
}
#
# now loop through the array data
# creating your tables
#
foreach ($data as $occup => $students) {
    # new occupation table
    echo "<table border='1' style='border-collapse:collapse'>
            <tr><th colspan='2'>$occup</th></tr>
            <tr><th>Student Name</th><th>Fathers Name</th></tr>\n";
    # output student rows
    foreach ($students as $student) {
        echo "<tr><td>$student[sname]</td><td>$student[fname]</td></tr>\n";
    }
    echo "</table><br>\n";
}

 

 

 

And this is the first method (again, no peeping)

 

 

$sql = "select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y' 
        order by foccup, sname";
$res = $db->query($sql);

#
# process data, starting new table on change of occupation
#
$current_occupation='';
foreach ($res as $student) {
    if ($student['foccup'] != $current_occupation) {
        if ($current_occupation) {
            echo "</table><br>\n";  // end previous table
        }
        # start new table
        echo "<table border='1' style='border-collapse:collapse'>
            <tr><th colspan='2'>$student[foccup]</th></tr>
            <tr><th>Student Name</th><th>Fathers Name</th></tr>\n";
        $current_occupation = $student['foccup'];
    }
    echo "<tr><td>$student[sname]</td><td>$student[fname]</td></tr>\n";
}
echo "</table><br>\n";  // end final table

 

 

Edited February 18, 2017 by Barand

[JDevOnline]

Hi Barand, thanks for the assistance. I tried running your code but it is resulting in the error:

 

( ! ) Fatal error: Call to a member function query() on resource in F:\Ampps\www\imp\imp3.php on line 13

 

I am using following connection string, is it OK?

$db = mysql_connect("localhost","root","mysql");
if (!$db){
die('Could not connect: ' . mysql_error());}
mysql_select_db("nominalroll", $db);

$sql = "select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y' 
        order by foccup, sname";
$res = $db->query($sql);

[Barand]

You peeped!

 

Your connection code is using the obsolete mysql_ library. Use mysqli_ or PDO connection

[JDevOnline]

You peeped!

 

Your connection code is using the obsolete mysql_ library. Use mysqli_ or PDO connection

Dear Barand you are a Hero! You've given me the simplest and most efficient code that I wanted and been trying very hard to product the same output. Once again, thanks a lot!

[JDevOnline]

Barand, one more thing. I have  2 more columns mother's name (mname) and mother's occupation (moccup), how can I display mother's info in same occupation tables, where moccup = 'Y'.

[Barand]

Include those columns in the query selection and add them to the output of the results.

[JDevOnline]

Include those columns in the query selection and add them to the output of the results.

I want to add student's name and mother's name in occupation tables as rows and not as columns in HTML tables. However, the data is stored in the same table in columns.

[Barand]

So, given this input ...

+----------+---------+-------+-----------------+---------+------------------+------+
| nroll_id | sname   | fname | foccup          | mname   | moccup           | fimp |
+----------+---------+-------+-----------------+---------+------------------+------+
|        1 | Mary    | Peter | butcher         | Jessica | baker            | Y    |
|        2 | Jane    | Paul  | baker           | Yvonne  | candlestickmaker | Y    |
|        3 | Peter   | Jack  | butcher         | Kath    | butcher          | Y    |
|        4 | Matthew | Henry | candlstickmaker | Naomi   | doctor           | Y    |
|        5 | Zeke    | Steve | baker           | Sarah   | banker           | Y    |
+----------+---------+-------+-----------------+---------+------------------+------+

... what would the output look like?

Edited February 19, 2017 by Barand

[JDevOnline]

Hi Barand, the table structure you've created is lacking one column, which is mimp, which marks important mothers by occupation like in the case of fathers fimp is used. Following is the mockup of the table i am trying to generate.

[Barand]

You would use a UNION in the query to get the fathers then the mothers. So the query becomes

select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y'
        UNION
        select sname
        , mname
        , moccup 
        from nroll 
        where mimp = 'Y'
        order by foccup, sname

[JDevOnline]

 

You would use a UNION in the query to get the fathers then the mothers. So the query becomes

select sname
        , fname
        , foccup 
        from nroll 
        where fimp = 'Y'
        UNION
        select sname
        , mname
        , moccup 
        from nroll 
        where mimp = 'Y'
        order by foccup, sname

 

Barand, this has perfectly concluded the current project. I am sooooooo very happy for your great help, thanks once again.