Display varying data in a list depending on criteria.

sunshine66

Hi,

I would like your suggestions how to clean it up this piece of code or to make it more condense because I'm newly self-taught to PHP and have limited skill with my coding techniques.

What it does:
1. Creates a session for a user who logged into a Web site
2. Views a list of employees based on the user's company, displayed in table format (data is pulled from MySQL database).
The MySQL database has the following tables: userProfiles, companyProfile, and employeeProfile.
3. Based on the marital status of the employee (taken from the health and dental plan types in the employeeProfile table), certain data appears or doesn't (ex: name of spouse) in the table.

Suggestions are appreciated. Thanks!

 

 <!-- PHP session ID code --> 
<?php

session_start();

if ($_SESSION['valid_log'][0] != $_SERVER['REMOTE_ADDR'] || 
$_SESSION['valid_log'][1] != $_SESSION['User'])
{

//redirect to error page
echo '<META HTTP-EQUIV="Refresh" Content="1; URL=/error_access.html">';
exit();
}

<!-- Begin body --> 
<h2>Employee Profiles</h2>
<P>The following displays employees of your company who are entered into our system.<BR>


$link = mysql_connect("localhost", "root", "password") or die("Could not connect. Please try again later.");
mysql_select_db("database",$link) or die("Could not select database. Please try again later.");

$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
$result = mysql_query($query);	
$row = mysql_fetch_array($result);

//Get ID values
 if($row) {
$CoID = $row["CompanyID"];
$id = $row["ProfileID"];
}

Print "User ID: $id <br>";	
Print "Company ID: $CoID<br>";

// Find employees profiles for Company ID 
    $query2 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID ORDER BY EmployeeLast"; 
    $result2 = mysql_query($query2);	


$i = 0;
$c = 0;

/*


//	$line = mysql_fetch_array($result2);
	$EEid      = $line["EmployeeID"];
	$last	 = $line["EmployeeLast"];
	$first     = $line["EmployeeFirst"];	
	$city      = $line["EmployeeCity"];	
	$state     = $line["EmployeeState"];	
	$hplan     = $line["HealthPlan"];
	$htype     = $line["HplanType"];
	$dplan     = $line["DentalPlan"];	
	$dtype 	 = $line["DplanType"];

print "$EEid<br>";
 	print "$first, $last<br>";
	print "$city, $state<br>";
	print "$hplan, $htype<br>";
	print "$dplan, $dtype<br>";
	print "---------------------------- <br>";

*/

