CODEWITHSUNDEEP
phpmysqlsqlmysqli
Vacek
Vacek

Reputation: 189

Display Unique Values from MySQL database

I'm reporting my question to be a little more succinct. (deleted other)

I have a time reporting system I have built, and I am trying to create an adherence type report.

Esentially I'm trying to do the following:

  1. Gather all unique names ($fn, $ln) in my database, based on a where clause and the fields practice=ccv & year=2016 (for example)
  2. Then I also want to gather up another list of names ($fn, $ln), based on practice=ccv & year=2016 and additional where clause week=8
  3. This will give me 2 DISTINCT lists that I can compare, and the difference will be the Outstanding timecards for week 8.

From there, I need to display all the names for submitted (from #3 above) and also the list of outstanding (by comparing the #1 and #2 above)

Thus far, I can get the list of names. However, I then need to display them with additional data from the database for each person (Region, Timestamp, etc ..)

Desired Output would be something like this:
Submitted
EMEA
John J
Wally R
North America
Davis D
Bob C
John Y

Outstanding
EMEA
Kyle D
North America
Cory T
Hugh R
John J
LATAM
etc ....

Thus far, here is what I have .. I'm sure I'm going about this the wrong way.

<?php
$con = mysqli_connect("localhost","xxx","xxx!","xxx");                  

//Get distinct names from practice
if ($con) {
$SQL = "SELECT DISTINCT fn,ln FROM $table WHERE year='$year' AND practice='$practice' AND archived!='yes' ORDER BY region,fn,ln";   
}

$result = mysqli_query($con,$SQL);

if (!$result) die('Couldn\'t fetch records'); 

while ( $db_field = mysqli_fetch_assoc($result) ) {
$name[] = trim($db_field['fn']. " " .$db_field['ln']);
}


//Get distinct names for current week practice
if ($con) {
$SQL2 = "SELECT DISTINCT fn,ln FROM $table WHERE year='$year' AND week_num='$week' AND practice='$practice' AND archived!='yes' ORDER BY region,fn,ln"; 
}

$result2 = mysqli_query($con,$SQL2);

if (!$result2) die('Couldn\'t fetch records Again'); 

while ( $db_field = mysqli_fetch_assoc($result2) ) {

$name2[] = trim($db_field['fn']. " " .$db_field['ln']);
}

//$SQL3 = "SELECT fn,ln,week_start_date,region FROM $table WHERE fn IN ( '".implode("', '", $differences)."' )");

mysqli_close($con);
?>

<TABLE><TR><TD>
<B>All Names</B><BR>
<?php
foreach ( $name as $item ) {
    echo $item . "<br/>";
}
?>
</TD>
<TD>
<B>Names for Week 8</B><BR>
<?php
foreach ( $name2 as $item2 ) {
    echo $item2 . "<br/>";
}
?>
</TD>
<TD>
<B>Outstanding</B><BR>
<?php
$results = array_diff($name, $name2);

foreach($results as $val) {
    echo $val ." - ".$val2."<BR>";          
}
?>

</TD>
</TR>
</TABLE>

Upvotes: 1

Views: 795

Answers (1)

cjohansson
cjohansson

Reputation: 1103

I think you should solve this at MySQL level.

// All names with all data
$SQL = "SELECT DISTINCT *, CONCAT(fn,' ',ln) AS `name` FROM $table WHERE year='$year' AND practice='$practice' AND archived!='yes' ORDER BY region,fn,ln";

// Names for specific week with all data
$SQL = "SELECT DISTINCT *, CONCAT(fn,' ',ln) AS `name` FROM $table WHERE year='$year' AND week_num='$week' AND practice='$practice' AND archived!='yes' ORDER BY region,fn,ln";

// Outstanding rows with all data
$SQL = "SELECT * FROM (SELECT DISTINCT *, CONCAT(fn,' ',ln) AS `name` FROM $table WHERE year='$year' AND practice='$practice' AND archived!='yes') AS `subquery` WHERE `name` NOT IN (SELECT DISTINCT CONCAT(fn,' ',ln) AS `name` FROM $table WHERE year='$year' AND week_num='$week' AND practice='$practice' AND archived!='yes') ORDER BY region,fn,ln";

This way you won't need to sort and process data at PHP-level. Can't test this since I don't have the database environment but it should work, may require some small changes.

Upvotes: 1

Related Questions