0

I have a PHP script that loops through 2000+- records uisng a while loop. Within this while loop a postgres sql query has to be performed, unfortunately it can't be excluded from the while loop.

$sql = "(SELECT (timestamp) AS time FROM followups as f 
          JOIN campaigns as c ON c.id = f.campid 
           WHERE c.clientid = ".trim($clientid)." AND c.contractno = '".trim($c)."' AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))) )
            UNION ALL (SELECT (timestamp) AS time FROM followups as f WHERE (contractno ='".trim($c)."' 
             OR contractno LIKE '%".trim($c)."||".trim($clientid)."%' 
              OR contractno = '".trim($c)."||".trim($clientid)."') AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X')))     ) 
                UNION ALL (select (f.timestamp) AS time FROM followups as f 
                 JOIN campaigns as c on c.id = f.campid WHERE c.clientid = ".trim($clientid)." 
                  AND c.clientid in ( 
                   SELECT id FROM easy_mapping where id = ".trim($clientid).") AND (LOWER(person) IN (SELECT LOWER(userid) 
                    FROM users WHERE type IN('S','X')) OR LOWER(person) IN 
                     (SELECT LOWER(name) FROM users WHERE type IN('S','X'))))";
$result = pg_query($conn,$sql);

The query above is included in the while loop, the first few records perform very quickly and then the script starts to slow down, taking almost a day to complete the script. Is there a way to write the exact query above differently to gain the same results?

UPDATE:

Here's the complete loop

$dates = array();
$clientid = str_replace("\t", '', $clientid);
foreach ($contracts as $c) {
    $c = str_replace("\t", '', $c);
    $sql = "(SELECT MAX(timestamp) AS time FROM followups as f
    JOIN campaigns as c ON c.id = f.campid
    WHERE c.clientid = ".trim($clientid)." AND c.contractno = '".trim($c)."' AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))) )
    UNION ALL (SELECT MAX(timestamp) AS time FROM followups as f WHERE (contractno ='".trim($c)."'
    OR contractno LIKE '%".trim($c)."||".trim($clientid)."%'
    OR contractno = '".trim($c)."||".trim($clientid)."') AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X')))     )
    UNION ALL (select MAX(f.timestamp) AS time FROM followups as f
    JOIN campaigns as c on c.id = f.campid WHERE c.clientid = ".trim($clientid)."
    AND c.clientid in ( SELECT id FROM easy_mapping where id = ".trim($clientid).") AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))))";
    $result = pg_query($conn,$sql);
    if (pg_num_rows($result)>0) {
        while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
            if (empty($row['time'])) {
                continue;
            }
            $dates[] = $row['time'];
        }
    }
    pg_free_result($result);
}
if (empty($dates)) {
    return false;
} else {
    $max = max($dates);
    if (strtotime(date("Y-m-d")) < strtotime(date("Y-m-t"))) {
        $compdate = date("Y-m-01", strtotime("-1 month") );
    } else {
        $compdate = date("Y-m-01");
    }
    if (strtotime($compdate) > $max) {
        return false;
    } else {
        return true;
    }
}
unset($dates);
5
  • show the loop around it Commented Jul 7, 2013 at 10:06
  • @mnagel - I've updated the question, containing the loop around it Commented Jul 7, 2013 at 10:09
  • how big is the returned dataset on each query? 10 rows, 100 rows 1000 rows? Commented Jul 7, 2013 at 10:18
  • 1
    The query looks a bit weird and complicated. Could you provide an ERM? Commented Jul 7, 2013 at 10:55
  • @Toby - It's anything between 0 and 100 Commented Jul 7, 2013 at 11:03

1 Answer 1

1

The following is the result of what I can understand from your really junkish code.

$clientid = trim(str_replace("\t", '', $clientid));
$sql = "
select max(time)
from (
(
    select max(timestamp) as time
    from
        followups f
        inner join
        campaigns c on c.id = f.campid
        inner join
        users u on lower(f.person) in (lower(u.userid), lower(u.name)) 
    where
        c.clientid = $clientid
        and u.type in('S','X')
)
union
(
    select max(timestamp) as time
    from
        followups as f
        inner join
        users u on lower(f.person) in (lower(u.userid), lower(u.name)) 
    where
        contractno like ('%' || $clientid || '%')
        and u.type in('S','X')
)
union
(
    select max(f.timestamp) as time
    from
        followups as f
        join
        campaigns as c on c.id = f.campid
        inner join
        users u on lower(f.person) in (lower(u.userid), lower(u.name))
        inner join
        easy_mapping em on c.clientid = em.id
    where
        c.clientid = $clientid
        and u.type in('S','X')
)) s
";
$result = pg_query($conn,$sql);
if (pg_num_rows($result) == 0) {
    return false;
} else {
    $max = $row['time'];
    if (strtotime(date("Y-m-d")) < strtotime(date("Y-m-t"))) {
        $compdate = date("Y-m-01", strtotime("-1 month") );
    } else {
        $compdate = date("Y-m-01");
    }
    if (strtotime($compdate) > $max) {
        return false;
    } else {
        return true;
    }
}
pg_free_result($result);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.