Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,607

  • Joined

  • Last visited

  • Days Won

    831

 Content Type 

Everything posted by Barand

  1. Barand
    With your new data... mysql> SELECT reps.rep_name -> , COALESCE(p41Sales.totalrum, 0) as totalrum -> , COALESCE(allSales.total, 0) as total -> , COALESCE(larimarSales.totallarimar, 0) as totallarimar -> FROM reps -> INNER JOIN ( -> SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total -> FROM `sales` -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') -> GROUP BY reps.rep_id -> ) -> allSales ON allSales.rep_id = reps.rep_id -> -> LEFT JOIN ( -> SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum -> FROM `sales` -> JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41' -> GROUP BY reps.rep_name -> ) -> p41Sales ON p41Sales.rep_id = reps.rep_id -> -> LEFT JOIN ( -> SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar -> FROM `sales_larimar` -> JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 -> WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15') -> GROUP BY reps.rep_id -> ) -> larimarSales ON larimarSales.rep_id = reps.rep_id; +---------------+----------+-------+--------------+ | rep_name | totalrum | total | totallarimar | +---------------+----------+-------+--------------+ | Julio SIL* | 44 | 724 | 787 | | Gaby SIL* | 37 | 563 | 648 | | Hans SIL* | 327 | 1629 | 211 | | Carlos SIL | 32 | 214 | 100 | | Smerling SIL* | 28 | 90 | 0 | +---------------+----------+-------+--------------+ Is that what you expect to see?
  2. Barand
    Can we see your table structure and example data?
  3. Barand
    This is my version. All code is in same file (ie the form calls itself to process the updates) <?php require 'db_inc.php'; // USE YOUR OWN $conn = myConnect('db2'); // CONNECTION CODE ################################################################################ ## UNCOMMENT THIS SECTION TO RECREATE TEST DATA # ################################################################################ /* $conn->query("drop table if exists test"); $conn->query("create table test ( id int not null primary key, descrip varchar(20) ) "); for ($i=1; $i<=10; $i++) { $conn->query("insert into test (id, descrip) values ($i, 'Record $i') "); } */ ################################################################################ ## HANDLE POSTED DATA TO DELETE SELECTED RECORDS # ################################################################################ if ($_SERVER['REQUEST_METHOD'] == 'POST') { if (isset($_POST['del_id'])) { $res = $conn->prepare("DELETE FROM test WHERE id = ? "); $res->bind_param('i', $id); foreach ($_POST['del_id'] as $id) { $res->execute(); } } header("Location: ?"); exit; } ################################################################################ ## GET REMAINING RECORDS AND DISPLAY # ################################################################################ $tdata = ''; $res = $conn->query("SELECT id , descrip FROM test ORDER BY id "); foreach ($res as $r) { $tdata .= "<tr><td>{$r['id']}</td> <td>{$r['descrip']}</td> <td><input type='checkbox' name='del_id[]' value='{$r['id']}'></td> </tr> "; } ?> <!DOCTYPE=html> <html lang="en"> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> th, td { padding: 4px 20px; } th { background-color: black; color: white; } </style> </head> <body> <form method='post'> <table border='1'> <tr><th>ID</th><th>Description</th><th>Select</th></tr> <?=$tdata?> </table> <br> <input type='submit' value='Delete Selected'><br> </form> </body> </html>
  4. Barand
    The only conclusion I can come to, if $res is not a traversable PDOStatement object, is that query() failed and returned false
  5. Barand
    Works OK for me, but try putting $data = []; at start of the code.
  6. Barand
    Then change $pdo in my code to $conn (as that is your db handle)
  7. Barand
    Changing to tour_operator_id = 5 (for consistency with othe queries, plus my reps table has no reps with "12") gives mysql> SELECT reps.rep_name -> , COALESCE(p41Sales.totalrum, 0) as totalrum -> , COALESCE(allSales.total, 0) as total -> , COALESCE(larimarSales.totallarimar, 0) as totallarimar -> FROM reps -> INNER JOIN ( -> SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total -> FROM `sales` -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') -> GROUP BY reps.rep_id -> ) -> allSales ON allSales.rep_id = reps.rep_id -> -> LEFT JOIN ( -> SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum -> FROM `sales` -> JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41' -> GROUP BY reps.rep_name -> ) -> p41Sales ON p41Sales.rep_id = reps.rep_id -> -> LEFT JOIN ( -> SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar -> FROM `sales_larimar` -> JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 -> WHERE (sales_larimar.sales_date BETWEEN '2023-05-01' AND '2023-05-15') -> GROUP BY reps.rep_id -> ) -> larimarSales ON larimarSales.rep_id = reps.rep_id; +----------+----------+-------+--------------+ | rep_name | totalrum | total | totallarimar | +----------+----------+-------+--------------+ | Cely | 76 | 206 | 104 | | Heike | 192 | 502 | 237 | +----------+----------+-------+--------------+ Have your reps for operator 12 sold any product 41s? BTW, added the COALESCE()s for you.
  8. Barand
    I don'tunderstand your latest problem. That posted query gives mysql> SELECT reps.rep_name, p41Sales.totalrum, allSales.total -> FROM reps -> INNER JOIN -> ( -> SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total -> FROM `sales` -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') -> GROUP BY reps.rep_id -> ) -> allSales ON allSales.rep_id = reps.rep_id -> LEFT JOIN -> ( -> SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum -> FROM `sales` -> JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr -> JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 -> WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41' -> GROUP BY reps.rep_name -> ) -> p41Sales ON p41Sales.rep_id = reps.rep_id; +----------+----------+-------+ | rep_name | totalrum | total | +----------+----------+-------+ | Cely | 76 | 206 | | Heike | 192 | 502 | +----------+----------+-------+ Those are the results you said you should get.
  9. Barand
    Functions with names beginning "mysql_" no longer exist in PHP (since 2015 when version 7.0 was released). You need to use either mysqli PDO (recommended)
  10. Barand
    The 416 appears to be correct. mysql> SELECT reps.rep_id -> , SUM(sales_totaldollars) as totaldollars -> FROM `sales` -> JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr -> AND salesdetails.salesdetails_productid = '41' -> JOIN reps ON reps.rep_id = sales.sales_repid -> AND reps.rep_touroperator_id = '5' -> AND reps.rep_active = 1 -> WHERE sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15' -> GROUP BY rep_id; +--------+--------------+ | rep_id | totaldollars | +--------+--------------+ | 77 | 206 | | 259 | 416 | +--------+--------------+ Why do you think it should be 502?
  11. Barand
    Which set of data gave you result you posted... Was it the data in your picture, or the data in those sql files I created data by typing from the pictures (not a chore I enjoy) but on running your query my results matched neither of those posted - expected/actual
  12. Barand
    Add ".txt" to the end of the filenames and attach the files or copy/paste the contents in code blocks (<> button) as you would php code.
  13. Barand
    The point is - don't post pictures of data (or code). They are a much use as chocolate teapots. For data, the preferred format is a data export dump, to make life easier for those trying to help you.
  14. Barand
    Better. Now tell me how you expect me to to load the image of your data into my database to test your query!
  15. Barand
    define combined. We haven't a clue what you are tryng to do, or what you expect, and in which way it isn't doing what you expect. Without information we cannot help. Your test data too would be good.
  16. Barand
    Your main problem is that you create a new form for each record and all these form have the same id "list-check-box". When you click submit, which one of these forms will be submitted? Id's must be unique.
  17. Barand
    You need to join on the rep ids SELECT reps.rep_name, sum(sales.sales_totaldollars) as total FROM `sales` JOIN reps on reps.rep_id = sales.sales_repid WHERE rep_touroperatorid = '5' AND (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_name
  18. Barand
    This works with Firefox <script type='text/javascript'> $(function() { $(".circ3").mouseover(function() { $(this).css("fill", "red") $(this).css("fill-opacity", "1") $("#grp1").removeClass('txt1').addClass('txt2') }) $(".circ3").mouseout(function() { $(this).css("fill", "blue") $(this).css("fill-opacity", "0.3") $("#grp1").removeClass('txt2').addClass('txt1') }) }) </script> <style type='text/css'> .sv { stroke:#aa1111; border-radius:20px; border-width:2px; border-color:#ccc; box-shadow:2px 2px darkgray; background-blend-mode: normal; } .sv:hover { background-color: #ccffcc; fill:black; width : border-width:2px; border-color:red; background-blend-mode: difference; /*filter: blur(0.5px) grayscale(2%);*/ } .txt1 { fill: white; } .txt2 { fill: black; } </style> <svg class="sv" width="200" height="400" xmlns="http://www.w3.org/2000/svg"> <circle x="0" y="0" cx="120" cy="-20" r="150" style="fill:darkgreen;fill-opacity:0.8;stroke:darkgreen;stroke-width:0" /> <text x="50" y="160" font-size="20px" font-family="monospace" font-style="italic"> TRIAL <tspan x="10" y="185">BANNER</tspan> </text> <circle x="20" y="200" cx="10" cy="480" r="250" style="fill:darkgreen;fill-opacity:0.8;stroke:darkgreen;stroke-width:0" /> <circle x="60" y="230" cx="40" cy="460" r="250" style="fill:blue;fill-opacity:.3;stroke:blue;stroke-width:0" class='circ3' /> <g id='grp1' class='txt1'> <!-- In the context of SVG embedded in an HTML document, the XHTML namespace could be omitted, but it is mandatory in the context of an SVG document --> <text x='10' y='310' >having a div inside the</text> <text x='10' y='330' >svg might be the only</text> <text x='10' y='350' >way to do this correctly,</text> <text x='10' y='370' >time permitting</text> </g> </svg>
  19. Barand
    try $res = $pdo->query("SELECT storeid , description FROM merge ORDER BY storeid "); foreach ($res as $r) { $data[$r['storeid']][] = $r['description']; } echo "<table>\n"; foreach ($data as $store =>$prods) { echo "<tr style='vertical-align: top;'><td>$store</td><td>" . join('<br>', $prods) . "</td></tr>\n"; } echo "</table>\n";
  20. Barand
    The only place that the code has a chance of running is in your own environment. Are you expecting everyone to have a database with your tables and table structures, to name just one obstacle?
  21. Barand
    Don't you think it could be the output from print_r()?
  22. Barand
    Try echo '<pre>' . print_r($row, true) . '</pre>'; to see what indexes $row does have. (Note - they are case-sensitive)
  23. Barand
    So, problem solved then.
  24. Barand
    TLDR Put code in a code element (or two or more if there are separate code elements) when posting - use the code button () Tell us what symptoms you get. What is happening or not happening? Limit the code you post to the bits that are relevant - no one is going to wade through all that.
  25. Barand
    Does your bandstage table have a record with 281 in the id column?
×
×
  • 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.