mysql select query with unusual criteria fails

[Phil Powell]

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_es say = 'Y') OR
(ica.has_ecsu_e ssay != '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_ye ar,
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_e csu_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_r ef_letter_1) = 'Y'
AND upper(ica.has_r ef_letter_2) = 'Y'
AND upper(ica.has_t ranscript) = '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 isCompletedInte rn($internID) { // BOOLEAN "METHOD"
global $dbHost,$dbPort ,$dbUser,$dbPwd ,$dbName;

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

$isCompleted = 1;

$query = 'SELECT * FROM intern_complete _application WHERE intern_id
= \'' . $internID . '\'';
$queryInfo = new mySqlQuery($que ry, $dbconnection);
$result = $queryInfo->getResult();
if (get_object_var s($result[0])) {
foreach (array('has_ref _letter_1', 'has_ref_letter _2',
'has_transcript ', 'has_grad_enrol lment', 'has_ecsu_essay ') as $key =>
$val) {
$arrayKeyName = $this->dbNameToArrayN ame($val);
${$arrayKeyName } = $result[0]->$val;
}
$result = null;
}

$query = 'SELECT partner_id, upper(partner_n ame) as name FROM
partners ' .
'WHERE upper(partner_n ame) = \'ECSU\' OR upper(partner_n ame) =
\'GRAD\'';
$queryInfo =& new mySqlQuery($que ry, $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(strtolo wer($$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($que ry, $dbconnection);
$result =& $queryInfo->getResult();
if (strcmp(strtolo wer($hasEcsuEss ay), '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->graduateComple tionFieldsSQL;
$queryInfo =& new mySqlQuery($que ry, $dbconnection);
$result =& $queryInfo->getResult();
if (strcmp(strtolo wer($hasGradEnr ollment), '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

[Pedro]

Re: mysql select query with unusual criteria fails

[ not crossposted to alt.php ]

Phil Powell wrote:
[snip][color=blue]
> SELECT distinct i.enrollment_ye ar,
> 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_e csu_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_r ef_letter_1) = 'Y'
> AND upper(ica.has_r ef_letter_2) = 'Y'
> AND upper(ica.has_t ranscript) = 'Y'[/color]

select if(<cond>, true, false) as whatever from wherever
works for me

select if(<cond>), true, false as whatever from wherever
doesn't


try:

SELECT ... IF (..._essay) = 'Y', '1', '0') AS allowed, ...



HTH

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.