Barand

Hint: Instead of a string of OR conditions it is much simpler to use IN(...) WHERE marks.subjectid IN ('CREATIVE ARTS', 'ENGLISH', 'HISTORY', 'ICT', 'MATHEMATICS', 'SCIENCE', 'O. W. O. P.')

Something ike ... $positions = [ 1 => [ 'Team A' => 1, 'Team B' => 2, 'Team C' => 3, 'Team D' => 4 ], 2 => [ 'Team C' => 1, 'Team B' => 2, 'Team D' => 3, 'Team A' => 4 ] ]; $result = []; foreach ($positions as $person => $plist) { foreach ($plist as $t => $pos) { if (!isset($result[$t])) { $result[$t] = $pos; } else { $result[$t] += $pos; } } } asort($result); echo '<pre>' . print_r($result, 1) . '</pre>'; outputs... Array ( [Team B] => 4 [Team C] => 4 [Team A] => 5 [Team D] => 7 )

if() statements are useful in situations like this $import_data = []; fgetcsv($fp); // discard the header row while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); if (empty($row[0])) continue; // skip rows where no value in first col $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); }

Do an fgetcsv() prior to the loop to read the header and discard it $import_data = []; fgetcsv($fp); // discard the header row while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } The first row is easy - you know where it is. The last row is more of a problem. Is there a way of recognising it?

Mysql has a couple of functions for determining the week number for a given date WEEK(date[, mode]) WEEKOFYEAR(date) - equivalent to WEEK(date, 3) The week number depends on how you define the first week of a year and on whether you want weeks starting on Sunday or Monday. Refer to the manual. mysql> select week('2023-11-17') as wkno; +------+ | wkno | +------+ | 46 | +------+ Having a table for each test type seems a very clunky way of storing your data and has already given you a problem. Consider combining the data... +--------------+ | test_type | +--------------+ | id |------+ | description | | +--------------+ | +--------------+ | | test_log | | +--------------+ | | id | +------<| test_type_id | | date | +--------------+ TABLE: test_type TABLE: test_log +------+----------------------------------+ +-----+--------------+------------+ | id | description | | id | test_type_id | date | +------+----------------------------------+ +-----+--------------+------------+ | 1 | Fire alarm | | 1 | 1 | 2023-01-05 | | 2 | Fire doors | | 2 | 2 | 2023-01-06 | | 3 | Shower head temperatures | | 3 | 5 | 2023-01-06 | | 4 | Wash basin temperatures | | 4 | 3 | 2023-01-07 | | 5 | Health and safety | | 5 | 3 | 2023-01-07 | +------+----------------------------------+ | 6 | 4 | 2023-01-08 | | 7 | 2 | 2023-01-09 | | 8 | 4 | 2023-01-09 | | | | |

Probably because you have a query which is searching for a specific semester_id and you are passing it a student_id expecting it to find the student.

Something like this, maybe.... BEFORE TABLE: stockid; TABLE: product +----+ (empty) | id | +----+ | 1 | | 2 | | 3 | +----+ CSV DATA ------------------------ "A1","A2","A3","A4","A5" "B1","B2","B3","B4","B5" "C1","C2","C3","C4","C5" RUN CODE $fp = fopen('products.csv', 'r'); // prepare product insert query $stmt = $pdo->prepare("INSERT INTO product (stock_id, prod_name) VALUES (?, ?)"); while ($row = fgetcsv($fp)) { $pdo->exec("INSERT INTO stockid (id) VALUES (NULL)"); $stock_id = $pdo->lastInsertId(); // get next stock id foreach ($row as $prod) { $stmt->execute([ $stock_id, $prod ]); } } fclose($fp); AFTER TABLE: stockid; TABLE: product +----+ +----+----------+-----------+ | id | | id | stock_id | prod_name | +----+ +----+----------+-----------+ | 1 | | 1 | 4 | A1 | | 2 | | 2 | 4 | A2 | | 3 | | 3 | 4 | A3 | | 4 | | 4 | 4 | A4 | | 5 | | 5 | 4 | A5 | | 6 | | 6 | 5 | B1 | +----+ | 7 | 5 | B2 | | 8 | 5 | B3 | | 9 | 5 | B4 | | 10 | 5 | B5 | | 11 | 6 | C1 | | 12 | 6 | C2 | | 13 | 6 | C3 | | 14 | 6 | C4 | | 15 | 6 | C5 | +----+----------+-----------+