while($line = mysql_fetch_array($result2))
{
	$EEid      = $line["EmployeeID"];
	$last	 = $line["EmployeeLast"];
	$first     = $line["EmployeeFirst"];	
	$city      = $line["EmployeeCity"];	
	$state     = $line["EmployeeState"];	
	$hplan     = $line["HealthPlan"];
	$htype     = $line["HplanType"];
	$dplan     = $line["DentalPlan"];	
	$dtype 	 = $line["DplanType"];

$spouselast	 = $line["SpouseLast"];
$spousefirst    = $line["SpouseFirst"];	

    if( $c == 0 ){
            $color = "dddddd";
            $c = 1;
    }else{
            $color = "white";
            $c = 0;
    }


//If employee plan type = single
if ($htype=="Single")
{	
$tableinfo .= "<tr bgcolor=$color>
<td nowrap>$EEid</td>	
<td nowrap>$last, $first</td>
<td nowrap>$city, $state</td>
<td nowrap>$hplan<br>$htype</td>
<td nowrap>$dplan<br>$dtype</td>
<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
<td nowrap>
<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
ALT=\"Edit Employee Profile\"></A></td>

<td nowrap class=\"small\">
<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
ALT=\"Delete Employee and Dependent Profiles\"></A>
</td>
 <td nowrap class=\"small\">n/a</td><td nowrap class=\"small\">n/a</td>
</tr>\n";
}

//If employee plan type = single parent
elseif ($htype=="Single Parent")
{	
$tableinfo .= "<tr bgcolor=$color>
<td nowrap>$EEid</td>	
<td nowrap>$last, $first</td>
<td nowrap>$city, $state</td>
<td nowrap>$hplan<br>$htype</td>
<td nowrap>$dplan<br>$dtype</td>
<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
<td nowrap>
<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
ALT=\"Edit Employee Profile\"></A></td>
<td nowrap class=\"small\">
<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
ALT=\"Delete Employee and Dependent Profiles\"></A>
</td>
 <td nowrap class=\"small\">n/a</td><td align=\"center\"><A HREF=\"dependents_add.php?id=$EEid\">ADD</A><br><A HREF=\"dependents.php?id=$EEid\">View</A></td>
</tr>\n";
}

//If employee plan type = married
elseif ( (($htype=="Married") && ($spousefirst!="n/a")) || (($dtype=="Married") && ($spousefirst!="n/a")) )
{
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$EEid</td>	
	<td nowrap>$last, $first</td>
	<td nowrap>$city, $state</td>
	<td nowrap>$hplan<br>$htype</td>
	<td nowrap>$dplan<br>$dtype</td>
	<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
	<td nowrap>
	<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
	ALT=\"Edit Employee Profile\"></A></td>
	<td nowrap class=\"small\">
	<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
	ALT=\"Delete Employee and Spouse Profiles\"></A>
	</td>
	<td nowrap class=\"small\">$spousefirst</td>
	<td class=\"small\">n/a</td>
	</tr>\n";

}

//If employee plan type = married and no spouse entered
elseif ( (($htype=="Married") && ($spousefirst=="n/a")) || (($dtype=="Married") && ($spousefirst=="n/a")) )
{
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$EEid</td>	
	<td nowrap>$last, $first</td>
	<td nowrap>$city, $state</td>
	<td nowrap>$hplan<br>$htype</td>
	<td nowrap>$dplan<br>$dtype</td>
	<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
	<td nowrap>
	<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
	ALT=\"Edit Employee Profile\"></A></td>
	<td nowrap class=\"small\">
	<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
	ALT=\"Delete Employee and Spouse Profiles\"></A>
	</td>
	<td nowrap class=\"small\"><A HREF=\"spouse_edit.php?id=$EEid\">Edit</A></td>
	<td class=\"small\">n/a</td>
	</tr>\n";

}


//If employee plan type = family and spouse entered
elseif (   (($htype=="Family") && ($spousefirst!="n/a"))  || (($dtype=="Family") && ($spousefirst!="n/a"))  )
{
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$EEid</td>	
	<td nowrap>$last, $first</td>
	<td nowrap>$city, $state</td>
	<td nowrap>$hplan<br>$htype</td>
	<td nowrap>$dplan<br>$dtype</td>
	<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
	<td nowrap>
	<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
	ALT=\"Edit Employee Profile\"></A></td>
	<td nowrap class=\"small\">
	<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
	ALT=\"Delete Employee, Spouse, and Dependent Profiles\"></A>
	</td>
	<td nowrap class=\"small\">$spousefirst</td>
	<td align=\"center\"><A HREF=\"dependents_add.php?id=$EEid\">ADD</A><br><A HREF=\"dependents.php?id=$EEid\">View</A></td></td>
	</tr>\n";
}

//If employee plan type = family and no spouse
elseif ( (($htype=="Family") && ($spousefirst=="n/a"))  || (($dtype=="Family") && ($spousefirst=="n/a")) )
{
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$EEid</td>	
	<td nowrap>$last, $first</td>
	<td nowrap>$city, $state</td>
	<td nowrap>$hplan<br>$htype</td>
	<td nowrap>$dplan<br>$dtype</td>
	<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
	<td nowrap>
	<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
	ALT=\"Edit Employee Profile\"></A></td>
	<td nowrap class=\"small\">
	<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
	ALT=\"Delete Employee, Spouse, and Dependent Profiles\"></A>
	</td>
	<td nowrap class=\"small\"><A HREF=\"spouse_edit.php?id=$EEid\">Edit</A></td>
	<td align=\"center\"><A HREF=\"dependents.php?id=$EEid\">VIEW</A></td></td>
	</tr>\n";
}


}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
       <tr>
	  <th align=\"left\" valign=\"bottom\">EE<br>ID</th>
	  <th align=\"left\" valign=\"bottom\">Name</th>
	  <th nowrap align=\"left\" valign=\"bottom\">City</th>
	  <th nowrap align=\"left\" valign=\"bottom\">Health Plan</th>
 	  <th nowrap valign=\"bottom\">Dental Plan</th>
	  <th valign=\"bottom\">&nbsp;</th>		
	  <th colspan='2' valign=\"bottom\">Employee</th>
	  <th valign=\"bottom\">Spouse</th>    

	  <th valign=\"bottom\">Dependents</th>		
	  </tr>";


