By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

mysql select query with unusual criteria fails

P: n/a
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,$dbNam e);
$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
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
[ not crossposted to alt.php ]

Phil Powell wrote:
[snip]
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'


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.
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.