Barand
-
Posts
24,341 -
Joined
-
Last visited
-
Days Won
795
Everything posted by Barand
-
I agree with mac_gyver except for the use of OR in the WHERE clause. Consider this product table... +------------+-------------------------+ | product_id | description | +------------+-------------------------+ | 1 | Black mamba | | 2 | Fireball XL5 | | 3 | Single coat black paint | | 4 | Coat of many colours | | 5 | Black coat XL | | 6 | Not of interest | +------------+-------------------------+ Code... $search = 'coat black xl'; $params = array_map(fn($v)=>"%$v%" , explode(' ', $search)); $q1 = "SELECT description FROM product -- search query using OR WHERE description LIKE ? OR description LIKE ? OR description LIKE ? "; $q2 = "SELECT description FROM product -- search query using AND WHERE description LIKE ? AND description LIKE ? AND description LIKE ? "; Results... Results using OR +-------------------------+ | description | +-------------------------+ | Black mamba | | Fireball XL5 | | Single coat black paint | | Coat of many colours | | Black coat XL | +-------------------------+ Results using AND +---------------+ | description | +---------------+ | Black coat XL | +---------------+ A couple of other options are open to you FULLTEXT Add fulltext index on description and $q3 = "SELECT description , MATCH(description) AGAINST('coat black xl') as relevance FROM product WHERE MATCH(description) AGAINST('coat black xl') ORDER BY relevance DESC " ; +-------------------------+-------------------+ | description | relevance | +-------------------------+-------------------+ | Black coat XL | 0.18123811483383 | | Single coat black paint | 0.18123811483383 | | Coat of many colours | 0.090619057416916 | | Black mamba | 0.090619057416916 | +-------------------------+-------------------+ NOTE: with fulltext, words of 3 or less characters (eg "XL") are ignored. Use separate columns for category, colour and size and search on those.
-
If it is going into a database then you should be using prepared queries which will guard against SQL injection. Mozilla Developer Network (MDN)
-
Then use the actual name of your PDO connection object instead of $pdo. My apologies for not being psychic.
-
Loop through the returned results of your function and output the options <?php foreach (getCompanyName($pdo) as $row) { echo "<option value='{$row['id']}'>{$row['name']}</option>" ; } ?>
-
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.
-
multiple inner join mysql query taking more time to execute
Barand replied to Senthilkumar's topic in MySQL Help
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) -
multiple inner join mysql query taking more time to execute
Barand replied to Senthilkumar's topic in MySQL Help
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). -
multiple inner join mysql query taking more time to execute
Barand replied to Senthilkumar's topic in MySQL Help
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) -
[insert_php] use foundationphp\UploadFile; in a page do not work
Barand replied to Mikedata's topic in PHP Coding Help
For over a decade now the correct opening tag for php has been <?php (unless short tags have been explicitly allowed). -
@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.
-
See
-
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>
-
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 | +-------+---------+------+
-
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 | +----------+--------+-------+------------------------------------+
-
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.
-
probably because of this... It's better to use the same column names.
-
Sorrry - should be ($a, $b). (Too much javascript recently)
-
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']];
-
Sort each row before you create the divs usort($row, fn(a,b)=>$a['order']<=>$b['order']);
