Jump to content
piano0011

how to update null

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();

 

 

Share this post


Link to post
Share on other sites

What problem are you having?

Share this post


Link to post
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

Edited by piano0011

Share this post


Link to post
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.

Share this post


Link to post
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.

 

Edited by mac_gyver

Share this post


Link to post
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...

Share this post


Link to post
Share on other sites

My other main problem is not understanding fully how the && and || condition works. I guess I have to pay careful attention when using && as it checks for all conditions whether it is true or not...

Share this post


Link to post
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
}

 

Share this post


Link to post
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

Edited by piano0011

Share this post


Link to post
Share on other sites

NULL is trouble.  In PHP it's approximately zero, it's special in the DB and requires that you use differrent syntax to set and/or check for it.

Avoid it when possible and use some other value (like 0).

Share this post


Link to post
Share on other sites

Thanks and if I want to check for null.. would i do.. if (null ($subscriptionplan)) {};

Edited by piano0011

Share this post


Link to post
Share on other sites
SELECT...WHERE subscriptionplan IS NULL

Conversely

SELECT ... WHERE subscriptionplan IS NOT NULL

In PHP

if ($subscriptionplan === null) {}

// or

if (is_null($subscriptionplan) ) {}

 

Edited by Barand

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.