experience40 Posted February 1 Share Posted February 1 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted February 1 Share Posted February 1 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 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. 1 1 Quote Link to comment Share on other sites More sharing options...
experience40 Posted February 2 Author Share Posted February 2 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 2 Share Posted February 2 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. Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 13 Share Posted February 13 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. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.