show if user has had a particular test or not.

[fife]

Hey Guys.

 

Im trying to show if the selected user has had all the required tests they need so they can have a treatment at a hairdressers.  For example the client needs to of had a skin test before they can have their hair dyed.  Im struggling on showing which tests the user still needs to have

 

Currently I'm working with 3 tables.

 

The treatmentTest table which stores all the required tests

 

treatmentTest

idtreatmentTest

 

the user table which has the users details

 

user

iduser

 

and the usertest table which stores when they had the test and if it has expired (if it has expired is set to 1)

 

userTest

iduser

idtreatmentTest

expired

 

 

so I need this box to pop up with a warning on each test that is still outstanding but I dont want it to show if there are no tests needed. I obviously need to loop through the results of my query but the query its self is confusing me...

  <ul class="list-unstyled">
                     
                        <li>[treatment Name]</li>
                        
                      
                        <li class="text-italic text-muted">Expired [date]</li>
                        
                        <li><a href="#" class="btn btn-success">Update</a></li>
                        
                    </ul>
 

First I tried looping through all the treatments then inside that loop writing the following

 
<?php do {
  //now foreach treatment test get the resulting test from the usertest table
 
 
if (isset($row_testReq['idtreatmentTest'])) {
 $col_userTest = $row_testReq['idtreatmentTest'];
}
 
if (isset($_SESSION['cClient'])) {
 $colname_userTest = $_SESSION['cClient'];
}
 
if (isset($expire)) {
 $colname_userTestExpire = '1';
}
 
mysql_select_db($database_dbconnect, $dbconnect);
$query_userTest = sprintf("SELECT * FROM userTest WHERE idtreatmentTest = %s AND iduser = %s AND  userTestExpire=%s",
GetSQLValueString($col_userTest, "int"),
GetSQLValueString($colname_userTest, "int"),
GetSQLValueString($colname_userTestExpire, "text"));
$userTest = mysql_query($query_userTest, $dbconnect) or die(mysql_error());
$row_userTest = mysql_fetch_assoc($userTest);
$totalRows_userTest = mysql_num_rows($userTest);
   ?>
            <li class="list-group-item">
              <div class="col-sm-2"><i class="fa fa-2x fa-close text-danger"></i></div>
              <div class="col-sm-9">
               <ul class="list-unstyled">
                     <?php if($totalRows_userTest>0){?>
                        <li><?php echo $row_testReq['treatmentTestName'];?></li>
                        
                      
                        <li class="text-italic text-muted">Expired <?php echo $fn->sqlShowDate($row_userTest['userTestExpire']); ?></li>
                        
                        <li><a href="client-treat-test.php?i=<?php echo $row_testReq['idtreatmentTest'];?>&t=<?php echo $row_testReq['treatmentTestToken'];?>" class="btn btn-success">Update</a></li>
                       <?php } ?>

                    </ul>

               

                 </div>

              <div class="clearfix"></div>

              </li>

             <?php } while ($row_testReq = mysql_fetch_assoc($testReq)); ?>

                   

 

this doesnt work properly at all. It gave me all the treatments that have expired but if a new treatmenttest was added it wouldnt display that there was a test required.  

 

Does anybody know a different way?  I also tried getting all the treatments then all the rows in the userTest table then comparing what the differences where but I struggle with arrays comparisons and im not even sure that was the best way to go about it.  Am I going to need two queries? One like the loop above and one that looks for expired treatments and one that looks for treatments the user hasn't had or can this be done in one and if so how??

Edited November 18, 2014 by fife

[Barand]

Your other table structure in your previous post was better, once you get round the notion that users in your tables are actually clients

 

Now you have lost both the association of a test with a treatment and the valid duration for a test before it expires.

"Expired" is a derived value (and therefore should not be in a table) based on the duration and when the test was last given. How do you now know if a test has expired without going through and manually updating those "expired" flags?

[NotionCommotion]

Try something like the following.

SELECT tt.idtreatmentTest, tt.expired
FROM treatmentTests AS tt
LEFT OUTER JOIN userTest AS ut ON ut.idtreatmentTest=tt.idtreatmentTest AND tt.iduser=123
WHERE tt.expired IS NULL OR tt.expired<NOW();

[fife]

Hi Barand.  Thanks for your continued help with my php problems. When the page is run it tests all the current tests the client has had against an expiry date field in the userTest table.  If today is > than the expirydate field it updates the Expired field to 1 for each of the test.  This is how I find all the expired tests.  That bit works ok as I tested it before.  The only problem I'm having is the one above.  I still have the tables from my previous attempt I've just added the userTest table.

 

