I guess it would be easier to allow the user to choose 1 service at a time because I have assigned an expiry date for the user and at the end of the expiry date, their service would be terminated. Is there a way to assign multiple expiry dates but I am not sure how this would work as I would have to do an if statement before inserting into the database…
That makes no sense. If a service expires at one date, how can it expire on another date?
I guess you are right that it doesn’t make sense but I am thinking that if the user can subscribe one service today and another in a few weeks time, how would this work?
you need to store the expiry date per user for every plan.
create table userplans(
user_id,
plan_id,
expiry
)
I have tried to add the following codes ::
<?php
if (!isset($_POST['submit'])) {
header("Location: ../signup.php");
exit();
} else {
include_once 'dbh.php';
$first = mysqli_real_escape_string($conn, $_POST['first']);
$last = mysqli_real_escape_string($conn, $_POST['last']);
$email = mysqli_real_escape_string($conn, $_POST['email']) ;
$uid = mysqli_real_escape_string($conn, $_POST['uid']);
$password = mysqli_real_escape_string($conn, $_POST['pwd']);
$user_permission = mysqli_real_escape_string($conn, $user_permission = 'Standard User');
$subscriptionplan1 = mysqli_real_escape_string($conn, $_POST['subscriptionplan1']);
$subscriptionplan2 = mysqli_real_escape_string($conn, $_POST['subscriptionplan2']);
$subscriptionplan3 = mysqli_real_escape_string($conn,$_POST['subscriptionplan3']);
$subscriptionplan4 = mysqli_real_escape_string($conn, $_POST['subscriptionplan4']);
$user_activate = mysqli_real_escape_string($conn, $user_activate = 0);
$overdue = mysqli_real_escape_string($conn, $overdue=0);
$penalty_amount = mysqli_real_escape_string($conn, $penalty_amount=0);
$fees = mysqli_real_escape_string($conn, $fees=0);
//Error handlers...
if (empty($first) || empty($last) || empty($email) || empty($uid)|| empty($password)) {
header("Location: ../signup.php?signup=empty");
exit();
} else {
//Check if input characters are valid
if (!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last)) {
header("Location: ../signup.php?signup=invalid");
exit();
} else {
//Checking for valid emails
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
header("Location: ../signup.php?signup=email");
exit();
} else {
if (Strlen($password) < 5) {
header("Location: ../signup.php?signup=invvalidlength");
exit();
} else {
if(!preg_match('/^(?=.*\d)(?=.*[A-Za-z])[0-9A-Za-z!@#$%]{8,20}$/', $password)) {
header ("Location: ../signup.php?signup=notalphanumeric");
exit();
} else {
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
header("Location: ../signup.php?signup=usertaken");
exit();
} else {
if ($subscriptionplan1 == '' && $subscription2 == '' && $subscriptionplan3 == '' && $subscriptionplan4 == '') {
header("Location: ../signup.php?signup=emptysubscription");
exit ();
} else {
if ($subscriptionplan1 == 'Primer Level' && $subscriptionplan2 == 'Primer Level' && $subscriptionplan3 == 'Primer Level' && $subscriptionplan4 = 'Primer Level') {
header("Location: ../signup.php?signup=samesubscriptionerror");
exit();
} else {
if ($subscriptionplan1 == 'Level 1' && $subscriptionplan2 == 'Level 1' && $subscriptionplan3 == 'Level 1' && $subscriptionplan4 == 'Level 1') {
header("Location: ../signup.php?signup=samesubscriptionerror");
exit();
} else {
if ($subscriptionplan1 == 'Level 2' && $subscriptionplan2 == 'Level 2' && $subscriptionplan3 == 'Level 2' && $subscriptionplan4 == 'Level 2') {
header("Location: ../signup.php?signup=samesubscriptionerror");
exit();
} else {
if ($subscriptionplan1 == 'Level 3' && $subscriptionplan2 == 'Level 3' && $subscriptionplan3 == 'Level 3' && $subscriptionplan4 == 'Level 3') {
header("Location: ../signup.php?signup=samesubscriptionerror");
exit();
} else {
if ($subscriptionplan1 == 'Primer Level' && $subscriptionplan2 == 'Primer Level' || $subscriptionplan3 == 'Primer Level' && $subscriptionplan4 == 'Primer Level' || $subscriptionplan2 == 'Primer Level' && $subscription3 == 'Primer Level') {
header("Location: ../signup.php?signup=chooseoneplanomly");
exit();
} else{
if ($subscriptionplan1 == 'Level 1' && $subscriptionplan2 == 'Level 1' || $subscriptionplan3 == 'Level 1' && $subscriptionplan4 == 'Level 1' || $subscriptionplan2 == 'Level 1' && $subscriptionplan3 == 'Level 1') {
header("Location: ../signup.php?signup=chooseoneplanomly");
exit();
} else {
if ($subscriptionplan1 == 'Level 2' && $subscriptionplan2 == 'Level 2' || $subscriptionplan3 == 'Level 2' && $subscriptionplan4 == 'Level 2' || $subscriptionplan2 == 'Level 2' && $subscriptionplan3 == 'Level 2') {
header("Location: ../signup.php?signup=chooseoneplanomly");
exit();
} else {
if ($subscriptionplan1 == 'Level 3' && $subscriptionplan2 == 'Level 3' || $subscriptionplan3 == 'Level 3' && $subscriptionplan4 == 'Level 3' || $subscriptionplan2 == 'Level 3' && $subscriptionplan3 == 'Level 3') {
header("Location: ../signup.php?signup=chooseoneplanomly");
exit();
} else {
if ($subscriptionplan1 == 'Primer Level' || $subscriptionplan2 == 'Primer Level' || $subscriptionplan3 == 'Primer Level' || $subscriptionplan4 == 'Primer Level') {
$fees = 0;
} else {
if ($subscriptionplan1 == 'Level 1' || $subscriptionplan2 == 'Level 1' || $subscriptionplan3 == 'Level 1' || $subscriptionplan4 == 'Level 1') {
$fees = $fees + 100;
} else {
if ($subscriptionplan1 == 'Level 2' || $subscriptionplan2 == 'Level 2' || $subscriptionplan3 == 'Level 2' || $subscriptionplan4 == 'Level 2') {
$fees = $fees + 150;
} else {
if ($subscriptionplan1 == 'Level 3' || $subscriptionplan2 == 'Level 3' || $subscriptionplan3 == 'Level 3' || $subscriptionplan4 == 'Level 3') {
$fees = $fees + 200;
}
}
}
}
}
}
}
}
}
}
}
}
}
}
// Select subscription based on user id
$sql = "SELECT dateofexpiry1, dateofexpiry2, dateofexpiry3 from users WHERE user_uid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
if ($dateofexpiry1 != '') {
$dateofexpiry2 = now() + interval 1 month;
} else {
if($dateofexpiry2 != '') {
$dateofexpiry3 = now() + interval 1 month;
}
}
}
// Supply a random generated token for email activation
$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
$token = str_shuffle($token);
$token = substr($token, 0, 10);
//Hashing the password
$hashedPwd = password_hash($password, PASSWORD_DEFAULT);
//Insert the user into the database
$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, subscriptionplan1, subscriptionplan2, subscriptionplan3, subscriptionplan4, totalfees, paidbydate, datejoined, dateofexpiry1, dateofexpiry2, dateofexpiry3, overdue, penalty_amount, user_token, user_activate) VALUES ('$first', '$last', '$email', '$uid', '$hashedPwd', '$user_permission', '$subscriptionplan1', '$subscriptionplan2', '$subscriptionplan3', '$subscriptionplan4', '$fees', now() + interval '1' week, now(), now() + interval '1' month, now(), now(), '$overdue', '$penalty_amount', '$token', '$user_activate');";
$name = $first .$last;
$to = $email;
$header = 'From: pianocourse101@hotmail.com';
$subject = 'Email activation is required';
$message = <<<EMAIL
Hello $first $last,
Thanks for registering with PianoCourse101!
Please activate your account below before you
can login. Activate your account by clicking
on the following link below....
http://localhost/loginsystem/includes/activate.php?email=$to&activatetoken=$token
EMAIL;
mail($to, $subject, $message, $header);
mysqli_query($conn, $sql);
// insert into permissions table
header("Location: ../signup.php?signup=success");
exit();
}
}
}
}
}
}
}
The new section of the code starts here:
// Select subscription based on user id
$sql = "SELECT dateofexpiry1, dateofexpiry2, dateofexpiry3 from users WHERE user_uid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
if ($dateofexpiry1 != '') {
$dateofexpiry2 = now() + interval 1 month;
} else {
if($dateofexpiry2 != '') {
$dateofexpiry3 = now() + interval 1 month;
}
}
}
But I get this error:
Parse error: syntax error, unexpected ‘1’ (T_LNUMBER) in C:\xampp\htdocs\loginsystem\includes\signup2.php on line 169
This is not valid PHP syntax.
1 Like
Muddling SQL with PHP.
But it does work when I do this directly into the sql = "insert into… " query with now () + interval 1 month but when I wrote this code…
$sql = "SELECT dateofexpiry1, dateofexpiry2, dateofexpiry3 from users WHERE user_uid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
if ($row['dateofexpiry1'] != '') {
$row['dateofexpiry2'] = now() + 'interval 1 day';
} else {
if($row['dateofexpiry2'] != '') {
$row['dateofexpiry3'] = now() + 'interval 1 day';
} else {
if($row['dateofexpiry1'] == '') {
$row['dateofexpiry'] = now() + 'interval 1 day';
}
}
}
}
}
}
}
It doesn’t work… just shows the date as zeros…I haven’t tried it yet but saw somewhere that it should be… $row [‘date1’] = ‘now () + interval 1 month’;
I might have placed the ‘’ incorrectly. …
^ This!
Yes, because NOW() + interval 1 month is SQL syntax.
It is not PHP syntax.
They are two different languages, you cannot mix-and-match different languages and syntax like that.
When you write:-
$query = "SELECT something FROM sometable WHERE this = that" ;
…in PHP, you are merely creating a sting containing an SQL query, written in SQL syntax to pass on to the database which understands that syntax. It does not mean that SQL syntax works in PHP.
So SQL syntax should only be seen in PHP as part of a string to be used as a query which is passed on to SQL.
So if I were to write…
$row['date1'] = 'now () + interval 1 month ';
That should work?
Define work…
If work means assign a string value to an associative array called $row, then yes.
If work means something else, that line of code may have to appear in the context of a script, with a definition of what work means, to get any kind of answer.
I tried it and it doesn’t insert multiple dates for multiple services… This is what it looks like in my database
and my code is:
<?php
if (!isset($_POST['submit'])) {
header("Location: ../signup.php");
exit();
} else {
include_once 'dbh.php';
$first = mysqli_real_escape_string($conn, $_POST['first']);
$last = mysqli_real_escape_string($conn, $_POST['last']);
$email = mysqli_real_escape_string($conn, $_POST['email']) ;
$uid = mysqli_real_escape_string($conn, $_POST['uid']);
$password = mysqli_real_escape_string($conn, $_POST['pwd']);
$user_permission = mysqli_real_escape_string($conn, $user_permission = 'Standard User');
$subscriptionplan1 = mysqli_real_escape_string($conn, $_POST['subscriptionplan1']);
$subscriptionplan2 = mysqli_real_escape_string($conn, $_POST['subscriptionplan2']);
$subscriptionplan3 = mysqli_real_escape_string($conn,$_POST['subscriptionplan3']);
$subscriptionplan4 = mysqli_real_escape_string($conn, $_POST['subscriptionplan4']);
$user_activate = mysqli_real_escape_string($conn, $user_activate = 0);
$overdue = mysqli_real_escape_string($conn, $overdue=0);
$penalty_amount = mysqli_real_escape_string($conn, $penalty_amount=0);
$fees = mysqli_real_escape_string($conn, $fees=0);
//Error handlers...
if (empty($first) || empty($last) || empty($email) || empty($uid)|| empty($password)) {
header("Location: ../signup.php?signup=empty");
exit();
} else {
//Check if input characters are valid
if (!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last)) {
header("Location: ../signup.php?signup=invalid");
exit();
} else {
//Checking for valid emails
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
header("Location: ../signup.php?signup=email");
exit();
} else {
if (Strlen($password) < 5) {
header("Location: ../signup.php?signup=invalidlength");
exit();
} else {
if(!preg_match('/^(?=.*\d)(?=.*[A-Za-z])[0-9A-Za-z!@#$%]{8,20}$/', $password)) {
header ("Location: ../signup.php?signup=notalphanumeric");
exit();
} else {
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
header("Location: ../signup.php?signup=usertaken");
exit();
} else {
// Select subscription based on user id for multiple services
$sql = "SELECT dateofexpiry1, dateofexpiry2, dateofexpiry3 from users WHERE user_uid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
if ($row['dateofexpiry1'] != '') {
$sql = "INSERT INTO users (dateofexpiry2) VALUES (now() + interval '1' month)";
mysqli_query($conn, $sql);
} else {
if ($row['dateofexpiry2'] != '') {
$sql = "INSERT INTO users (dateofexpiry3) VALUES (now() + interval '1' month)";
mysqli_query($conn, $sql);
} else {
}
}
}
}
}
The main problem that I am having is from this point…
$sql = "SELECT dateofexpiry1, dateofexpiry2, dateofexpiry3 from users WHERE user_uid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
if ($row['dateofexpiry1'] != '') {
$sql = "INSERT INTO users (dateofexpiry2) VALUES (now() + interval '1' month)";
mysqli_query($conn, $sql);
} else {
if ($row['dateofexpiry2'] != '') {
$sql = "INSERT INTO users (dateofexpiry3) VALUES (now() + interval '1' month)";
mysqli_query($conn, $sql);
} else {
}
}
}
}
}
Is it possible to do this? or should I have another section called update lesson plan, where the user can select another plan?
// Supply a random generated token for email activation
$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
$token = str_shuffle($token);
$token = substr($token, 0, 10);
//Hashing the password
$hashedPwd = password_hash($password, PASSWORD_DEFAULT);
//Insert the user into the database
$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, subscriptionplan1, subscriptionplan2, subscriptionplan3, subscriptionplan4, totalfees, paidbydate, datejoined, dateofexpiry1, overdue, penalty_amount, user_token, user_activate) VALUES ('$first', '$last', '$email', '$uid', '$hashedPwd', '$user_permission', '$subscriptionplan1', '$subscriptionplan2', '$subscriptionplan3', '$subscriptionplan4', '$fees', now() + interval '1' week, now(), '$dateofexpiry1', '$overdue', '$penalty_amount', '$token', '$user_activate');";
$name = $first .$last;
$to = $email;
$header = 'From: pianocourse101@hotmail.com';
$subject = 'Email activation is required';
$message = <<<EMAIL
Hello $first $last,
Thanks for registering with PianoCourse101!
Please activate your account below before you
can login. Activate your account by clicking
on the following link below....
http://localhost/loginsystem/includes/activate.php?email=$to&activatetoken=$token
EMAIL;
mail($to, $subject, $message, $header);
mysqli_query($conn, $sql);
// insert into permissions table
header("Location: ../signup.php?signup=success");
exit();
}
}
}
}
}
}
Maybe i am overthinking it but I can get it to work if I enrol the user first to accesz the free level contens and then letting the user upgrade his or her plan and using another table… is this a better idea?
Is this really what you want to do?
$sql = "INSERT INTO users (dateofexpiry2) VALUES (now() + interval '1' month)";
This query will insert a new row into your users table, which only contains a single date and nothing else, in particular no user_uid, or maybe just a new user_uid value if you have that set as an auto-incrementing column. It might not insert at all, if you have any other columns that cannot be null.
I would imagine that this should be an UPDATE query to set the various dates in the existing row. The logic seems incorrect though, but that might be that I am not understanding the relationship between the three dates.
I guesz my original plan was not logical because it is now only working if I sign up the user first for a free account and then allowing him or her to upgrade their account.
I can’t get the user to sign up for multiple plans straight up…
If the “multiple plans” are just a case of setting the multiple expiry dates in your table, then there is no reason that they could not all be done at the same time. As someone else said in another thread, though, it’s not a good database layout. What happens when you have 25 different plans they can choose from - are you going to have 25 columns for the expiry dates?
So I guess that it is common practice to have a separate table for a different service…
If you have a one-to-many relationship between users and services - that is, one user can have many services - then a separate table would be the sensible way to configure things. So you would have three tables: the users table, the services table, and the one that describes which services a user has purchased / subscribed to.
Have a read up on “Database normalisation”, that normally gives good advice on how things should be organised.
3 Likes
The keyword is “relational”.
When I started I used to create “one big table with everything”. For example, it seemed foolish to have three tables that could work by joining the three on id
id - firstname
1 - Bill
id - lastname
1 - Gates
id - email
1 - bill@microsoft.com
instead of
id - firstname - lastname - email
1 - Bill - Gates - bill@microsoft.com
But then if later he had a second email I needed to alter the table to have a second email field. If he had a third, fourth, fifth, etc. I needed to add corresponding fields.
Meanwhile, for those with only a single email, all those additional fields went unused.
In db-speak, this is a one-to-many relationship. ie. one user can have many email addresses.
So a more efficient and more maintainable approach is to have separate tables for users and emails.
Because a user can only have a single first name and a single last name (not entirely true, but you get the idea) they are OK in the user table
id - firstname - lastname
1 - Bill - Gates
id - email
1 - bill@microsoft.com
1 - bg@ microsoft.com
1 - bill.gates@microsoft.com
By putting the emails into a separate table, it means the queries need to use joins so are a bit more complex, but is a much better database design.
3 Likes
I have this page where I can show user’s their details and to provide them the option to upgrade their plan but I tried to include all my 3 services in one tags would be better as shown:
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta charset="utf-8">
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
</body>
</html>
<?php
include_once 'dbh.php';
session_start();
$sql = "SELECT user_first, user_last, user_email, user_uid, freelesson, datejoined FROM users WHERE user_uid = '".$_SESSION['u_uid']."'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo 'Welcome '.$row['user_first']. ' ' .$row['user_last'];
echo '<br></br>';
echo 'Here are your profile details!';
echo '<br></br>';
echo 'Email Address: '.$row['user_email'];
echo '<br></br>';
echo 'User ID: '.$row['user_uid'];
echo '<br></br>';
echo 'Lesson Currently Enrolled: '.$row['freelesson'];
echo '<br></br>';
echo 'Date Joined: '.$row['datejoined'];
}
}
$sql = "SELECT * FROM user_lessonsubscription WHERE user_uid = '".$_SESSION['u_uid']."'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo '<br></br>';
echo 'Premium Plan Enrolled:' .$row['subscriptionplan']. '.The fee is: $'.$row['fees'];
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<form action="subscription.php" method="POST">
<select name="subscriptionplan">
<option value="">Choose Plan 1</option>
<option value="Level 1">Level 1:$100/month</option>
</select>
<select name="subscriptionplan2">
<option value="">Choose Plan 2</option>
<option value="Level 2">Level 2:$150/month</option>
</select>
<select name="subscriptionplan3">
<option value="">Choose Plan 3</option>
<option value="Level 3">Level 3:$200/month</option>
</select>
<button type="submit" value="submit">Submit</button>
</form>
</body>
</html>
I will also supply my other subscription page here:
<?php
include_once 'dbh.php';
include_once '../index.php';
session_start();
$fees =0;
$user_uid = mysqli_real_escape_string($conn, $_SESSION['u_uid']);
$overduefees = 0;
$subscriptionplan = mysqli_real_escape_string($conn, $_POST['subscriptionplan']);
$subscriptionplan2 = mysqli_real_escape_string($conn, $_POST['subscriptionplan2']);
$subscriptionplan3 = mysqli_real_escape_string($conn, $_POST['subscriptionplan3']);
if ($subscriptionplan == 'Level 1') {
$fees = 100;
} else {
if ($subscriptionplan2 == 'Level 2') {
$fees = 150;
} else {
if ($subscriptionplan3 == 'Level 3') {
$fees = 200;
}
}
}
// Check to see if lesson plan has been previously selected
$sql = "SELECT * FROM user_lessonsubscription WHERE user_uid='".$_SESSION['u_uid']."'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
if($row['subscriptionplan'] != '') {
header("Location: update.php?update=alreadychosenplan1");
exit ();
} else {
if ($row['subscriptionplan2'] != '') {
header("Location: update.php?update=alreadychosenplan2");
exit();
} else {
if ($row['subscriptionplan3'] != '') {
header("Location: update.php?update=alreadychosenplan3");
exit();
}
}
}
}
}
$sql = "INSERT INTO user_lessonsubscription (user_uid, subscriptionplan, subscriptionplan2, subscriptionplan3, fees, paidbydate, expirydate, overduefees) VALUES ('$user_uid','$subscriptionplan','$subscriptionplan2', '$subscriptionplan3', '$fees', now() + interval '1' week, now() + interval '1' day, '$overduefees')";
mysqli_query($conn, $sql);
// Send email with the user's subscription details
I got it to work now but I can't get my fees to be inserted into the database
header ("Location: update.php?update=success");
exit();
It appears to be inserting all at the same time… Maybe, I should use the update instead but am not sure if my syntax is correct… Must I add “.$subscription.” or is it ‘$subscription’?
I am also wondering if the $fees should be working because it is not inserting into the database…