Hi,
maybe some improvements:
1) first of all check if you have indexes on the fields you use in your where clause. You can execute a
EXPLAIN <select_query>
to see if the mysql databases uses indexes at all. Depending on the MySQL version and the type of fields you might want to add indexes for at least city_num, cusername. That could speed things up a lot. Just use EXPLAIN and play around with the indexes if you already haven't done so.
You call
subDate(completedate, $todaydate);
instead of
subDate($completedate, $todaydate);
A $ is missing.
To reduce the amount of code a bit you could remove the
else
{
$diff=0;
}
part and place a
$diff=0;
in front of the if statement.
But the biggest improvement could be the following.
Calculate the date adding 10 workdays from now on and use the result in the query, e.g.
modify your subDate function so that it returns the difference as an unix timestamp.
then change the select to
$strSQL = <<< EOSQL
SELECT * FROM tbl_Permit
WHERE city_num='$city_row[citynum]'
AND cusername = '$username'
AND (UNIX_TIMESTAMP(completedate) <= $diff OR status<>'Complete')
ORDER BY $sort
EOSQL;
$showlist =mysql_query($strSQL);
You can then remove the
if ($a_row[status] == "Complete")
{
$todaydate = date("Y-m-d");
$completedate = $a_row[completedate];
$diff = subDate(completedate, $todaydate);
}
else
{
$diff = 0;
}
if ($diff <= 10)
{
stuff and just output your content in the while loop.
You might want to add indexes to completedate and status.
I didn't check that query, there might be a syntax error in it 🙂
I don't know if this is faster than doing the whole stuff in php but if you have a really big database or if you database will constantly be growing it should be faster.
