Failing_Solutions

This question is only for my self education. Even if there are no good answers I hope my findings help others. I wrote a query that was designed to find all parts in a particular location, the webpage simply requested the user to give the location then the query would find all parts in that location it looked like this. SELECT p.part_number,t.transaction_id, t.received_id, t.quantity, l.location, g.group_id FROM transactions AS t JOIN operators AS o ON o.operator_id = t.operator_id JOIN parts AS p ON p.part_id = t.part_id JOIN actions AS a ON a.action_id = t.action_id JOIN locations AS l ON l.location_id = t.location_id JOIN received AS r ON r.received_id = t.received_id JOIN group_family AS g ON g.received_id = r.received_id WHERE l.location= '$loc' AND t.action_id IN('1','3','5','8','10','12','14','15','17') AND l.consumption='Storage' AND t.quantity > 0 ORDER BY p.part_number, r.received_id This query works, but took a very long time to execute, an annoying long time if the location had many parts / records. The time it took for this query to execute seemed way off to me so I started tinkering. I decided to change the WHERE l.location to where l.location_id = so it looked like this SELECT p.part_number,t.transaction_id, t.received_id, t.quantity, l.location, g.group_id FROM transactions AS t JOIN operators AS o ON o.operator_id = t.operator_id JOIN parts AS p ON p.part_id = t.part_id JOIN actions AS a ON a.action_id = t.action_id JOIN locations AS l ON l.location_id = t.location_id JOIN received AS r ON r.received_id = t.received_id JOIN group_family AS g ON g.received_id = r.received_id WHERE l.location_id= '$loc_id' AND t.action_id IN('1','3','5','8','10','12','14','15','17') AND l.consumption='Storage' AND t.quantity > 0 ORDER BY p.part_number, r.received_id And I was simply amazed in the difference, example; 1st query could take 50 seconds, second query only 2 seconds. The only difference is that instead of using the locations table "location" column, I am using the locations table location_id column which is the primary key. I'm wondering why this saves so much execution time? I had always been under the impression that you should try to limit the webpage to as few queries as possible, however using this method I have to take the users inputted location, and query the location_id. In the end, I am running 2 queries one to get the location id, then one to get the results, and yet I'm this is 1000% faster then the original query. Any thoughts, or input is appreciated. Thank you

Thank you, don't consider it rude at all. If I have questions on something that seems odd, I just ask the person who told me. I'm a great pupil that way Thank you for your time, and your follow up response, the code was very helpful and I now fully understand exactly what is going on.

Hi Psycho, The query executed perfectly. I do have 1 question. If I were to run into in issue where the table would be updated how does the sql actually accomplish that? $sql = "INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = VALUES(`facts_qty_on_hand`)"; This seems ilogical to me we don't want to set teh facts_qty_on_hand to facts_qty_on_hand do we? Is that saying skip this field? Little confused? Thank you

@boompa / litebearer So I was echoing the sql statement what I ended up with is a multi-insert statement as mac_gyver pointed out like.. @mac_gyver 2 things 1st Absolutely love your Avatar img! SG-1 is my fav. 2cd The issue then must be as you pointed out that I'm trying to run mutliple queries in one shot. I am to understand looping through them is not correct, I'll have to think on how to handle it now. Thank you all for the input!

Think I'm missing something here. I can print/echo the $sql variable and paste it into phpMyadmin and it works just fine, but for some reason it will not work correctly here. Anybody see anything off? Thank you <?php ///Write Text File To Local Database //declare variables $i='1'; $handle= fopen('inventory.txt','r'); $sql=''; $find='"'; $replace=''; $first=''; $second=''; ///read file while(!feof($handle)) { $gets = fgets($handle,4096); list($first,$second)=explode(',',$gets); //create sql $sql.="INSERT INTO facts_inventory (`part_number`,`facts_qty_on_hand`) VALUES ('".trim(str_replace($find,$replace,$first))."','".trim(str_replace($find,$replace,$second))."') ON DUPLICATE KEY UPDATE `facts_qty_on_hand` = '".trim(str_replace($find,$replace,$second))."';"; $i++; } if(mysql_query($sql)) { ///it Worked echo 'Updated '.$i.' fields.'; } else { //Query Failed echo "<br>Failed at executing sql query. "; } ?>

Are you sure you are referencing the right databases http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage

