NYCPHP Meetup

[Alberto dos Santos]

Dan:

Although it didn't work, it drove me into reading mysql manual with another
focus, and alas, I fount it!
I will explain the problem and the solution, because it may just help
somebody else.

This is in a .org site, non-profit, and it relates to bible readings.
"catalog_reading" is the complete bible references repository for the site,
no matter what language or liturgic calendar, all the references are entered
and read from there.

Then we have country-specific liturgical calendars, like
calendar_liturgic_am for american english ;), and our problem was to know
which of the 3900+ entries on the references table was not used by any of
the daily readings on any country.
My solution was found on the manual in the UNION sintax pages, and it goes
like this:

require_once("../common/php_functions.inc"); // please notice I use my
libraries
$trans =
array('am','ar','bbn','de','default','fr','it','maa','maa_orig','nl','pl','p
t','sp','tra','trf');
if (!sql_server_connect())
	{echo "Connection error<br>";}
else
	{echo "Connected<br>";}
if (!sql_choose_db())
	{echo "Db not selected}";}
else
	{echo "Db selected<br>";}

$CR_query = "SELECT * FROM catalog_reading WHERE NOT EXISTS (";

for ($count = 0 ; $count <= 14 ; $count++)
{
	$CR_query .= "SELECT CL_FR_CRID,CL_PS_CRID,CL_SR_CRID,CL_GSP_CRID
FROM calendar_liturgic_".$trans[$count]." WHERE ";
	$CR_query .=
"calendar_liturgic_".$trans[$count].".CL_FR_CRID=catalog_reading.CR_ID OR ";
	$CR_query .=
"calendar_liturgic_".$trans[$count].".CL_PS_CRID=catalog_reading.CR_ID OR ";
	$CR_query .=
"calendar_liturgic_".$trans[$count].".CL_SR_CRID=catalog_reading.CR_ID OR ";
	$CR_query .=
"calendar_liturgic_".$trans[$count].".CL_GSP_CRID=catalog_reading.CR_ID
UNION ";
}
$CR_query = substr($CR_query,0,-7);
$CR_query .= ")";

$CR_query_result = sql_do_query($CR_query);
$CR_count = sql_count_lines($CR_query_result);

echo "lines = ".$CR_count."<br>";
while ($CR_line = sql_do_array($CR_query_result))
{
	echo "CR_ID: ".$CR_line['CR_ID']." CR_READING:
".$CR_line['CR_READING']."<br>";
}
 
Thank you, Dan and the other, for the inspiration.

--
Alberto dos Santos
email: acas at sapo.pt
skype: fatflash