[RESOLVED] query mysql to find 'missing' data

[sheepsheep]

Every week users are required to enter data about a specific store/shop into the mysql database.
I want to query the database to find out who hasn't done it, and output it in a csv file.

Database is structured;
tblResults - showing records where users have entered the data
tblUser - user info
tblStore - info about a store
tblDateLog - info about the week number/date

So I am thinking a query along the lines of
$query tblStore and show me the Stores where for for this current DateLog there isn't a record in tblResults and also for each store show me the relevant User information for the person working at the store so I can contact them and find out what is going on !

Could you have a look at my code and point out where I am going wrong, or on the other hand tell me I am going about the query in completely the wrong way and tell me a better way of doing it..... thanks

Code I have (which outputs all the stores and all the users, not only those with missing info)

session_start ();					
$_SESSION['username'] = $username;
require_once ('../mysql_connect.php');

header('Content-Type: application/download; name="readings_status.csv"');  

header('Content-Disposition:attachment; filename="readings_status.csv"'); 
header('Content-Transfer-Encoding: binary');  

//CREATE STATUS.CSV FILE
$query="SELECT `ConcesRef` FROM `tblUser` WHERE `ULogIn` = '$username'";
$result3 = mysql_query($query)
or die(mysql_error());
$row3 = mysql_fetch_array($result3);
$ConcesRef = $row3["ConcesRef"];

if (isset($ConcesRef))
{
require_once ('../mysql_connect.php');		//connect to the database
$List = array();
$count = 0;

if (sizeof($List) == 0)
{
//START FILLING IN THE FIELDNAME ON TOP OF EXCEL SHEET.
echo "Store Ref" . "," . "Common Name" . "," . "Serial No" . "," . "Contract Ref" . "," . "User Ref" . "," . "User Name" . ",";
echo ("\n");

//START FILLING IN THE ROWS IN EXCEL SHEET.
$query = "SELECT DateLogId FROM tblDateLog WHERE ConcesRef = $ConcesRef ORDER BY EndDate DESC LIMIT 1";
$result5 = mysql_query($query)
	or die(mysql_error());
$row5 = mysql_fetch_array($result5);
$DateLogId = $row5["DateLogId"];

$sql = "SELECT DISTINCT tblStore.StoreId, 
				tblStore.CommonName, 
				tblStore.SerialNumber, 
				tblStore.ContractRef,  

				tblUser.UserId, 
				tblUser.UserName 
FROM ((tblStore INNER JOIN tblUser
ON (tblUser.ConcesRef = tblStore.ConcesRef)
AND (tblUser.UserId = tblStore.UserRef))
		Inner JOIN tblResults
ON (tblStore.ConcesRef = tblResults.ConcesRef)
AND (tblStore.UserRef = tblResults.UserRef)
AND (tblStore.ContractRef = tblResults.ContractRef)
AND (tblStore.StoreId = tblResults.StoreRef)
AND tblResults.DateLogRef = '$DateLogId' 
AND tblResults.ResultsId Is Null
AND tblUser.ConcesRef = '$ConcesRef' )";

$getinfo = mysql_query($sql)
 or die (mysql_error());
while($row = mysql_fetch_array($getinfo, MYSQL_ASSOC))
{
//$row = array_unique($row); 
while (list($key, $value) = each($row))
{
echo ("$value" . ",");
} // close 2nd while
echo ("\n");
} // close 1st while
mysql_close();
} // close 2nd if
else
{
listErrors();
} // close 1st else
} //close 1st if
else
{
echo "please try again";
} // close 2nd else

[Piranha]

I don't know for sure if it is the right tables, but something like this would work:

SELECT user.id
FROM user
WHERE user.id NOT IN (SELECT shop.userid FROM shop WHERE date = '$some_value')

or you can do it like this:

SELECT user.id
FORM user
LEFT JOIN shop
ON shop.userid = user.id
WHERE shop.date = '$some_value'
AND shop.userid IS NULL

