Getting a week number from a date format

[newcoder2023]

Hi there,

 

how do i get data from all tables in my database

for example if i have a database called test and tables test1, test2, test3 etc

how do i make a query for each table

also

If i have a row in each database table called date (name date type date) how do i convert this to the relevant week number of that date ?

So for example if the row date had 2023-11-17 i want to convert it to 46 (week number)

I have a table of week numbers (week 1 to 52)

so in the above example in my table i want to display whether week 46 is valid or not (dependant on if date was entered) using an if else statement

so if week 46 was not entered in my form, in week 46 in my table it would say "No" but if i entered a date (as above) in week 46 in my form, in my table it would say "Yes"

I hope this makes sense

so test1 , test2, test3 tables i want to convert date to week number

then in my html table at side i have test1, test2, test3 down the side and across the top i have week 1 to 52

in the date example above if test1 table date was empty it would say in the html table "no"  else if it had a date it would say "yes"

 

as daft as this sounds something like this :

<td>convert date to week number then if test1 week number 46 is empty say "no" otherwise say "yes"</td>
<td>convert date to week number then if test2 week number 46 is empty say "no" otherwise say "yes"</td>

 

i know it sounds complicatd

Thanks

 

Edited November 17, 2023 by newcoder2023

[newcoder2023]

Sorry typo week number !

Edited November 17, 2023 by newcoder2023
typo

[Barand]

Mysql has a couple of functions for determining the week number for a given date

• WEEK(date[, mode])
• WEEKOFYEAR(date) - equivalent to WEEK(date, 3)

The week number depends on how you define the first week of a year and on whether you want weeks starting on Sunday or Monday. Refer to the manual.

mysql> select week('2023-11-17') as wkno;
+------+
| wkno |
+------+
|   46 |
+------+

 

Having a table for each test type seems a very clunky way of storing your data and has already given you a problem. Consider combining the data...

+--------------+
| test_type    |
+--------------+
| id           |------+
| description  |      |
+--------------+      |       +--------------+
                      |       | test_log     |
                      |       +--------------+
                      |       | id           |
                      +------<| test_type_id |
                              | date         |
                              +--------------+

TABLE: test_type                                     TABLE: test_log
+------+----------------------------------+          +-----+--------------+------------+
|  id  | description                      |          | id  | test_type_id | date       | 
+------+----------------------------------+          +-----+--------------+------------+
|  1   | Fire alarm                       |          |  1  |       1      | 2023-01-05 |
|  2   | Fire doors                       |          |  2  |       2      | 2023-01-06 |
|  3   | Shower head temperatures         |          |  3  |       5      | 2023-01-06 |
|  4   | Wash basin temperatures          |          |  4  |       3      | 2023-01-07 |
|  5   | Health and safety                |          |  5  |       3      | 2023-01-07 |
+------+----------------------------------+          |  6  |       4      | 2023-01-08 |
                                                     |  7  |       2      | 2023-01-09 |
                                                     |  8  |       4      | 2023-01-09 |
                                                     |     |              |            |