Help with a query?

General PHP Help
#1

Here is the original query that I have to change:

$qualified_query = “SELECT * FROM contacts AS c INNER JOIN skills as s ON s.skill_id=c.skill_id “.$crit.” AND s.type=‘0’ AND c.agent_time >= ‘60’”;
$qualified_contacts = $db->query($qualified_query);
$qualified_contacts_rows = $qualified_contacts->num_rows;

We are having to remove this part: c.agent_time >= ‘60’. Now, we have a column in my skills table called “threshold” and we need to find if it is <= against either the agent_time or the total_time columns in the contacts table. I have added a column to the skills table called time_class (couldn’t figure anything better to call it) which has a 0 that needs to point it to c.agent_time 1 for c.total_time.

Can someone help me come up with a query for this?

#2

I’ll take a stab in the dark.

[php]$qualified_query = “SELECT * FROM contacts AS c INNER JOIN skills as s ON s.skill_id=c.skill_id “.$crit.” AND s.type=‘0’ AND (s.threshold <= c.agent_time or s.threshold <= c.total_time)”;[/php]

I don’t follow you on this statement

I have added a column to the skills table called time_class (couldn't figure anything better to call it) which has a 0 that needs to point it to c.agent_time 1 for c.total_time.
#3

Okay - in the contacts table, there are two columns - one is agent_time the other is total_time. Two of the skill_ids have to refer to the total_time, and the rest refer to the agent time. so, I made the new column in the skills table that I called time_class, and I put a 0 wiht the skill_ids that go to agent_time and a 1 for the one ones that go to total_time (this is a CRM for a call center if that helps put this into context).

So, what I need is to see if the threshold value (it is either 30, 60 or 135 depending on the skill_id) is <= whichever time_class the skill_id requires.

Does that make anymore sense?

Rachel

#4

Well, I know I’m on the right path, because this is what I had before your reply:

$qualified_query = “SELECT * FROM contacts as c INNER JOIN skills as s ON s.skill_id = c.skill_id “.$crit.” WHERE s.threshold <= c.agent_time ORDER BY s.skill_id”;

So, now I guess I just need to figure out the c.agent_time or c.total_time.

Rachel

#5

Something like this?

[php]$qualified_query = “SELECT * FROM contacts AS c INNER JOIN skills as s ON s.skill_id=c.skill_id “.$crit.” AND s.type=‘0’ AND (time_class = 0 and s.threshold <= c.agent_time) or (time_class = 1 and s.threshold <= c.total_time)”;
[/php]

#6

Worth a try. :slight_smile: Entering it into navicat now to test it.

#7

I get an error. :frowning:

Rachel

#8

A friend suggested putting an if/ifelse statement inside a foreach loop. So this is what I’ve come up with so far… but I"m still lost. :’(

$qualified_query = "SELECT * FROM contacts as c INNER JOIN skills as s ON s.skill_id = c.skill_id “.$crit.”;
$qualified_contacts = $db->query($qualified_query);
$qc=0;
foreach($qualified_contacts as $qc) {
if($qc[‘time_class’] == 0) {

} elseif($qc['time_class'] == 1 {

}
}

#9

$qualified_query = "SELECT * FROM contacts as c INNER JOIN skills as s ON s.skill_id = c.skill_id “.$crit.”;
$qualified_contacts = $db->query($qualified_query);
$qc=0;
foreach($qualified_contacts as $qc) {
if($qc[‘time_class’] == 0) {
$qc[‘agent_time’] <= $qc[‘threshold’];
$qc++;
} elseif($qc[‘time_class’] == 1 {
$qc[‘total_time’] <= $qc[‘threshold’];
$qc++;
}
}

How does that look?

#10

Try this…

$qualified_query = “SELECT * FROM contacts AS c INNER JOIN skills as s ON s.skill_id=c.skill_id “.$crit.” AND s.type=‘0’ AND ((s.time_class = 0 and s.threshold <= c.agent_time) or (s.time_class = 1 and s.threshold <= c.total_time))”;

And tell me what error you get.

#11

Are you just trying to get a total count? Then you can just do that in the query…

Sponsor our Newsletter | Privacy Policy | Terms of Service