[tomhath]

Instead of in INNER JOIN to tblResults (which will find the ones who have entered data), change to a NOT EXISTS to find teh ones who didn't enter:

Replace this:

 Inner JOIN tblResults
ON (tblStore.ConcesRef = tblResults.ConcesRef)
AND (tblStore.UserRef = tblResults.UserRef)
AND (tblStore.ContractRef = tblResults.ContractRef)
AND (tblStore.StoreId = tblResults.StoreRef)
AND tblResults.DateLogRef = '$DateLogId'
AND tblResults.ResultsId Is Null
AND tblUser.ConcesRef = '$ConcesRef' )

With something like this (untested):

)
WHERE NOT EXISTS (
             SELECT 1 FROM  tblResults
             WHERE  tblStore.ConcesRef = tblResults.ConcesRef
                 AND tblStore.UserRef = tblResults.UserRef
                 AND tblStore.ContractRef = tblResults.ContractRef
                 AND tblStore.StoreId = tblResults.StoreRef
                 AND tblResults.DateLogRef = '$DateLogId'
                 AND tblResults.ResultsId Is Null
                 AND tblUser.ConcesRef = '$ConcesRef' )

[sheepsheep]

thank you for your replies
I am currently working on Piranha's idea...

SELECT user.id
FROM user
WHERE user.id NOT IN (SELECT shop.userid FROM shop WHERE date = '$some_value')

Is giving me the correct output in terms of showing me the stores which have records missing for the current dateref.
I am now expanding on it to show me the corresponding user information for each of the stores, but at least I am on the right track now - thank you

[sheepsheep]

Since my last post I have been going round in circles trying to include data from another table in this query.

I now have (following your advice) the query to show the stores that haven't sent data this week. However I am now trying to include in this query the user information to show in the csv file the user related to that store.

code that works - show the 'missing' stores

$sql = "SELECT tblStore.StoreId, tblStore.CommonName, tblStore.SerialNumber, 
tblStore.ContractRef, tblStore.UserRef, tblUser.CoName FROM tblStore
WHERE StoreId NOT IN(SELECT StoreRef FROM tblResults WHERE DateLogRef = '$DateLogId' AND ConcesRef = '$ConcesRef')
";
$getinfo = mysql_query($sql)
 or die (mysql_error());
while($row = mysql_fetch_array($getinfo, MYSQL_ASSOC))
{
while (list($key, $value) = each($row))
{
echo ("$value" . "," );

} // close 2nd while
echo ("\n");
} // close 1st while
mysql_close();
} // close 2nd if
else
{
listErrors();
} // close 1st else
} //close 1st if
else
{
echo "please try again";
} // close 2nd else

I have tried to extend this code to try to include data from tblUser as follows

$sql = "SELECT tblStore.StoreId, tblStore.CommonName, tblStore.SerialNumber, 
tblStore.ContractRef, tblStore.UserRef, tblUser.CoName FROM tblStore, tblUser
WHERE StoreId NOT IN(SELECT StoreRef FROM tblResults WHERE DateLogRef = '$DateLogId' AND ConcesRef = '$ConcesRef')
AND tblUser.UserId = tblStore.UserRef
";
$getinfo = mysql_query($sql)
 or die (mysql_error());
while($row = mysql_fetch_array($getinfo, MYSQL_ASSOC))
{
while (list($key, $value) = each($row))
{
echo ("$value" . "," );

} // close 2nd while
echo ("\n");
} // close 1st while
mysql_close();
} // close 2nd if
else
{
listErrors();
} // close 1st else
} //close 1st if
else
{
echo "please try again";
} // close 2nd else

This gives the correct output and shows the User information as I would expect, however, it stops after the first 2 records. In my test I know there are 8 records - the first, shorter code outputs all 8 records, but this extended code only does 2.

I have tried so many different permutations of the code and cannot get it to work at all - any help will be gratefully received - thanks.