I have question about Formatting HTML table after it's pull out data from the database. Meanwhile, After the table is load in the Table, there are repeated Client_Name. There is a relationship of tables from my databases of ONE to Many relationship from the parent table (Clients) to the child table (Cards, and Sites).
My Database Schema:
+---------------+----------------+--------------+
| **Clients** | **Sites** | **Cards** |
+---------------+----------------+--------------+
| Client_ID(pk) | Sites_ID(pk) | Card_# |
+---------------+----------------+--------------+
| Name | Location | repair |
+---------------+----------------+--------------+
|Address | Client_ID(fk) | sites_ID(fk) |
+---------------+----------------+--------------+
| | name |client_ID(fk) |
+---------------+----------------+--------------+
And here is mySQL Statement:
SELECT
cl.client_name,
jb.job_number,
jb.repair,
st.site_name
FROM
cards jb
INNER JOIN
clients cl ON (cl.client_id = jb.client_id)
LEFT JOIN
sites st on (st.site_id = jb.site_id)
WHERE
anD cl.client_id = jb.client_id
and my HTML code for showing data in table is
<table width='100%' cellpadding= '1' border='0'>
<thead>
<tr>
<td style="font-size:13px; text-align:Left;"><b>Date</b></td>
<td style="font-size:13px; text-align:Left;"><b>Job #</b></td>
<td style="font-size:13px; text-align:left;"><b>Site name</b></td>
<td style="font-size:13px; text-align:left;"><b>Description</b></td>
</tr>
</thead>
<?php while($userData = mysql_fetch_assoc($tresult)){
if($i%2==0)
$classname = 'evenRow';
else if($i%2==1)
?>
<tr>
<td>
<?php echo mysql_real_escape_string ($userData['client_name)']); ?>
</td>
<td>
<?php echo mysql_real_escape_string($userData['job_number']);?>
</td>
<td>
<?php echo mysql_real_escape_string($userData['site_name']);?>
</td>
<td>
<?php echo mysql_real_escape_string($userData['repair']);?>
</td>
</tr>
<?php $i++;
}
?>
</tr>
</table>
This is how it Displays?
| **Client_Name*| **Job#** | **Site_name**|**repair** |
+---------------+--------- +--------------+
|client 1 | 899500 | Marcell | rebooted
+---------------+----------+--------------+
|Client 1 | 6623120 | Lovan | cool-down
+---------------+----------+--------------+
|client 2 | 442120 | Brackes | setup
+---------------+----------+--------------+
|client 2 | 546698 | client_ID(fk)| replaced
+---------------+----------------+--------+
I don't Want to see repeated Client_Name. What I need is: Client 1 listed is child Example:
| **Client_Name*| **Job#** | **Site_name**|**repair** |
+---------------+--------- +--------------+
|client 1 | 899500 | Marcell | rebooted
+---------------+----------+--------------+
| | 6623120 | Lovan | cool-down
+---------------+----------+--------------+
|client 2 | 442120 | Brackes | setup
+---------------+----------+--------------+
| | 546698 | client_ID(fk)| replaced
+---------------+----------------+--------+
Display on the HTML table. Is it Possible to display this format of table? What Do I have to Do?
Please anyone HELP is Very Welcome!!!
Here is my full code
<?php
session_start();
include ('db.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Company Monthly Reports</title>
</head>
<body id="main_body" >
<div id="form_container">
<table width='100%' cellpadding= '1' border='0'>
<thead>
<tr>
<td style="font-size:13px; text-align:Left;"><b>Client Name</b></td>
<td style="font-size:13px; text-align:Left;"><b>Date</b></td>
<td style="font-size:13px; text-align:Left;"><b>Job #</b></td>
<td style="font-size:13px; text-align:left;"><b>Site name</b></td>
<td style="font-size:13px; text-align:left;"><b>Description</b></td>
</tr>
<?php
$sqlSelect = "SELECT
cl.client_name,
DATE(jb.date),
jb.job_number,
jb.repair,
st.site_name
FROM
job_cards jb
INNER JOIN
clients cl ON (cl.client_id = jb.client_id)
LEFT JOIN
sites st on (st.site_id = jb.site_id)
WHERE
jb.completed = 1
AND cl.client_id = jb.client_id
AND jb.date >= DATE_ADD(DATE(NOW()), INTERVAL - 30 DAY)
ORDER BY cl.client_name ASC";
//echo $sqlSelect;
$tresult = mysql_query($sqlSelect);
$last = '';
while($userData = mysql_fetch_assoc($tresult)){
if($i%2==0)
$classname = 'evenRow';
else if($i%2==1)
?>
</thead>
<tbody>
<tr class='<?php if(isset($classname)) echo $classname;?>'>
<td width=250>
<?php
if ($last == strtolower($userData['client_name)'])) {
echo " ";
}
else {
$last == strtolower($userData['client_name)']);
echo mysql_real_escape_string ($userData['client_name)']);
}
//echo mysql_real_escape_string ($userData['client_name']); ?>
</td>
<td width=100>
<?php echo mysql_real_escape_string ($userData['DATE(jb.date)']); ?>
</td>
<td width=50>
<?php echo mysql_real_escape_string($userData['job_number']);?>
</td>
<td>
<?php echo mysql_real_escape_string($userData['site_name']);?>
</td>
<td>
<?php echo mysql_real_escape_string($userData['repair']);?>
</td>
<?php $i++;
}
?>
</tr>
</tbody>
</table>
</div>
</body>
</html>
You need to modify your php code at this place
<?php echo mysql_real_escape_string ($userData['client_name']); ?>
change it to something like this
<?php
if ($last == strtolower($userData['client_name'])) {
echo " ";
}
else {
$last = strtolower($userData['client_name']);
echo mysql_real_escape_string ($userData['client_name']);
}
?>
and you need a change just before the loop at
</thead>
<?php while($userData = mysql_fetch_assoc($tresult)){
change it to
</thead>
<?php
$last = '';
while($userData = mysql_fetch_assoc($tresult)){
that should do the work