Barand

Now, you apparently want User_Id | UserName | Date | In | Leave Which?

My code decodes the json data as an array $data = json_decode($datajs, 1); // decode as an array

That's right.

ClientId would be in the history table to get totals for each client.

try $data = json_decode($datajs, 1); // decode as an array echo '<pre>',print_r($data, true),'</pre>'; $amountAll = 0; foreach ($data['products'] as $product) { foreach ($product['tax'] as $item) { $amountAll += $item['quantity'] * $item['price']; } } echo number_format($amountAll, 2);

If you want the quarters with no history, create a "quarter" table with a row for each quarter and LEFT JOIN to history table CREATE TABLE quarters ( qtr INT NOT NULL PRIMARY KEY ); INSERT INTO quarters (qtr) VALUES (1), (2), (3), (4); SELECT q.qtr , SUM(h.amount_paid) as total FROM quarters q LEFT JOIN history h ON q.qtr = QUARTER(h.last_payment) WHERE YEAR(h.last_payment) = YEAR(CURDATE()) GROUP BY qtr

Consider a trivial example to find male employees born between 1960 and 1979 and females born between 1980 and 1999 SELECT id , name , dob , gender FROM employee WHERE YEAR(dob) BETWEEN IF(gender = 'M', 1960, 1980) AND IF(gender = 'F', 1979, 1999) ORDER BY dob This should demonstrate the IF syntax. Also, you can't use "= NULL" or "!= NULL; the correct syntax is "IS NULL" or "IS NOT NULL" Of course, the above would normally be SELECT id , name , dob , gender FROM employee WHERE (gender = 'M' AND dob BETWEEN 1960 AND 1979) OR (gender = 'F' AND dob BETWEEN 1980 AND 1999) ORDER BY dob

$result will only be false if there is an error. Not finding a record is not an error. Finding a record with a blank profile_image is not an error You need to check was a record found? if yes, what is the value in profile image?

The table CREATE TABLE `date_sample` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dob` date NOT NULL, `name` varchar(25) DEFAULT NULL, PRIMARY KEY (`id`) ) The data mysql> SELECT * FROM date_sample; +----+------------+-------+ | id | dob | name | +----+------------+-------+ | 1 | 1945-09-11 | Peter | | 2 | 1949-01-22 | Fred | +----+------------+-------+ My output from the above code METHOD 1 Fred - 66 Peter - 69 METHOD 2 Fred - 66 Peter - 69

I was wondering if you were having the same problem as in this post http://forums.phpfreaks.com/topic/296012-php-sqlsrv-query-method-giving-strange-blank-results/?do=findComment&comment=1510557

It is an obscure one and one I have never used. I had a niggling feeling that I'd seen it in the function index so looked it up first just to check

Use the datetime functions SELECT SUM(balances) as total_balance FROM fiscalTable WHERE YEAR(date) = YEAR(CURDATE()) AND QUARTER(date) = 1 BTW, in your query the date values should be in single quotes and not backticks (backtickes are for column and table identifiers)

Assuming you now have your dates of birth (dob) as type date (yyyy-mm-dd) <?php $mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); //--------------------------------------------- // Method 1 // Calculate age using PHP //--------------------------------------------- function age($dob) { $dt = new DateTime($dob); return $dt->diff(new DateTime())->y; } $sql = "SELECT name , dob FROM bonacuora_clients ORDER BY name"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { echo $row['name'] . " - " . age($row['dob']) . '<br/>'; } //--------------------------------------------- // Method 2 // Calculate age using SQL //--------------------------------------------- $sql = "SELECT name , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age FROM bonacuora_clients ORDER BY name"; $result = $mysqli->query($sql); while ($row = $result->fetch_assoc()) { echo $row['name'] . " - " . $row['age'] . '<br/>'; } ?>

The first "if" test for new mastertot record so we can update with the array of values accumulated for the previous mastertot record. The second "if" is so we don't update on the first record when we have not yet accumulated any data

Instead of your error message, try outputting $mysql->error to see why it failed. You need to able to write NULL values to id_tempo so it should not be "NOT NULL" I ran your code on my table and all 17 were inserted. Those not in the $hora array had null values as expected.

Or perhaps something along these lines where all values in a record are updated in a single query instead of lots of individual queries for each value <?php $db= new mysqli($servername, $username, $password, $dbname); $sql = "SELECT DataRichiesta , CodicePz , DescrPrestaz , risultato FROM mastertot ORDER BY DataRichiesta , CodicePz"; $res = $db->query($sql); $results = []; $curdat = $curcode = ''; while (list($dat, $code, $descr, $risult) = $res->fetch_row()) { if (($curdat != $dat) || ($curcode != $code)) { if ($curdat) { updateMastertot2($db, $curdat, $curcode, $results); } $curdat = $dat; $curcode = $code; $results = []; } $results[$descr] = $risult; } updateMastertot2($db, $curdat, $curcode, $results); // // function to update all values in mastertot2 with a single query // function updateMastertot2($db, $curdat, $curcode, $results) { $sets = []; foreach($results as $desc=>$res) { $sets[] = "`$desc` = $res"; } $sql = "UPDATE mastertot2 SET \n" . join(",\n", $sets) . "\nWHERE DataRichiesta='$curdat' AND CodicePz='$curcode' "; $db->query($sql); } ?>

It's slow because you are hammering the server by running queries inside a loop. If the first query returns 10,000 rows then you are calling the queries inside the loop 10000 times. You should run a single query using JOINs and process that.

EG $dob = DateTime::createFromFormat('d.m.Y', '20.02.1989'); $now = new DateTime(); $age = $dob->diff($now)->y; echo $age; // 26 But, as Psycho said, you really should fix your stored date format. You can't even sort by date with your format.

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>