Barand

Probably works because mysql is more relaxed than the standard dictates

Your query failed, so $result contains false and not a result object. Put this code before your call to nysql_connect... mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); ... it won't stop it failing but it will tell you why.

You group by whatever you want to aggregate by. If you want total monthly sales, say, then you'd have something like SELECT YEAR(sales_date) as year , MONTH(sales_date) as month , SUM(sales_amount) as total FROM sales GROUP BY year, month If you wanted customer totals SELECT customer_id , SUM(sales_amount) as total FROM sales GROUP BY customer_id Neither customer_id nor sales_date is the primary key of the sales table.

And what erroneous logic path leads you to that conclusion? As stated above - to prevent the inclusion of meaningless values in results. https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

An employee can have many orderid's. When you group by employee you get one row per employee and, therefore, only one of the orderid values would be shown. As this could be from any of the many orders its specific value is meaningless. On the other hand, gouping by orderid means there will be a unique employee. In standared SQL, the rule is enforced that only columns in the group by clause, or that are being aggregated, can be selected.

yet also Good luck with that.

Very unlikely. The tables only existed for the few microseconds it tok to run the script and was gone before the user viewed the results on the client.

should be SELECT l.*

... which makes them the safer option

Each connection wil have its own temp table https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

The duration of a temp table is that of the connection. If you create it in a php script it disappears when the script fininishes and the connection closes.

MySql does not make unnecessary changes mysql> select * from user; +----+-----------+----------+-----------+----------+ | id | firstname | lastname | user_name | password | +----+-----------+----------+-----------+----------+ | 1 | Sarah | Tonin | saraht | NULL | | 2 | Tom | DiCanari | tomd | NULL | | 3 | Laura | Norder | lauran | NULL | | 4 | Anna | Robik | annar | NULL | | 5 | Peter | Dowt | peted | NULL | +----+-----------+----------+-----------+----------+ 5 rows in set (0.04 sec) mysql> update user set user_name = 'tomd' where id=2; Query OK, 0 rows affected (0.71 sec) <<<<<<< Rows matched: 1 Changed: 0 Warnings: 0 <<<<<<<

You could either DROP the copylinks table when you have finished with it, or Create it as a temporary table - but then you'd need to do it twice (copylinksa and copylinksb) as you can't open a temp table twice in a query BTW, your links_test table doesn't have LINKLOCATION column.

Like this... $res = $connect->query("SELECT c.id , c.category , COUNT(p.id) as numposts FROM categories c LEFT JOIN posts p ON c.id = p.category_id AND active = 'Yes' GROUP BY c.id ORDER BY category "); foreach ($res as $row) { $tc = ($row['numposts']==1) ? '' : 's'; echo "a href='category.php?id={$row['id']}' class='list-group-item list-group-item-action'> <i class='fa fa-arrow-right'></i>&nbsp;{$row['category']} - {$row['numposts']} article$tc </a>"; }

Don't run queries inside loops. Use a single query with a JOIN. Don't use SELECT *. Specify the columns you need. SELECT c.id , c.category , COUNT(p.id) as numposts FROM categories c LEFT JOIN posts p ON c.id = p.category_id AND active = 'Yes' GROUP BY c.id ORDER BY category;

Alternative? date('Y-m', strtotime("first day of last month"));

The ORDER BY at the end of a set of UNIONs applies to the whole resultset. You may need a two-query approach (no data so untested) -- QUERY 1 CREATE TABLE copylinks SELECT * FROM `links` as l LEFT JOIN `accounts` as a ON a.id=l.author WHERE `l`.`live` = '1' ORDER BY `l`.`lastVisitDateTime` DESC LIMIT 10; -- QUERY 2 INSERT INTO copylinks SELECT * FROM `links` as l LEFT JOIN `accounts` as a ON a.id=l.author LEFT JOIN copylinks l2 ON l.id = l2.id WHERE `l`.`live` = '1' AND `l`.`linklocation` = 'main' AND l2.id IS NULL ORDER BY `l`.`timestamp` DESC; -- CHECK RESULT SELECT * FROM copylinks;

Now that you mention it, I recall ... SELECT SUM(p.product_price) FROM cart_details c JOIN products p ON c.product_id = p.product_id WHERE c.ip_address = ? It often seems we're throwing seed on to stony ground.

And how is this latest code related to the previous code? When you post code in this forum, use a code block when you paste the code (the <> button) In future, please use meaningful topic titles related to the problem which will help others searching for a solution to a similar problem.

