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 have a query that shows me the Stores where for for this current DateLog there isn't a record in tblResults, but now I want to add in data from tblUser to 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 !
Code I have (which outputs all the stores with missing info)
$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.
