Jump to content

how to update null


piano0011

Recommended Posts

I am trying to update the following to null whenever the lessonplan has expired and I have part of the code here. Once it has expired, it should reset back to null or 0 for most of the variables and I have defined the variables here:

Do I need to declare the variables if setting to null?

 

$paidbydate = date('Y-m-d H:i:s', strtotime("+2 day"));
$emailreminder = 1;
$emailreminderreset = null;
$subscriptionplandelete = null;
$paidbydatedelete = null;
$subscriptionplandatedelete = null;
$expirydatedelete = null;
$feesdelete = null;
$totalfees = null;
$overdue = 1;
$overduedelete = 0;
$activatedelete = 0;
$level1promo_activate = 0;
 

 

 //Created a template
                            $sql = "SELECT * FROM memberships WHERE user_uid = ?;";
                            //Create a prepared statement

                            $stmt = mysqli_stmt_init($conn);
                            //Prepare the prepared statement
                            if (!mysqli_stmt_prepare($stmt, $sql)) {
                                echo 'SQL statement failed';
                            } else {
                                 //Bind parameters to the placeholder
                                 mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                                 //Run parameters inside database
                                 mysqli_stmt_execute($stmt);
                                 
                                    $result = mysqli_stmt_get_result($stmt);

                                 while ($row = mysqli_fetch_assoc($result))  {
                                 
                                
                                     
                               


                                     if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] ==0 && $row['level1promo_activate'] == 0) {

                                         header("Location: index.php?level1=notactivated");
                                         exit();

                                      } else {
                                          if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['emailreminder'] == 0 && date("Y-m-d H:i:s") > $row['paidbydate'] && $row['paid'] == 0 && $row['overdue'] == 0) {
                                             
                                             
                                              
                                            

                                             $sql = "UPDATE memberships
                                                     SET paidbydate = ?, emailreminder = ?, overdue = ?
                                                     WHERE user_uid = ?;

                                                    ";

                                             $stmt = mysqli_stmt_init($conn);
                                             if (!mysqli_stmt_prepare($stmt, $sql)) {
                                                 echo "SQL error";

                                             } else {
                                                  mysqli_stmt_bind_param($stmt, "siis", $paidbydate, $emailreminder, $overdue, $_SESSION['u_uid']);
                                                  mysqli_stmt_execute($stmt);

                                                 
                                             
                                            }
                                         
                                                   header("Location: index.php?level1=overdue");
                                                   exit();
                                             

                                             } else {
                                                  if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['emailreminder'] == 1 && date("Y-m-d H:i:s") > $row['paidbydate'] && $row['paid'] == 0 && $row['overdue'] == 1) {
                                                  
                                                  $sql = "UPDATE memberships
                                                     SET subscriptionplan = ?, subscriptionplandate = ?, fees = ?, expirydate = ?, paidbydate = ?, emailreminder = ?, overdue = ?, activate = ?
                                                     WHERE user_uid = ?;

                                                    ";

                                                  $stmt = mysqli_stmt_init($conn);
                                                  if(!mysqli_stmt_prepare($stmt, $sql)) {
                                                     echo "SQL error";

                                                   } else {
                                                        mysqli_stmt_bind_param($stmt, "ssissiiis", $subscriptionplandelete, $subscriptionplandatedelete, $feesdelete, $expirydatedelete, $paidbydatedelete, $emailreminderreset, $overduedelete, $activatedelete, $_SESSION['u_uid']);
                                                        mysqli_stmt_execute($stmt);
                                                   
                                                  }
                                               

                                                     header("Location: index.php?level1=cancelled");
                                                     exit();

                                                  } else {
                                                       if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && date("Y-m-d H:i:s") > $row['expirydate'] && $row['paid'] == 1) {
                                                       
                                                       
                                                       $sql = "UPDATE memberships
                                                               SET subscriptionplan = ?, subscriptionplandate = ?, fees = ?, expirydate = ?, paidbydate = ?, emailreminder = ?, overdue = ?, activate = ?
                                                               WHERE user_uid = ?;

                                                               ";

                                                        $stmt = mysqli_stmt_init($conn);
                                                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                                                           echo "SQL error";

                                                         } else {
                                                              mysqli_stmt_bind_param($stmt, "ssissiiis", $subscriptionplandelete, $subscriptionplandatedelete, $feesdelete, $expirydatedelete, $paidbydatedelete, $emailreminderreset, $overduedelete, $activatedelete, $_SESSION['u_uid']);
                                                              mysqli_stmt_execute($stmt);
                                                         
                                                        }
                                                     

                                                            header("Location: index.php?level1=expired");
                                                            exit();

 

 

Link to comment
Share on other sites

