NYCPHP Meetup

[soazine at pop.erols.com]

Here is the scope of what I need to do;

want:

enrollment_year
allowed (even if null)
all of ica

criteria:

1) join ica and i
2) ica.intern_id = 821
3) either (q.partner_name = 'ECSU' and joins with permissions according to
your intern_id AND ica.has_ecsu_essay = 'Y') OR
(ica.has_ecsu_essay != 'Y' AND there is NO row in permissions with
partner_id where q.partner_id = 'ECSU' according to 821)

This is what I've done so far and it fails:

SELECT distinct i.enrollment_year,
IF (p.intern_id = i.id AND p.intern_id = '821' AND p.partner_name =
q.partner_name AND upper(q.partner_name) = 'ECSU' AND
upper(ica.has_ecsu_essay) = 'Y'), '1', '0' AS allowed,
ica.*
FROM interns i, intern_complete_application ica, permissions p, partners q
WHERE ica.intern_id = i.id
AND ica.intern_id = '821'
AND upper(ica.has_ref_letter_1) = 'Y'
AND upper(ica.has_ref_letter_2) = 'Y'
AND upper(ica.has_transcript) = 'Y'

I get syntax errors on this query, and every other combination gives me
syntax errors or wrong data. I have been trying for 2 days to get this
query and it's either that or I write this horrifically bloated PHP method
to do the same thing (it works but it takes about 15 seconds to execute the
whole thing):

function isCompletedIntern($internID) { // BOOLEAN "METHOD"
global $dbHost,$dbPort,$dbUser,$dbPwd,$dbName;

$db = new dbConnection($dbHost,$dbPort,$dbUser,$dbPwd,$dbName);
$dbconnection = $db->connect();

$isCompleted = 1;

$query = 'SELECT * FROM intern_complete_application WHERE intern_id = \'' .
$internID . '\'';
$queryInfo = new mySqlQuery($query, $dbconnection);
$result = $queryInfo->getResult();
if (get_object_vars($result[0])) {
foreach (array('has_ref_letter_1', 'has_ref_letter_2', 'has_transcript',
'has_grad_enrollment', 'has_ecsu_essay') as $key => $val) {
$arrayKeyName = $this->dbNameToArrayName($val);
${$arrayKeyName} = $result[0]->$val;
}
$result = null;
}

$query = 'SELECT partner_id, upper(partner_name) as name FROM partners ' .
'WHERE upper(partner_name) = \'ECSU\' OR upper(partner_name) = \'GRAD\'';
$queryInfo =& new mySqlQuery($query, $dbconnection);
$result =& $queryInfo->getResult();

for ($i = 0; $i < sizeof($result); $i++) {
switch ($result[$i]->name) {
case 'ECSU':
$ecsuID = $result[$i]->partner_id;
break;
case 'GRAD':
$gradID = $result[$i]->partner_id;
break;
default:
// DO NOTHING
break;
}
}

// CHECK FOR ALL THREE REQUIRED CHECKBOXES TO MAP TO
intern_complete_application TO BE MARKED 'Y'
foreach(array('hasRefLetter1', 'hasRefLetter2', 'hasTranscript') as $key =>
$val) {
if (strcmp(strtolower($$val), 'y') != 0) $isCompleted = 0;
}

/*--------------------------------------------------------------------------
--------------------------------
If they are an ECSU permitted intern and their required 1-page essay is not
yet marked 'checked'
in intern_complete_application they are not yet completed
----------------------------------------------------------------------------
-------------------------------*/
if ($isCompleted) {
$query = 'SELECT allowed FROM permissions WHERE intern_id = \'' . $internID
. '\' ' .
' AND partner_id = \'' . $ecsuID . '\'';
$queryInfo =& new mySqlQuery($query, $dbconnection);
$result =& $queryInfo->getResult();
if (strcmp(strtolower($hasEcsuEssay), 'y') != 0 &&
get_object_vars($result[0])) {
if ($result[0]->allowed) $isCompleted = 0;
}
}
//---END OF ECSU REQUIREMENT
BLOCK-----------------------------------------------------------------------
---


/*--------------------------------------------------------------------------
--------------------------------
If they are a grad student (be sure to check in grads table as well as
permissions through LEFT JOIN
statements) and they have not submitted their proof-of-enrollment form,
that mark in intern_complete_
application will also be not marked 'Y' and thus they are not yet completed
----------------------------------------------------------------------------
-------------------------------*/
if ($isCompleted) {
$query = 'SELECT id FROM interns WHERE id = \'' . $internID . '\' ' .
' AND enrollment_year IN ' . $this->graduateCompletionFieldsSQL;
$queryInfo =& new mySqlQuery($query, $dbconnection);
$result =& $queryInfo->getResult();
if (strcmp(strtolower($hasGradEnrollment), 'y') != 0 &&
get_object_vars($result[0])) $isCompleted = 0;
}
//---END OF GRAD REQUIREMENT
BLOCK-----------------------------------------------------------------------
---

$db->close();
$result = null;
$queryInfo = null;
return $isCompleted;
}

I'm sorry to ask so many questions, guys. It's evident I am not a good
coder.

Phil

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .