Barand

Before I retired (approx 12 years ago) I regularly worked on a windows based intranet. I found the ADLDAP class a great tool. In fact the system admins used to ask me to create reports that they couldn't accesss via their Microsoft network tools. https://github.com/adldap/adLDAP

As you are sending data via the url querystring you should be using $_GET, not $_POST. Key/value pairs in the querystring should not be comma-separated

Put this line mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); just before your "$conn = new mysqli(...)" and see what the error message says when you attempt the insert.

As the data I was given has no data for RAGUL's empno in any tables (except usertable) I cannot verify any of my theories on what may be wrong. Also for branch 2 I have only this... +---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ | month | name | CustomerTotal | CustomerActual | ProductionTotal | ProductionActual | MarketingTotal | MarketingActual | +---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ | 2024-03 | Palanikumar B | | | | | 1 | | | 2024-02 | NAVEENKUMAR P | | | | | 1 | | +---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ For branch 5... +---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ | month | name | CustomerTotal | CustomerActual | ProductionTotal | ProductionActual | MarketingTotal | MarketingActual | +---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ | 2024-02 | Nethaji.MK | | | | | 1 | | | 2024-02 | T.Anandakrishnan | | | | | 1 | | | 2024-02 | Sivanraj.A | | | | | 1 | | | 2024-02 | kannan.r | | | | | 1 | | | 2024-01 | thayyanayaki | 5 | 5 | 2 | 2 | | | | 2024-02 | thayyanayaki | 5 | 5 | 2 | 2 | | | | 2024-03 | thayyanayaki | 5 | 5 | 2 | 2 | 1 | | | 2024-04 | thayyanayaki | 5 | 3 | 2 | 1 | | | | 2024-01 | Vignesh.M | | | | | 1 | 1 | | 2024-02 | Vignesh.M | | | | | 1 | | | 2024-02 | N.Manikandan | | | | | 1 | | | 2024-02 | Dinesh.C | | | | | 1 | | +---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+ The previous queries (your and mine) would only report on whatever empno, branch and month values existed in the production table (the one that isn't LEFT JOINED). This version removes that reliance ... SELECT u.Month , Name , CustomerTotal , CustomerActual , ProductionTotal , ProductionActual , MarketingTotal , MarketingActual FROM ( WITH RECURSIVE allmonths (id, month) as ( SELECT 1, '2024-01' UNION ALL SELECT id+1, concat('2024-', lpad(id+1,2,'0')) FROM allmonths WHERE id < 4 ) SELECT empNo as empId , fname as name , month , ? as branch FROM usertable, allmonths -- WHERE role IN (4, 5) ) u LEFT JOIN ( SELECT count(*) as CustomerTotal , sum(VisitType = 'No Due' OR VisitDate != '') as CustomerActual , month , empid , branch FROM customerdata GROUP BY month,branch,empid ) ca ON u.month = ca.month AND u.empid = ca.empid AND u.branch = ca.branch LEFT JOIN ( SELECT count(*) as ProductionTotal , sum(MCubicmeter OR MHourmeter) as ProductionActual , month , empid , branch FROM production GROUP BY month, branch, empid ) pa ON u.month = pa.month AND u.empid = pa.empid AND u.branch = pa.branch LEFT JOIN ( SELECT count(*) as MarketingTotal , month , empid , branch FROM marketing_target GROUP BY month, branch, empid ) mt ON u.month = mt.month AND u.empid = mt.empid AND u.branch = mt.branch LEFT JOIN ( SELECT count(*) as MarketingActual , month , empid FROM marketing_data GROUP BY month, empid ) ma ON u.month = ma.month AND u.empid = ma.empid AND u.branch = mt.branch HAVING CustomerTotal OR CustomerActual OR ProductionTotal OR ProductionActual OR MarketingTotal OR MarketingActual ORDER BY u.empid, u.month;