NotionCommotion thank you I'll try that in a mo and get back to you on this post

[NotionCommotion]

My query assumed tt.expired is a datetime field indicating when the user's certificate will expire.

[fife]

NotionCommotion this indeed is showing expired tests (with a few changes to the query.  Only so I understand it) but it is not showing tests they have not had

mysql_select_db($database_dbconnect, $dbconnect);
$query_testReq = sprintf("SELECT treatmentTest.idtreatmentTest, treatmentTest.treatmentTestName
FROM treatmentTest
LEFT JOIN userTest  ON userTest.idtreatmentTest = treatmentTest.idtreatmentTest
WHERE userTest.iduser=%s AND userTest.userTestExpired=%s", 
GetSQLValueString(60, "int"),
GetSQLValueString(1, "int"));
$testReq = mysql_query($query_testReq, $dbconnect);
$row = mysql_fetch_assoc($testReq);
$totalRows_testReq = mysql_num_rows($testReq);
 
 
print_r($row);
 
 
//outputs
Array ( [idtreatmentTest] => 1 [treatmentTestName] => Skin test )
 
 

there is a second treatment that they have not had which doesnt show in the list

Edited November 18, 2014 by fife

[NotionCommotion]

Fife,

 

While it might not be doing what you want it to do, the good news is it is doing exactly what it is suppose to do.

 

You JOIN your tables, and then put a WHERE clause on it to filter for a given user.  This removes all the tests which the user has never completed, which obviously is not your intent.

 

I made a couple mistakes in my previous query, but I still think it is the way to go.  See how the condition to check the user is in the ON clause and not in the WHERE clause?  Note that it assumes ut.expires is the date when the user must retake the test.  Does this make sense?  If it is just a flag, then I agree with Barand's remarks, and you should rethink your strategy.

SELECT tt.idtreatmentTest
FROM treatmentTests AS tt
LEFT OUTER JOIN userTest AS ut ON ut.idtreatmentTest=tt.idtreatmentTest AND ut.iduser=123
WHERE ut.expired IS NULL OR ut.expired<NOW();

EDIT.  Also, when getting unexpected results, I highly recommend testing your queries the old fashion way directly in MySQL instead of through PHP.  Nine times out of ten, it will be obvious right when you see the query.

Edited November 18, 2014 by NotionCommotion

[Barand]

I used your initial tables and added an appointment table

CREATE TABLE treatmentUser (
idtreatmentUser  INT NOT NULL PRIMARY KEY,
idtreatment INT,
iduser INT,
dateOfTreatment DATE       -- yyyy-mm-dd
);

CREATE TABLE treatmentTest (
idtreatmentTest INT NOT NULL PRIMARY KEY,
treatmentTestExpiry INT    -- days for which the test is valid
);

CREATE TABLE treatment (
idtreatment  INT NOT NULL PRIMARY KEY,
idtreatmentTest INT
);

CREATE TABLE appointment (
idappointment INT NOT NULL PRIMARY KEY,
iduser INT,
apptime DATETIME,         -- yyyy-mm-dd hh:ii:ss
idstylist INT
);

And used this query. (The subquery is used so you use only the latest test date for a user as they may have have several tests on record)

SELECT a.iduser
    , CONCAT(first_name,' ',last_name) as name
    , DATE_FORMAT(apptime, '%a %D %l:%i%p') as time
    , a.idtreatment
    , a.idstylist
    , t.idtreatmentTest
    , tt.idtreatmentTest
    , tu.dateOfTreatment
    , tt.treatmentTestExpiry
    , CASE
        WHEN DATEDIFF(a.apptime, tu.dateOfTreatment) > treatmentTestExpiry
        THEN 'TEST REQUIRED'
        ELSE ''
        END  as testReq
FROM appointment a
    INNER JOIN user u USING (iduser)
    INNER JOIN treatment t ON a.idtreatment = t.idtreatment
    LEFT JOIN treatmenttest tt ON t.idtreatmenttest = tt.idtreatmenttest
    LEFT JOIN treatmentuser tu 
        ON a.idtreatment = tu.idtreatment
        AND a.iduser = tu.iduser
    LEFT JOIN (
        SELECT iduser, idtreatment, MAX(dateOfTreatment) as dateOfTreatment
        FROM treatmentuser
        GROUP BY iduser, idtreatment
        ) latest ON tu.iduser = latest.iduser
            AND tu.idtreatment = latest.idtreatment
            AND tu.dateOfTreatment = latest.dateOfTreatment
ORDER BY time;

edit - my data

 

 

CREATE DATABASE  IF NOT EXISTS `fife` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `fife`;
-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
--
-- Host: 127.0.0.1    Database: fife
-- ------------------------------------------------------
-- Server version	5.1.57-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `treatmentuser`
--

