Jump to content

Barand

Moderators
  • Posts

    22,736
  • Joined

  • Last visited

  • Days Won

    647

Everything posted by Barand

  1. 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
  2. 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
  3. 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));
  4. Instead of storing the slot times, store the frequency for each salon Table: slot_frequency +----------+------+------------+-------------+ | salon_id | freq | valid_from | valid_until | +----------+------+------------+-------------+ | 1 | 45 | 2022-05-01 | NULL | | 2 | 60 | 2022-05-01 | 2022-05-31 | | 2 | 45 | 2022-06-01 | NULL | +----------+------+------------+-------------+ When you process a booking for salon X, get the frequency in use today and generate a temporary slot table +-----------+ | starttime | +-----------+ | 08:00:00 | | 08:45:00 | | 09:30:00 | | 10:15:00 | | 11:00:00 | | 11:45:00 | | 12:30:00 | | 13:15:00 | | 14:00:00 | | 14:45:00 | | 15:30:00 | | 16:15:00 | | 17:00:00 | just for the duration of this booking
  5. Don't go away - I'm just about to type up an alternative solution for you
  6. Not necessarily today - 1, it would be the day before the new ones come into effect Insert new slot times for salon X setting valid_from to Y Update current slots for salon X setting validUntil to Y-1 [edit] Unless you are always changing your practices or opening new salons it is going to be a relatively infrequent process
  7. Slots 1 - 13 are used every day from until forever (no until date) Slots 14 - 23 are valid until end of May Slots 24 - 36 replace 14 - 23 on June 1st. You use those slots where "'$SelectedDate' BETWEEN valid_from AND valid_until" for required salon SELECT s.slot_start_time FROM sb_time_slots s LEFT JOIN sb_bookings b ON s.slot_start_time = TIME(b.start_time) AND s.salon_id = b.salon_id AND '$SelectedDate' BETWEEN s.valid_from AND s.valid_until AND '$SelectedDate' = b.booking_date WHERE booking_start IS NULL");
  8. One possibility, assuming you will also have a salon_id in the booking table too ... +----------+-----------+------------+-------------+---------+ | salon_id | starttime | valid_from | valid_until | slot_id | +----------+-----------+------------+-------------+---------+ | 1 | 08:00:00 | 2022-05-01 | NULL | 1 | | 1 | 08:45:00 | 2022-05-01 | NULL | 2 | | 1 | 09:30:00 | 2022-05-01 | NULL | 3 | | 1 | 10:15:00 | 2022-05-01 | NULL | 4 | | 1 | 11:00:00 | 2022-05-01 | NULL | 5 | | 1 | 11:45:00 | 2022-05-01 | NULL | 6 | | 1 | 12:30:00 | 2022-05-01 | NULL | 7 | | 1 | 13:15:00 | 2022-05-01 | NULL | 8 | | 1 | 14:00:00 | 2022-05-01 | NULL | 9 | | 1 | 14:45:00 | 2022-05-01 | NULL | 10 | | 1 | 15:30:00 | 2022-05-01 | NULL | 11 | | 1 | 16:15:00 | 2022-05-01 | NULL | 12 | | 1 | 17:00:00 | 2022-05-01 | NULL | 13 | | 2 | 08:00:00 | 2022-05-01 | 2022-05-31 | 14 | | 2 | 09:00:00 | 2022-05-01 | 2022-05-31 | 15 | | 2 | 10:00:00 | 2022-05-01 | 2022-05-31 | 16 | | 2 | 11:00:00 | 2022-05-01 | 2022-05-31 | 17 | | 2 | 12:00:00 | 2022-05-01 | 2022-05-31 | 18 | | 2 | 13:00:00 | 2022-05-01 | 2022-05-31 | 19 | | 2 | 14:00:00 | 2022-05-01 | 2022-05-31 | 20 | | 2 | 15:00:00 | 2022-05-01 | 2022-05-31 | 21 | | 2 | 16:00:00 | 2022-05-01 | 2022-05-31 | 22 | | 2 | 17:00:00 | 2022-05-01 | 2022-05-31 | 23 | | 2 | 08:00:00 | 2022-06-01 | NULL | 24 | | 2 | 08:45:00 | 2022-06-01 | NULL | 25 | | 2 | 09:30:00 | 2022-06-01 | NULL | 26 | | 2 | 10:15:00 | 2022-06-01 | NULL | 27 | | 2 | 11:00:00 | 2022-06-01 | NULL | 28 | | 2 | 11:45:00 | 2022-06-01 | NULL | 29 | | 2 | 12:30:00 | 2022-06-01 | NULL | 30 | | 2 | 13:15:00 | 2022-06-01 | NULL | 31 | | 2 | 14:00:00 | 2022-06-01 | NULL | 32 | | 2 | 14:45:00 | 2022-06-01 | NULL | 33 | | 2 | 15:30:00 | 2022-06-01 | NULL | 34 | | 2 | 16:15:00 | 2022-06-01 | NULL | 35 | | 2 | 17:00:00 | 2022-06-01 | NULL | 36 | +----------+-----------+------------+-------------+---------+ In the example data, salon one is on 45 minute slots but salon 2 is on 60 minute slots. On June 1st, salon 2 also changes to 45 minute slots.
  9. Yes, 1 time per row (normalized). Never use your alternative (unnormalized) structure of muliple values in a single row. CREATE TABLE slot ( starttime TIME NOT NULL PRIMARY KEY ); C
  10. It will look something like SELECT slot.starttime FROM slot LEFT JOIN sb_booking ON slot.starttime = TIME(booking_start) AND DATE(booking_start) = CURDATE() WHERE booking_start IS NULL
  11. As well as your booking table you need a "slot" table 08:00:00 09:00:00 10:00:00 11:00:00 12:00:00 13:00:00 14:00:00 15:00:00 16:00:00 17:00:00 which defines the slots for each day. Then you can match this table with slots booked (left join) and output the unused slots.
  12. I'd use PHP to generate the dates via an AJAX request, putting the returned range into the JS dateRange. <?php if (isset($_GET['ajax'])) { if ($_GET['ajax']=='daterange') { $d1 = new DateTime($_GET['start']); $d2 = new DateTime($_GET['end']); $d2->modify('+1 day'); $interval = new DateInterval('P1M'); $dp = new DatePeriod($d1, $interval, $d2); foreach ($dp as $d) { $dstr = $d->format('Y-m-d'); $dates[$dstr] = ['period'=>$dstr]; } $res = json_encode($dates) ; exit ($res); } } ?> <!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 dateRange function getRange() { let start = $("#start_period").val() let end = $("#end_period").val() $.get( "", {"ajax":"daterange", "start":start, "end":end}, function(resp) { dateRange = resp }, "JSON" ) } </script> </head> <body> <div> <input id="start_period" type="date"> <input id="end_period" type="date"> <button onclick="getRange()">Click Me!</button> </div> </body> </html>
  13. What does your table contain in the "image" column? What folder are your images in? (Your code is ambiguous in this respect)
  14. Don't store the image in the database, just store its name (or path/name). On output use an HTML <img> tag to display the image from your server.
  15. 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
  16. You need to get PDO to report any errors that it encounters. Here's my connection code as an example const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DATABASE = 'test'; // default db function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } In particular, the second line of the function tells PDO to throw exceptions on error.
  17. Your tblAppointment contains a column "vaccinated_or_not". Does that refer to the customer, the specialist, the user, or the admin (all are referenced)?
  18. Is that all of your code? No attempt to fetch and display any results returned?
  19. PS Don't post pictures when asked for code or data structures - there is no way I can recreate a similar table at my end from that. If you want help don't give us extra work to do.
  20. To me, that table looks more like a spreadsheet than a relational DB table, containing customer data as well as appointment data. I do not see any indication of the duration of the appointment nor a time until so how do you know when it is due to end? If you don't know that you cannot check for overlapping appointments. Some weird programming stuff going on in there too. You add a new appointment then immediately query the table looking for the booking number of the email and phone number just added. Why? If the customer has been before you would probably get the booking number of their earliest appointment and not the latest. Secondly, you have just generated the booking number using mt_rand() so you already know what it is! Why not use an auto_incrementing booking number and then just call last_insert_id() to get the value?
  21. I can try to help you to write the code (I'm not doing your assignment for you). Do you have regular bookable timeslots? What is the structure of "tblappointment"? (and other tables you are using?)
  22. echo '$' . number_format(array_sum(file('balance.txt')) - array_sum(file('eexpense.txt')), 2); --> $270.00
  23. If the slots that can be booked are all regular intervals, put a UNIQUE constraint on (specialist, time) so, say, "Anthony, 10:00" can be entered once only. However if "Anthony, 10:01" were also valid then it gets more complicated.
  24. You don't. Store marks in the result table EG +---------+-----------+-------+ | pupilid | subjectid | pcent | +---------+-----------+-------+ | 19 | 9 | 79 | | 24 | 5 | 86 | | 12 | 2 | 83 | | 14 | 1 | 79 | | 24 | 8 | 93 | | 20 | 1 | 81 | | 13 | 4 | 62 | | 10 | 4 | 90 | | 13 | 1 | 93 | | 5 | 8 | 95 | +---------+-----------+-------+ create a "grading" table EG +------------+--------+--------+-------+-----------------+ CREATE TABLE grading ( | grading_id | lomark | himark | grade | comment | grading_id int not null auto_increment primary key, +------------+--------+--------+-------+-----------------+ lomark int, | 1 | 0 | 39 | F9 | Ungraded | himark int, | 2 | 40 | 49 | F9 | Fail | grade char(2), | 3 | 50 | 54 | E8 | Pass | comment varchar(20) | 4 | 55 | 59 | D7 | Pass | ); | 5 | 60 | 64 | C6 | Credit | | 6 | 65 | 69 | C5 | Credit | INSERT INTO grading (lomark, himark, grade, comment) values | 7 | 70 | 74 | C4 | Credit | ( 0, 39, 'F9', 'Ungraded'), | 8 | 75 | 79 | B3 | Good | (40, 49, 'F9', 'Fail'), | 9 | 80 | 84 | B2 | Very good | (50, 54, 'E8', 'Pass'), | 10 | 85 | 89 | A1 | Distinction | (55, 59, 'D7', 'Pass'), | 11 | 90 | 100 | A* | Congratulations | (60, 64, 'C6', 'Credit'), +------------+--------+--------+-------+-----------------+ (65, 69, 'C5', 'Credit'), (70, 74, 'C4', 'Credit'), (75, 79, 'B3', 'Good'), (80, 84, 'B2', 'Very good'), (85, 89, 'A1', 'Distinction'), (90,100, 'A*', 'Congratulations'); A joined query does the rest. select pupilid , subjectid , pcent , grade , comment from result join grading on pcent between lomark and himark +---------+-----------+-------+-------+-----------------+ | pupilid | subjectid | pcent | grade | comment | +---------+-----------+-------+-------+-----------------+ | 1 | 1 | 78 | B3 | Good | | 3 | 1 | 58 | D7 | Pass | | 4 | 1 | 54 | E8 | Pass | | 8 | 1 | 91 | A* | Congratulations | | 9 | 1 | 62 | C6 | Credit | | 11 | 1 | 77 | B3 | Good | | 13 | 1 | 60 | C6 | Credit | | 14 | 1 | 64 | C6 | Credit | | 15 | 1 | 93 | A* | Congratulations | | 18 | 1 | 87 | A1 | Distinction | | 19 | 1 | 60 | C6 | Credit | | 20 | 1 | 81 | B2 | Very good | | 1 | 2 | 87 | A1 | Distinction | | 2 | 2 | 53 | E8 | Pass | | 4 | 2 | 87 | A1 | Distinction | | 12 | 2 | 93 | A* | Congratulations | | 14 | 2 | 62 | C6 | Credit |
×
×
  • 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.