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

Hi I'm trying to calculate the Parts Per Hour production_net/(production_time/60) where production time is saved in total minutes ((300) = 5 hours)) So when grouping by a time period like a week or month I need to average the average. For example, if I have 1 record then this formula is perfect.... production_net/(production_time/60) but if I have more then 1 record for then I need to modify the formula accordingly like production_net/(production_time/(60*X)) CREATE TABLE IF NOT EXISTS `production` ( `production_id` int(4) NOT NULL AUTO_INCREMENT, `product_id` int(4) NOT NULL, `production_date` date NOT NULL, `mix_date` date NOT NULL, `production_batch` varchar(15) NOT NULL, `ir_number` varchar(65) NOT NULL, `production_shift` int(4) NOT NULL, `worker_id` int(4) NOT NULL, `production_total` int(4) NOT NULL, `production_defects` int(4) NOT NULL, `production_net` int(4) NOT NULL, `cure_date` date NOT NULL, `production_time` varchar(12) NOT NULL, `location_id` int(4) NOT NULL, `mix_ir_number` varchar(70) NOT NULL, `compound_number` varchar(65) NOT NULL, `received_id` int(4) NOT NULL, `production_entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`production_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1399 ; CREATE TABLE IF NOT EXISTS `workers` ( `worker_id` int(4) NOT NULL AUTO_INCREMENT, `worker_name` varchar(60) NOT NULL, `worker_description` varchar(80) NOT NULL, `worker_active` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`worker_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ;

There are many ways to handle a string of words in php for example Search = London Steak Resturante <?php $string=$_POST['search']; $array=explode(' ',$string); // Explode on space $piece1=$array[0]; ///London $piece2=$array[1]; ///Steak $piece3=$array[3]; ///Resturante ?> Sometimes like in the case of a search you may not know how many pieces you'll have so then you would use loops like foreach($array as $value) { do something; } Searchs can get very complicated because the idea is to return the results that are the most appropriate. I've seen large Content Management Systems with wreched searches. Google on the other hand is probably the gold standard for getting the best search results. All that said it is possible to do this sort of thing

