Display Unique Values from MySQL database

Question

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:

Gather all unique names ($fn, $ln) in my database, based on a where clause and the fields practice=ccv & year=2016 (for example)
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
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>

score 1 · Accepted Answer · 2016-02-22 16:50:55Z

1

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.

edited Feb 22, 2016 at 16:50

answered Feb 22, 2016 at 15:35

user771405

Sign up to request clarification or add additional context in comments.

9 Comments

Vacek Over a year ago

This is what I was looking for ... I knew the sql level was a better spot to work this out. I'll do some testing and report back ! Thanks very much.

Vacek Over a year ago

struggling with the third query. First 2 work fine if I remove " , * " .... $SQL3 = "SELECT DISTINCT CONCAT(fn,' ',ln) AS 'name', * FROM $table WHERE year='$year' AND practice='$practice' AND archived!='yes' AND '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') AS subquery ORDER BY region,fn,ln";

Vacek Over a year ago

throws the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM rtu_prem_ww WHERE year='2016' AND practice='ccv' AND archived!='yes' AND ' at line 1

Vacek Over a year ago

if I remove " , * " and replace it with " , region " it moves on but then I get: server version for the right syntax to use near 'AS subquery ORDER BY region,fn,ln' at line 1

user771405 Over a year ago

Try my edited queries above. Think I found the errors.

|

Collectives™ on Stack Overflow

Display Unique Values from MySQL database

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related