Jump to content

Barand

Moderators
  • Posts

    22,853
  • Joined

  • Last visited

  • Days Won

    657

Community Answers

  1. 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' "; ^ ^  
  2. 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 + " : &pound;" + 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>  
  3. 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); }) }  
  4. 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>';  
  5. 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.
  6. 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.
  7. 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.
     
  8. 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'>&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>  
  9. 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);  
  10. 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";  
  11. 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.
  12. 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?
  13. 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 | | +------------+---------------+------+-----+---------+----------------+  
  14. 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.
  15. 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.
  16. 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
  17. 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
  18. 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.
  19. 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 *.
  20. Barand's post in database structure was marked as the answer   
    Store the test dates - that will tell you who got there first.
  21. 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 | +-------+---------------------+  
  22. 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
  23. 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.
  24. 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

  25. 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.
×
×
  • 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.