kicken gave the explanation. Selecing * grabs all the columns. In small applications this may have no consquences however in larger more complex applications you should try to limit your use to gettting exactly what you need, when you need it.

Using * will get every column. If you are trying to limit overhead then you would write out each colum you need. Example Select user_id, user_name from members...

Yep that was it. Thanks for all the help, I'm still digesting your coding style (PHP) I'm self taught and it looks you're using some very shorthand methods, which would be great if I understood them. But I have to disect it line by line and lookup as I go, either way, it is a very good learning experience and very much appreicated. Thanks, Don

Thank you, I just re-read all your responses. I also want to comment specifically on the time you spent creating a sample page for me. That was very very nice of you. I just put code into a page locally and can really appreciate the fact you used my table names and variables. (I had to convert mysqlite to mysql no biggy, I also realize that mysql support will be deprecated) . Only thing I'm not liking is that the query is getting every user, where the ideally I would only get workers who have actually made that part. I'm fiiddling with it now. Thank you, Don

True, Whats the difference in a "average of sums" and "average of averages"? mathmatically speaking User A values... value 1: 300 parts in 480 mins value 2: 320 parts in 480 mins value 3: 305 parts in 450 hours Avg of Sums SUM(parts) = 925 SUM(hours/60) = 15.42 hours Parts Per Hour = 925/23.5 = 39.36 AVG of AVG value 1 avg is 300/(480/60) = 37.5 value 2 avg is 320/(480/60) = 40.0 value 3 avg is 305/(450/60) = 40.67 Parts Per Hour = Average of (37.5+40.0+40.67)/3 = 39.39 giving up the decimal rounding I did the results are the same or am I missing something??

Hi Barand as you accurately pointed out my solution wasn't working. The problem was with where I was calling the AVG() function with conjuction to the IF statment. I should have changed it, however I do appreciate your continued help even after I had marked it solved. I WAS USING INCORRECTLY using: ROUND(AVG(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1', CORRECT: IF(Week(production_date)=1, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week1, I ended up with this type of query... SELECT IFNULL(Worker,'Totals') Worker, Week1, Week2, Week3, Week4, Week5, Week6, Week7, Week8, Week9, Week10, Week11, `Week12`, quantity AS Count, ttl as 'Avg' FROM ( SELECT w.worker_name as Worker, IF(Week(production_date)=1, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week1, IF(Week(production_date)=2, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week2, IF(Week(production_date)=3, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week3, IF(Week(production_date)=4, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week4, IF(Week(production_date)=5, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week5, IF(Week(production_date)=6, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week6, IF(Week(production_date)=7, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week7, IF(Week(production_date)=8, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week8, IF(Week(production_date)=9, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week9, IF(Week(production_date)=10, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week10, IF(Week(production_date)=11, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week11, IF(Week(production_date)=12, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week12, COUNT(*) As quantity, ROUND(AVG(production_net/(production_time/60)), 2) AS ttl FROM production p JOIN workers w USING(worker_id) WHERE product_id='1321' GROUP BY w.worker_name WITH ROLLUP ) as poo I'm using PHP to build the query based on user date inputs. Many Many Thanks, Don

Hi Hann You said you're using a loop, therefore you must be using PHP or something to process the request. As such you can not overide the php.ini setting with php calls like set_time_limit(300) as this only at script level. But you can try to edit the php.ini setting (max_execution_time) with php this may work because in php max_execution_time is a set anywhere "changeable mode value" . <?php ini_set('max_execution_time','300'); //default is 30 (30 secs); ?> Not sure if it will work, but can't hurt to try. In general people try to offer advice even when they don't understand the question. While Jessica gave you sound advice your question wasn't how is the best way to handle X problem. It was specifically asking how to edit php time limits. This is a valid question for many scenarios. While it is not normal to save images as blobs() it can be done and in some cases makes perfect sense.

In the end I figured it out I needed to replace ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' With ROUND(AVG(production_net/(production_time/60)),2) As 'Week 1', By introducing the if statement with the averages I was getting incorrect results because I was counting records that should not have been counted to figure the average.

Hi Barand I also was looking at your baagrid link in your sig it seemed to have some pivot table functionality which I thought maybe handy for me to look at. Noticed it didn't have a sample database so found it hard to thoroughly look at. Thanks, Don