Barand

I agree with Gizmola^.
Using a multidimensional array ...
$probs = [ 'QB' => [ 'RB' => [ 1 => 11.22, 2 => 3.91, 3 => 0.15 ], 'TE' => [ 1 => 5.17, 2 => 0.80, 3 => 0.00 ], 'QB' => [ 1 => 9.44, 2 => 0.00, 3 => 0.00 ], 'WR' => [ 1 => 10.46, 2 => 8.67, 3 => 4.53 ], 'K' => [ 1 => 3.81, 2 => 0.19, 3 => 0.00 ], 'D' => [ 1 => 2.85, 2 => 0.07, 3 => 0.00 ] ] // rinse and repeat with the other MVPs and FLEXs ]; $lineup = 'QB, WR, WR, RB, K'; $arr = array_map('trim', explode(',', $lineup)); $mvp = array_shift($arr); $counts = array_count_values($arr); $probability = 0; foreach ($counts as $p => $n) { $probability += $probs[$mvp][$p][$n]; } echo '<br>'.$probability; // 23.7  

ORDER BY level IS NULL, level

Whe you join a table with M records to a table with N matching records you get M*N records returned. Therefore you are now totalling Nx more records than before.
You need to ensure you join 1 row from 1 tablw with 1 matching record in the other. You can do this with subqueries to get the individal tables' totals.
SELECT t1.customer_id , t2.total_sales , t3.amt_paid FROM customers t1 LEFT JOIN ( SELECT customer_id , SUM(total_price) as total_sales FROM tbl_sales GROUP BY customer_id ) t2 USING (customer_id) LEFT JOIN ( SELECT customer_id , SUM(amt_paid) as amt_paid FROM tbl_sales_total WHERE status = 0 GROUP BY customer_id ) t3 USING (customer_id) WHERE t1.customer_id = 489639 Also, you have a LEFT JOIN to tbl_sales_total t3. You therefore cannot use WHERE t3.status = 0. That condition needs to go into the join condition otherwise the query behaves as though it were an ordinary (INNER) JOIN

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 | +----+----------+-----------+  

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 ");  

perhaps
select i.name as itemName, qs.name as sectionName, i.id as itemId, i.GBP, i.USD, i.CAD, cb.charge_by, COUNT(cp.item_id) > 0 as icConsumable from items i inner join quote_sections qs on i.section_id=qs.id inner join charge_by cb on i.charge_by_id = cb.id left join consumable_price cp ON i.id = cp.item_id group by i.id union select ci.name as itemName, qs.name as sectionName, concat("CI", ci.id) as itemId, ci.price as GBP, ci.price as USD, ci.price as CAD, cb.charge_by, 0 as isConsumable from custom_item ci inner join quote_sections qs on ci.section_id=qs.id inner join charge_by cb on ci.charge_by = cb.id  

Something like this...
+---------------+ | invoice_item | +---------------+ | id | | prod_id |------+ | quantity | | +----------+ |---------------+ | | price | | +----------+ +-----<| prod_id | | minqty | | maxqty | | usd | | cad | | eur | +----------+ SELECT i.prod_id , p.product_name , pr.usd , pr.cadd , pr.eur FROM invoice_item i JOIN product p ON i.prod_id = p.id JOIN price pr ON i.prod_id = pr.prod_id AND i.quantity BETWEEN pr.minqty AND pr.maxqty  

Alternatively
$bookquery = array_filter($bookquery, fn($v)=>in_array($v['room_id'], $reservedrooms)); $totalprice = array_sum(array_column($bookquery, 'price'));  

Just using the single available table here...
SELECT dr.id , device_id , status_id , action_time FROM deployment_register dr JOIN ( SELECT device_id , MAX(action_time) as action_time FROM deployment_register WHERE status_id IN (2, 5) GROUP BY device_id ) latest USING (device_id, action_time); +----+-----------+-----------+---------------------+ | id | device_id | status_id | action_time | +----+-----------+-----------+---------------------+ | 7 | 3 | 5 | 2023-10-07 21:06:45 | +----+-----------+-----------+---------------------+ Subquery to find latest row for each device then join against that.

code
$data = file('data.txt', FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES); usort($data, fn($a, $b)=>substr($a,11,2)<=>substr($b,11,2)); // sort by code number $codes = []; foreach ($data as $d) { $p = strpos($d, ')'); $k = substr($d, 11, $p-10); if (!isset($codes[$k])) { $codes[$k] = 1; } else { $codes[$k]++; } } echo (join("<br>", $data)); echo ("<h3>Total clicks</h3>"); foreach ($codes as $code => $total) { printf("%s Total clicks = %d<br>", $code, $total); } output
15/09/2023 03 Resist the Curse (Resiste la Maldición) 11-12-2022.mp3 Finland Helsinki Uusimaa 18/09/2023 03 Resist the Curse (Resiste la Maldición) 11-12-2022.mp3 Finland Helsinki Uusimaa 21/09/2023 03 Resist the Curse (Resiste la Maldición) 11-12-2022.mp3 Germany Gera Thuringia 14/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United States Edmore Michigan 14/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Alhaurin de la Torre Malaga 14/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Alhaurin de la Torre Malaga 14/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United States Cedar Springs Michigan 15/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United States Cedar Springs Michigan 15/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Madrid Madrid 15/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United States Cedar Springs Michigan 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Nerja Malaga 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Nerja Malaga 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 Spain Nerja Malaga 16/09/2023 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) 10-9-2023.mp3 United Kingdom Sheffield Sheffield 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 Spain Nerja Malaga 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 Spain Cartagena Murcia 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Chesaning Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Chesaning Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Chesaning Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Chesaning Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Edmore Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Edmore Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Edmore Michigan 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Edmore Michigan 21/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 Spain Nerja Malaga 22/09/2023 05 Day of Atonement 2023 (Día de la Expiación) 17-9-2023.mp3 United States Garden City New York 21/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 Norway Sira Agder 08/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United Kingdom Sheffield Sheffield 08/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United Kingdom Sheffield Sheffield 08/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United Kingdom Sheffield Sheffield 08/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 Spain Muro del Alcoy Alicante 08/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Cedar Springs Michigan 09/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Cedar Springs Michigan 09/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Cedar Springs Michigan 09/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Cedar Springs Michigan 09/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Edmore Michigan 10/09/2023 19 The Bread of Life (El Pan de la Vida) 27-8-2023.mp3 United States Cedar Springs Michigan 11/09/2023 20 The Legacy of Jesus (El Legado de Jesús)20-8-2023.mp3 United States Ashburn Virginia 15/09/2023 20 Be With Me Where I Am (Está Conmigo Donde Estoy)20-8-2017.mp3 Finland Helsinki Uusimaa 18/09/2023 20 Be With Me Where I Am (Está Conmigo Donde Estoy)20-8-2017.mp3 Finland Helsinki Uusimaa 21/09/2023 20 Be With Me Where I Am (Está Conmigo Donde Estoy)20-8-2017.mp3 Germany Gera Thuringia Total clicks 03 Resist the Curse (Resiste la Maldición) Total clicks = 3 04 Feast of Trumpets 2023 (Fiesta de las Trompetas) Total clicks = 19 05 Day of Atonement 2023 (Día de la Expiación) Total clicks = 12 19 The Bread of Life (El Pan de la Vida) Total clicks = 11 20 The Legacy of Jesus (El Legado de Jesús) Total clicks = 1 20 Be With Me Where I Am (Está Conmigo Donde Estoy) Total clicks = 3  

