Jump to content

Barand

Moderators
  • Posts

    24,072
  • Joined

  • Last visited

  • Days Won

    752

Community Answers

  1. Barand's post in Assistance thinking this through. was marked as the answer   
    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  
  2. Barand's post in Sorting issue... was marked as the answer   
    ORDER BY level IS NULL, level
  3. Barand's post in What could be wrong was marked as the answer   
    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
  4. Barand's post in PHP Get Maximum Value From DB and Set Start Value for Loop was marked as the answer   
    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 | +----+----------+-----------+  
  5. Barand's post in Import data from excel to databse was marked as the answer   
    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 ");  
  6. Barand's post in Union Select was marked as the answer   
    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  
  7. Barand's post in (Advice) - Storing price break points in database was marked as the answer   
    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  
  8. Barand's post in How to calculate price of 1 or more hotel rooms in php using arrays was marked as the answer   
    Alternatively
    $bookquery = array_filter($bookquery, fn($v)=>in_array($v['room_id'], $reservedrooms)); $totalprice = array_sum(array_column($bookquery, 'price'));  
  9. Barand's post in Select statement selecting wrong data was marked as the answer   
    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.
  10. Barand's post in php count lines in a text file with a specific variable was marked as the answer   
    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  
  11. Barand's post in Problem displaying database attribute inside a javascript popup was marked as the answer   
    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"
     
  12. Barand's post in Using row_number on attribute to only return 5 rows was marked as the answer   
    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
  13. Barand's post in How can I generate Class Position? was marked as the answer   
    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 ]; }  
  14. Barand's post in Not getting the needed values was marked as the answer   
    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.
  15. Barand's post in Any need of this SQLi filtering rules? was marked as the answer   
    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.
  16. Barand's post in Group Concat code not working as expected - and only showing 1 result was marked as the answer   
    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.
  17. Barand's post in "New posts" was marked as the answer   
    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
  18. Barand's post in SQL statement with multiple joins - unknown column error was marked as the answer   
    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?
  19. Barand's post in Access single array element was marked as the answer   
    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.
  20. Barand's post in How to make visible of unvisible error while inserting data into mariadb database? was marked as the answer   
    One way is not to turn off the error reporting...
     
     
  21. Barand's post in Validating password characters was marked as the answer   
    Do your database , table and db connection all share the same utf8 encoding?
  22. Barand's post in Ajax log on handler wrapping redirected to page in response content was marked as the answer   
    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.
  23. Barand's post in Current password hashing standard was marked as the answer   
    password_hash() and password_verify()
  24. Barand's post in Duplicating Mysql Table rows but also updating some content was marked as the answer   
    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.
  25. Barand's post in Sql statement join question was marked as the answer   
    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  
×
×
  • 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.