Code Query Optimize Help

Anon

Hello:
Hopefully someone has some insite on how I can optimize the query in the following code.
I have a list of checkboxes on an html form that is dynamic, each of them is defined as such:

Below is the code on submitting of the form:

if ($approve) {

$file_num_rows = count($files);

for ($i=0; $i < $file_num_rows; $i++) {

//Select file if it has been flagged in the database.
$sql = "SELECT file_id FROM acct_app WHERE file_id='$files[$i]'";
$result = @mysql($sql);
$app = @mysql_result($result, 0, "file_id");

//Prepare the query.
$app_files = "file_id=$files[$i]";
$file_app_id = $file_app_id . $app_files . " OR ";
$file_app_id = substr($file_app_id, 0, -3);

//Insert flag if the file hasn't been flagged.
if ($g_app == "acct" && $files[$i] != $app) {
$sql = "INSERT INTO acct_app (file_id, acct) VALUES('$files[$i]','$acct_name')";
$result = @mysql($sql);
}
}
}

It seems to me that the SELECT query isn't very efficient because it is in the FOR loop. So if I had 20 checkboxes checked I would have the run the SELECT query through each checkbox checked totaling 20. I'm worried that if the database were to get large this would take some time to run. I don't know the best way to re-write this so the SELECT query is outside the FOR loop. Does anyone have any insite on how to optimize this better or if I am going about this in the right approach?

Thanks,
Troy

Anon

Well I have a few ideas, the fist is to include all the values in one query using "item=value1 or item=value2"

So you'd do something like this

$firstrun=TRUE;

for($i=0;$i<count($files);$i++)
{
if(!firstrun)
$query = $query." or file_id='$files[$i]'";
else
{

$query = $query."file_id='$files[$i]'";
$firstrun=FALSE;
}
}

$sql = "SELECT file_id FROM acct_app WHERE $query";
$result = @mysql($sql);
$app = @mysql_result($result, 0, "file_id");

As long as the list doesn't get too long you should see quite an improvement in the access speed. Personaly I'd modify the second database so that all the files would be saved into a single text block with all the file names separated by ';' or some other symbol, this is quite easy to extract the original strings into a database using the explode() function:

$string = "filename1;filename2;filename3";
$filenames = explode(";",$string);

Now all you have to do to get the filenames out of the array $filenames is go through them one by one.

for($i=0;$i<count($filenames);$i++)
{
echo $filenames[$i]."<br>";
}

This would print up all on a diferent line.

So this help ya?

Anon

Thanks for your response:

I tried using your first method that you recommended earlier creating one SQL query and was able to do that successfully. I would prefer to use this method, the only reason that I didn't is I couldn't figure out how to use that query result with the INSERT statement later in the code. Let me see if I can clarify this a bit. Here is what my current code looks like:

if ($approve) {

$file_num_rows = count($files);

for ($i=0; $i < $file_num_rows; $i++) {

//Select file if it has been flagged in the database.
$sql = "SELECT file_id FROM acct_app WHERE file_id='$files[$i]'";
$result = @mysql($sql);
$app = @mysql_result($result, 0, "file_id");

Notice my last if statement. In the earlier SELECT query the value of the query is placed in $app. So $app lets say equals 2(which tells me that file_id 2 is already in the database so don't add it again).
Then the if statement checks for $g_app (a session variable) and if the $file[$i] value is equal to $app. If it does it doesn't perform the INSERT.
How can I then use to some effect your first example to loop through the SELECT array and perform an INSERT like the results I am getting with the current code results?

So basically I am trying to achieve the idea that if the value exists in the database from the SELECT query don't perform an INSERT because file_id already exists in the database.

Thanks,
Troy

Anon

Hmmm well to tell ya the truth I highly suggest changing your storage methood. With the individualized way your storing the items to the database it's very hard to do a combined query like in the first example, but if you were to modfiy your storage methood a little then you would really simplify the process...

If I knew more about what you were using this for then I could probably help more, but it's kinda dificult to recode something just from a small example of code lacking the big picture.