Barand
-
Posts
24,563 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
Use an explicit join ( trk_races JOIN drivers) instead of trk_races,drivers SELECT a.race_winner, w.wins, SUBSTRING_INDEX(a.race_winner, ' ', -1) AS last_name, a.race_name, DATE_FORMAT(a.race_date, '%m/%d') AS race_date, d.driver_num FROM trk_races a JOIN drivers d ON a.season = d.driver_season AND a.race_winner = d.driver_name LEFT OUTER JOIN ( SELECT race_winner, COUNT(race_date) AS wins FROM trk_races WHERE race_winner > '' AND Season=2021 GROUP BY race_winner ) w ON w.race_winner = a.race_winner WHERE a.race_winner > '' AND a.Season = 2021 +-------------+------+-----------+-----------+-----------+------------+ | race_winner | wins | last_name | race_name | race_date | driver_num | +-------------+------+-----------+-----------+-----------+------------+ | G Harrison | 2 | Harrison | Race 1 | 01/01 | 38 | | G Harrison | 2 | Harrison | Race 2 | 01/08 | 38 | | J Lennon | 1 | Lennon | Race 3 | 01/15 | 37 | | R Starr | 1 | Starr | Race 4 | 01/22 | 39 | | P McCartney | 1 | McCartney | Race 5 | 01/29 | 36 | +-------------+------+-----------+-----------+-----------+------------+
-
Easiest way is to use a link <a>..</a> which takes you to the required page, passing the id of the article in the query string. Style the link to look like a button. For example <head> <title>Example</title> <style type='text/css'> body { font-family: verdana, sans-serif; font-size: 11pt; } .link-button { display: inline-block; width: 50px; padding: 5px; margin-left: 16px; background-color: blue; color: white; border: 1px solid black; text-align: center; text-decoration: none; border-radius: 8px; } .link-button:hover { background-color: gray; } </style> </head> <body> <p> Storm news <br> <a href='read_article.php?id=1' class='link-button'>Read</a> </p> <p> Flood news <br> <a href='read_article.php?id=2' class='link-button'>Read</a> </p> </body> giving
-
try $data = []; $fp = fopen('test.csv', 'r'); while ($line = fgetcsv($fp, 1024, ';')) { $data[ $line[0]][] = $line[2]; } fclose($fp); $fp = fopen('test.gz', 'w'); foreach ($data as $date => $prices) { fwrite($fp, $date . ';' . join(';', $prices) . "\n"); } fclose($fp);
-
How to pre fill an array with missings keys?
Barand replied to beginnerForPHP's topic in PHP Coding Help
$test = [ '0000' => [2 =>'tes000'], '1111' => [0=>'tes1', 1=>'tes11', 2 =>'tes111'], '2222' => [0=>'tes2', 2 =>'tes333'] ] ; $test = array_map( function($v) { return array_replace(array_fill_keys(range(0,2), 'NIL'), $v); } , $test); echo '<pre>' . print_r($test, 1) . '</pre>'; giving Array ( [0000] => Array ( [0] => NIL [1] => NIL [2] => tes000 ) [1111] => Array ( [0] => tes1 [1] => tes11 [2] => tes111 ) [2222] => Array ( [0] => tes2 [1] => NIL [2] => tes333 ) ) -
According to your post, your table doesn't have a "Firstname" column (it's "Name"). Also make sure the character cases in your indexes match what is returned in the results. (EG "email" or "Email"?) (Personally, I follow the convention of having all table and column names lower case.)
-
I know, hence my "hot air balloon" example. If no-one has one, it won't show.
-
How wide are the spacings beween the labels?
-
PS I did some experimenting with the QR reader app on my smartphone (10 years old with a camera resolution you wouldn't write home about). It had no problem scanning those barcodes from the screen examples in my above post (with the code) or from a print of the PDF file on my 300dpi inkjet printer. Although it couldn't scan the (smaller) example output you posted???
-
And why does that stop you experimenting? Perhaps you may need different sized stickers. Or maybe a higher resolution printer?
-
Given you current structure that could only be a good thing to do. Your date storage format is useless. Can't be processed or sorted. Always store in yyyy-mm-dd format (type DATE) You shouldn't store age, it needs continual updating and can be easily derived from a correctly formatted date (SELECT timestampdiff(YEAR, curdate(), birthday) as age ) Description like "SuperCar" should appear once in a database and not be used as FKs. Only ids should occur in more that one table. If one of your people buys a "Hot Air Balloon" it won't appear on the form to add the option as no-one currenly has one. (In my design I merely add another record to "Vehicle_type" table - job done.) I have rewritten my query and code to use your database structure. I had to use a subquery to get the available vehicle types instead of my table) // connect to DB here if ($_SERVER['REQUEST_METHOD']=='POST') { // echo '<pre>' . print_r($_POST, 1) . '</pre>'; try { $db->beginTransaction(); $stmt1 = $db->prepare("DELETE FROM vehicle WHERE formid = ? "); $stmt1->execute( [ $_POST['formid'] ] ); if (isset($_POST['trans'])) { $stmt2 = $db->prepare("INSERT INTO vehicle (formid, vehselection) VALUES (?, ?) "); foreach ($_POST['trans'] as $veh) { $stmt2->execute( [ $_POST['formid'], $veh ] ); } } $db->commit(); } catch(PDOException $e) { $db->rollBack(); throw $e; } header("Refresh: 0"); exit; } $res = $db->query("SELECT f.formid , f.name , f.lastname , fv.vehselection , CASE WHEN v.vehselection IS NULL THEN 0 ELSE 1 END AS checked FROM form f CROSS JOIN ( SELECT DISTINCT vehselection FROM vehicle ) fv LEFT JOIN vehicle v ON f.formid = v.formid AND v.vehselection = fv.vehselection ORDER BY lastname, vehselection "); $data = []; // store results in a conveniently structured array foreach ($res as $r) { if (!isset($data[$r['formid']])) { $data[$r['formid']] = [ 'name' => "{$r['name']} {$r['lastname']}", 'trans' => [] ]; } $data[$r['formid']]['trans'][$r['vehselection']] = $r['checked']; } //echo '<pre>' . print_r($data, 1) . '</pre>'; # view array structure //exit; ?> <!DOCTYPE html> <html lang='en'> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="generator" content="PhpED 19.5 (Build 19523, 64bit)"> <title>Example</title> <meta name="author" content="Barand"> <meta name="creation-date" content="04/09/2022"> <style type='text/css'> table { border-collapse: collapse; margin: 20px auto; width: 60%; } td, th { padding: 8px; } </style> </head> <body> <table border='1'> <tr> <th>Name</th> <th>Transport types</th> <th> </th> </tr> <?php // output the array foreach ($data as $fid => $udata) { echo "<tr> <form method='post'> <td>{$udata['name']}</td> <td>"; foreach ($udata['trans'] as $ttype => $check) { $chk = $check ? 'checked' : ''; echo "<label><input type='checkbox' name='trans[]' value='$ttype' $chk>$ttype</label><br>"; } echo "</td> <td> <input type='hidden' name='formid' value='$fid'> <input type='submit' value='Update'> </td> </form> </tr> "; } ?> </table> </body> </html>
-
Unless you rewrite the classes, the only thing you can change is the output. Does adjusting the width of the output barcode affect readability?
-
For completeness, my processing would be if ($_SERVER['REQUEST_METHOD']=='POST') { try { $db->beginTransaction(); $stmt1 = $db->prepare("DELETE FROM user_transport WHERE user_id = ? "); $stmt1->execute( [ $_POST['uid'] ] ); if (isset($_POST['trans'])) { $stmt2 = $db->prepare("INSERT INTO user_transport (user_id, trans_type) VALUES (?, ?) "); foreach ($_POST['trans'] as $tid) { $stmt2->execute( [ $_POST['uid'], $tid ] ); } } $db->commit(); } catch(PDOException $e) { $db->rollBack(); throw $e; } header("Refresh: 0"); exit; } $res = $db->query("SELECT u.user_id , u.fname , u.lname , tt.description , tt.trans_type , CASE WHEN ut.user_id IS NULL THEN 0 ELSE 1 END AS checked FROM user_demo u CROSS JOIN transport_type tt LEFT JOIN user_transport ut ON u.user_id = ut.user_id AND tt.trans_type = ut.trans_type ORDER BY lname, trans_type "); $data = []; // store results in a conveniently structured array foreach ($res as $r) { if (!isset($data[$r['user_id']])) { $data[$r['user_id']] = [ 'name' => "{$r['fname']} {$r['lname']}", 'trans' => [] ]; } $data[$r['user_id']]['trans'][$r['trans_type']] = ['desc' => $r['description'], 'check' => $r['checked']]; } // echo '<pre>' . print_r($data, 1) . '</pre>'; # view array structure ?> <!DOCTYPE html> <html lang='en'> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="generator" content="PhpED 19.5 (Build 19523, 64bit)"> <title>Example</title> <meta name="author" content="Barand"> <meta name="creation-date" content="04/09/2022"> <style type='text/css'> table { border-collapse: collapse; margin: 20px auto; width: 60%; } td, th { padding: 8px; } </style> </head> <body> <table border='1'> <tr> <th>Name</th> <th>Transport types</th> <th> </th> </tr> <?php // output the array foreach ($data as $uid => $udata) { echo "<tr> <form method='post'> <td>{$udata['name']}</td> <td>"; foreach ($udata['trans'] as $ttype => $tdata) { $chk = $tdata['check'] ? 'checked' : ''; echo "<label><input type='checkbox' name='trans[]' value='$ttype' $chk>{$tdata['desc']}</label><br>"; } echo "</td> <td> <input type='hidden' name='uid' value='$uid'> <input type='submit' value='Update'> </td> </form> </tr> "; } ?> </table> </body> </html> Results
-
This is the method I would use ** DATA ********************************************************************************************************* user_demo user_transport transport_type +---------+----------+-------+----------+ +---------+------------+ +------------+-------------+ | user_id | username | fname | lname | | user_id | trans_type | | trans_type | description | +---------+----------+-------+----------+ +---------+------------+ +------------+-------------+ | 1 | norderl | Laura | Norder | | 1 | 1 | | 1 | Yacht | | 2 | tomd | Tom | DiCanari | | 1 | 2 | | 2 | SuperCar | | 3 | cheggs | Scott | Chegg | | 3 | 1 | | 3 | Plane | | 4 | dowtp | Peter | Dowt | | 3 | 2 | +------------+-------------+ | 5 | robika | Anna | Robik | | 3 | 3 | +---------+----------+-------+----------+ | 4 | 1 | | 4 | 3 | | 5 | 2 | +---------+------------+ ** QUERY ******************************************************************************************************** SELECT u.user_id , u.fname , u.lname , tt.description , tt.trans_type , CASE WHEN ut.user_id IS NULL THEN 0 ELSE 1 END AS checked FROM user_demo u CROSS JOIN -- get all combinations of user/transport type transport_type tt LEFT JOIN -- get which ones are used by each user user_transport ut ON u.user_id = ut.user_id AND tt.trans_type = ut.trans_type ORDER BY lname, trans_type; ** RESULTS ***************************************************************************************************** +---------+-------+----------+-------------+------------+---------+ | user_id | fname | lname | description | trans_type | checked | +---------+-------+----------+-------------+------------+---------+ | 3 | Scott | Chegg | Yacht | 1 | 1 | | 3 | Scott | Chegg | SuperCar | 2 | 1 | | 3 | Scott | Chegg | Plane | 3 | 1 | | 2 | Tom | DiCanari | Yacht | 1 | 0 | | 2 | Tom | DiCanari | SuperCar | 2 | 0 | | 2 | Tom | DiCanari | Plane | 3 | 0 | | 4 | Peter | Dowt | Yacht | 1 | 1 | | 4 | Peter | Dowt | SuperCar | 2 | 0 | | 4 | Peter | Dowt | Plane | 3 | 1 | | 1 | Laura | Norder | Yacht | 1 | 1 | | 1 | Laura | Norder | SuperCar | 2 | 1 | | 1 | Laura | Norder | Plane | 3 | 0 | | 5 | Anna | Robik | Yacht | 1 | 0 | | 5 | Anna | Robik | SuperCar | 2 | 1 | | 5 | Anna | Robik | Plane | 3 | 0 | +---------+-------+----------+-------------+------------+---------+ Now you can just list the query results in your form, using the "checked" field to tell you if the check box should be checked or not.
-
PHP calc price with different time range cost
Barand replied to kit123321's topic in PHP Coding Help
Like this? ... $rentals = [ [ "2022-04-08 12:00:00", "2022-04-08 18:00:00" ], [ "2022-04-08 18:00:00", "2022-04-09 00:00:00" ], [ "2022-04-09 00:00:00", "2022-04-09 02:00:00" ], [ "2022-04-09 02:00:00", "2022-04-09 18:00:00" ] ]; echo '<pre>'; foreach ($rentals as $times) { $instance = new PriceCalculator($times[0], $times[1]); printf('%s | %s | %10s<br>', $times[0], $times[1], $instance->calculate()); } echo '</pre>'; giving 2022-04-08 12:00:00 | 2022-04-08 18:00:00 | 528.00 2022-04-08 18:00:00 | 2022-04-09 00:00:00 | 708.00 2022-04-09 00:00:00 | 2022-04-09 02:00:00 | 256.00 2022-04-09 02:00:00 | 2022-04-09 18:00:00 | 1,568.00 NOTE: 02:00 to 18:00 is not 10 hours as in your example calculations -
Start by trying to find out if and why any of your queries are failing. Put this line of code before you create your database connection... mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
-
It means the syntax checker has found that variable name in a place where it doesn't expect to find a variable.
-
Simple. Triple the page width and offset each label. require 'code128.php'; $data = ['item_name' => 'Fuel Vapour Hose' ,'code_purchase' => 'ABC-2342' ,'code_sale' => 'DFS-4312' ,'item_code' => '47900001' ]; class Barcode_Label extends PDF_Code128 { protected $data; //constructor public function __construct() { parent::__construct('L','mm',[190, 35]); } public function printLabel($data) { $this->setMargins(5,5,5); $this->SetAutoPageBreak(0); $this->AddPage(); $this->setFont('Times', 'B', 10); for ($lab=0; $lab<3; $lab++) { $offset = $lab * 65; $this->setXY($offset, 5); $this->Cell(50, 5, $data['item_name'], 0, 2, 'C'); $this->Cell(25, 5, $data['code_purchase'], 0, 0, 'C'); $this->Cell(25, 5, $data['code_sale'], 0, 2, 'C'); $barcode = $this->Code128($offset + 5,15,$data['item_code'],50,10); $this->setXY($offset, 25); $this->Cell(50, 5, $data['item_code'], 0, 1, 'C'); } } } #Barcode_Label $label= new Barcode_Label(); for ($i=0; $i<3; $i++) { $label->printLabel($data); } $label->Output(); [edit] PS I don't know your label dimensions so you may have to adjust offset, page size and margins
-
PHP calc price with different time range cost
Barand replied to kit123321's topic in PHP Coding Help
Here's one way class PriceCalculator { private $start; private $end; private $price = [ 0 => [ 98, 128], 1 => [ 88, 118], 2 => [ 88, 118], 3 => [ 88, 118], 4 => [ 88, 118], 5 => [ 88, 118], 6 => [ 98, 128] ]; public function __construct ($time1, $time2) { $this->start = new DateTime($time1); $this->end = new DateTime($time2); } public function calculate() { $total = 0; $dp = new DatePeriod($this->start, new DateInterval('PT1M'), $this->end ); foreach ($dp as $min) { $day = $min->format('w'); $peak = '02' <= $min->format('H') && $min->format('H') < '18' ? 0 : 1; $total += $this->price[$day][$peak]/60; } return number_format($total, 2); } } $time1 = "2022-03-12 16:12:00"; $time2 = "2022-03-12 18:31:00"; $instance = new PriceCalculator($time1, $time2); echo $instance->calculate(); // 242.53 -
try <?php require 'code128.php'; $data = ['item_name' => 'Fuel Vapour Hose' ,'code_purchase' => 'ABC-2342' ,'code_sale' => 'DFS-4312' ,'item_code' => '47900001' ]; class Barcode_Label extends PDF_Code128 { protected $data; //constructor public function __construct() { parent::__construct('L','mm',[60, 35]); } public function printLabel($data) { $this->setMargins(5,5,5); $this->SetAutoPageBreak(0); $this->AddPage(); $this->setFont('Times', 'B', 10); $this->Cell(50, 5, $data['item_name'], 0, 1, 'C'); $this->Cell(25, 5, $data['code_purchase'], 0, 0, 'C'); $this->Cell(25, 5, $data['code_sale'], 0, 1, 'C'); $barcode = $this->Code128( 5,15,$data['item_code'],50,10); $this->setY(25); $this->Cell(50, 5, $data['item_code'], 0, 1, 'C'); $this->ln(2); } } #Barcode_Label $label= new Barcode_Label(); for ($i=0; $i<3; $i++) { $label->printLabel($data); } $label->Output(); ?>
-
Try this code
-
try to get active and non active from two different date rows
Barand replied to Tanja's topic in MySQL Help
Try mysql> SELECT kennelname as `Active Kennel` -> FROM owner -> JOIN -> dog ON dog.owner_id = owner.owner_id -> WHERE timestampdiff(YEAR, birthday, CURDATE()) < 8 -> AND sex = 'female' -> AND kennelname <> '' -> UNION -> SELECT kennelname -> FROM owner -> JOIN -> dog ON dog.breeder_id = owner.owner_id -> WHERE timestampdiff(YEAR, birthday, CURDATE()) < 8 -> AND kennelname <> '' -> ; +---------------+ | Active Kennel | +---------------+ | green | | silver | | yellow | +---------------+ -
try to get active and non active from two different date rows
Barand replied to Tanja's topic in MySQL Help
How do you know A had the litter if you only have the data at the time the page is called? -
When your form is submitted (you need a Submit button) the chosen option will be in $_POST['size']
-
try to get active and non active from two different date rows
Barand replied to Tanja's topic in MySQL Help
In 2020 kennel A has litter from its only bitch. In 2021 the bitch that had the litter is sold to the breeder at kennel B Which is the now active kennel? Do you track who the breeder/owner was at the time of the litter?
