Jump to content

Complex PHP/MySQL Issues

jay7981

By jay7981
in PHP Coding Help

 Share

Recommended Posts

jay7981 Member

jay7981

Posted
Posted

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.

Link to comment
https://forums.phpfreaks.com/topic/274372-complex-phpmysql-issues/
Share on other sites
jay7981 Member

jay7981

Posted
  • Author
Posted (edited) 

<?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 by jay7981
jay7981 Member

jay7981

Posted
  • Author
Posted (edited)

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 by jay7981
Psycho Prolific Member

Psycho

Posted
Posted 
<?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 Member

jay7981

Posted
  • Author
Posted (edited)

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 by jay7981
jay7981 Member

jay7981

Posted
  • Author
Posted (edited)

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 by jay7981
jay7981 Member

jay7981

Posted
  • Author
Posted

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);
?>

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.