Use SQL Update Query Outside of Results Loop

[experience40]

Trying to make this faster, have tried using a results array but am struggling on how to do it so looking for advice to check if it is possible to use the 2nd query outside of the loop or not?

I have 2 SQL statements, the 1st query retrieves stock_id and brand, i want to use the results from this in the 2nd query

How can i use the result outside of the loop so i'm not making multiple update queries for each result row?

$company_id = 1;
$stmt = $conn->prepare("SELECT s.stock_id, s.csv_data AS brand FROM stock_" .$company_id ." s WHERE s.company_id = ? AND s.attribute_id = 5");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
        if($result != NULL){		
            while($row=$result->fetch_assoc()){
                $brand =$row['brand'];		
                $stock_id =$row['stock_id'];

                $stmt2 = $conn->prepare("UPDATE prices_" .$company_id ." p INNER JOIN price_rules pr ON p.company_id = pr.company_id INNER JOIN feeds f on f.id = p.feed_id SET p.brand_markup_percentage = (p.stock_price / 100) * pr.brand_markup_percentage WHERE pr.company_id = ? AND f.disable_price_rules = 0 AND pr.brand = ? AND p.stock_id = ? AND pr.brand_price_or_percent = 1 AND p.stock_price BETWEEN pr.min_price AND pr.max_price AND p.price_profile = ?");
                $stmt2->bind_param("isii", $company_id, $brand, $stock_id, $price_profile);
                $stmt2->execute();
        	}			
	}

 

Edited February 1 by experience40

[ginerjm]

Can you just describe the problem and what you are trying to do?  Better than us trying to figure out your code it would help if you described the task.

When someone tells us that they have 2 query results arrays the better answer is to change the query to only make one result.

Edited February 1 by ginerjm

[Barand]

All you are doing in the outer query is getting brand and stock_id then using those to determine the records that get updated.

Use a single query joining to the stock table

UPDATE prices_{$company_id} p 
       INNER JOIN price_rules pr ON p.company_id = pr.company_id 
       INNER JOIN feeds f ON f.id = p.feed_id
       INNER JOIN stock_{$company_id} s ON s.stock_id = p.stock_id
                                        AND s.csv_data = pr.brand
                                        AND s.company_id = p.company_id
                                        AND s.attribute_id = 5
SET p.brand_markup_percentage = (p.stock_price / 100) * pr.brand_markup_percentage
WHERE p.company_id = ? 
      AND f.disable_price_rules = 0 
      AND pr.brand_price_or_percent = 1 
      AND p.stock_price BETWEEN pr.min_price AND pr.max_price 
      AND p.price_profile = ?

Why do write your queries so that you have scroll right into the middle of next week to see what it's doing? You wouldn't write your other code on one line without linebreaks and indentations.

[experience40]

Thanks for the advice, very much appreciated 

I thought including the 2nd query in a loop was creating unneccesary calls to the database as the results from the 1st query could contain 100's of rows, leading to the 2nd query updating the database 100s of times so thought i was doing it wrong but it works 

I tried combining the 2 queries into 1 as you have advised which works, however my database doesnt like it as the update has gone from 7 seconds to 111 seconds so without indexing the easiest solution is to keep it as it is but thanks for the suggestion!

I've got into a bad habit of writing my queries on one row which has stuck with me as i've learnt the language, i'll have to break the habit!!

@Barand I noticed on the single query you suggested that you amended " .$company_id ." to {$company_id}, is {$company_id} more efficient or just the correct way to include it and also is it as safe to use to protect from injection?

Edited February 2 by experience40

[Barand]

Indexing is a prime requirement of relational databases.

Even if you aren't using formal foreign key constraints, the foreign keys should be indexed. So should other columns frequently used in searching.

[gizmola]

On 2/2/2024 at 3:23 AM, experience40 said:

 

@Barand I noticed on the single query you suggested that you amended " .$company_id ." to {$company_id}, is {$company_id} more efficient or just the correct way to include it and also is it as safe to use to protect from injection?

It's just an example of utilizing variable interpolation into a string.  In general it is easier to read and maintain code that doesn't have a bunch of unnecessary string concatenation, when you can just use interpolation as Barand did.