add this and job almost done... $tot = array_sum($counts); $pcents = array_map(fn($v)=>$v*100/$tot, $counts);

Try something like... $urls = array_column($statement->fetchAll(), 'domain_names'); $tlds = array_map(fn($v)=>strstr($v, '.'), $urls); $counts = array_count_values($tlds);

You may echo all of them but, each time through the loop, $tld is overwritten - you are just left with last one.

Took a fair bit of experimenting to get the right incantations for the update, but... Data SELECT * FROM json_test; +----+-------------------------------------------------------------------------------------------------------------------------------+ | id | jstuff | +----+-------------------------------------------------------------------------------------------------------------------------------+ | 1 | {"card": {"4": {"cardName": "This is a card", "iconSelection": "1", "linkLocation": "5", "paragraph": "This is para"}}} | +----+-------------------------------------------------------------------------------------------------------------------------------+ Update UPDATE json_test SET jstuff = JSON_SET(jstuff, '$."card".4."cardName"', 'This is a different card') WHERE id = 1; Check SELECT * FROM json_test; +----+-----------------------------------------------------------------------------------------------------------------------------------+ | id | jstuff | +----+-----------------------------------------------------------------------------------------------------------------------------------+ | 1 | {"card": {"4": {"cardName": "This is a different card", "iconSelection": "1", "linkLocation": "5", "paragraph": "This is para"}}} | +----+-----------------------------------------------------------------------------------------------------------------------------------+

When?

... then what do think that is on line 4?

I thought I had. Read my replies, which were... You are joining on "month" so if different subqueries are selecting different months, then they aren't going to match. Your marketing table is missing 2024-04 data so you aren't going to get any totals from that table for that month. Do you need me to spell it out in more detail and make it clearer? I had a go at rewriting your query with simple subquery for each total SELECT ca.Month , Name , CustomerTotal , CustomerActual , ProductionTotal , ProductionActual , MarketingTotal , MarketingActual FROM ( SELECT empNo as empId , fname as name FROM usertable WHERE role IN (4, 5) ) u JOIN ( SELECT count(*) as CustomerTotal , sum(VisitType = 'No Due' OR VisitDate != '') as CustomerActual , month , empid , branch FROM customerdata GROUP BY month,branch,empid ) ca USING (empid) LEFT JOIN ( SELECT count(*) as ProductionTotal , sum(MCubicmeter OR MHourmeter) as ProductionActual , month , empid , branch FROM production GROUP BY month, branch, empid ) pa USING (month, branch, empid) LEFT JOIN ( SELECT count(*) as MarketingTotal , month , empid , branch FROM marketing_target GROUP BY month, branch, empid ) mt USING (month, branch, empid) LEFT JOIN ( SELECT count(*) as MarketingActual , month , empid FROM marketing_data GROUP BY month, empid ) ma USING (month, empid) WHERE ca.branch = '5' AND ca.month = '2024-04' ;

Perhaps https://dev.mysql.com/doc/refman/8.0/en/tutorial.html

By the way @KAVIYA, I think you should get together with @Senthilkumar and join forces. His database name, column names and query contents and use of varchar for almost everything bear an amazing similarity to yours.

It helps if all the months that you searching for are the same. I can't see any data for 2024-04 in marketing_data table, so it gives this when I combine all

You stand a better chance of help if you tell us exactly what your problem is we know what your data looks like (table structure and test data export dump) we know what output you want.

Store the field sequence number as an attribute and don't rely on its position in the array. (This would apply equally to a conventional database table where you shouldn't rely on id for position). Also, have your sequence numbers initially incrementing by 10s (or, even, 100s). If you increment by 1, changing the sequence is a problem. EG if you start with firstname lastname date_of_birth username password and you then decide you want username to be first, you have to increment 1, 2, and 3 then change 4 to 1 (and run the risk of creating illegal duplicates on the way if you are using array positions or id keys). If you started with 100, 200, 300, .... then moving the username to the top would just be a matter of changing 400 to 50.