Where is $total_price coming from?

$previous_month = date('Y-m', strtotime("-1 month"));

We have a separate forum for you to use when you require work to be done... https://forums.phpfreaks.com/forum/77-job-offerings/ Post you requirements there and provide contact details.

Of course, just because a query returns no results it doesn't mean it failed - just that there were no results meeting the specified criteria at the time it was run. Indeed, no results might be the best result. Consider SELECT cust.name , comp.comment FROM customer cust JOIN british_railways_complaints_received comp USING (customer_id) WHERE complaint_date = ? The desired, but highly improbable, result would be none. Conversely, a query that returns results hasn't ncessarily worked as was intended in its specification.

If that now is how the query should be, then it has apparently been producing the wrong results for years and not working "fine" as you thought.

I would use these table (or similar) TABLE: category TABLE: model TABLE: sale +----+------------+ +----+------------+-------------+ +----+------------+-------+--------+---------+ | id | cat_name | | id | model_name | category_id | | id | model_id | year | month | qty | +----+------------+ +----+------------+-------------+ +----+------------+-------+--------+---------+ | 1 | Category A | | 1 | Model 1 | 1 | CREATE TABLE `sale` ( | 2 | Category B | | 2 | Model 2 | 1 | `id` int(11) NOT NULL AUTO_INCREMENT, | 3 | Category C | | 3 | Model 3 | 1 | `model_id` int(11) DEFAULT NULL, +----+------------+ | 4 | Model 4 | 2 | `year` year(4) DEFAULT NULL, | 5 | Model 5 | 2 | `month` tinyint(4) DEFAULT NULL, | 6 | Model 6 | 2 | `qty` int(11) DEFAULT NULL, | 7 | Model 7 | 3 | PRIMARY KEY (`id`), | 8 | Model 8 | 3 | UNIQUE KEY `idx_model_yr_mth` (`model_id`,`year`,`month`) +----+------------+-------------+ ) For the javascript, I prefer to use a combination of class and data attriibutes for element grouping EG <?php require 'db_inc.php'; // USE YOUR OWN $pdo = pdoConnect('db2'); // CONNECTION CODE ################################################################################ # PROCESS POSTED DATA # ################################################################################ if ($_SERVER['REQUEST_METHOD']=='POST') { #echo '<pre>' . print_r($_POST, 1) . '</pre>'; $year = $_POST['year']; $pdata = []; foreach ($_POST['qty'] as $mod => $mdata) { foreach ($mdata as $mth => $qty) { if (intval($qty) && intval($mth) && intval($year) && intval($mod) ) { $pdata[] = sprintf("(%d, %d, %d, %d)", $mod, $year, $mth, $qty ); } } } $pdo->exec("INSERT INTO sale (model_id, year, month, qty) VALUES " . join(',', $pdata) . "ON DUPLICATE KEY UPDATE qty = VALUES(qty) "); header("Location: ?year=$year"); exit; } ################################################################################ # TABLE HEADINGS # ################################################################################ $year = $_GET['year'] ?? date('Y'); $dt1 = new DateTime("{$year}-01-01"); $dt2 = clone $dt1; $dt2->modify("+12 months"); $range = new DatePeriod($dt1, new DateInterval('P1M'), $dt2); $theadings = "<tr><th>S.No</th><th>Category</th><th>Model</th>"; foreach ($range as $d) { $theadings .= "<th>" . $d->format('My') . "</th>"; } $theadings .= "<th>Total</th><th>MS &nbsp;%</th></tr>\n"; ################################################################################ # CATEGORIES AND MODELS # ################################################################################ $res = $pdo->prepare("SELECT c.id as cid , cat_name , m.id as mid , model_name , month , qty FROM category c JOIN model m ON c.id = m.category_id LEFT JOIN sale s ON s.year = ? AND m.id = s.model_id ORDER BY cid, mid, month "); $res->execute([ $year ]); $empty = array_fill_keys(range(1,12), null); $data = []; ################################################################################ # PUT RESULTS INTO SUITABLY STRUCTURED ARRAY TO MATCH THE REQUIRED OUTPUT # ################################################################################ foreach ($res as $r) { if (!isset($data[$r['cid']])) { $data[$r['cid']] = ['cat' => $r['cat_name'], 'models' => [] ]; } if (!isset($data[$r['cid']]['models'][$r['mid']])) { $data[$r['cid']]['models'][$r['mid']] = [ 'mod' => $r['model_name'], 'vals' => $empty ]; } if ($r['qty']) { $data[$r['cid']]['models'][$r['mid']]['vals'][$r['month']] = $r['qty']; } } ################################################################################ # TAKE THE ARRAY DATA AND OUTPUT TO THE TABLE # ################################################################################ $tdata = ''; $n = 1; $prev = ''; foreach ($data as $catid => $cdata) { $catname = $cdata['cat']; $tdata .= "<tbody data-cat='$catid}'>\n"; foreach ($cdata['models'] as $modid => $mdata) { $modname = $mdata['mod']; $tdata .= "<tr class='qty-row' data-model='$modid'><td>$n</td><td>$catname</td><td>$modname</td>"; $catname = ''; foreach ($mdata['vals'] as $m => $qty) { $tdata .= "<td><input class='w3-input w3-border qty-mth' data-cat='$catid' data-model='$modid' data-mth='$m' name='qty[$modid][$m]' value='$qty'> </td>"; } $tdata .= "<td><input class='w3-input w3-border qty-yr' data-cat='$catid' data-model='$modid' ></td> <td><input class='w3-input w3-border ms-yr' data-cat='$catid' data-model='$modid' ></td> "; ++$n; } $tdata .= "</tbody>\n <tr class='total-row'><td colspan='3'>TOTAL</td>"; for ($m=1; $m<=12; $m++) { $tdata .= "<td><input class='w3-input w3-border tot-mth' data-cat='$catid' data-mth='$m' ></td>"; } $tdata .= "<td><input class='w3-input w3-border tot-yr' data-cat='$catid' ></td> <td>&nbsp;</td> "; } ################################################################################ # FUNCTIONS # ################################################################################ function yrOpts($current) { $yrs = range(date('Y'), date('Y')-4); $opts = "<option value=''>- select year -</option>\n"; foreach ($yrs as $y) { $sel = $current==$y ? 'selected':''; $opts .= "<option $sel>$y</option>\n"; } return $opts; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> $(function() { $("#year").change( function() { let yr = $(this).val() location.href = "?year="+yr }) $(".qty-mth").on('input', function() { calc_totals() }) calc_totals() }) function calc_totals() { $(".qty-yr").each(function(k,v) { let mod = $(v).data("model") let toty = 0 $(".tot-yr").val(0) $(".qty-mth[data-model="+mod+"]").each(function(k1,v1) { let q = parseInt($(v1).val()) toty += isNaN(q) ? 0 : q }) $(v).val(toty) }) $(".tot-yr").each(function(k,v) { let cat = $(v).data("cat") calc_cat_totals(cat) calc_percents(cat) }) } function calc_cat_totals(cat) { let ytot = 0 for (let m=1; m<=12; m++) { let mtot = 0 $(".qty-mth[data-cat="+cat+"][data-mth="+m+"]").each(function(k,v){ let q = parseInt($(v).val()) mtot += isNaN(q) ? 0 : q ytot += isNaN(q) ? 0 : q }) $(".tot-mth[data-cat="+cat+"][data-mth="+m+"]").val(mtot) } $(".tot-yr[data-cat="+cat+"]").val(ytot) } function calc_percents(cat) { let ytot = $(".tot-yr[data-cat="+cat+"]").val() if (ytot==0) return $(".qty-yr[data-cat="+cat+"]").each(function(k,v) { let mod = $(v).data("model") let q = $(v).val() * 100 / ytot q = q.toFixed(1) $(".ms-yr[data-model="+mod+"]").val(q) }) } </script> <style type='text/css'> table { width: 100%; border-collapse: collapse; margin: 20px 0; } th, td { padding: 2px 4px; font-size: 9pt; text-align: center; } th { background-color: black; color: white; } .qty-mth, .tot-mth, .qty-yr, .ms-yr, .tot-yr { width: 45px; margin: 0 auto; } .total-row { background-color: #D0D0D0; } .qty-row { background-color: #F8F8F8; } input { text-align: center; } </style> <script type='text/javascript'> </script> </head> <body> <div class='w3-container w3-padding'> <form id='form1' method='POST'> <div class='w3-bar'> <div class='w3-bar-item'>Year </div> <select id='year' name='year' class='w3-bar-item w3-border'> <?= yrOpts($year) ?> </select> </div> <table border='1' class="table" id="myTable2"> <thead> <?= $theadings ?> </thead> <?= $tdata ?> </table> <div class='w3-container w3-center'> <button class='w3-button w3-blue'>Save data</button> </div> </form> </div> </body> </html>