2nd table not inserting..


#1

Hey guys!

I hope that I can some assistance here and this has been driving me nutz for a while now… I did get my implode function to work and to insert multiple select options in a single column but suddenly, I can’t get my memberships table to be inserted and I don’t think I made any mistakes or typos… Here are my codes: I am still learning about prepared statement so please bare with me…

[php]<?php
error_reporting(E_ALL);
ini_set(‘display_erros’, ‘On’);

if (!isset($_POST[‘submit’])) {
header(“Location: …/signup.php”);
exit();
} else {

   include_once 'dbh.php';

   // information from users table

   $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');
   $freelesson = mysqli_real_escape_string($conn, $_POST['freelesson']);
   $datejoined =  mysqli_real_escape_string($conn, $datejoined = date('Y-m-d H:i:s', strtotime('+1 day')));
   $user_activate = mysqli_real_escape_string($conn, $user_activate = 0);
   $premium = mysqli_real_escape_string($conn, $premium = 0);



  // information from memberships table

    
   $subscriptionplan = $_POST['subscriptionplan'];
   $subscriptionplan = array_map('mysqli_real_escape_string', $subscriptionplan);
  
   $subscriptionplandate =  mysqli_real_escape_string($conn, $subscriptionplandate = date('Y-m-d H:i:s', strtotime('+1 day')));
   $subscriptionplandate2 =  mysqli_real_escape_string($conn, $subscriptionplandate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $subscriptionplandate3 =  mysqli_real_escape_string($conn, $subscriptionplandate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $fees = mysqli_real_escape_string($conn, $fees = 0);
   $fees2 = mysqli_real_escape_string($conn, $fees2 = 0);
   $fees3 = mysqli_real_escape_string($conn, $fees3 = 0);
   $totalfees = mysqli_real_escape_string($conn, $totalfees = 0);
   $paid = mysqli_real_escape_string($conn, $paid = 0);
   $paid2 = mysqli_real_escape_string($conn, $paid2 = 0);
   $paid3 = mysqli_real_escape_string($conn, $paid3 = 0);
   $expirydate = mysqli_real_escape_string($conn, $expirydate = date('Y-m-d H:i:s', strtotime('+1 day')));
   $expirydate2 = mysqli_real_escape_string($conn, $expirydate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $expirydate3 = mysqli_real_escape_string($conn, $expirydate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $paidbydate = mysqli_real_escape_string($conn, $paidbydate = date('Y-m-d H:i:s', strtotime('+1 day')));
   $paidbydate2 = mysqli_real_escape_string($conn, $paidbydate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $paidbydate3 = mysqli_real_escape_string($conn, $paidbydate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
   $overdue = mysqli_real_escape_string($conn, $overdue = 0);
   $overdue2 = mysqli_real_escape_string($conn, $overdue2 = 0);
   $overdue3 = mysqli_real_escape_string($conn, $overdue2 = 0);
   
  // $fees = mysqli_real_escape_string($conn, $_POST['fees']);
  // $totalfees = mysqli_real_escape_string($conn, $_POST['totalfees']);
   
  
   
   $activate = mysqli_real_escape_string($conn, $activate = 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 WHERE user_uid = ?;";
                  // Create a prepared statement
                  $stmt = mysqli_stmt_init($conn);
                  //Prepare the prepared stement

                  if (!mysqli_stmt_prepare($stmt, $sql)) {
                     echo "SQL statement failed";

                  } else {
                  	   //Bind parameters to the placeholder
                  	mysqli_stmt_bind_param($stmt, "s", $uid);
                  }

                  
                  if($resultCheck > 0) {
                     header("Location: ../signup.php?signup=usertaken");
                     exit();
                  } else {

                
							
                                       
									
						            
                        



       // Select subscription 

      


                                 
   

                     // Supply a random generated token for email activation
                    

                  	$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
                  	$token = str_shuffle($token);
                  	$token = substr($token, 0, 10);

                  	$token2 = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
                    $token2 = str_shuffle($token2);
                  	$token2 = substr($token2, 0, 10);
                    $paid = 0;
                  	
                  	$activate = 0;

                  	//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, freelesson, datejoined, user_token, user_activate, premium) VALUES (?,?,?,?,?,?,?,?,?,?,?);";

                  	$stmt = mysqli_stmt_init($conn);
                  	if(!mysqli_stmt_prepare($stmt, $sql)) {
                  		 echo "SQL error";
                  	} else {
                  		mysqli_stmt_bind_param($stmt, "sssssssssss", $first, $last, $email, $uid, $hashedPwd, $user_permission, $freelesson,$datejoined, $token, $user_activate, $premium);
                  	  mysqli_stmt_execute($stmt);
                  	}

                   // insert into user_lessonsubscription table

                   
                   
                     
                   

                      
                 

               // $subscriptionplan  = implode(',',$subscriptionplan);

                    
                    
                   
              

                  $sql ="INSERT INTO memberships (user_uid, subscriptionplan, subscriptionplandate, subscriptionplandate2, subscriptionplandate3, fees, fees2, fees3, totalfees, paid, paid2, paid3, expirydate, expirydate2, expirydate3, paidbydate, paidbydate2, paidbydate3, overdue, overdue2, overdue3, token, activate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

                  $stmt = mysqli_stmt_init($conn);
                  	if(!mysqli_stmt_prepare($stmt, $sql)) {
                  		 echo "SQL error";
                  	} else {
                  		mysqli_stmt_bind_param($stmt, "sssssssssssssssssssssss", $uid, $subscriptionplan, $subscriptionplandate, $subscriptionplandate2, $subscriptionplandate3, $fees, $fees2, $fees3, $totalfees, $paid, $paid2, $paid3, $expirydate, $expirydate2, $expirydate3, $paidbydate, $paidbydate2, $paidbydate3, $overdue, $overdue2, $overdue3, $token2, $activate);
                  	  mysqli_stmt_execute($stmt);
                  	}

                  


                    // Send an email to the user:
                    $company = "pianocourse101@hotmail.com";
                    $subject = "Activate your email account";
                    $mailTo = "piano0011@hotmail.com";
                    $headers = "From: ".$company;
                   $txt = "Thank you for registering with pianocourse101! At pianocourse101, your child can now learn how to play the piano right from the comfort of your own home! \n\nOur lessons are based from the Bastien Piano Basics series because it is both fun and educational for your child. \n\nHowever, you must activate your FREE membership account by clicking on the link below: \n\n http://localhost/loginsystem/includes/activate.php?email=".$mailTo."&activatetoken=".$token."" ;

                    mail($mailTo, $subject, $txt, $headers);
                    





                  	header("Location: ../signup.php?signup=success");
                  	exit();
                    }
             }
         }
     }
 
   }
   }

}

[/php]


#2
I did get my implode function to work and to insert multiple select options in a single column

NO!!! That is NOT how you do that!

Remove ALL OF THIS:

[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'); $freelesson = mysqli_real_escape_string($conn, $_POST['freelesson']); $datejoined = mysqli_real_escape_string($conn, $datejoined = date('Y-m-d H:i:s', strtotime('+1 day'))); $user_activate = mysqli_real_escape_string($conn, $user_activate = 0); $premium = mysqli_real_escape_string($conn, $premium = 0);
 	   $subscriptionplandate =  mysqli_real_escape_string($conn, $subscriptionplandate = date('Y-m-d H:i:s', strtotime('+1 day')));
 	   $subscriptionplandate2 =  mysqli_real_escape_string($conn, $subscriptionplandate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
 	   $subscriptionplandate3 =  mysqli_real_escape_string($conn, $subscriptionplandate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
 	   $fees = mysqli_real_escape_string($conn, $fees = 0);
     $fees2 = mysqli_real_escape_string($conn, $fees2 = 0);
     $fees3 = mysqli_real_escape_string($conn, $fees3 = 0);
     $totalfees = mysqli_real_escape_string($conn, $totalfees = 0);
     $paid = mysqli_real_escape_string($conn, $paid = 0);
     $paid2 = mysqli_real_escape_string($conn, $paid2 = 0);
     $paid3 = mysqli_real_escape_string($conn, $paid3 = 0);
     $expirydate = mysqli_real_escape_string($conn, $expirydate = date('Y-m-d H:i:s', strtotime('+1 day')));
     $expirydate2 = mysqli_real_escape_string($conn, $expirydate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
     $expirydate3 = mysqli_real_escape_string($conn, $expirydate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
     $paidbydate = mysqli_real_escape_string($conn, $paidbydate = date('Y-m-d H:i:s', strtotime('+1 day')));
     $paidbydate2 = mysqli_real_escape_string($conn, $paidbydate2 = date('Y-m-d H:i:s', strtotime('+1 day')));
     $paidbydate3 = mysqli_real_escape_string($conn, $paidbydate3 = date('Y-m-d H:i:s', strtotime('+1 day')));
     $overdue = mysqli_real_escape_string($conn, $overdue = 0);
     $overdue2 = mysqli_real_escape_string($conn, $overdue2 = 0);
     $overdue3 = mysqli_real_escape_string($conn, $overdue2 = 0);

[/php]

[php] // Supply a random generated token for email activation
                   	$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
                   	$token = str_shuffle($token);
                   	$token = substr($token, 0, 10);

                   	$token2 = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';[/php]</blockquote>

Also no.

[php]INSERT INTO memberships (user_uid, subscriptionplan, subscriptionplandate, subscriptionplandate2, subscriptionplandate3, fees, fees2, fees3, totalfees, paid, paid2, paid3, expirydate, expirydate2, expirydate3, paidbydate, paidbydate2, paidbydate3, overdue, overdue2, overdue3, token, activate)[/php]

Bad data model.

Your email message should actually be in a template file. Makes it easier to modify and update. And the mail() function is notoriously unstable. MailGun would be my recommendation, but there are libraries as well like, PHPMailer and SwiftMail that do an adequate job.


#3

Sample Data Model based on your post.

[php]

create table Members (
uuid char(36) null primary key,
firstname varchar(20) not null,
lastname varchar(20) not null,
email varchar(50) not null,
user_uid varchar(52) not null,
user_password varchar(64) not null,
– user_permission, Don’t know what this is for
freelesson boolean default false,
datejoined datetime not null,
user_token varchar(64) not null,
user_activate boolean default false,
premium boolean default false,
dateAdded timestamp not null default CURRENT_DATE,
userAdded int not null
) engine=InnoDB;

DELIMITER ;;
CREATE TRIGGER before_insert_members
BEFORE INSERT ON Members
FOR EACH ROW
BEGIN
IF new.uuid IS NULL THEN
SET new.uuid = uuid();
END IF;
END
;;

create table MembershipPlan (
id int not null auto_increment primary key,
description varchar(200),
name varchar(20) not null,
cost int not null,
isActive boolean not null default TRUE,
dateAdded timestamp not null default CURRENT_DATE,
userAdded int not null
) engine=InnoDB;

create table Member_Membership (
id int not null auto_increment primary key,
member_id char(36) not null,
membership_id int not null,
activation_date datetime null,
end_date datetime null,
due_day int not null,
dateAdded timestamp not null default CURRENT_DATE,
userAdded int not null,
FOREIGN KEY (member_id) REFERENCES Member(id) ON DELETE CASCADE,
FOREIGN KEY (membership_id) REFERENCES MembershipPlan(id) ON DELETE CASCADE
) engine=InnoDB;

create table Payment (
id int not null auto_increment primary key,
plan_id int not null,
amount int not null,
dateAdded timestamp not null default CURRENT_DATE,
userAdded int not null,
FOREIGN KEY (customer_id) REFERENCES Customer(id) ON DELETE CASCADE,
FOREIGN KEY (plan_id) REFERENCES Member_Membership(id) ON DELETE CASCADE
) engine=InnoDB;

/*
I would add a fee’s table, but I don’t know what they refer too in order to model them effectively
*/
[/php]


#4

OP already has lengthy threads and responses on another forum. OP, you are not going to get any different answers cross posting the same thing on multiple forums. All it does is take up more peoples time.