Not sure if I completely understood the question but here are my thoughts... Query = "London Restaurant" Use a pre processor like PHP to break it up and set it each section to a variable $piece1='London' $piece2='Restaurante' SELECT * FROM table WHERE city RLIKE '$piece1|$piece2' OR category RLIKE '$piece1|$piece2' OR name RLIKE '$piece1|$piece2' OR description RLIKE '$piece1|$piece2' Order by city, category, name, description Also there is the LIKE (%$piece1%) and city IN('london','restaurante','steak) operators

Hi I'm looking for a little direction trying to calculate averages with a pivot table type query I run a query like this: SELECT IFNULL(Worker, 'Totals') AS Operator, `Week 1` FROM ( SELECT w.worker_name as 'Worker', ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' FROM production JOIN workers AS w USING (worker_id) WHERE product_id='1321' GROUP BY worker_id ) AS sums And get this... Operator Week1 ABaits 0.00 DHarris 0.00 JAvalas 665.14 KIgner 0.00 MAhe 0.00 AReynolds 196.43 JWhitt 0.00 RAloney 422.97 AStorms 148.40 IGlesias 716.62 Which is only correct if in this time period (Week 1) the user has only 1 record. For example JAvalas Week 1 should read 665.15/4=166.29 (there are 4 records this week for him) But AReynolds (196.43) is actually correct because in this period he only had 1 record Since I'm using SUM everything is actually working correctly for what I'm asking, but I need to introduce a way to AVG() these groupd results. I've tried introuducing COUNT(*) at ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60*(COUNT(*)),0)),2) As 'Week 1' to modify the 60 divisor, I've tried using ROUND(AVG(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' instead of SUM() but end up with some very odd results. And I've tried to sum(production_net)/sum(production_time) but get an invalid use of group functions. I'm not sure what I can do to get the averages here. Any help or advise is always welcome, Thank you

Whelp It isn't easy, but by creating verticle array from the query results then looping through them you can get a table Here's my code...... $results.='<div><table border="0" class="sortable"><th>Operator</th>'; for($n=0;$n<$count_dates;$n++) { //create the date column headers in us english format $results.='<th>'.date('m-d-Y',strtotime($valid_date_array[$n])).'</th>'; } //echo $rollup_sql; //create the operator array $operators = array(); ///create all the verticle results arrays foreach($valid_date_array as $value) { $col[$value]=array(); } while($rollup_row=mysql_fetch_array($rollup_query)) { array_push($operators, $rollup_row['Operator']); //push values to the vertical arrays foreach($valid_date_array as $value) { array_push($col[$value],$rollup_row[$value]); } /* //verify vertical arrays foreach($valid_date_array as $value) { print_r($col[$value]); } okay! */ } $count_rows=count($operators); ///echo $count_rows . ' rows<br>'; //echo $count_dates+1 . ' columns<br>'; $i=0; //loop through all the veritical arrays and creat rows for($k=0;$k<$count_dates;$k++) { //set row color if ($i%2 !=0) { $rowColor = 'tr1'; } else { $rowColor ='tr2'; } //first we create a row with the operators name $results.='<tr class="'.$rowColor.'"><td>'.$operators[$k].'</td>'; //now loop through each date array foreach($valid_date_array as $value) { $results.='<td>'.$col[$value][$k].'</td>'; } //then end the row and let the loop continue to create the next $results.='</tr>'; } $results.='</table></div>';

I have a query that generates a mysql pivot table which is great, but now I can't figure out how to echo that out in php. The query is: SELECT IFNULL(Worker, 'Totals') AS Operator, sums.`2012-11-26`, sums.`2012-11-27`, sums.`2012-11-28` FROM ( SELECT w.worker_name as 'Worker', ROUND(SUM(IF(production_date='2012-11-26',production_net/(production_time/60),0)),2) As '2012-11-26', ROUND(SUM(IF(production_date='2012-11-27',production_net/(production_time/60),0)),2) As '2012-11-27', ROUND(SUM(IF(production_date='2012-11-28',production_net/(production_time/60),0)),2) As '2012-11-28' FROM production JOIN workers AS w USING (worker_id) WHERE product_id='1267' GROUP BY worker_id ) AS sums To fetch data I've generally used something like while($rollup_row=mysql_fetch_array($rollup_query)) { echo $rollup_row['Something']; } However that doesn't seem to a valid way to get through these results. I do have an array that holds the aliases to create this query but I'm not sure how to itterate through it to get a nice pretty table. Anybody have some advice, Thank you

Thank you Illusion / Barand completly missed that.

Hi Barand Thanks for the response, I had thought I tried all varations of the backticks and apstrophes, but to be sure I ran them again with this query... SELECT IFNULL(worker_id,'Totals') AS Operator, sums.`2012-11-26`, sums.`2012-11-27`, sums.`2012-11-28`, sums.`2012-11-26` + sums.`2012-11-27` + sums.`2012-11-28` AS Sums FROM ( SELECT worker_id, SUM(IF(production_date='2012-11-26',production_net,0)) As `11-26-2012`, SUM(IF(production_date='2012-11-27',production_net,0)) As `11-27-2012`, SUM(IF(production_date='2012-11-28',production_net,0)) As `11-28-2012` FROM production GROUP BY worker_id WITH ROLLUP ) AS sums And still receive this error...#1054 - Unknown column 'sums.2012-11-26' in 'field list' Whats more to further negate the issue with improperly encapsulated headers I decided to duplicate this table, and updated the date field to a varchar and remove the dashes so I end up with straight number values like this Then ran this query SELECT IFNULL(worker_id,'Totals') AS Operator, sums.20121126, sums.20121127, sums.20121128, sums.20121126 + sums.20121127 + sums.20121128 AS Sums FROM ( SELECT worker_id, SUM(IF(production_date=20121126,production_net,0)) As '11262012', SUM(IF(production_date=20121127,production_net,0)) As '11272012', SUM(IF(production_date=20121128,production_net,0)) As '11282012' FROM production GROUP BY worker_id WITH ROLLUP ) AS sums And got the same error #1054 - Unknown column 'sums.20121126' in 'field list' Any ideas ? attached is a the sql to create the table I am dealing with if that would help.. production.txt

Hello gang, I'm looking for help with my query SELECT IFNULL(worker_id,'Totals') AS Operator, -- outer query labels rollup row sums.2012-11-26, sums.2012-11-27, sums.2012-11-28, -- and calculates horizontal sums sums.2012-11-26 + sums.2012-11-27 + sums.2012-11-28 AS Sums FROM ( -- inner query groups by employee SELECT -- with an expression for each column worker_id, SUM(IF(production_date=2012-11-26,production_net,0)) As '11-26-2012', SUM(IF(production_date=2012-11-27,production_net,0)) As '11-27-2012', SUM(IF(production_date=2012-11-28,production_net,0)) As '11-28-2012' FROM production GROUP BY worker_id WITH ROLLUP ) AS sums This is my attempt to mimic the logic found here http://www.artfulsof...rytip.php?id=78 What I keep getting is errors like this "Unknown column 'sums.2012-11-26' in 'field list'" Which usually I assumed, meant I had not properly encapsulated the header names so I tried (sums.'2012-11-26' and 'sums.'2012-11-26' as well as variations with back ticks and no luck whatsoever. Any help is very much appreciated.. This is how the data is stored in the database id |production_date| production_net| worker_id -------------------------------------------------- 1 |2013-01-10 | 390 | MMahe -------------------------------------------------- 2 |2013-01-10 | 400 | RMaloney -------------------------------------------------- 3 |2013-01-11 | 460 | JBurris -------------------------------------------------- 4 |2013-01-11 | 210 | MMahe -------------------------------------------------- 5 |2013-01-14 | 285 | LTaylor -------------------------------------------------- I'm wanting the results to look something like this.. operator| 2013-01-10| 2013-01-11| 2013-01-14| Sums | ---------------------------------------------------- MMahe | 390 | 210 | | 600 | ---------------------------------------------------- RMaloney| 400 | | | 400 | ---------------------------------------------------- JBurris | | 460 | | 460 | ---------------------------------------------------- LTaylor | | | 285 | 285 | ---------------------------------------------------- Totals | 790 | 670 | 285 |1745 |

Thank you @Psycho & @kicken your advice helps. I forgot about array_chunk and that seems just what I need here, also your psycho is correct on the query overhead thank you both.