You need to a way for your js function to specify which popup to show.
One way would be to pass the room id in the function call
onclick = "myFunction(xx)"
and to append the room id to your ids to make them unique
id="mypopupxx"
 

Another way to it is to forget about row_number() OVER etc and, instead, use the simpler
SELECT ... , substring_index(GROUP_CONCAT(f.description separator ' &bull; '), ' &bull; ', 5) as rmfac to get the whole list of facilies then select the first 5

try
foreach ($res as $r) { if (!isset($data[$r['subjectname']])) { $data[$r['subjectname']]['students'] = []; } $position = ordinal($r['rank']); $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ]; }  

Sorry, I forgot to explain your probems
There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270") For find_in_set() to work, the values should be comma-separated, not comma-space-separated.

If you use prepared queries correctly, with placeholders for user-supplied data, you don't need that code - the values will not be embedded in the SQL code.

When you use an agregation function (like SUM() or GROUP_CONCAT() ) without a GROUP BY clause, you get a single aggregation for the whole table.

Do I take it that a new image is one that is less than 2 weeks old?
$day = date('Y-m-d', strtotime("2023-08-30 16:03:32")); if($day > date('Y-m-d', strtotime('-2 weeks'))){ echo "img src='https://i.imgur.com/0rlIlUD.gif'"; } @requinix - I see what you mean

You don't need any data from the booking table so the last join can go.
At present your query does not know which rooms are booked, and when, as there is no join between room and room_booking tables. Your join to the room_booking table should therefore be ON r.room_id = rb.room_id
How do you know what facilities a room has? There is no room_id in the room_facility table. Instead you have a size id???
Your SELECT has no room_id or room_number so how will you identify the rooms in the results?

For the "[0]" to be needed then the json data would need to be
[{ "id": 1, "item_id": 19, "min_qty": 1, "max_qty": 499, "GBP": 500, "USD": 750, "CAD": 875 }] with the outer [..] to make it an array.

One way is not to turn off the error reporting...
 
 

Do your database , table and db connection all share the same utf8 encoding?

When you send an ajax request to a page, all output then goes into the response that is returned. (ie what you would see if you called that page in your browser is the reponse (which you can use to your advantage to test ajax responders).
Remove the location header from the responder code and do the redirect in the calling page when you receive a succesful response.

password_hash() and password_verify()

Start with
SELECT q.id , q.version FROM quote q JOIN ( SELECT client_id , max(version) as version FROM quote WHERE client_id = 15 ) latest USING (client_id, version); +----+---------+ | id | version | +----+---------+ | 71 | 6 | +----+---------+ Now you know that for client #15 the latest quote has id = 71 and its version is 6.
Duplicate quote #71, giving the dupe a verion of 7, and duplicate the items for #71 with the quote_id of the new duped quote.

Something like this? ...
 
SELECT j.id as job_id , j.name , count(q.id) as quotes FROM job j LEFT JOIN quote q ON j.id = q.job_id GROUP BY j.id