Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,563

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    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.
  2. Barand
    Should look something like this <?php if ($_SERVER['REQUEST_METHOD'] == 'POST') { $quantities = array_filter($_POST['qty']); # remove 0 qtys foreach ($quantities as $id => $qty) { echo "Product #$id : $qty ordered<br>" ; } } $results = $pdo->query("SELECT id, description FROM product"); ?> <!DOCTYPE html> <html lang='en'> <head> <title>Vehicle Allocation</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <form method='POST'> <table style='width:500px'> <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td></tr> <?php foreach($results as $r) { echo "<tr><td>{$r['id']}</td> <td>{$r['description']}</td> <td> <input type='number' value='0' name='qty[{$r['id']}]'> </td> </tr> "; } ?> </table> <br> <button>Submit</button> </form> </body> </html>
  3. Barand
    We can't see the query which get the list of products, so we don't know what the index should be. It will be whatever your SELECT clause says it is.
  4. Barand
    Should be possible. Without seeing your SQL code we can't help. This worked... mysql> CREATE TABLE `person` ( -> `id` int(11) NOT NULL, -> `username` varchar(20) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.21 sec) mysql> CREATE TABLE `favourite_car` ( -> `id` int(11) NOT NULL, -> `year` year(4) DEFAULT NULL, -> `make` varchar(45) DEFAULT NULL, -> `model` varchar(45) DEFAULT NULL, -> `color` varchar(45) DEFAULT NULL, -> `person_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `FK_car_person_idx` (`person_id`), -> CONSTRAINT `FK_car_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.26 sec) mysql> CREATE TABLE `favourite_vacation` ( -> `id` int(11) NOT NULL, -> `location` varchar(45) DEFAULT NULL, -> `hotel` varchar(45) DEFAULT NULL, -> `duration` int(11) DEFAULT NULL, -> `person_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `FK_vacation_person_idx` (`person_id`), -> CONSTRAINT `FK_vacation_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.31 sec)
  5. Barand
    Since DATETIME and TIMESTAMP now behave similarly, you can now have a column to auto-record the time inserted and a separate on to record update times EG CREATE TABLE `a_test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `finish` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The differences are Being 1 byte smaller (4 bytes) TIMESTAMP has a reduced date range TIMESTAMP is stored as UTC time and converted back to current timezone on retrieval
  6. Barand
    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.
  7. Barand
    Here's my version of a calendar script. It uses PDO::FETCH_GROUP as @mac_gyver suggested. I am using it to group by week number to make it easy to start a new row for each week. My test data is a schedule of fictional adult evening classes in English, pottery, dog grooming and origami. It also makes use of my "digit" table to generate dates. +----+------------+------------+ mysql> select num from digit; | id | coursecode | coursedate | +------+ +----+------------+------------+ | num | | 21 | ENG | 2022-05-06 | +------+ | 22 | ENG | 2022-05-13 | | 0 | | 23 | ENG | 2022-05-27 | | 1 | | 24 | ENG | 2022-06-03 | | 2 | | 25 | ENG | 2022-06-10 | | 3 | | 26 | DOG | 2022-05-02 | | 4 | | 27 | DOG | 2022-05-09 | | 5 | | 28 | DOG | 2022-05-16 | | 6 | | 29 | DOG | 2022-05-23 | | 7 | | 30 | DOG | 2022-05-30 | | 8 | | 31 | POT | 2022-05-03 | | 9 | | 32 | POT | 2022-05-10 | +------+ | 33 | POT | 2022-05-17 | | 34 | POT | 2022-05-24 | | 35 | POT | 2022-05-31 | | 36 | ORI | 2022-06-03 | | 37 | ORI | 2022-06-17 | | 38 | ORI | 2022-07-01 | | 39 | ORI | 2022-05-13 | | 40 | ORI | 2022-05-16 | | 41 | ORI | 2022-06-17 | | 42 | DOG | 2022-06-10 | +----+------------+------------+ and the output for May 2022 is CODE <?php include 'db_inc.php'; # use your own $pdo = pdoConnect('test'); # connection code $numYear = 2022; $numMonth = 5; $dt1 = (new DateTime("{$numYear}-{$numMonth}-01")); $lastday = (clone $dt1)->modify('+1 month')->format('Y-m-d'); $monthname = $dt1->format('F'); $dstr = $dt1->modify('last monday')->format('Y-m-d'); $res = $pdo->query("SELECT weekofyear(thedate) , thedate , day(thedate) as day , month(thedate) as mth , dayofweek(thedate) as dayno , GROUP_CONCAT(coursecode SEPARATOR '<br>') as courses FROM ( SELECT '$dstr' + INTERVAL a.num*10 + b.num DAY as thedate -- subquery to generate all FROM digit a, digit b -- dates in required range WHERE '$dstr' + INTERVAL a.num*10 + b.num DAY < '$lastday' ) d LEFT JOIN a_course_date a ON d.thedate = a.coursedate GROUP BY thedate "); $dates = $res->fetchAll(PDO::FETCH_GROUP); ?> <!DOCTYPE html> <html lang='en'> <head> <title>Sample Calendar</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> <style type='text/css'> body { font-family: calibri, sans-serif; font-size: 11pt; } table { border-collapse: collapse; width: 700px; margin: 50px auto; } tr { vertical-align: top; } th { background-color: #EEE; width: 14%; padding: 8px; } th.we { background-color: #CCC; } td { background-color: #e6ffe6; width: 14%; height: 75px; padding: 4px; text-align: center; } td.we { background-color: #b3ffb3; } td.today { border: 3px solid #8F1FCF; } td.blank { background-color: white; color: #AAA; } span.dno { float: right; font-size: 9pt; } </style> </head> <body> <table border='1'> <caption><?="$monthname $numYear"?></caption> <tr><th>Mon</th> <th>Tue</th> <th>Wed</th> <th>Thu</th> <th>Fri</th> <th class='we'>Sat</th> <th class='we'>Sun</th> </tr> <?php foreach ($dates as $wk => $days) { echo "<tr>\n"; foreach ($days as $d) { if ($d['mth'] == $numMonth) { $today = $d['thedate'] == date('Y-m-d') ? 'today' : ''; $we = in_array($d['dayno'], [1,7]) ? 'we' : ''; $cls = "$we $today"; } else { $cls = 'blank'; } echo "<td class='$cls' > <span class='dno'>{$d['day']}</span> <br> {$d['courses']} </td>\n"; } echo "</tr>\n"; } ?> </table> </body> </html>
  8. Barand
    And if you are worried about storage, a DATETIME (without fractional seconds) only requires 5 bytes.
  9. Barand
    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
  10. Barand
    If I were a customer, I wouldn't want to remove an item and then end up paying for it because the programmer couldn't be bothered to recalculate.
  11. Barand
    It looks like you have an item quantity with a minus sign to the left and a plus sign to the right, presumably to amend the quantity. If the quantity is "1" and you click "minus", doesn't that have the effect you want - reduce it to zero?
  12. Barand
    ... or reference it anywhere else in the query (eg JOIN ON, WHERE, ORDER BY)
  13. Barand
    Yes, thanks - rogue post deleted.
  14. Barand
    My preference would be fetch all the data in a single query but I cannot categorically say it would always be faster.
  15. Barand
    You have the db data and the array, time it. $t1 = microtime(1); for($i=0; $i<1000; $i++) { // method 1 code } %t2 = microtime(1); for($i=0; $i<1000; $i++) { // method 2 code } %t3 = microtime(1); printf("Method 1 took %0.4f seconds<br>", $t2 - $t1); printf("Method 2 took %0.4f seconds<br>", $t3 - $t2);
  16. Barand
    How can one remove unwanted status updates from their profile?
  17. Barand
    As I already said in my earlier reply, you DON'T post the grades and comments to the result table, just store the marks (pcent in my example code).
  18. Barand
    Then output it there.
  19. Barand
    $sqlDate appears to have the correct format (more by good luck than design) so the question is "What format are the dates in the RepairCalendar table?" And that is not how you use prepared queries. $sql = "SELECT * FROM RepairCalendar WHERE DATE(rc_date) = ?"; $stmt = $pdo->prepare($sql); $stmt->execute( [ $sqlDate ] ); The whole point is not to put values directly into the SQL string.
  20. Barand
    With <a href="profile.php?username=<?php echo $admin;?>">XXX</a> the bit that is visible in the browser is the text you put where I have XXX https://developer.mozilla.org/en-US/docs/Web/HTML/Element/a
  21. Barand
    https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX/Getting_Started
  22. Barand
    The usual way to communicate between JS and PHP is by using an AJAX request.
  23. Barand
    I was assuming the problem was that data wasn't available for all dates. If all dates are available then count the records that contain "word", not select the records that contain "word" SELECT s_created_at , SUM(subject LIKE '%word%') as rec_cnt FROM tickets GROUP BY s_created_at ORDER BY s_created_at DESC
  24. Barand
    A useful accessory to any database is a "digit" table containing the numbers 0 - 9. SELECT date , COUNT(s_created_at) as rec_cnt FROM ( SELECT '2022-05-31' - INTERVAL a.num * 1000 + b.num * 100 + c.num * 10 + d.num DAY AS date FROM digit a, digit b, digit c, digit d WHERE '2022-05-31' - INTERVAL a.num * 1000 + b.num * 100 + c.num * 10 + d.num DAY > '2019-04-30' ) last3years LEFT JOIN `tickets` ON last3years.date = tickets.DATE(s_created_at) AND subject LIKE "%Word%" GROUP BY date ORDER BY date DESC
  25. Barand
    Like this $selected_salon = 1; $stmt = $pdo->prepare("SELECT freq FROM slot_frequency WHERE salon_id = ? AND CURDATE() BETWEEN valid_from AND coalesce(valid_until, '9999-12-31') "); $stmt->execute([ $selected_salon]); $freq = $stmt->fetchColumn(); $t1 = new DateTime('08:00:00'); $t2 = new DateTime('17:15'); $step = new DateInterval("PT{$freq}M"); $period = new DatePeriod($t1, $step, $t2); // generate time slots foreach ($period as $d) { $data[] = sprintf("('%s')", $d->format('H:i:s')); } // create temp table $pdo->exec("CREATE TEMPORARY TABLE sb_time_slot ( slot_start_time TIME primary key )"); // insert into the temporary table $pdo->exec("INSERT INTO sb_time_slot(slot_start_time) VALUES " . join(',', $data));
×
×
  • 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.