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 1 2747
[ not crossposted to alt.php ]
Phil Powell wrote:
[snip] 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'
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Benjamin Dickgießer |
last post by:
Hi, I want to create a sql query but don't know if this is possible with
mysql.
The Query should do the following:
Select all db entries from table in which entry a is smaller than the number
(count) of all db entries with criteria x from another table.
Thx for your help!
Benjamin Dickgießer
|
by: Gordon |
last post by:
I have 2 tables t and t1. In this case, t1 is a copy of t. I want to
delete rows from t1 based on criteria on the t table and a relationship
between t ad t1 (in this case the id column). In the results below, I
would think that the delete should have deleted row 1 {1 5 me) and not
row 3 (1 5 they) when I run this statement
delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me';
Any ideas on why row 2 is deleted?
|
by: harm |
last post by:
Hi all,
I think there is a bug in mysql 4:
select week("2003-08-15") from po.orders limit 1;
produces: 32
after:
|
by: w_curtis |
last post by:
I'm an Access user, and I'm trying to learn MySQL and then PHP so I can make
some web databases. But it just isn't clicking.
I've followed some tutorials, and picked up a book, but just getting to
square one has been a pain. I can follow the tutorials and get to the point
where I can make tables and stuff, but I don't know how I got there, or what
to do if something changes that makes it different than the tutorial.
MySQL doesn't seem...
|
by: Ridge Burner |
last post by:
Can someone tell me which of these 2 SQL queries will be more efficient? I'm
having a debate with another guy about which would be less resource
intensive for MySQL.
The first uses MySQL to pick a random row in a single statement:
<?php
$sql = "SELECT FROM myTable ORDER BY RAND() LIMIT 1";
$query = mysql_query($sql,$conn);
?>
| |
by: basestring |
last post by:
Hi I am busy now for many hours without luck
I have a database and when I use PHP to add date to it, it works only one time
when i want to add the next data, It doesn't work. but i don't get any errors???
see my code:
$query = "INSERT INTO products VALUES ('$typedata','$pro_code_ourdata','$pronum','$proname','$prodetails','$facnum','$picturedata','$price','$probox','$proquantity')";
mysql_query($query);
|
by: =?iso-8859-1?B?Sm/jbyBNb3JhaXM=?= |
last post by:
Hi there guys,
My doubt is related to PHP and MySQL usage, and it's related to check
if performed queries are performed with success, since we know that:
Each time we perform an sql query, we can check if it was performed
with success like this:
|
by: The Natural Philosopher |
last post by:
This is so weird.
What I am trying to do is to upload files and stuff them in a mysql
database.
Everything works except the file content is zero.
using the load_file command from mysql command line as 'root' works and
i can download the inserted file ok.
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get through this without much trouble. Programming knowledge is not required.
Index
What is SQL?
Why MySQL?
Installing MySQL.
Using the MySQL command line interface
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |