Complex PHP/MySQL Issues

[jay7981]

Hey all,

 

I am trying to build a page with a table that shows employees listed by department with the data being pulled from a DB, My issue is that i am a novice with Mysql and i am trying to make the table look very specific. any help here would be greatful! One important thing about the database is that the data is user submitted so some spelling and/or capitalization may be off and i need to make sure that everyone is listed and in the correct department.

 

Table needs to look like this

 

Department Name1 (count of employees in this department)

FirstName LastName (all names need to be sorted by last name asc per department and all Case has to be First letter Caps and rest lowercase)

<br>

Department Name2 (count of employees in this department)

FirstName LastName

ect... until all possible departments are used and none used twice.

 

the DB has these fields,

ID

Lname

Fname

Dept

and alot more but are not relevant to this page.

 

Thank you in advance for any help.

[Barand]

What have you attempted so far?

[jay7981]

<?php include_once('./Connections/local.php');
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
 if (PHP_VERSION < 6) {
   $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 }
 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 switch ($theType) {
   case "text":
  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  break;   
   case "long":
   case "int":
  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  break;
   case "double":
  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
  break;
   case "date":
  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  break;
   case "defined":
  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  break;
 }
 return $theValue;
}
}
$maxRows_Recordset1 = 20;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
 $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_local, $local);
$query_Recordset1 = "SELECT LName,FName,Department FROM $table GROUP BY Department ORDER BY LName ASC, Department ASC;";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $local) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
 $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 $all_Recordset1 = mysql_query($query_Recordset1);
 $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
mysql_free_result($Recordset1);
?>
<div id="left">
<table border="0" cellpadding="2" cellspacing="2">
 <?php do { ?>
  <tr>
    <td colspan="2"><?php echo $row_Recordset1['department']; ?></td>
  </tr>
  <tr>
  <td><?php echo $row_Recordset1['FName']; ?></td>
  <td><?php echo $row_Recordset1['LName']; ?></td>
   </tr>
   <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</div>

 

 

i am a noob at all this but i understand it more than anyone in my office so i was chosen to do this sadly... However i have learned many many new things working with php and mysql from this site and i appreciate all the help that you guys offer.

Edited February 12, 2013 by jay7981

[jay7981]

Ok So i am getting Closer on my own still not quite the way i need it... here is what i have so far....

 

<div id="left5">
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break;
case "long":
case "int":
 $theValue = ($theValue != "") ? intval($theValue) : "NULL";
 break;
case "double":
 $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
 break;
case "date":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break;
case "defined":
 $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
 break;
}
return $theValue;
}
}
$currentPage = $_SERVER["PHP_SELF"];
$maxRows_Recordset1 = 20;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_local, $local);
$query_Recordset1 = "SELECT mID, Department, LName, FName FROM botb_members ORDER BY LName ASC";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $local) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_Recordset1") == false &&
 stristr($param, "totalRows_Recordset1") == false) {
 array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>
<table border="0" cellpadding="2" cellspacing="2">
<tr>
 <td>First Name</td>
 <td>Last Name</td>
 <td>Department</td>
</tr>
<?php do { ?>
 <tr>
 <td><?php echo $row_Recordset1['FName']; ?></td>
 <td><?php echo $row_Recordset1['LName']; ?></td>
 <td><?php echo $row_Recordset1['Department']; ?></td>
 </tr>
 <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<?php
mysql_free_result($Recordset1);
?>
<table>
<tr>
 <td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>
	 <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>"><img src="/images/First.gif"></a>
	 <?php } // Show if not first page ?></td>
 <td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>
	 <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>"><img src="/images/Previous.gif"></a>
	 <?php } // Show if not first page ?></td>
 <td><?php if ($pageNum_Recordset1 < $totalPages_Recordset1) { // Show if not last page ?>
	 <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>"><img src="/images/Next.gif"></a>
	 <?php } // Show if not last page ?></td>
 <td><?php if ($pageNum_Recordset1 < $totalPages_Recordset1) { // Show if not last page ?>
	 <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, $totalPages_Recordset1, $queryString_Recordset1); ?>"><img src="/images/Last.gif"></a>
	 <?php } // Show if not last page ?></td>
</tr>
</table>
<?php echo ($startRow_Recordset1 + 1) ?> to <?php echo min($startRow_Recordset1 + $maxRows_Recordset1, $totalRows_Recordset1) ?> of <?php echo $totalRows_Recordset1 ?> Records </div>

 

This has all the elements that i want just none of the pizzaz that it needs, ... like breaking it down to departments, caps and such...

Edited February 12, 2013 by jay7981

[Psycho]

<?php

function outputDepartmentList($deptName, $userList)
{
   if(!$deptName) { return false; }
   $userCount = count($users);
   $output  = "{$currentDept} ({$userCount})<br>\n";
   $output .= " implode("<br>\n", $users);
   return $output;
}