Define the table you are importing into so its stock_id is auto_incementing and leave it to the database. As for your existing function ... Don't connect the db every time you call the function (connecting is the slowest component). Instead, connect once in your script and pass the connection variable to the function when you call it. The function should return the id. Use PDO instead of mysqli - it's better and easier. function get_stock_id($pdo) { $res = $pdo->query("SELECT MAX(stock_id) AS max_stock_id FROM stock_1"); return res->fetchColumn(); }

This thread may help

What is the current process for adjusting targets. How are the revised values determined?

<?php echo "<span style='font-size: 6pt'>http://forums.phpfreaks.com</span>"; ?>

Something like this perhaps (I'm using ids here as I don't have all your supporting tables) <?php $tdata = ""; $data = []; $res = $pdo->query("SELECT t.branch_id , t.dept_id , t.grp_id , t.category_id , t.sub_id , concat(t.branch_id, t.dept_id, t.grp_id, t.category_id, t.sub_id, t.description) as `key` , t.description , t.value , pc.quarter , pc.pcent FROM target t JOIN target_pcent pc USING (branch_id, dept_id, grp_id, category_id, sub_id) "); foreach ($res as $row) { if (!isset($data[$row['key']])) { $data[$row['key']] = [ 'main' => [ 'bid' => $row['branch_id'], 'did' => $row['dept_id'], 'gid' => $row['grp_id'], 'cid' => $row['category_id'], 'sid' => $row['sub_id'], 'desc' => $row['description'], 'val' => $row['value'] ], 'qtrs' => [] ]; } $data[$row['key']]['qtrs'][$row['quarter']] = $row['pcent']; } foreach ($data as $k => $row) { $tdata .= "<tr><td>" . join("</td><td>", $row['main']) . "</td><td>" .join("</td><td>", $row['qtrs']) . "</td></tr>\n"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> </style> </head> <body> <table class="table"> <tr> <th>Branch</th> <th>Department</th> <th>Group</th> <th>Category</th> <th>Equipment</th> <th>Description</th> <th>Target(Rs)</th> <th>Q1(%)</th> <th>Q2(%)</th> <th>Q3(%)</th> <th>Q4(%)</th> </tr> <?= $tdata ?> </table> </body> </html>

Yes, but don't tell him. He'll only get annoyed with you..

In which case ... CREATE TABLE `target` ( CREATE TABLE `target_pcent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id` int(11) NOT NULL AUTO_INCREMENT, `branch_id` int(11) DEFAULT NULL, `branch_id` int(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, `grp_id` int(11) DEFAULT NULL, `grp_id` int(11) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `sub_id` int(11) DEFAULT NULL, `sub_id` int(11) DEFAULT NULL, `description` varchar(50) DEFAULT NULL, `year` year(4) DEFAULT NULL, `value` int(11) DEFAULT NULL, `quarter` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) `pcent` tinyint(4) DEFAULT NULL, ) ENGINE=InnoDB; PRIMARY KEY (`id`) ) ENGINE=InnoDB; Table: target Table: target_pcent +----+-----------+---------+--------+-------------+--------+-------------+-------+ +------+-----------+---------+--------+-------------+--------+------+---------+-------+ | id | branch_id | dept_id | grp_id | category_id | sub_id | description | value | | id | branch_id | dept_id | grp_id | category_id | sub_id | year | quarter | pcent | +----+-----------+---------+--------+-------------+--------+-------------+-------+ +------+-----------+---------+--------+-------------+--------+------+---------+-------+ | 1 | 1 | 1 | 1 | 1 | 1 | Wear Part | 100 | | 1 | 1 | 1 | 1 | 1 | 1 | 2023 | 1 | 15 | | 2 | 1 | 1 | 1 | 1 | 1 | Consumables | 50 | | 2 | 1 | 1 | 1 | 1 | 1 | 2023 | 2 | 25 | | 3 | 1 | 1 | 1 | 1 | 1 | Accessories | 50 | | 3 | 1 | 1 | 1 | 1 | 1 | 2023 | 3 | 25 | | 4 | 1 | 1 | 1 | 1 | 1 | Electrical | 50 | | 4 | 1 | 1 | 1 | 1 | 1 | 2023 | 4 | 35 | | 5 | 1 | 1 | 1 | 1 | 1 | Other Parts | 50 | +------+-----------+---------+--------+-------------+--------+------+---------+-------+ +----+-----------+---------+--------+-------------+--------+-------------+-------+ revised code ... ################################################################################ # CSV COLUMNS # ################################################################################ $main = [ 1 => 'branch_id', 3 => 'dept_id', 5 => 'grp_id', 7 => 'category_id', 9 => 'sub_id' ]; $descrips = [ 10 => 'Wear Part', 11 => 'Consumables', 12 => 'Accessories', 13 => 'Electrical', 14 => 'Other Parts', 15 => 'Pipeline', 16 => 'Mixer Drum' ]; $qtrs = [ 17 => 1, 18 => 2, 19 => 3, 20 => 4 ]; ################################################################################ # READ CSV AND CREATE DATA ARRAY # ################################################################################ $fp = fopen('test2.csv', 'r'); $hdr = fgetcsv($fp); // ignore header row $data = []; $pcdata = []; while ($line = fgetcsv($fp)) { $reca = array_intersect_key($line, $main ); $qarray = array_intersect_key($line, $qtrs ); if (array_sum($qarray) == 0) { $qarray = [17=>15, 18=>25, 19=>25, 20=>35]; // default tatget percents } foreach ($qarray as $k => $pc) { if ($pc) { $recc = [ date('Y'), $qtrs[$k], (empty($pc) ? 0 : $pc ) ]; $pcdata[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d)", array_merge($reca, $recc)); } } foreach (array_intersect_key($line, $descrips ) as $k => $d) { // need a record for each description with a value if ($d) { $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ]; $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d)", array_merge($reca, $recb)); } } } fclose($fp); ################################################################################ # WRITE RECORDS FROM ARRAYS TO target TABLE and target_pcent TABLE # ################################################################################ $chunks = array_chunk($data, 2000); foreach ($chunks as $ch) { $pdo->exec("INSERT INTO target (branch_id, dept_id, grp_id, category_id, sub_id, description, value) VALUES ". join(',', $ch) ); } $chunks = array_chunk($pcdata, 2000); foreach ($chunks as $ch) { $pdo->exec("INSERT INTO target_pcent (branch_id, dept_id, grp_id, category_id, sub_id, year, quarter, pcent) VALUES ". join(',', $ch) ); }

