-
Posts
22,853 -
Joined
-
Last visited
-
Days Won
657
Community Answers
-
Barand's post in Unknown column in where clause and how to fix it was marked as the answer
If $id contains "Shooter" as a string value it need to be in quotes otherwise SQL thinks it is a column name
$sql = "SELECT * FROM games WHERE genre = '$id' "; ^ ^
-
Barand's post in Showing an integer as currency with Jquery was marked as the answer
Does this help?
<!DOCTYPE html> <html lang='en'> <head> <title>sample</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> var prices = { "Item A":12500, "Item B":9500, "Item C":99, "Item D":9900 } function listPrices() { $.each( prices, function(k, v) { price = v/100 let item = $("<li>", { "html": k + " : £" + price.toFixed(2)}) $("#price-list").append(item) }) } </script> <style type='text/css'> body { background-color: #fbf7e9; } </style> </head> <body> <button onclick='listPrices()'>Prices</button> <ul id='price-list'> </ul> </body> </html>
-
Barand's post in Maths on dynamically created form was marked as the answer
@Adamhumbug Your code appeared to be trying to calculate all totals in a loop (eg on button click)
<button onclick='calculate()'>Calculate all totals</button> script
function calculate() { $.each($(".completeLine"), function(k, v) { var qty = $(v).find('.itemQty option:selected').val(); var price = $(v).find('.itemSelected option:selected').data('priceuk'); $(v).find('.line-total').val(qty*price); }) }
-
Barand's post in How to treat an XML code in html page correctly? was marked as the answer
Perhaps
echo '<pre>' . htmlentities(' <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ..... </url> <url> ..... </url> <url> ..... </url> <url> ..... </url> </urlset> ') . '</pre>';
-
Barand's post in PDO - Uncaught Error: Call to a member function was marked as the answer
PS
Also, require is not a function
The $pdo variable is created inside the code within your page scope.
-
Barand's post in Getting no data with if($stmt -> fetch()) was marked as the answer
You need to turn the mysql error reporting.
Your function's query gets 3 result columns and binds ony 2 of them.
Also the over number and ballinover values will be meaningless in that query as it uses an aggregation function.
-
Barand's post in Create a pdf from my website was marked as the answer
The most common one, I think, is FPDF
Another one, which is an enhanced version of that one, is TCPDF but I find the documentation a nightmare (the source file itself seems the only place). There are lots of example but it is difficult to know what the arguments for the methods are without a decent reference manual. For example
// Image example with resizing $pdf->Image('images/image_demo.jpg', 15, 140, 75, 113, 'JPG', 'http://www.tcpdf.org', '', true, 150, '', false, false, 1, false, false, false); You then have to dig into the source file to find
/** * Puts an image in the page. * The upper-left corner must be given. * The dimensions can be specified in different ways:<ul> * <li>explicit width and height (expressed in user unit)</li> * <li>one explicit dimension, the other being calculated automatically in order to keep the original proportions</li> * <li>no explicit dimension, in which case the image is put at 72 dpi</li></ul> * Supported formats are JPEG and PNG images whitout GD library and all images supported by GD: GD, GD2, GD2PART, GIF, JPEG, PNG, BMP, XBM, XPM; * The format can be specified explicitly or inferred from the file extension.<br /> * It is possible to put a link on the image.<br /> * Remark: if an image is used several times, only one copy will be embedded in the file.<br /> * @param $file (string) Name of the file containing the image or a '@' character followed by the image data string. To link an image without embedding it on the document, set an asterisk character before the URL (i.e.: '*http://www.example.com/image.jpg'). * @param $x (float) Abscissa of the upper-left corner (LTR) or upper-right corner (RTL). * @param $y (float) Ordinate of the upper-left corner (LTR) or upper-right corner (RTL). * @param $w (float) Width of the image in the page. If not specified or equal to zero, it is automatically calculated. * @param $h (float) Height of the image in the page. If not specified or equal to zero, it is automatically calculated. * @param $type (string) Image format. Possible values are (case insensitive): JPEG and PNG (whitout GD library) and all images supported by GD: GD, GD2, GD2PART, GIF, JPEG, PNG, BMP, XBM, XPM;. If not specified, the type is inferred from the file extension. * @param $link (mixed) URL or identifier returned by AddLink(). * @param $align (string) Indicates the alignment of the pointer next to image insertion relative to image height. The value can be:<ul><li>T: top-right for LTR or top-left for RTL</li><li>M: middle-right for LTR or middle-left for RTL</li><li>B: bottom-right for LTR or bottom-left for RTL</li><li>N: next line</li></ul> * @param $resize (mixed) If true resize (reduce) the image to fit $w and $h (requires GD or ImageMagick library); if false do not resize; if 2 force resize in all cases (upscaling and downscaling). * @param $dpi (int) dot-per-inch resolution used on resize * @param $palign (string) Allows to center or align the image on the current line. Possible values are:<ul><li>L : left align</li><li>C : center</li><li>R : right align</li><li>'' : empty string : left for LTR or right for RTL</li></ul> * @param $ismask (boolean) true if this image is a mask, false otherwise * @param $imgmask (mixed) image object returned by this function or false * @param $border (mixed) Indicates if borders must be drawn around the cell. The value can be a number:<ul><li>0: no border (default)</li><li>1: frame</li></ul> or a string containing some or all of the following characters (in any order):<ul><li>L: left</li><li>T: top</li><li>R: right</li><li>B: bottom</li></ul> or an array of line styles for each border group - for example: array('LTRB' => array('width' => 2, 'cap' => 'butt', 'join' => 'miter', 'dash' => 0, 'color' => array(0, 0, 0))) * @param $fitbox (mixed) If not false scale image dimensions proportionally to fit within the ($w, $h) box. $fitbox can be true or a 2 characters string indicating the image alignment inside the box. The first character indicate the horizontal alignment (L = left, C = center, R = right) the second character indicate the vertical algnment (T = top, M = middle, B = bottom). * @param $hidden (boolean) If true do not display the image. * @param $fitonpage (boolean) If true the image is resized to not exceed page dimensions. * @param $alt (boolean) If true the image will be added as alternative and not directly printed (the ID of the image will be returned). * @param $altimgs (array) Array of alternate images IDs. Each alternative image must be an array with two values: an integer representing the image ID (the value returned by the Image method) and a boolean value to indicate if the image is the default for printing. * @return image information * @public * @since 1.1 */ public function Image($file, $x='', $y='', $w=0, $h=0, $type='', $link='', $align='', $resize=false, $dpi=300, $palign='', $ismask=false, $imgmask=false, $border=0, $fitbox=false, $hidden=false, $fitonpage=false, $alt=false, $altimgs=array()) { At least FPDF has a manual. Luckily that same manual applies largely to TCPDF - although not the extras that TCPDF has. The most useful addition is the tcpdf::WriteHTML($html_string) method which renders html markup as PDF. I haven't used that facility much so I'm not sure how well it uses CSS.
Other improvements are better rendering of non-latin text (eg Arabic) and the ability to embed SVG graphics.
-
Barand's post in Pivot Table or Cross Tab in PHP using MYSQLI for attendance was marked as the answer
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'> </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'>✓</td>"; } else $tdata .= "<td class='$cls'> </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>
-
Barand's post in MySQL update query to automatically Deduct quantity sold from quantity in the stock table was marked as the answer
try
INSERT INTO stock (stock_id, qty) SELECT stock_id , newqty FROM ( SELECT stock_id , @sales := IF(@prev = k.item_id, @sales, sold) as sales , IF(qty <= @sales, 0, qty - @sales) as newqty , @sales := IF(@sales >= qty, @sales - qty, 0) , @prev := k.item_id as item FROM stock k JOIN ( SELECT item_id , sum(quantity) as sold FROM sales GROUP BY item_id ) s ON k.item_id = s.item_id AND qty_type = 'a' JOIN (SELECT @prev:=null, @sales:=0) init ORDER BY k.item_id, stock_id ) calc ON DUPLICATE KEY UPDATE qty = VALUES(qty);
-
Barand's post in What is wrong with my code? was marked as the answer
The error message tells you the query failed. Try adding a space after "LIMIT"
$getQuery = "SELECT * FROM games LIMIT $initial_page, $limit";
-
Barand's post in Foreign key NOT working was marked as the answer
Your create table doesn't show whether they are InnoDb or not. They need to be, MyISAM won't work.
-
Barand's post in using foreach to loop over resultset from fetch_assoc(); was marked as the answer
I showed you exactly how to do it in your previous topic. How do you still manage to keep screwing it up?
-
Barand's post in Another database structure was marked as the answer
These two are useful for showing us your table structures:
SHOW CREATE TABLE order_item;
CREATE TABLE `order_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `product_id` int(11) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
or, using the mysql CLI,
mysql> DESCRIBE order_item;
+------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | order_id | int(11) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+
-
Barand's post in Setting up foreign key was marked as the answer
Yes. The referential integrity enforced by the FK will allow you to insert car or vacation records only if the parent person exists.
No, they can be used in updates and deletions too.
-
Barand's post in generating invoice and getting values of quantity into an array was marked as the answer
i would name my qty fields using the product id as the key
<td><input type="text" id="qty" name="qty[<?=$d_row['id']?>]"></td> then your $_POST['qty'] array for products 1, 4 and 7 will look like
Array ( 1 => 5, 4 => 15, 7 => 10 ) - you're products and ids in one bundle.
-
Barand's post in Problem comparing dates, but not getting results. was marked as the answer
Remove the ";" from the end of that line. It is saying "while(...) do nothing"
PS
If you only expect a single row for a date, use "if()" instead of "while()". Prepare your statement once before the loop - execute within the loop with new value -
Barand's post in Problem Query was marked as the answer
If you use
sum(t1.refag_importo) AS Total without a GROUP BY clause it will give you a single row with the total for all the selected records.
if, for example, you have
SELECT t2.ui_company , sum(t1.refag_importo) AS Total FROM . . . GROUP BY ui.company then you get the total for each company
-
Barand's post in Subtract eexpense.txt from balance.txt and print result was marked as the answer
I thought you would have realised in your initial code that you had the total_receivables from your array_sum() function call. If in doubt what a function is doing, there is always the manual
Getting total expenses would be very similar.
Subtracting one from the other is an easy bit.
-
Barand's post in Database reference material was marked as the answer
So just, for example, "SELECT id,name, email FROM ..." so you only retrieve what you need. That's why you shouldn't use "SELECT * " in queries as that retrieves every column, need it or not, and slow your queries.
[EDIT]
PS That's one of the reasons not to use SELECT *.
-
Barand's post in database structure was marked as the answer
Store the test dates - that will tell you who got there first.
-
Barand's post in working with time/date was marked as the answer
Always store in the correct format (yyyy-mm-dd hh:ii:ss).
Retrieval is flexible...
mysql> SELECT name, submitted FROM test WHERE submitted BETWEEN '2022-05-05 09:05:00' AND '2022-05-05 13:30:00'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE submitted BETWEEN '20220505090500' AND '20220505133000'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE DATE(submitted) = '2022-05-05' AND TIME(submitted) BETWEEN '09:05:00' AND '13:30:00'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE DATE(submitted) = '20220505' AND TIME(submitted) BETWEEN '090500' AND '133000'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+
-
Barand's post in using array_replace on a 2 dimensional array was marked as the answer
With similar...
$new = [ 1 => ['age' => 33], 5 => ['fname' => 'Sandra'], 3 => ['lname' => 'Cunningham'] ]; $table = array_replace_recursive($table, $new); Original $table $new resulting $table
-
Barand's post in Drop Down List was marked as the answer
The only condition you have for not inserting is if the reason has an error.
You should prevent insertion if there are any errors.
-
Barand's post in using for loop to display elements from array was marked as the answer
How many arrays do you now have?
$table = array ( 0 => array ( 'fname' => 'Peter', 'lname' => 'Smith', 'age' => '37' ), 1 => array ( 'fname' => 'Paul', 'lname' => 'Hartley', 'age' => '48' ), 2 => array ( 'fname' => 'Mary', 'lname' => 'Baker', 'age' => '42' ), 3 => array ( 'fname' => 'Jane', 'lname' => 'Doe', 'age' => '51' ) ); $newdata = array ( 4 => array ( 'fname' => 'Jon', 'lname' => 'Atkins', 'age' => '27' ), 5 => array ( 'fname' => 'Phil', 'lname' => 'Jones', 'age' => '14' ), 6 => array ( 'fname' => 'Frank', 'lname' => 'Lampard', 'age' => '48' ), 7 => array ( 'fname' => 'Toney', 'lname' => 'Brentford', 'age' => '25' ) ); $table = array_merge($table, $newdata); foreach ($table as $row => $data) { echo "<p><b>Row number $row</b></p>"; echo "<ul>"; foreach ($data as $col) { echo "<li>$col</li>"; } echo "</ul>"; } giving
-
Barand's post in creating a shopping list and then delete item with double click was marked as the answer
Your deleteList() function (as it says on the tin) deletes the whole list. You need to to delete just the list item that is doubleclicked
Here's my version
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script type='text/javascript'> $().ready(function() { $("#addNew").click(function () { let txt = $("#addItem").val() // get the item text let li = $("<li>", {"text":txt, "class":"sItem"}) // create an <li> item $(li).dblclick(function() { // add dblclick event handler $(this).remove() $("#addItem").focus() }) $("#sList").append(li); // append li to ol item $("#addItem").val("").focus() // clear input field and ready for next input }) $("#addItem").focus() }) </script> [edit] PS Yours always deletes the first item in the list; mine deletes the one that is doubleclicked.