Barand

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>

All you need is one query $result = $pdo->prepare("SELECT SUM(p.product_price) FROM cart_details c JOIN products p ON c.product_id = p.product_id WHERE c.ip_address = ? "); $result->execute([ getIPAddress() ]); $total_price = $result->fetchColumn();

I didn't notice before (because you didn't use a code block) but you have used backticks around `%search_data_value%`. Those will force SQL to treat it as a column name. Use single quotes around strings.

If $result_query is boolean, then the query failed and returned "false". For future reference, call this line of code before you connect to your database so any mysql errors get reported... mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

As a rule, output to the page generally happens in the same order that it appears in your code. If you want the table after the form, output it after the form in your code. Not rocket science.

<html> <body> <form> form stuff </form> <?php if (!empty($from) && !empty($to)) { ?> <table> table stuff </table> <?php } ?> </body> </html>

make the output of the table conditional on there being input of dates (as you did with the processing).

You are referencing $_GET['view'] but you do not have an input with that name ('view'). The name of your <select> is "sales_repid" so you need use $_GET['sales_repid']. Also, the "for" in you label for the select should be the id of the input element (should be for='rep' )

Your call to fetchAll() puts the returned rows into $a_data. You need to loop through the array "$a_data" and output the content of each row.

I don't see any code there that should output the results.