Learn about normalizing data. First, Second and Third Normal Forms should suffice. For example, I am assuming that the quarterly target percentages are defined for each branch_id, dept_id, grp_id, category_id, sub_id but, as far as I know, they could be set at branch level or branch/department level or even company-wide. Normalization requires knowledge of your data which I do not have, nor can I see all the data (only 1 line in the link) to analyse for myself.

Whichever is the primary key.

It's better, but not there yet. The Qn percentages are the same throughout each group of 7 descriptions and should be in a separate table with each quarter in its own row target_pcents ------------------ branch_id dept_id grp_id category_id sub_id year quarter pcent The part descriptions are also repeated many times. They should be another table and the id used instead of the description

Very similar to the last one - I'm sure you should have been able to work it out. But, as an example for others... ################################################################################ # CSV COLUMNS # ################################################################################ $main = [ 1 => 'branch_id', 3 => 'dept_id', 5 => 'grp_id', 7 => 'category_id', 9 => 'sub_id' ]; $descrips = [ 10 => 'Wear Part', // if you have ids for these items, use those instead of text descriptions 11 => 'Consumables', 12 => 'Accessories', 13 => 'Electrical', 14 => 'Other Parts', 15 => 'Pipeline', 16 => 'Mixer Drum' ]; $qtrs = [ 17 => 'Q1', 18 => 'Q2', 19 => 'Q3', 20 => 'Q4' ]; ################################################################################ # READ CSV AND CREATE DATA ARRAY # ################################################################################ $fp = fopen('test2.csv', 'r'); $hdr = fgetcsv($fp); // ignore header row $data = []; while ($line = fgetcsv($fp)) { $reca = array_intersect_key($line, $main ); $recc = array_intersect_key($line, $qtrs ); foreach (array_intersect_key($line, $descrips ) as $k => $d) { // need a record for each description with a value if ($d) { $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ]; $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d,%d,%d,%d)", array_merge($reca, $recb, $recc)); } } } fclose($fp); ################################################################################ # WRITE RECORDS FROM ARRAY TO target TABLE # ################################################################################ $chunks = array_chunk($data, 2000); foreach ($chunks as $ch) { $pdo->exec("INSERT INTO target (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4) VALUES ". join(',', $ch) ); }

