Leaderboard

You are grouping by jobId so you will get one row for each job. The max(quoteId) should be OK (as that is a aggregation) and also jobName and clientId from the job record, but the remaining values (like version and currency) could be arbitrarily selected from from any quote record associated with jobId.

the issue of any external, unknown, dynamic value being output on a web page, possibly containing html special characters that would break the html syntax, should be handled by applying htmlentities, with the ENT_QUOTES flag, to value when it is output. this will allow any single-quote, double-quote, <, >, or & in the actual value to work. they will be converted, by the browser, back to the actual literal character when the value is submitted.

no. do not update quantities or delete data to accomplish this. databases are for recording information. by updating/deleting data, you lose an audit trail that would let you know if a programming mistake, an accidental key was pressed, or nefarious activity changed a value. you would INSERT data for every order/transaction that affects a value. a sale would insert a row into an order/transaction table with a 'type' indicating it is for a sale, then insert row(s) into an order_item table for each item that was sold with the order_id, item_id, and quantity. to void a sale, you would insert another row into the order/transaction table with a 'type' indicating it is for a void/return, with a reference to the original order_id, then insert row(s) into the order_item table, with a negative quantity for the items that are returned and will be restocked (some of the items might have been kept, some might have been damaged, and won't be restocked.) to get the total quantity you would just SUM() the quantities per item_id.

Have you considered a common format such as ".csv" file EG First_Name,Last_Name,Sex,Fruits Scott,Chegg,male,"orange, berry" Laura,Norder,female,"apple, orange" code if($_SERVER['REQUEST_METHOD']=='POST'){ if (!file_exists('file.txt')) { file_put_contents('file.txt', "First_Name,Last_Name,Sex, Fruits\n"); // write header line } $file=fopen("file.txt", "a"); $record = [$_POST['firstName'], $_POST['lastName'], $_POST['sex'], join(',', $_POST['fruit'])]; fputcsv($file, $record); }

or... $res = $pdo->query("SELECT `option`, total FROM vote"); $data = $res->fetchAll(); $votes_cast = array_sum( array_column($data, 'total') ); foreach ($data as $r) { printf ("%s has %d votes (%0.1f %%)<br>", $r['option'], $r['total'], $r['total']*100/$votes_cast); }

I've been trawling my archive and found a sample pagination script from a few years ago. Outputs... Code (There is code at the end of the script to create the test data if required) <?php /* PDO CONNECTION *********************************************/ $host = 'localhost'; $username = '????'; $password = '????'; $database = 'jointute'; $dsn = "mysql:dbname=$database; host=$host; charset=utf8"; $db = new pdo($dsn, $username, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); /***************************************************************/ const PERPAGE = 2; $res = $db->query("SELECT COUNT(*) FROM pupil "); $total = $res->fetchColumn(); $page = $_GET['page'] ?? 1; $stmt = $db->prepare("SELECT fname , lname , DATE_FORMAT(dob, '%b %D') as birthday FROM pupil ORDER BY MONTH(dob), DAY(dob) LIMIT ?,? "); $stmt->execute( [ ($page-1) * PERPAGE, PERPAGE ]); $output = ''; foreach ($stmt as $rec) { $output .= "<div class='pupil'> <div class='label'>Name:</div> {$rec['fname']} {$rec['lname']}<br> <div class='label'>Birthday:</div> {$rec['birthday']} </div>\n"; } /************************************************************************************** * function to output page selection buttons * * @param int $total total records * @param int $page current page number * @return string selection buttons html */ function page_selector($total, $page) { if ($total==0) { return ''; } $kPages = ceil($total/PERPAGE); $filler = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;'; $lim1 = max(1, $page-2); $lim2 = min($kPages, $page+3); $p = $page==1 ? 1 : $page - 1; $n = $page== $kPages ? $kPages : $page + 1;; $out = "$kPages page" . ($kPages==1 ? '' : 's') . " &emsp;"; if ($kPages==1) { return $out; } $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : ''; if ($page > 4) { $out .= "<div class='pagipage' data-pn='1'>1</div> $filler"; } elseif ($page==4) { $out .= "<div class='pagipage' data-pn='1'>1</div>"; } for ($i=$lim1; $i<=$lim2; $i++) { if ($page==$i) $out .= "<div class='pagicurrent'>$i</div>"; else $out .= "<div class='pagipage' data-pn='$i'>$i</div>"; } if ($page < $kPages-3) { $out .= "$filler <div class='pagipage' data-pn='$kPages'>$kPages</div>"; } $out .= $page < $kPages ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : ''; return $out; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)"> <title>Pagination sample</title> <meta name="author" content="Barand"> <meta name="creation-date" content="07/02/2018"> <style type="text/css"> body { font-family: verdana; font-size: 10pt; } #title { font-size: 16pt; background-color: #369; color: #FFF; text-align: center; padding: 10px; margin-bottom: 40px; } .pagipage { display: inline; width: 25px; height: 15px; padding: 3px 5px; text-align: center; font-size: 9pt; border: 1px solid #3C9DBA ; color: #3C9DBA; background-color: #FFF; cursor: pointer; margin-left: -1px; } .pagipage:hover { background-color: #3C9DBA; border-color: #F0F; color: white; } .pagicurrent { display: inline; width: 25px; height: 15px; text-align: center; font-size: 9pt; font-weight: 600; border: 1px solid #3C9DBA; background-color: #3C9DBA; color: white; padding: 3px 5px; } .paginate_panel { text-align: center; margin: 20px 0; width: 100%; color: #3C9DBA; } .pupil { width: 300px; padding: 10px; margin-top: 5px; margin-left: auto; margin-right: auto; border: 1px solid gray; } .label { width: 100px; font-weight: 600; display: inline-block; } </style> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type="text/javascript"> $().ready( function() { $(".pagipage").click( function() { $("#page").val( $(this).data("pn") ); $("#form1").submit(); }) }) </script> </head> <body> <div id='title'> Pagination Sample </div> <form id='form1'> <input type="hidden" name="page" id="page" value="0"> </form> <?=$output?> <div class="paginate_panel"> <?=page_selector($total, $page)?> </div> <!-- DATA --- CREATE TABLE `pupil` ( `pupilID` int(10) unsigned NOT NULL AUTO_INCREMENT, `fname` varchar(45) NOT NULL, `lname` varchar(45) NOT NULL, `houseID` int(10) unsigned NOT NULL DEFAULT '1', `classid` char(1) NOT NULL DEFAULT 'A', `dob` date DEFAULT NULL, PRIMARY KEY (`pupilID`), KEY `house` (`houseID`), KEY `idx_pupil_classid` (`classid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `pupil` VALUES (1,'Adam','Simms',1,'A','2001-06-22'), (2,'Allan','Blair',2,'B','2001-03-04'), (3,'Anna','Hamilton',4,'B','2002-01-16'), (4,'Anne','Bailey',3,'D','2001-08-02'), (5,'Anthony','Bell',2,'E','2001-10-01'), (6,'Caroline','Freeman',2,'F','2000-12-13'), (7,'David','Powell',1,'A','2001-05-03'), (8,'Emma','Watson',4,'C','2001-11-20'), (9,'George','Wilson',1,'C','2001-06-30'), (10,'Henry','Irving',4,'D','2001-08-12'), (11,'Jane','Morrison',1,'E','2001-08-24'), (12,'John','Patterson',3,'F','2001-09-06'), (13,'John','Tully',3,'A','2001-09-03'), (14,'John','Watson',2,'B','2001-09-30'), (15,'Jack','Williams',2,'D','2001-09-08'), (16,'Margaret','Norton',4,'D','2001-04-23'), (17,'Mary','Blake',4,'E','2001-10-04'), (18,'Mary','Sheldon',3,'F','2001-06-14'), (19,'Mary','Whitehouse',2,'A','2001-09-06'), (20,'Michael','Grove',3,'B','2001-08-11'), (21,'Peter','Adamson',1,'C','2001-09-18'), (22,'Peter','Appleby',3,'D','2001-04-26'), (23,'Wayne','Jones',1,'E','2001-05-06'), (24,'William','Smith',4,'F','2001-12-08'); --> </body> </html>

Here's my attempt. This the data I used. Some of my column names may differ from yours, so adjust the queries accordingly. attendance member service +-----------+-----------+ +----------+-------------+ +--------------+-------------+ | Field | Type | | Field | Type | | Field | Type | +-----------+-----------+ +----------+-------------+ +--------------+-------------+ | atid | int(11) | | memid | int(11) | | serid | int(11) | | memid | int(11) | | mem_name | varchar(45) | | service_name | varchar(45) | | serid | int(11) | | group_no | int(11) | | service_code | varchar(3) | | entrydate | timestamp | +----------+-------------+ +--------------+-------------+ | createdat | timestamp | | updatedat | timestamp | | viewfrom | int(11) | | astatus | int(11) | | stype | int(11) | +-----------+-----------+ Output Code <?php include 'db_inc.php'; ### CREATE YOUR OWN $pdo = pdoConnect('test'); ### PDO CONNCTION HERE $month = $_GET['month'] ?? date('n')-1; $year = $_GET['year'] ?? date('Y'); $day1 = "{$year}-{$month}-01"; $dt = new DateTime($day1); $monthname = $dt->format('F'); ################################################################################ # # # BUILD REPORT HEADINGS FOR SELECTED MONTH # # # ################################################################################ $res = $pdo->prepare("SELECT DISTINCT weekofyear(entrydate) - weekofyear(?) + 1 as wk_no , date_format(entrydate, '%e/%c') as edate , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser , service_code FROM attendance JOIN service s USING (serid) WHERE month(entrydate) = ? ORDER BY entrydate, serid "); $res->execute([ $day1, $month ]); $rows = $res->fetchAll(); $empty = array_fill_keys(array_column($rows, 'dayser'), [ 'wk'=>0, 'att'=>0 ]); $hdata = []; foreach ($rows as $r) { if (!isset($hdata[$r['wk_no']])) { $hdata[$r['wk_no']] = [ 'dates' => [], 'servs' => [] ]; } $hdata[$r['wk_no']]['dates'][] = $r['edate']; $hdata[$r['wk_no']]['servs'][] = $r['service_code']; $empty[$r['dayser']]['wk'] = $r['wk_no']; } $thead = "<tr><th rowspan='3' class='namecol'>Name</th>"; foreach ($hdata as $w => $wdata) { $cs = count($wdata['dates']); $cls = $w % 2 ? 'oddwk' : ''; $thead .= "<th class='$cls' colspan='$cs'>WEEK $w</th>"; } $thead .= "</tr>\n<tr>"; foreach ($hdata as $w => $wdata) { foreach ($wdata['dates'] as $d) { $cls = $w % 2 ? 'oddwk' : ''; $thead .= "<th class='$cls'>$d</th>"; } } $thead .= "</tr>\n<tr>"; foreach ($hdata as $w => $wdata) { foreach ($wdata['servs'] as $d) { $cls = $w % 2 ? 'oddgrn' : 'evengrn'; $thead .= "<th class='$cls'>$d</th>"; } } $thead .= "</tr>\n"; ################################################################################ # # # BUILD DATA ROWS FOR SELECTED MONTH # # # ################################################################################ $res = $pdo->prepare("SELECT weekofyear(entrydate) - weekofyear(?) + 1 as wk_no , concat(date_format(entrydate, '%d'), ' ', service_code) as dayser , group_no , mem_name , memid FROM attendance a JOIN member m USING (memid) JOIN service s USING (serid) WHERE month(entrydate) = ? ORDER BY group_no, mem_name, dayser "); $res->execute([ $day1, $month ]); $mdata = []; foreach ($res as $r) { if (!isset($mdata[$r['group_no']][$r['mem_name']])) { $mdata[$r['group_no']][$r['mem_name']] = $empty; } $mdata[$r['group_no']][$r['mem_name']][$r['dayser']]['att'] = 1; } $tdata = ''; foreach ($mdata as $grp => $mems) { $cs = count($empty); $tdata .= "<tr><td class='w3-brown'>Group $grp</td> <td colspan='$cs'>&nbsp;</td> </tr> "; foreach ($mems as $mname => $attend) { $tdata .= "<tr><td>$mname</td>"; $prevwk = 0; foreach ($attend as $a) { $bar = $a['wk'] != $prevwk ? 'w3-leftbar' : ''; $prevwk = $a['wk']; $cls = $bar; if ($a['att']) { $clr = $a['wk'] % 2 ? 'oddgrn' : 'evengrn'; $cls .= " check $clr"; $tdata .= "<td class='$cls'>&check;</td>"; } else $tdata .= "<td class='$cls'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Attendances</title> <link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } caption { text-transform: uppercase; font-size: 14pt; } th { padding: 4px 0; width: 50px; } td { padding: 4px; } th.oddwk { background-color: #eee; } th.namecol { width: 150px; } .oddgrn { background-color: #80ff80; } .evengrn { background-color: #b3ffb3; } .check { font-weight: 600; text-align: center; } </style> </head> <body> <header class='w3-dark-gray w3-padding'> <h1>Attendance</h1> </header> <form class='w3-bar w3-light-gray w3-padding'> <label class='w3-bar-item'>Year </label> <input type='number' class='w3-bar-item' name='year' value='<?=$year?>'> <label class='w3-bar-item'>Month</label> <input type='number' class='w3-bar-item' name='month' value='<?=$month?>'> <button class='w3-button w3-bar-item w3-indigo'>Search</button> </form> <div class='w3-content w3-margin-top'> <table border='1' class='w3-small'> <caption class='w3-light-green w3-padding w3-center'><?=$monthname?></caption> <?=$thead?> <?=$tdata?> </table> </div> </body> </html>