Problem with DB SELECT / UPDATE + Count 30 Days


#1

Hello,

I’m having a problem with calling the DB with SELECT.

I need to call EMAIL, TIME_TS and USERNAME, and TIME_TS counts from 30 days to day 0.

<?php if($account->teamspeak_vip) : ?>
               
               <script>
			   
			   /*
				 * Basic Count Down to Date and Time
				 * Author: @guwii / guwii.com
				 */
				(function (e) {
				  e.fn.countdown = function (t, n) {
					function i() {
					  eventDate = Date.parse(r.date) / 1e3;
					  currentDate = Math.floor(e.now() / 1e3);
					  if (eventDate <= currentDate) {
						n.call(this);
						clearInterval(interval)
					  }
					  seconds = eventDate - currentDate;
					  days = Math.floor(seconds / 86400);
					  seconds -= days * 60 * 60 * 24;
					  hours = Math.floor(seconds / 3600);
					  seconds -= hours * 60 * 60;
					  minutes = Math.floor(seconds / 60);
					  seconds -= minutes * 60;
					  days == 1 ? thisEl.find(".timeRefDays").text("Day Left") : thisEl.find(".timeRefDays").text("Days Left");
					  hours == 1 ? thisEl.find(".timeRefHours").text("hour") : thisEl.find(".timeRefHours").text("hours");
					  minutes == 1 ? thisEl.find(".timeRefMinutes").text("minute") : thisEl.find(".timeRefMinutes").text("minutes");
					  seconds == 1 ? thisEl.find(".timeRefSeconds").text("second") : thisEl.find(".timeRefSeconds").text("seconds");
					  if (r["format"] == "on") {
						days = String(days).length >= 2 ? days : "0" + days;
						hours = String(hours).length >= 2 ? hours : "0" + hours;
						minutes = String(minutes).length >= 2 ? minutes : "0" + minutes;
						seconds = String(seconds).length >= 2 ? seconds : "0" + seconds
					  }
					  if (!isNaN(eventDate)) {
						thisEl.find(".days").text(days);
						thisEl.find(".hours").text(hours);
						thisEl.find(".minutes").text(minutes);
						thisEl.find(".seconds").text(seconds)
					  } else {
						alert("Invalid date. Example: 30 Tuesday 2013 15:50:00");
						clearInterval(interval)
					  }
					}
					var thisEl = e(this);
					var r = {
					  date: null,
					  format: null
					};
					t && e.extend(r, t);
					i();
					interval = setInterval(i, 1e3)
				  }
				})(jQuery);
				$(document).ready(function () {
				  function e() {
					var e = new Date;
					e.setDate(e.getDate() + 60);
					dd = e.getDate();
					mm = e.getMonth() + 1;
					y = e.getFullYear();
					futureFormattedDate = mm + "/" + dd + "/" + y;
					return futureFormattedDate
				  }
				  $("#countdown").countdown({
					date: "10 September 2018 12:30:00", // Change this to your desired date to countdown to
					format: "on"
				  });
				});
				
				</script>
                
                <style>
				#countdown {
					text-align: center;
				}
				#countdown p {
					display: inline-block;
					padding: 2px;
					background: #151515;
					margin: 0 0 20px;
					border-radius: 3px;
					color: white;
					min-width: 1.6rem;
				}
				</style>
                                    
                <span id="countdown">
                  <p class="card-text days"></p>
                  <p class="card-text timeRefDays"></p>
                  <!--<p class="hours">00</p>
                  <p class="timeRefHours">hours</p>
                  <p class="minutes">00</p>
                  <p class="timeRefMinutes">minutes</p>
                  <p class="seconds">00</p>
                  <p class="timeRefSeconds">seconds</p>-->
                </span>

               <?php

                    // Some example function we want to run.
                    function someFunctionToExecute(){
					
						$user_id = $_SESSION['user_id'];
						
						$call_email_username_byusers = "SELECT username, email FROM users WHERE user_id = $user_id";
						$result = $database->query($call_email_username_byusers);
						
						if ($result->num_rows > 0) {
							// output data of each row
							while($row = $result->fetch_assoc()) {
								$username = $row["username"];
								$user_email = $row["email"];
								echo "id: " . $row["username"]. " - email: " . $row["email"]. "<br>";
							}
						} else {
							echo "0 results";
						}
						
                        //$result = $database->query('SELECT * FROM `users` WHERE `user_id`=$user_id');
						$call_time_ts = "INSERT INTO users (time_ts) VALUES (?)";
						$result = $database->query($call_time_ts);
                        while($row = $result->fetch_assoc()) {
                            $time_ts = $row["time_ts"];
                        }
                        if($time_ts>0){
                        //$database->query('UPDATE `users` SET `time_ts`= $time_ts WHERE `user_id` = $user_id');
						$database->query("DELETE `users` SET `time_ts` = $time_ts WHERE `user_id` = $user_id");
						$profile_account->teamspeak_vip == 0;
                        }else{
							$emailContent = file_get_contents('emails/ts_information_users.html', true);
					
							$emailContent = str_replace("{{TS-INFO-USERNAME}}",$language->ts_information->email->username,$emailContent);
							$emailContent = str_replace("{{TS-INFO-UNDER-USER}}",$language->ts_information->email->under_user,$emailContent);
							$emailContent = str_replace("{{TS-INFO-CONTENT}}",$language->ts_information->email->content,$emailContent);
							$emailContent = str_replace("{{TS-INFO-FOOTER}}",$language->footer_emails->footer,$emailContent);
							
							$emailContent = str_replace("{{USERNAME}}",$username,$emailContent);
							$emailContent = str_replace("{{USER-EMAIL}}",$user_email,$emailContent);
							$emailContent = str_replace("{{USERNAME-TS}}",$username_ts,$emailContent);
							$emailContent = str_replace("{{UID-TS}}",$uid_ts,$emailContent);
									
                            sendmail($user_email, $language->resend_activation->email->title, $emailContent);
                        }

                    }

                    ?>

				<?php else : ?>
                
                    <font color="#0C9620"><?= $language->store->teamspeak_vip->limit ?></font>
                                        
                <?php endif; ?>

The issue is to put it counting the 30 days, from the day the button was clicked (not a specific date), in this case I am using “<a href …” and would be to insert in the automatically.

I need to implement the count in php with mysql … for when a person clicks the buy button, start counting 30 days, 29, 28, 27 days etc … until day 0 … and when it reaches day 0, it’s supposed to send an email…

I don’t know if u understand…

Best Regards


#2

So, when the person clicks the “buy” button, you insert a record into the database. Now you have a timestamp of when the clicked the button.


#3

Thanks for your reply…
Now I changed the code… and I can do register the date (current_timestamp) on database. :wink:

But now in javascript how can do the countdown the 30 days to 29 days / 28 days etc… with update on database and call the (if($time_ts = 0) {) when day is 0 …


#4

Why would that be a done in javascript? That should be done on the backend.