I thought it was working before but now when i set the expiry date to less than today's date, it doesn't update.. the subscriptionplan should be set to null. In my database, most of the variables are set to null and 0.

 

The header is correct and it does say cancel but the database is not updating

Link to comment
Share on other sites

To start with, you have numerous missing curly braces. A proper IDE will forever keep you from such simple mistakes There is also quite a bit of redundant code. The multi-nested if/elses are a real mess. You need to really think through the logic.

As I mentioned to you some time ago on another forum, you need to fix your database FIRST before you write any code. As I told you before, learn Database Normalization and fix your Database. Again I will say, you should be using PDO. It is going to make learning things much easier. Here is a PDO tutorial to get you going. https://phpdelusions.net/pdo.

You can also download my PDO Bumpstart Database to get you up and running right away. I wrote it just for people like you getting started to learn from. https://github.com/benanamen/pdo_bumpstart_ver1.6

If you follow our advice your coding journey will move along much faster and easier.

Link to comment
Share on other sites

the relevant query is failing to execute, probably due to a problem with a data value being supplied to the query. the execute() call is failing in this case and is returning an error, if you had error handling for the execute() calls. the WHERE clause in the queries are true, since the first SELECT query is matching its data.

in one of your threads, i went to the trouble of reviewing your code and giving a list of recommendations. one of them pointed out that the execute() calls can fail, that you need to have error handling for them, and that you should simply use exceptions to handle database statement errors. i recommend that you review points #6 to #9 at the following link and actually use the information that is being provided in the replies to your threads -  https://forums.phpfreaks.com/topic/307451-trying-to-understand-the-logic-of-the-if-and-else-statement/?do=findComment&comment=1559317

 

short-answer - 1) enable exceptions (the line of code you need is posted in the linked to reply), 2) remove the existing if/else 'sql statement failed' message logic that's testing the mysqli_stmt_prepare() statements, 3) replace each of the mysqli_stmt_init($conn) and mysqli_stmt_prepare($stmt, $sql) pairs with a call to mysqli_prepare($conn,$sql), 4) remove the while(){} loop you are using to fetch a single row of data and just fetch the data, and 5) in another thread i pointed out that when you have an exit(); statement, you don't need an else{} around the rest of the code, because code execution won't run past the exit and you can remove the else{}.

with all the unnecessary bits (programming pun intended) removed from the code, you should end up with something that looks like this -

<?php

// select the user membership data
$sql = "SELECT * FROM memberships WHERE user_uid = ?";
$stmt = mysqli_prepare($conn,$sql);
mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

if(!$row = mysqli_fetch_assoc($result))
{
	echo "The requested membership row was not found.";
} else {
	if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 0 &&
		$row['level1promo_activate'] == 0)
	{
		header("Location: index.php?level1=notactivated");
		exit();
	}
	
	if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 &&
		$row['emailreminder'] == 0 && date("Y-m-d H:i:s") > $row['paidbydate'] &&
		$row['paid'] == 0 && $row['overdue'] == 0)
	{
		$sql = "UPDATE memberships
		SET paidbydate = ?, emailreminder = ?, overdue = ?
		WHERE user_uid = ?;
		";
		$stmt = mysqli_prepare($conn,$sql);
		mysqli_stmt_bind_param($stmt, "siis", $paidbydate, $emailreminder, $overdue, $_SESSION['u_uid']);
		mysqli_stmt_execute($stmt);

		header("Location: index.php?level1=overdue");
		exit();
	}
	
	if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 &&
	$row['emailreminder'] == 1 && date("Y-m-d H:i:s") > $row['paidbydate'] &&
	$row['paid'] == 0 && $row['overdue'] == 1)
	{
		$sql = "UPDATE memberships
		SET subscriptionplan = ?, subscriptionplandate = ?, fees = ?, expirydate = ?, paidbydate = ?, emailreminder = ?, overdue = ?, activate = ?
		WHERE user_uid = ?;
		";
		$stmt = mysqli_prepare($conn,$sql);
		mysqli_stmt_bind_param($stmt, "ssissiiis", $subscriptionplandelete, $subscriptionplandatedelete, $feesdelete, $expirydatedelete, $paidbydatedelete, $emailreminderreset, $overduedelete, $activatedelete, $_SESSION['u_uid']);
		mysqli_stmt_execute($stmt);

		header("Location: index.php?level1=cancelled");
		exit();
	}
	
	if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 &&
	date("Y-m-d H:i:s") > $row['expirydate'] && $row['paid'] == 1)
	{
		$sql = "UPDATE memberships
		SET subscriptionplan = ?, subscriptionplandate = ?, fees = ?, expirydate = ?, paidbydate = ?, emailreminder = ?, overdue = ?, activate = ?
		WHERE user_uid = ?;
		";
		$stmt = mysqli_prepare($conn,$sql);
		mysqli_stmt_bind_param($stmt, "ssissiiis", $subscriptionplandelete, $subscriptionplandatedelete, $feesdelete, $expirydatedelete, $paidbydatedelete, $emailreminderreset, $overduedelete, $activatedelete, $_SESSION['u_uid']);
		mysqli_stmt_execute($stmt);

		header("Location: index.php?level1=expired");
		exit();
	}
}

