Issue displaying correct week from MySQL database-CodePudding

I have a expenses script that displays the current week by week number based on week number in the database (w10) and can select other weeks by using ?selectWeek=w46

We have been using this script for a year now and does not show the information for this week (w10) because there are 2 entries in the database for w10 and it's getting confused which one to display.

Database Structure

ID - Base_Charge - Week - Year - Created_ At

1 - 20000 - w10 - 2021 - 2021-03-08
53 - 454333 - w10 - 2022 - 2022-03-07

Code:

<?php
include '../main.php';
check_loggedin($pdo);
// output message (errors, etc)
$msg = '';

$stmt = $pdo->prepare('SELECT * FROM expense_base_charge');
$stmt->execute();
$weekList = $stmt->fetchAll(PDO::FETCH_ASSOC);

if(isset($_GET['selectWeek'])){
    $week  =       $_GET['selectWeek'];
}else{
    $ddate  =       date('y-m-d');
    $date = new DateTime($ddate);
    $week = 'w'.$date->format("W");
}


$stmt = $pdo->prepare('SELECT * FROM expense_base_charge where week = ?');
$stmt->execute([$week]);
$base_charge = $stmt->fetch(PDO::FETCH_ASSOC);


$stmt = $pdo->prepare('SELECT * FROM expense where week = ?');
$stmt->execute([$base_charge['id']]);
$expense = $stmt->fetchAll(PDO::FETCH_ASSOC);

?>

Any idea how to make it view the correct week please?

CodePudding user response：

When you want select only current year records you can improve your where statement in next way:

$week = 'w10';

$stmt = $pdo->prepare(
    'SELECT * FROM expense_base_charge WHERE week = ? AND year = YEAR(NOW())'
);

$stmt->execute([$week]);
$base_charge = $stmt->fetch(PDO::FETCH_ASSOC);

PHP MySQL Feddle

Another way, you can calculate default week/year values in PHP (in next example I also changed week column format to int)

//set current week/year values when they not provided by GET
$week = $_GET['selectWeek'] ?? date('W');
$year = $_GET['selectYear'] ?? date('Y');

//debug only print
printf('YEAR: %d, WEEK: %d ' . PHP_EOL, $year, $week);

//select data fom MySQL using week & year
$stmt = $pdo->prepare(
    'SELECT * FROM expense_base_charge WHERE week = ? AND year = ?'
);

$stmt->execute([$week, $year]);

$base_charge = $stmt->fetch(PDO::FETCH_ASSOC);

Test online

CodePudding user response：

The problem is that you are storing the value for week and year in a denormalized form. The week number should not be stored with prefix w. Ideally, you would store both of them in a single column. Then you could use YEARWEEK() function in SQL.

This is of course assuming that you are using ISO 8601 week numbering. If you are not following this scheme then fetching the correct value will be very difficult.

If you don't want to change the table schema now, you can still get the same value using PHP. Use 'o' for year number and 'W' for week number.

$week = $_GET['selectWeek'] ?? 'w'.(new DateTime())->format("W");
$year = $_GET['selectYear'] ?? (new DateTime())->format("o");

$stmt = $pdo->prepare('SELECT * FROM expense_base_charge where week = ? and year = ?');
$stmt->execute([$week, $year]);
$base_charge = $stmt->fetch(PDO::FETCH_ASSOC);