Barand
-
Posts
24,338 -
Joined
-
Last visited
-
Days Won
795
Posts posted by Barand
-
-
Simple method using a couple of classes (See https://dev.to/alexandrefreire/bootstrap-4-hiding-elements-in-responsive-layout-3g25)
Code
<html> <head> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css"> </head> <body> <div > <img src='no_photo.jpg' border='0' width='102' height='104' alt='no_photo.jpg (2,382 bytes)' class='d-block d-md-none'> <img src='parrots.JPG' border='0' width='397' height='191' alt='parrots.JPG (22,358 bytes)' class='d-none d-md-block'> </div> </body> </html>
Output width = 420
Output width = 800
-
Whenever I want to print, I write to a PDF file which then downloads to the browser for printing.
-
Ensure all columns used in the joins are indexed. For example, adding an index on plant.branchid gave atime of 0.03 seconds.
+----------------+----------------+---------------+---------------+----------+---------------+ | Category | Budget | Billing | PTarget | PBilling | CTarget | +----------------+----------------+---------------+---------------+----------+---------------+ | Batching Plant | 210,236,000.00 | 17,732,014.85 | 14,716,520.00 | 0.00 | 17,519,666.00 | | Concrete Pump | 125,428,000.00 | 18,451,593.09 | 8,779,960.00 | 0.00 | 10,452,333.00 | | TM | 71,957,300.00 | 7,340,912.62 | 5,037,011.00 | 0.00 | 5,996,442.00 | | SLM | 30,373,660.00 | 1,678,367.08 | 2,126,156.00 | 0.00 | 2,531,139.00 | | Projects | 95,000,000.00 | 306,795.76 | 6,650,000.00 | 0.00 | 7,916,667.00 | +----------------+----------------+---------------+---------------+----------+---------------+ 5 rows in set (0.03 sec)
-
1
-
-
The only difference I could see between your 2nd and 3rd subqueries was the 2nd summed months between 01 and 03 and the the 3rd only summed month 03, so I hve combined them and shaved 45% off the time
mysql> SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM -> (SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM target as a -> inner join material as b on b.id = a.Category -> inner join equipment as c on c.eqipID = b.eqipID -> inner join plant as d on d.plantCode = a.Branch -> inner join branch as e on e.branchID = d.branchID where e.branchID='2' group by c.eqipID) TG -> LEFT JOIN ( -> SELECT c.eqipName AS Category -> , SUM(a.gross_amount) AS Billing -> , SUM( -> CASE DATE_FORMAT(a.billing_date,'%Y-%m') -> WHEN '2024-03' THEN a.gross_amount -> ELSE 0 -> END -> ) AS PBilling -> FROM billing AS a -> INNER JOIN division AS b ON b.divCode = a.division -> INNER JOIN equipment AS c ON c.eqipID = b.eqipID -> INNER JOIN distributionchannel AS d ON d.dcno = a.dchannel -> INNER JOIN plant AS e ON e.plantCode= a.sales_office -> INNER JOIN branch AS f ON f.branchID = e.branchID -> WHERE c.equipcatID = '1' -> AND d.dcgroupid='1' -> AND DATE_FORMAT(a.billing_date,'%Y-%m') BETWEEN '2024-01' AND '2024-03' -> AND f.branchID='2' -> AND a.sales_doc_type NOT IN ('ZL2W', 'ZS2','ZIPJ') -> GROUP BY c.eqipName -> ) Billing USING (Category); +----------------+-----------+-------------+----------+----------+----------+ | Category | Budget | Billing | PTarget | PBilling | CTarget | +----------------+-----------+-------------+----------+----------+----------+ | Batching Plant | 210236000 | 17732014.85 | 14716520 | 0.00 | 17519666 | | Concrete Pump | 125428000 | 18451593.09 | 8779960 | 0.00 | 10452333 | | TM | 71957300 | 7340912.62 | 5037011 | 0.00 | 5996442 | | SLM | 30373660 | 1678367.08 | 2126156 | 0.00 | 2531139 | | Projects | 95000000 | 306795.76 | 6650000 | 0.00 | 7916667 | +----------------+-----------+-------------+----------+----------+----------+ 5 rows in set (0.20 sec)Note that the typing of your columns is sloppy.
- You are joining ints to varchar(45)s
- INT(25) is somewhat optimistic (in you target spreadsheet table) - the max INT is 11 digits, not 25
- The amount column you are totalling is defined as VARCHAR(45)!!! - should be a numeric type such as DECIMAL(15,2).
-
0.36 seconds - Perhaps your battery needs recharging...
mysql> SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM -> (SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM target as a -> inner join material as b on b.id = a.Category -> inner join equipment as c on c.eqipID = b.eqipID -> inner join plant as d on d.plantCode = a.Branch -> inner join branch as e on e.branchID = d.branchID where e.branchID='2' group by c.eqipID) TG -> -> LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as Billing from billing as a -> inner join division as b on b.divCode = a.division -> inner join equipment as c on c.eqipID = b.eqipID -> inner join distributionchannel as d on d.dcno = a.dchannel -> inner join plant as e on e.plantCode= a.sales_office -> inner join branch as f on f.branchID = e.branchID -> where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') between '2024-01' and '2024-03' and f.branchID='2' -> and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) Billing USING (Category) -> -> LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as PBilling from billing as a -> inner join division as b on b.divCode = a.division -> inner join equipment as c on c.eqipID = b.eqipID -> inner join distributionchannel as d on d.dcno = a.dchannel -> inner join plant as e on e.plantCode= a.sales_office -> inner join branch as f on f.branchID = e.branchID -> where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') = '2024-03' and f.branchID='2' -> and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) PBilling USING (Category); +----------------+-----------+--------------------+----------+----------+----------+ | Category | Budget | Billing | PTarget | PBilling | CTarget | +----------------+-----------+--------------------+----------+----------+----------+ | Batching Plant | 210236000 | 17732014.850000016 | 14716520 | NULL | 17519666 | | Concrete Pump | 125428000 | 18451593.089999996 | 8779960 | NULL | 10452333 | | TM | 71957300 | 7340912.620000004 | 5037011 | NULL | 5996442 | | SLM | 30373660 | 1678367.0800000003 | 2126156 | NULL | 2531139 | | Projects | 95000000 | 306795.76 | 6650000 | NULL | 7916667 | +----------------+-----------+--------------------+----------+----------+----------+ 5 rows in set (0.36 sec) -
For over a decade now the correct opening tag for php has been <?php (unless short tags have been explicitly allowed).
-
I am trying to decide if your post is a product of Artificial Intelligence or Natural Ignorance. That a member of ICTInnovation's QA team would advocate code which changes record IDs should be a huge red flag to any of their prospective clients.
Just my 0.02 worth.
-
-
Is this closer?
<input id='mytext' type='text'> <button id='btn'>Create output</button> <br><br> <textarea id='target' cols='80' rows='5'></textarea> <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script> <script type='text/javascript'> $("#btn").click(function() { let txt = $("#mytext").val() let para = `<p style="background: black; padding: 15px; color:white;">${txt}</p>` $("#target").text(para) }) </script>
-
-
Are you looking for something like this?
<!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script> <title>Example</title> <style type='text/css'> p.desc { background-color: black; color: white; padding: 15px; width: 400px; } </style> </head> <body> <div id='target'> <!-- descriptions go here--> </div> <script type='text/javascript'> const texts = [ "Description One", "Decription Two", "Description Three" ] $.each(texts, function(key,txt) { let para = $("<p>", {class:"desc", html:txt}) $("#target").append(para) }) </script> </body> </html>
-
1
-
-
You need to output a row number, not the actual id. One way is to increment a $rowno variable each time you output a row of your query results and output that.
Alternatively, there are a couple of ways you can do it with a single query.
Test data
TABLE: reindeer +----+---------+ | id | name | +----+---------+ | 1 | Comet | | 3 | Cupid | | 4 | Dasher | | 6 | Vixen | | 8 | Prancer | | 10 | Dancer | | 15 | Donner | | 16 | Blitzen | +----+---------+
-
Using an SQL variable
SELECT @rowno := @rowno+1 as rowno , name , id FROM reindeer JOIN (SELECT @rowno := 0) as init ORDER BY name;
-
Using the ROW_NUMBER() window function (MariaDB v11 or MySql v8)
SELECT ROW_NUMBER() OVER (order by name) as rowno , name , id FROM reindeer ORDER BY name;
Both the above output
+-------+---------+------+ | rowno | name | id | +-------+---------+------+ | 1 | Blitzen | 16 | | 2 | Comet | 1 | | 3 | Cupid | 3 | | 4 | Dancer | 10 | | 5 | Dasher | 4 | | 6 | Donner | 15 | | 7 | Prancer | 8 | | 8 | Vixen | 6 | +-------+---------+------+
-
Using an SQL variable
-
Record IDs ...
- should be unique.
- should never be changed
- should never be reallocated
Breaking those rules destroys the integrity of your database.
And as for you update query, what is the point of setting the id to X where the id = X. That is like you changing your username on this forum to "Legendary_003"
-
If I understood correctly (although I can't see what your data looks like) you may want something like this...
TEST DATA
TABLE: items TABLE: weapons +----------+--------+ +----+----------+--------+-------+ | playerid | itemid | | id | playerid | weapid | level | +----------+--------+ +----+----------+--------+-------+ | 2 | 2 | | 1 | 2 | 1 | 2 | | 2 | 3 | | 2 | 3 | 1 | 2 | | 2 | 4 | +----+----------+--------+-------+ | 3 | 2 | +----------+--------+
QUERY and RESULTS (Find which players have an item that is 1 greater than current weapon level)
SELECT w.playerid , w.weapid , w.level , COALESCE(i.itemid, 'You don\'t have an item for upgrade') as item FROM weapons w LEFT JOIN items i ON w.playerid = i.playerid AND i.itemid = w.level + 1; +----------+--------+-------+------------------------------------+ | playerid | weapid | level | item | +----------+--------+-------+------------------------------------+ | 2 | 1 | 2 | 3 | | 3 | 1 | 2 | You don't have an item for upgrade | +----------+--------+-------+------------------------------------+-
1
-
-
Perhaps it has something to do with your only ever reading the first row of the query results
-
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html
That's where I'd be looking to answer your question so may as well cut out the middle man.
-
7 minutes ago, Adamhumbug said:
I am getting unfeined array key for the icons.
probably because of this...
It's better to use the same column names.
-
1
-
-
Sorrry - should be ($a, $b). (Too much javascript recently)
-
2 minutes ago, Adamhumbug said:
is this part of the SQL?
No - usort() is php function for custom sorts of arrays.
PS It might be $cards (and not $row) that needs sorting. I get lost reading your code when a variable is used as a key value then a couple of lines further on it becomes reused as an array
-
As for your problem with joins on icon_ids, I wouldn't bother.
Create an array of icons from the icon table
$res = $pdo->query("SELECT icon_id, icon_code FROM icon"); $icons = array_column($res->fetchAll(), 'icon_code', 'icon_id');
then use the $card['Icon'] to access key to this array when outputting
$icon_code = $icons[$card['Icon']];
-
1
-
-
Sort each row before you create the divs
usort($row, fn(a,b)=>$a['order']<=>$b['order']);
-
Looks like you are probably trying to decode the fetched array and the string that you want to json_decode() is in $data['cards'] .
-
Insuffucient context. You may as well ask how long a piece of string is.
- How are you sending the result to the page?
- What type of element do you want to build?
- Are you planning on building it with php or javascript?
-
To process json data in php, json_decode() it to get an array.
$jsonData = '{ "Card 1": { "Name": "Card 1", "Paragraph": "This is the first card", "Icon": "1" }, "Card 2": { "Name": "Card 2", "Paragraph": "This is the second card", "Icon": "2" }, "Card 3": { "Name": "Card 3", "Paragraph": "This is the third card", "Icon": "3" }, "Card 4": { "Name": "Card 4", "Paragraph": "This is the fourth card", "Icon": "4" } }'; $data = json_decode($jsonData, 1); echo '<pre>' . print_r($data, 1) . '</pre>';
Array ( [Card 1] => Array ( [Name] => Card 1 [Paragraph] => This is the first card [Icon] => 1 ) [Card 2] => Array ( [Name] => Card 2 [Paragraph] => This is the second card [Icon] => 2 ) [Card 3] => Array ( [Name] => Card 3 [Paragraph] => This is the third card [Icon] => 3 ) [Card 4] => Array ( [Name] => Card 4 [Paragraph] => This is the fourth card [Icon] => 4 ) )
Populate combobox dynamically
in PHP Coding Help
Posted
Loop through the returned results of your function and output the options