Barand

I wanted to create a single row for each CodicePz,DataRichiesta combination, hence the group by. But to get the individual values into 1 row I had to use an aggregation function (eg MIN) other wise it would only take the values from the first row of each group. The IFs were to put each value in its own column.

The backticks specify it as a column name but in this instance it is string value and needs to be in single quotes IE MIN(IF(DescrPrestaz='Acido Urico',risultato,NULL)) as `Acido Urico` Don't throw your normalized table away, keep updating that one as it is far more versatile. Suggest you recreate the summary table and run this query overnight as as cron job. Use the summary table purely for speeding up the web queries.

Have you specified id_tempo NOT NULL in the table defiition?

JOIN to the subquery on product SELECT product_model_id , model_code , GROUP_CONCAT(quantity, ':', total_ordered) AS qty_orders FROM product_model LEFT JOIN ( SELECT product_model_id , quantity , COUNT(op_id) AS total_ordered FROM order_product GROUP BY product_model_id,quantity ) AS sq USING (product_model_id) GROUP BY product_model_id;

Sounds like missing quote marks. What is the query you are running?

Oops!. Not usually using that method, I prefer $mysqli->query()

I generated a test table with 2,550,000 records and used two different methods to produce the output Using PHP to match dates/user and format output Using MySQL to match dates/user and format output Both the above methods took around 34 seconds to process the output. I then ran a query to create a summary table as in your table2. INSERT INTO result_summary (usercode,accessdate, a1,a2,a3) SELECT usercode , accessdate , MIN(IF(analysisname='a1',analysisresult,NULL)) as a1 , MIN(IF(analysisname='a2',analysisresult,NULL)) as a2 , MIN(IF(analysisname='a3',analysisresult,NULL)) as a3 FROM result GROUP BY usercode , accessdate This took around 100 seconds but then querying the summary table (method 3 below) only took about 0.25 seconds. All three gave the same results. <?php $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); /************************************************************* * METHOD 1 * * USE PHP QUERY TO MATCH * AND SELECT REQUIRED VALUES * AND FORMAT THE OUTPUT **************************************************************/ $sql = "SELECT usercode , accessdate , analysisname , analysisresult FROM result WHERE ((analysisname = 'a1' AND analysisresult > 10) OR (analysisname = 'a2' AND analysisresult > 13) OR (analysisname = 'a3' AND analysisresult > 19)) ORDER BY usercode, accessdate"; $res = $db->query($sql); $empty_results = ['a1'=>'0', 'a2'=>0, 'a3'=>0]; $output1 = ''; $currdate = $curruser = ''; $results = $empty_results; while (list($user, $date, $anal, $val) = $res->fetch_row()) { if (($currdate != $date)||($curruser != $user)) { if ($currdate && $results['a1'] && $results['a2'] && $results['a3']) { $output1 .= "<tr><td>$curruser</td><td>$currdate</td><td>"; $output1 .= join('</td><td>', $results) . "</td></tr>\n"; } $currdate = $date; $curruser = $user; $results = $empty_results; } $results[$anal] = $val; // store value in row } // don't forget the last row if ($currdate && $results['a1'] && $results['a2'] && $results['a3']) { $output1 .= "<tr><td>$curruser</td><td>$currdate</td><td>"; $output1 .= join('</td><td>', $results) . "</td></tr>\n"; } /************************************************************* * METHOD 2 * * USE MYSQL QUERY TO MATCH * AND SELECT REQUIRED VALUES * AND FORMAT THE OUTPUT **************************************************************/ $sql = "SELECT usercode , accessdate , MIN(IF(analysisname='a1',analysisresult,NULL)) as an1 , MIN(IF(analysisname='a2',analysisresult,NULL)) as an2 , MIN(IF(analysisname='a3',analysisresult,NULL)) as an3 FROM ( SELECT usercode , accessdate , analysisname , analysisresult FROM result WHERE (analysisname = 'a1' AND analysisresult > 10) OR (analysisname = 'a2' AND analysisresult > 13) OR (analysisname = 'a3' AND analysisresult > 19) ) x GROUP BY usercode , accessdate HAVING an1 IS NOT NULL and an2 IS NOT NULL AND an3 IS NOT NULL"; $res = $db->query($sql); $output2 = ''; while (list($user, $date, $a1, $a2, $a3) = $res->fetch_row()) { $output2 .= "<tr><td>$user</td><td>$date</td><td>$a1</td><td>$a2</td><td>$a3</td></tr>\n"; } /************************************************************* * METHOD 3 * * USE MYSQL QUERY TO CREATE * SUMMARY TABLE IN REQUIRED * FORMAT AND QUERY THE SUMMARY * TABLE FOR REQUIRED VALUES **************************************************************/ $t1 = microtime(1); $sql = "SELECT usercode , accessdate , a1 , a2 , a3 FROM result_summary WHERE a1 > 10 AND a2 > 13 AND a3 > 19 ORDER BY usercode, accessdate"; $res = $db->query($sql); $output3 = ''; while (list($user, $date, $a1, $a2, $a3) = $res->fetch_row()) { $output3 .= "<tr><td>$user</td><td>$date</td><td>$a1</td><td>$a2</td><td>$a3</td></tr>\n"; } $t2 = microtime(1); ?> <!DOCTYPE=html> <html> <head> <title>Example</title> </head> <body> <table border='1' style="border-collapse: collapse;"> <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr> <?=$output1?> </table> <br> <hr> <table border='1' style="border-collapse: collapse;"> <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr> <?=$output2?> </table> <table border='1' style="border-collapse: collapse;"> <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr> <?=$output3?> </table> <br> </body> </html>

I agree with ch0cu3r. The data in tabe1 is correctly normalized and you should keep it like that. If you want to display the data in the format of table2, do it on output after querying table1.

Use DateTime class. $startTime = '13:30:00'; $endTime = '14:00:00'; $dt1 = new DateTime($startTime); $dt2 = new DateTime($endTime); $diff = $dt1->diff($dt2); echo $diff->format('%h:%I'); //--> 0:30

Both I and gizmola have given you the correct syntax. What's your problem?

$summary = substr(strip_tags($text),0, 200); echo $summary; maybe?

Brought on by storing the markup with the data. Solution: Don't.

Your first challenge is to look at the data you need to store and the processes involved and design a relational database model that supports both.

If it's not correct, what happens? Do they try again until they get it right, so everyone finally finishes the test with 100% correct?

Have you forgotten the ".=" and just used "$results ="

You said that there was already a WHERE clause. Have you added extra conditions to gizmola's WHERE clause using AND? Can you post the actual query you are now running.

Not sure prepare and multiple insert syntax work together. Try $query = "INSERT IGNORE INTO blogTags (tag) VALUES (?)"; $stmt = $this->db_connect->prepare($query); foreach($this->blogTags as $tag){ $stmt->bindValue(1, $tag, PDO::PARAM_STR); print "param: $param<br /> tag: $tag<br />"; $stmt->execute(); }

That returns the value then increments (post-increment). Either use ++$param or start with a value of 1 instead of zero.

to round x to the nearest multiple of y result = round(x/y) * y (eg round(563/25) * 25 = 575) If you want to round down as in your example, use floor() instead of round()

As Gizmola said in his reply UPDATE product p INNER JOIN ( SELECT product_id, SUM(price) as tot -- you FROM -- would ( -- need SELECT product_id, price FROM product_a -- these UNION ALL -- eight SELECT product_id, price FROM product_b -- lines ) ab -- of the GROUP BY product_id -- query ) tots ON p.id = tots.product_id SET p.total = p.total + tots.tot;

Yes. If $result is the result from your query execution, then something like $row = $result->fetch_assoc(); echo $row['total']; // print the total

A query can find no records without failing, it's a perfectly normal result. You need to check the number of rows in the results.

Instead of output buffering, use pagination with mysql LIMIT clause. http://lmgtfy.com/?q=mysql+pagination

The query string needs to be enclosed in double quotes, not single, if you want to expand variable values in the string. Table and column names should not be in quotes $insert = "INSERT INTO XXXXXXXXXX (ID, FirstName, LastName, City, State) VALUES (NULL,'$FirstName','$LastName','$City','$State')";

But, should you want to update regardless, assuming you add a "total" column to the product table UPDATE product p INNER JOIN ( SELECT product_id, SUM(price) as tot FROM ( SELECT product_id, price FROM product_a UNION ALL SELECT product_id, price FROM product_b ) ab GROUP BY product_id ) tots ON p.id = tots.product_id SET p.total = p.total + tots.tot; If you combine the a and b tables as suggested then UPDATE product p LEFT JOIN ( SELECT product_id, SUM(price) as tot FROM product_ GROUP BY product_id ) tots ON p.id = tots.product_id SET p.total = p.total + tots.tot;