Jump to content

Use SQL Update Query Outside of Results Loop


Recommended Posts

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 by experience40
Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

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.

  • Like 1
  • Haha 1
Link to comment
Share on other sites

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 by experience40
Link to comment
Share on other sites

  • 2 weeks later...
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.

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.