once you enable exceptions, php will catch the database statement errors, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. if you have php's error_reporting set to report all errors and either display them or log them, you should start getting errors at the relevant execute() calls telling you why the query is failing.

 

Link to comment
Share on other sites

That is a good idea... I should set my error reporting on but I remember that it did work before and I do understand what you mean by table normalisation and perhaps i should include a separate table called payment. I have payment and subscriptions together but got a total of 15 variables in one table...

Link to comment
Share on other sites

As the values in your query are null or literal constants there is no need for all those parameters

 else {
                if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['emailreminder'] == 1 && date("Y-m-d H:i:s") > $row['paidbydate'] && $row['paid'] == 0 && $row['overdue'] == 1) {

                    $sql = "UPDATE memberships
                            SET subscriptionplan = null, subscriptionplandate = null, fees = null, expirydate = null, paidbydate = null, 
                                emailreminder = null, overdue = 0, activate = 0
                            WHERE user_uid = ?;
                            ";

                    $stmt = mysqli_stmt_init($conn);
                    if(!mysqli_stmt_prepare($stmt, $sql)) {
                        echo "SQL error";

                    } else {
                        mysqli_stmt_bind_param($stmt, "s",  $_SESSION['user_uid']);
                        mysqli_stmt_execute($stmt);

                    }

                    header("Location: index.php?level1=cancelled");
                    exit();

Similarly, your other query need only be

$sql = "UPDATE memberships
		SET paidbydate = CURDATE() + INTERVAL 2 DAY, emailreminder = 1, overdue = 1
		WHERE user_uid = ?;

* * *

On the question of && and ||
 

if (A && B && C) {
    // only executes if conditions A, B and C *all* evaluate to true
}

if (A || B || C) {
    // executes if any one or more of the conditions evaluates to true
}

 

Link to comment
Share on other sites

I forgot to mention though, wouldn't I need to use while ($row=mysqli_fetch_assoc($result); if I were to display things like $row['user'] etc? because for one of my other codes that I have, I need to display some information like here and I am not sure how to make the title bigger with CSS because it is not an image class, so would I need to do a div class?

 

 include_once 'includes/dbh.php';
   $sql = "SELECT * FROM users WHERE user_uid = ?;";

         $stmt = mysqli_stmt_init($conn);

          if (!mysqli_stmt_prepare($stmt, $sql)) {
            header("Location: header.php?index=notexists");
             exit();
          } else {
              mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
              mysqli_stmt_execute($stmt);
              
              $result = mysqli_stmt_get_result($stmt);
           
              
              while ($row = mysqli_fetch_assoc($result)) {
               echo '<div class="heading">';
               echo '<h1>Welcome to PianoCourse101 '.$row['user_first'].' '.$row['user_last'].'</h1>';
              echo '</div>';
               echo '<br></br>';
               echo '<h1>Below are your general information. Please feel free to update them in the update section</h1>';
               echo '<br></br>';
               echo '<h1>Email Address: '.$row['user_email'].'</h1>';
               echo '<br></br>';
               echo '<h1>User Permission: '.$row['user_permission'].'</h1>';
               echo '<br></br>';
               echo '<h1>Lesson Subscriptionplan: '.$row['freelesson'].'</h1>';
               echo '<br></br>';
               echo '<h1>Date of Subscription: '.$row['datejoined'].'</h1>';
               echo '<br></br>';
               echo '<h1>Last Login: '.$row['user_session'].'</h1>';
               echo '<br></br>';
               echo '<h1><a href="practice_diary.php">Click here to access your practice diary!: </a></h1>';
               echo '<br></br>';
               echo '<h1><a href="refer.php">Click here to access your referral page!: </a></h1>';
                echo '<br></br>';
               echo '<h1><a href="redeem.php">Click here to redeem your points!!: </a></h1>';
               echo '<br></br>';
               echo '<h1><a href="uploadform.php">Click here to upload your picture profile!: </a></h1>';
               echo '<br></br>';
               echo '<h1><a href="deleteprofile.php">Click here to delete your picture profile!: </a></h1>';
               

      }
    }

This is my css code for a div class:

div.heading {
  text-align: center !important;
  font-size: 20px;
 

}

Currently, it looks like this on my screen: it is too far to the right...

 

image.png.8dd349729d2658a3b122465b40e746b6.png

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.