Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,563

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. 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
  2. Barand
    Whenever I want to print, I write to a PDF file which then downloads to the browser for printing.
  3. Barand
    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)
  4. Barand
    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).
  5. Barand
    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)
  6. Barand
    For over a decade now the correct opening tag for php has been <?php (unless short tags have been explicitly allowed).
  7. Barand
    @Danishhafeez, 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.
  8. Barand
    See
  9. Barand
    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>
  10. Barand
  11. Barand
    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>
  12. Barand
    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 | +-------+---------+------+
  13. Barand
    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"
  14. Barand
    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 | +----------+--------+-------+------------------------------------+
  15. Barand
    Perhaps it has something to do with your only ever reading the first row of the query results
  16. Barand
    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.
  17. Barand
    probably because of this... It's better to use the same column names.
  18. Barand
    Sorrry - should be ($a, $b). (Too much javascript recently)
  19. Barand
    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
  20. Barand
    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']];
  21. Barand
    Sort each row before you create the divs usort($row, fn(a,b)=>$a['order']<=>$b['order']);
  22. Barand
    Looks like you are probably trying to decode the fetched array and the string that you want to json_decode() is in $data['cards'] .
  23. Barand
    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?
  24. Barand
    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 ) )
  25. Barand
    Yes, you can't put double quotes inside a double-quoted string without excaping them. mysql> select * from json_test where id = 2; +----+-----------------------------------------------------------------------------------------------------------------------+ | id | jstuff | +----+-----------------------------------------------------------------------------------------------------------------------+ | 2 | {"Portal Content": {"Colours": {"Primary": "Red", "Secondary": "Green", "Tertiary": "Blue", "Quaternary": "String"}}} | +----+-----------------------------------------------------------------------------------------------------------------------+ $stmt = $pdo->prepare("UPDATE json_test SET jstuff = JSON_SET(jstuff, ?, ?, ?, ?, ?, ?, ?, ?) WHERE id = ? "); $stmt->execute([ '$."Portal Content"."Colours"."Primary"', 'Orange', '$."Portal Content"."Colours"."Secondary"', 'Limegreen', '$."Portal Content"."Colours"."Tertiary"', 'Hotpink', '$."Portal Content"."Colours"."Quaternary"', 'Cornsilk', 2 ]); mysql> select * from json_test where id = 2; +----+-----------------------------------------------------------------------------------------------------------------------------------+ | id | jstuff | +----+-----------------------------------------------------------------------------------------------------------------------------------+ | 2 | {"Portal Content": {"Colours": {"Primary": "Orange", "Secondary": "Limegreen", "Tertiary": "Hotpink", "Quaternary": "Cornsilk"}}} | +----+-----------------------------------------------------------------------------------------------------------------------------------+
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.