DROP TABLE IF EXISTS `treatmentuser`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `treatmentuser` (
  `idtreatmentUser` int(11) NOT NULL,
  `idtreatment` int(11) DEFAULT NULL,
  `iduser` int(11) DEFAULT NULL,
  `dateOfTreatment` date DEFAULT NULL,
  PRIMARY KEY (`idtreatmentUser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `treatmentuser`
--

LOCK TABLES `treatmentuser` WRITE;
/*!40000 ALTER TABLE `treatmentuser` DISABLE KEYS */;
INSERT INTO `treatmentuser` VALUES (1,2,1,'2014-07-15'),(2,2,2,'2014-10-31'),(3,3,3,'2014-09-15'),(4,3,4,'2013-11-30');
/*!40000 ALTER TABLE `treatmentuser` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `appointment`
--

DROP TABLE IF EXISTS `appointment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `appointment` (
  `idappointment` int(11) NOT NULL,
  `iduser` int(11) DEFAULT NULL,
  `apptime` datetime DEFAULT NULL,
  `idstylist` int(11) DEFAULT NULL,
  `idtreatment` int(11) DEFAULT NULL,
  PRIMARY KEY (`idappointment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `appointment`
--

LOCK TABLES `appointment` WRITE;
/*!40000 ALTER TABLE `appointment` DISABLE KEYS */;
INSERT INTO `appointment` VALUES (1,1,'2014-11-18 10:00:00',1,1),(2,1,'2014-11-25 11:30:00',2,2),(3,2,'2014-11-18 12:00:00',1,2),(4,3,'2014-11-18 10:00:00',2,3),(5,4,'2014-11-18 15:00:00',1,3);
/*!40000 ALTER TABLE `appointment` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `treatmenttest`
--

DROP TABLE IF EXISTS `treatmenttest`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `treatmenttest` (
  `idtreatmentTest` int(11) NOT NULL,
  `treatmentTestExpiry` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtreatmentTest`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `treatmenttest`
--

LOCK TABLES `treatmenttest` WRITE;
/*!40000 ALTER TABLE `treatmenttest` DISABLE KEYS */;
INSERT INTO `treatmenttest` VALUES (1,30),(2,90);
/*!40000 ALTER TABLE `treatmenttest` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `treatment`
--

DROP TABLE IF EXISTS `treatment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `treatment` (
  `idtreatment` int(11) NOT NULL,
  `idtreatmentTest` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtreatment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `treatment`
--

LOCK TABLES `treatment` WRITE;
/*!40000 ALTER TABLE `treatment` DISABLE KEYS */;
INSERT INTO `treatment` VALUES (1,NULL),(2,1),(3,2),(4,NULL);
/*!40000 ALTER TABLE `treatment` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `iduser` int(11) NOT NULL,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`iduser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'Stella','Katapoulis'),(2,'Amanda','Brown'),(3,'Katy','Green'),(4,'Jane','Wilson');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-11-18 23:25:35

 

 

 

These were my results

Edited November 18, 2014 by Barand

[Barand]

My query above did not take into account new clients who had no previous test results.

SELECT a.iduser
    , CONCAT(first_name,' ',last_name) as name
    , DATE_FORMAT(apptime, '%a %D %l:%i%p') as time
    , a.idtreatment
    , a.idstylist
    , t.idtreatmentTest
    , tu.dateOfTreatment
    , tt.treatmentTestExpiry
    , CASE
        WHEN DATEDIFF(a.apptime, IFNULL(tu.dateOfTreatment,'0000-01-01')) > treatmentTestExpiry
        THEN 'TEST REQUIRED'
        ELSE ''
        END  as testReq
FROM appointment a
    INNER JOIN user u USING (iduser)
    INNER JOIN treatment t ON a.idtreatment = t.idtreatment
    LEFT JOIN treatmenttest tt ON t.idtreatmenttest = tt.idtreatmenttest
    LEFT JOIN (
        SELECT iduser, idtreatment, MAX(dateOfTreatment) as dateOfTreatment
        FROM treatmentuser
        GROUP BY iduser, idtreatment
        ) tu ON a.idtreatment = tu.idtreatment
            AND a.iduser = tu.iduser
ORDER BY time;

[fife]

Barand.  Apologies for the late reply.  Thank you for your time on this topic.  You always seem to go that extra mile.  Unfortunately I dont understand your solution and wouldn't be able to fix it if it broke.  Ive opted to scratch this feature for now while I think about how to do it differently.  Thank you anyway and Merry Christmas to all the PHP Freaks