print "$tableinfo";
print "</table>";
print "<br>";
print "<center><table>";
print "<center>$nav</center>\n";
print "</table></center>";


// memory flush
	mysql_free_result($result);
	mysql_free_result($result2);

?> 
<!--   END of PHP/MYSQL code -->

remnant

It's not bad, but I see a few ways to improve this. The first problem I see here is the way you duplicate your variables. Example:

$row = mysql_fetch_array($result); 

//Get ID values 
 if($row) { 
$CoID = $row["CompanyID"]; 
$id = $row["ProfileID"]; 
}

It's enough just to say:

$row = mysql_fetch_row($result);
// Sipmly do: echo $row['CompanyID']; to return the value.
// You don't need the IF because if no results are found there'll be no $row['CompanyID'] and if there's a SQL error your script will halt.

The biggest way to improve this script is to cut the redundancy. For example:

Slight variations of this snippet appear 6 times throughout your code

$tableinfo .= "<tr bgcolor=$color> 
        <td nowrap>$EEid</td>     

        <td nowrap>$last, $first</td> 
        <td nowrap>$city, $state</td> 
        <td nowrap>$hplan<br>$htype</td> 
        <td nowrap>$dplan<br>$dtype</td> 
        <td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td> 
        <td nowrap> 
        <A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\"  

        ALT=\"Edit Employee Profile\"></A></td> 
        <td nowrap class=\"small\"> 
        <A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\"  

        ALT=\"Delete Employee and Spouse Profiles\"></A> 
        </td> 
        <td nowrap class=\"small\"><A HREF=\"spouse_edit.php?id=$EEid\">Edit</A></td> 
        <td class=\"small\">n/a</td> 
        </tr>\n";

This is the perfect time to use a function:

// This is loosely written, you'll need to work through creating your own.
function tableInfo($color, $EEid, $last, $city, $hplan, $dplan, $status)
   {
$tableinfo .= "<tr bgcolor=$color> 
        <td nowrap>$EEid</td>     

        <td nowrap>$last, $first</td> 
        <td nowrap>$city, $state</td> 
        <td nowrap>$hplan<br>$htype</td> 
        <td nowrap>$dplan<br>$dtype</td> 
        <td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td> 
        <td nowrap> 
        <A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\"  

        ALT=\"Edit Employee Profile\"></A></td> 
        <td nowrap class=\"small\"> 

if ($status == "x")
   {
   echo "some html";
   }
else
   {
   echo "alternative html";
   }
        <td nowrap class=\"small\"><A HREF=\"spouse_edit.php?id=$EEid\">Edit</A></td> 
        <td class=\"small\">n/a</td> 
        </tr>\n"; 
   }

// Show table
tableInfo($varsX..);
// Show another table
tableInfo($varsY...);

One other minor point. Your HTML should be more standard. Example:

<table bgcolor=$color> should be <table bgcolor="$color">

This is not really that big of a deal, but it could become important later on, like if you start using XML. It's just better to do it correct from the start.

Also, I like to break out of PHP for large blocks of HTML, but that's just because I find it a lot easier to parse.

HTH