Which excel format is the correct one, the one in your first post or the one in your link?

Step 1 - export the Excel file to CSV.

How may times do I have to tell you not to to store spreadsheets in a database? I give up. As far as I'm concerned, you're on your own.

I'd do it like this... ################################################################################ # CREATE TEMPORARY STAGING TABLE # ################################################################################ $pdo->exec("CREATE TEMPORARY TABLE tempdata ( id int not null auto_increment primary key, sales_doc_type varchar(10), billing_date date, material varchar(10), gross_amount int, sales_office varchar(10), plant varchar(10) ) "); ################################################################################ # READ CSV, # # EXTRACT REQUIRED DATA, # # STORE IN ARRAY FOR INSERTION TO TEMP STAGING TABLE # ################################################################################ $fp = fopen('test1.csv', 'r'); $req_cols = [ 0 => 'sales_doc_type', 2 => 'billing_date', 11 => 'material', 25 => 'gross_amount', 45 => 'sales_office', 47 => 'plant' ]; $import_data = []; while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } fclose($fp); ################################################################################ # ADD RECS FROM ARRAY TO TEMP TABLE - 2000 AT A TIME # ################################################################################ $chunks = array_chunk($import_data, 2000); foreach ($chunks as $ch) { $sql = "INSERT INTO tempdata (sales_doc_type, billing_date, material, gross_amount, sales_office, plant) VALUES " . join(',', $ch); $pdo->exec($sql); } ###################################################################################################################### # # # Now we have the data from the csv stored in a temporary staging table # # which makes it easy to manipulate the data with queries to update our # # database tables with efficient queries rather than running multiple # # queries inside loops. # # # # NOTE - as I never received a reply to my question about the origin of the # # data used toupdate the members table I have had to omit that part # # of the processing. The UPDATE query will be similar to the INSERT # # below except it will update where there is a matching record # # # ###################################################################################################################### ################################################################################ # INSERT TEMPDATA REC INTO billing WHERE NO MATCHING billing_date IN members # ################################################################################ $pdo->exec("INSERT INTO billing (sales_doc_type, billing_date, gross_amount, sales_office, plant, material) SELECT t.sales_doc_type , t.billing_date , t.gross_amount , t.sales_office , t.plant , t.material FROM tempdata t LEFT JOIN members m ON DATE(t.billing_date) = m.billing_date WHERE m.billing_date IS NULL ");

The posted code works for me too once I add divs with the required ids

Could be "jsdelivr.net" have stopped support for remote connection to it. Try to find an alternative source.

In the members update query ... UPDATE members SET first_name = '$first_name', last_name = '$last_name', email = '$email', phone = '$phone', status = '$status', modified = NOW() WHERE email = '$email'; where do the variables ($first_name, $last_name , $email, $phone, $status, $modified) come from?