$query = "SELECT Department, Fname, Lname
	  FROM table_name
	  ORDER BY Dept, Lname";
$result = mysql_query($query) or die (mysql_error());

$currentDept = false;
while($row = mysql_fetch_assoc($result))
{
   if($currentDept != $row['Dept'])
   {
    echo outputDepartmentList($currentDept, $users);
    $users = array();
    $currentDept = $row['Dept'];
   }
   $users[] = ucfirst($row['Fname']) . ' ' . ucfirst($row['Lname']);
}
echo outputDepartmentList($currentDept, $users);

?>

[jay7981]

Ok so i tried your function and it didnt quite work as posted so i modified it a bit fixing a syntax error or two and added a connection and this is what i got...

<?php
include("./includes/sql-conn.php");

function outputDepartmentList($deptName, $userList)
{
 if(!$deptName) { return false; }
 $userCount = count($users);
 $output = "{$currentDept} ({$userCount})<br>\n";
 $output .= "implode('<br>\n', $users)";
 return $output;
}
$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);
$query = "SELECT Department,FName,LName FROM $table ORDER BY Department, LName ASC" or die ('Failed to Query: ' . mysql_error());
$result = mysql_query($query) or die ('Failed Getting Results: ' . mysql_error());
$currentDept = false;
while($row = mysql_fetch_assoc($result))
{
 if($currentDept != $row['Department'])
 {
		 echo outputDepartmentList($currentDept, $users);
		 $users = array();
		 $currentDept = $row['Department'];
 }
 $users[] = ucfirst($row['FName']) . ' ' . ucfirst($row['LName']);
}
echo outputDepartmentList($currentDept, $users);
mysql_free_result($result);
mysql_close($db);
?>

 

Which ouput this:

(0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', ) (0)
implode('
', )

 

From the looks of the code it should work as intended (by the way awsome job i would have never thought of using a function to do this ... ) Not sure what is going on....

Edited February 13, 2013 by jay7981

[jay7981]

Any ideas?

[jazzman1]

Check again the Psycho's script.

 

$output .= " implode("<br>\n", $users);

it's not the same to:

$output .= " implode('<br>\n', $users);

[jay7981]

ok so i did the suggested fix and now i have this error

Warning: implode(): Invalid arguments passed in /homepages/23/d451520845/htdocs/tea.php on line 9

(0)

when i echo $users i get Array and thats it ... as i understand this the line users[] = .... is supposed to add to the array from the database ... but its not.

Here is the code in total:

<?php
include("./includes/sql-conn.php");

function outputDepartmentList($deptName, $userList)
{
 if(!$deptName) { return false; }
 $userCount = count($users);
 $output = "{$currentDept} ({$userCount})<br>\n";
 $output .= implode('<br>\n', $users);
 return $output;
}
$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);
$query = "SELECT Department, FName, LName FROM table_name ORDER BY Department, LName ASC" or die ('Failed to Query: ' . mysql_error());
$result = mysql_query($query) or die ('Failed Getting Results: ' . mysql_error());
$currentDept = false;
while($row = mysql_fetch_assoc($result))
{
 if($currentDept != $row["Department"])
 {
		 echo outputDepartmentList($currentDept, $users);
		 $users = array();
		 $currentDept = $row['Department'];
 }
 $users[] = ucfirst($row['FName']) . ' ' . ucfirst($row['LName']);
}
echo outputDepartmentList($currentDept, $users);
mysql_free_result($result);
mysql_close($db);
?>

Edited February 14, 2013 by jay7981

[Barand]

The function parameter is $userList but uses $users in the function code.

Edited February 14, 2013 by Barand

[jay7981]

Thank you Barand, I must have looked over that 500 times by now ... ok so here is the code i had to make a couple of formatting changes but it is working great now! Thank you all so much!

 

<?php
include("./includes/sql-conn.php");

function outputDepartmentList($currentDept, $users)
{
    if(!$currentDept) { return false; }
    $userCount = count($users);
    $output  = "<br>\n{$currentDept} ({$userCount})";
 $output .= "<br>\n";
    $output .= implode("<br>\n", $users);
 $output .= "<br>\n";
    return $output;
}
$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);
$query = "SELECT Department, FName, LName FROM botb_members ORDER BY Department, LName ASC" or die ('Failed to Query: ' . mysql_error());
$result = mysql_query($query) or die ('Failed Getting Results: ' . mysql_error());
$currentDept = false;
while($row = mysql_fetch_assoc($result))
{
    if($currentDept != $row["Department"])
    {
		    echo outputDepartmentList($currentDept, $users);
		    $users = array();
		    $currentDept = $row['Department'];

    }
    $users[] = ucfirst($row['FName']) . ' ' . ucfirst($row['LName']);
}
//print_r ($users);
echo outputDepartmentList($currentDept, $users);
mysql_free_result($result);
mysql_close($db);
?>