I find data attributes useful for associating inputs for the same record. I get the data-id of the select field then change other fields with the same data-id. Here's an example <php $employees = [ [ 'id'=>21, 'name'=>'Curly', 'status'=>1, 'title'=>'Sales Manager', 'salary'=>65000 ], [ 'id'=>22, 'name'=>'Larry', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>33, 'name'=>'Mo', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>46, 'name'=>'Tom', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>47, 'name'=>'Dick', 'status'=>1, 'title'=>'Trainee', 'salary'=>25000 ], [ 'id'=>51, 'name'=>'Harry', 'status'=>1, 'title'=>'Trainee', 'salary'=>25000 ] ]; $tdata = ''; foreach ($employees as $e) { $tdata .= "<tr> <td>{$e['name']}</td> <td><select name='emp[{$e['id']}][status]' class='status' data-id='{$e['id']}'>" . statusOptions($e['status']) . "</td> <td><input type='text' name='emp[{$e['id']}][title]' class='title' data-id='{$e['id']}' value='{$e['title']}'></td> <td><input type='text' name='emp[{$e['id']}][salary]' class='salary' data-id='{$e['id']}' value='{$e['salary']}'></td> </tr>"; } function statusOptions($current) { $stats = [1 => "Working", 2 => "Not Working"]; $opts = "<option value=''>- select status -</option>"; foreach ($stats as $s => $desc) { $sel = $s == $current ? 'selected' : ''; $opts .= "<option $sel value='$s'>$desc</option>"; } return $opts; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script> <script type='text/javascript'> $(function() { $(".status").change(function() { let id = $(this).data("id") if ( $(this).val() == 2 ) { $(".title[data-id="+id+"]").attr("disabled", true) $(".salary[data-id="+id+"]").attr("disabled", true) } else { $(".title[data-id="+id+"]").attr("disabled", false) $(".salary[data-id="+id+"]").attr("disabled", false) } }) }) </script> <style type='text/css'> table { border-collapse: collapse; width: 80%; margin: 30px auto; } th { text-align: left; padding: 8px; color: white; background-color: black; } td { padding: 4px 8px; } </style> </head> <body> <h1>Example</h1> <form method='POST'> <table border='1'> <tr> <th>Name</th> <th>Status</th> <th>Job Title</th> <th>Salary</th> </tr> <?= $tdata ?> <tr><td colspan='4'><input type='submit'></tr> </table> </form> </body> </html>

The keys were 0, 1. This what you have in your table. Examples... $sql2 = "INSERT INTO test_1 (field_selection) VALUES (:fids)"; $stmt2 = $pdo->prepare($sql2); $fieldIds = [0=>1, 1=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); $fieldIds = [42=>1, 57=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); // GIVES: // +----+---------------------+--------------------+ // | id | created | field_selection | // +----+---------------------+--------------------+ // | 1 | 2024-03-01 00:09:04 | [1, 2] | // | 2 | 2024-03-01 00:09:04 | {"42": 1, "57": 2} | // +----+---------------------+--------------------+

What do you expect to be getting? What's in $_POST['field'] ?

NOTE: I missed out a "NOT" - I edited my reply to correct.

try WHERE EmpID='83201858' AND NOT (VisitType <> 'No Due' AND VisitDate ='')

You should first learn the basic concepts, such as the difference between column names and column values and other attributes

Some are the result of running a query from the command line but most, particularly the relationhip diagrams, are hand-typed.

Do you have php v8?

Now you provide it! When I think of the time I've spent building these... +--------+-----------+-----------------------------+ | cat_id | cat_name | attributes | +--------+-----------+-----------------------------+ | 1 |Membership | ["Type", "Duration"] | | 2 |Book | ["Title", "Author"] | | 3 |T-shirt | ["Size", "Colour", "Style"] | +--------+-----------+-----------------------------+