CODEWITHSUNDEEP
php
mike
mike

Reputation: 17

does any one see how i can make this more effiecient?

so my code works but its not very fast when i have a $id with a large amount of applicationID's and im afraid that that table will just grow and grow.

$x = '0';
$y = '0';
$query0 = "SELECT applicationID 
   FROM app
   WHERE schoolID='$id'";
$result0 = mysql_db_query($aidDB, $query0, $connection);
while ($r0 = mysql_fetch_array($result0)) {
    $query = "SELECT App_Last, App_First, applicationID
     FROM applicant
     WHERE (App_Last IS NOT NULL OR App_First IS NOT NULL) AND applicationID='" . $r0['0'] . "'"; //echo $query;
    $result = mysql_db_query($aidDB, $query, $connection);
    while ($r = mysql_fetch_array($result)) {
        //$temp_array[$x][0]=$r[2];//appid*/
        $query1 = "SELECT DISTINCT applicationID 
    FROM OrderTrack
    WHERE applicationID='" . $r['2'] . "'"; //echo $query1;
        $result1 = mysql_db_query($aidDB, $query1, $connection);
        while ($r1 = mysql_fetch_array($result1)) {
            $temp_array1[$y][0] = "<option name=appid value=\"" . $r1['0'] . "\">" . $r['1'] . "," . $r['0'] . "</option>";
            $temp_array[$x][0] = $r1[0]; //appid cause it to count the distinct amount on customer names may need to change to appid
            $x ++;
            $y ++;
        }
    }
}
}

Upvotes: 0

Views: 99

Answers (4)

smp7d
smp7d

Reputation: 5032

you need to do some joining....

try something like: Select DISTINCT applicationId from app INNER JOIN applicant ON applicant.applicationID=app.applicationID INNER JOIN OrderTrack ON applicant.applicationID=OrderTrack.applicationID WHERE App_Last IS NOT NULL OR App_First IS NOT NULL AND schoolID={id}

....and then loop your result set

WARNING: As I just whipped this up real quick, I didn't create the tables and test. Also, the naming conventions are a bit odd and I cant see your entire tables....but this should work unless i've made an egregious logic error.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

You can rewrite 3 queries into 1.
$query = "SELECT DISTINCT app.applicationID, a.App_Last, a.App_First
FROM app ap
INNER JOIN applicant a ON (a.applicationID = app.applicationID AND App_Last IS NOT NULL OR App_First IS NOT NULL)
INNER JOIN OrderTrack o ON (o.applicationID = a.applicationID)
WHERE ap.schoolID='".mysql_real_escape_string($id)."'
Now you need just 1 loop and 1 call to mysql_db_query.

Upvotes: 0

Gordon
Gordon

Reputation: 317147

Yes, you could remove the excess curly brace at the end. That would change the script from not running to running ;)

You should also consider using prepared statements to fix the SQL Injection attack vectors in your query. Also, you might be able to use a JOIN for fetching the data in one go instead of three.

Another suggestion would be to separate the HTML building and fetching from each other. That wouldnt make it more efficient, but more readable and maintainable.

Upvotes: 1

Jim
Jim

Reputation: 18863

Nested queries are never a good way to code. They are, as you found, are slow. If you can pull out the data in one query, which you should be able to, this will vastly improve your codes speed / efficiency.

An example, untested obviously. 


$query = "SELECT DISTINCT applicationID, App_Last, App_First 
FROM app a 
  JOIN applicant ap ON a.applicationID = ap.ApplicationID 
  JOIN OrderTrack ot ON a.applicationID = ot.applicationID 
WHERE (ap.App_Last IS NOT NULL OR ap.App_First IS NOT NULL) AND a.schoolID = '$id'";

This way you should only have 1 query for all of that, which means 1 loop, and 1 query pulled from the MySQL server. Hopefully that query works, if not trying and re-trying it should help it out.

Upvotes: 1

Related Questions