Fetch user data who haven't submitted yet payment this month and year - PHP code

Question

I am new in PHP. I am creating a project. In this project, I want to fetch information of users who have not deposited money/payment yet in this month and year. My code is not working properly. I just want to show the username of those who have not deposited money/payment this month and year. maybe need an IF/else formula but I don't know. please help.

usename unique see the table

table_name=credit inserted rows like

usename	month	Year
A	November	2022
B	November	2022
B	December	2022

user A and B both are submit payment previous month and year, this month and year user B submit payment, but user A not submit payment. I want to show user A. my acceptation output given below

usename	month	Year
A	November	2022

<form method="post" accept="" class="row g-3">

                <div class="col-lg-4">
                  <label  for="val-select2">Month <span class="text-danger">*</span></label>
                  <select  class="js-select2 form-select form-control" id="val-select2" name="pay_month" style="width: 100%;" required>
                    <option value="<?php echo date("F")?>" hidden><?php echo date("F")?></option>
                    <option value="January">January</option>
                    <option value="February">February </option>
                    <option value="March">March</option>
                    <option value="April">April</option>
                    <option value="May">May</option>
                    <option value="June">June</option>
                    <option value="July">July</option>
                    <option value="August">August</option>
                    <option value="September">September</option>
                    <option value="October">October</option>
                    <option value="November">November</option>
                    <option value="December">December</option>
                  </select>
                </div>
                <div class="col-lg-4">
                  <label >Year<span class="text-danger">*</span></label>
                  <input type="text" class="form-control"  name="pay_year" placeholder="year" required value="<?php echo date("Y");?>
                  ">
                </div>
                <div class="col-lg-2">
                  <label></label>
                  <button class="form-control btn btn-primary" name="save" type="submit"><i class="fa fa-eye" aria-hidden="true"></i>
                  View</button>
                </div>
              </form>

<?php
              if (isset($_POST['save'])) {
               $sql = "SELECT * FROM credit WHERE  NOT (pay_month='" . $_POST['pay_month'] . "' AND  pay_year='" . $_POST['pay_year'] . "') ORDER BY username";
               if($result = mysqli_query($db, $sql)){
                if(mysqli_num_rows($result) > 0){
                  $count = 0;
                  while($row = mysqli_fetch_array($result)){
                    if(mysqli_num_rows($result) > 0)
                    $count ++;
                    echo '<tr id="'.$row["id"].'">';
                    echo '<td class="fw-semibold">'.$count.'
                    </td>';
                    echo '<td class="fw-semibold fs-sm text-success"> <a href="single_info.php?name='.$row["usename"].'">'.$row["username"].'</a>
                    </td>';
                    echo "</tr>";
                  }
                  mysqli_free_result($result);
                } else{
                  echo "No data found.";
                }
              } else{
                echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);
              }} ?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Dec 01 '22 at 13:26
Your SQL statement is having logical error. (it will still include B). Hence, change it to `select * from credit where username not in (select username from credit where month=? and year=?)` and use parameterized prepared statement. — Ken Lee, Dec 01 '22 at 13:29

score 0 · Answer 1 · answered Dec 01 '22 at 13:36

I think you have to change your SQL to look for the number of payments in the desired period and to filter by those with 0 payment.

Something like :

SELECT username, COUNT(*) AS n FROM credit 
WHERE month = ? AND year = ? HAVING n=0;

(I used prepared statement in the example)

Fetch user data who haven't submitted yet payment this month and year - PHP code

1 Answers1