piano0011 Posted July 9, 2018 Share Posted July 9, 2018 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 More sharing options...
ginerjm Posted July 9, 2018 Share Posted July 9, 2018 What problem are you having? Link to comment Share on other sites More sharing options...
piano0011 Posted July 9, 2018 Author Share Posted July 9, 2018 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 More sharing options...
benanamen Posted July 9, 2018 Share Posted July 9, 2018 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 More sharing options...
mac_gyver Posted July 9, 2018 Share Posted July 9, 2018 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 More sharing options...
piano0011 Posted July 10, 2018 Author Share Posted July 10, 2018 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 More sharing options...
piano0011 Posted July 10, 2018 Author Share Posted July 10, 2018 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... Link to comment Share on other sites More sharing options...
Barand Posted July 10, 2018 Share Posted July 10, 2018 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 More sharing options...
piano0011 Posted July 10, 2018 Author Share Posted July 10, 2018 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... Link to comment Share on other sites More sharing options...
dalecosp Posted July 10, 2018 Share Posted July 10, 2018 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). Link to comment Share on other sites More sharing options...
piano0011 Posted July 11, 2018 Author Share Posted July 11, 2018 Thanks and if I want to check for null.. would i do.. if (null ($subscriptionplan)) {}; Link to comment Share on other sites More sharing options...
Barand Posted July 11, 2018 Share Posted July 11, 2018 SELECT...WHERE subscriptionplan IS NULL Conversely SELECT ... WHERE subscriptionplan IS NOT NULL In PHP if ($subscriptionplan === null) {} // or if (is_null($subscriptionplan) ) {} Link to comment Share on other sites More sharing options...
piano0011 Posted July 12, 2018 Author Share Posted July 12, 2018 Thanks! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.