can't insert into my memberships table and trying to implode as well

Question

gdi888 0 Newbie Poster

8 Years Ago

Hey guys!

For the past few days, my database was working fine for both tables but just recently (I must have done something to my code but can't remember), my memberships table has stopped inserting information from my sql query. I am also trying to get my multiple select option to work and I am trying to learn how to use implode. I am not getting any errors but I think the problem is in my signup2.php file... Attach are both my signup2.php and my html form at signup.php. I am a newbie so am trying to understand php and would appreciate any kind assistance... This is my signup.php file

if(isset($_SESSION['u_uid'])) {
   header("Location: index.php?signup=mustsignupfirst");
   exit();

} else {
   include_once 'index.php';
   include_once 'includes/dbh.php';
}

?>

<section class="main-container">
   <div class="main-wrapper">
      <h2>Signup</h2>
      <form class="signup-form" action="includes/signup2.php" method="POST">
         <label>Firstname</label>
         <br></br>
         <input type="text" name="first" placeholder="Firstname">
         <label>Lastname</label>
         <br></br>
         <input type="text" name="last" placeholder="Lastname">
         <label>E-Mail</label>
         <br></br>
         <input type="text" name="email" placeholder="E-mail">
         <label>Username</label>
         <br></br>
         <input type="text" name="uid" placeholder="Username">
         <label>Password</label>
         <br></br>
         <input type="password" name="pwd" placeholder="Password">
         <label>Basic Subscription Plan</label>
         <br></br>
         <select name="freelesson">
            <option value="Primer Level">Primer Level: Free</option>
         </select>
         <br></br>
          <label>Premium Subscription Plan 1</label>
          <br></br>
         <select name="subscriptionplan[]" multiple="multiple">
            <option value="">Choose Subscription Plan 1</option>
            <option value="None">None</option>
            <option value="Level 1">Level 1</option>
            <option value="Level 2">Level 2</option>
            <option value="Level 3">Level 3</option>
         </select>
         <br></br>

         <button type="submit" name="submit">Sign up</button>
      </form>
      <?php
         $fullUrl = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";

         if (strpos($fullUrl, "signup=empty") == true) {
               echo "<br>";
               echo "<p class='error'>You did not fill in all fields!</p>";
         }
         elseif (strpos($fullUrl, "signup=invalid") == true) {
               echo "<br>";
               echo "<p class='error'>Your first name and last name have invalid characters!</p>";
         }
         elseif (strpos($fullUrl, "signup=email") == true) {
               echo "<br>";
               echo "<p class='error'>You have an invalid email address!</p>";
         }
         elseif (strpos($fullUrl, "signup=notalphanumeric") == true) {
               echo "<br>";
               echo "<p class='error'>Password has to contain both letters and numbers!</p>";
         }
         elseif (strpos($fullUrl, "signup=usertaken") == true) {
               echo "<br>";
               echo "<p class='error'>Username already taken!</p>";
         }

         elseif (strpos($fullUrl, "signup=success") == true) {
               echo "<br>";
               echo "<p class='success'>You have been signed up!</p>";
         }

         elseif (strpos($fullUrl, "signup=chooseoneplanonly") == true) {
               echo "<br>";
               echo "<p class='error'>You can't choose more than one of the same plan!</p>";
         }

         elseif (strpos($fullUrl, "signup.php?signup=cannotchoosealllevel1") == true) {
               echo "<br>";
               echo "<p class='error'>Cannot choose the same level plan!</p>";
         }

         elseif (strpos($fullUrl, "signup.php?signup=cannotchoosealllevel2") == true) {
               echo "<br>";
               echo "<p class='error'>Cannot choose the same level plan!</p>";
         }

         elseif (strpos($fullUrl, "signup.php?signup=cannotchoosealllevel3") == true) {
               echo "<br>";
               echo "<p class='error'>Cannot choose the same level plan!</p>";
         }

         elseif (strpos($fullUrl, "signup=mustchooseplan") == true) {
               echo "<br>";
               echo "<p class='error'>Must choose something from the premium plan!</p>";
         }

      ?>
   </div>
</section>

<?php
   include_once 'footer.php';

 ?>

This is my signup2.php file

<?php

   if (!isset($_POST['submit'])) {
      header("Location: ../signup.php");
      exit();
   } else {

       include_once 'dbh.php';

       // information from users table

       $first = $_POST['first'];
       $last = $_POST['last'];
       $email = $_POST['email'] ;
       $uid = $_POST['uid'];
       $password = $_POST['pwd'];
       $user_permission = 'Standard User';
       $freelesson = $_POST['freelesson'];
       $datejoined = date('Y-m-d H:i:s', strtotime("+1 day"));
       $user_activate = 0;
       $premium = 0;

      // information from memberships table

     //  $subscriptionplan = implode(',', $subscriptionplan);
       $subscriptionplan = $_POST['subscriptionplan'];
       $subscriptionplan = implode(',', $subscriptionplan);

       $subscriptionplandate = date('Y-m-d H:i:s', strtotime("+1 day"));
      $subscriptionplandate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
       $subscriptionplandate3 = date('Y-m-d H:i:s', strtotime("+1 day"));
       $fees =  0;
       $fees2 = 0;
       $fees3 = 0;
       $totalfees = 0;
       $paid = 0;
       $paid2 = 0;
       $paid3 = 0;
       $expirydate = date('Y-m-d H:i:s', strtotime("+1 day"));
       $expirydate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
       $expirydate3 = date('Y-m-d H:i:s', strtotime("+1 day"));
       $paidbydate = date('Y-m-d H:i:s', strtotime("+1 day"));
       $paidbydate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
       $paidbydate3 =  date('Y-m-d H:i:s', strtotime("+1 day"));
       $overdue = 0;
       $overdue2 = 0;
       $overdue3 = 0;

      // $fees = mysqli_real_escape_string($conn, $_POST['fees']);
      // $totalfees = mysqli_real_escape_string($conn, $_POST['totalfees']);

       $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 {
                        if($subscriptionplan == '') {
                            header("Location: ../signup.php?signup=mustchooseplan");
                            exit();
                        } 

                      $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, token2, 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();
                        }
                 }
             }
         }

       }
      }
    }

   /*

database-design php

2 Contributors
1 Reply
324 Views
1 Day Discussion Span
Latest Post 8 Years Ago Latest Post by benanamen

Dani AI

Generated 9 Months Ago

Several concrete issues in the posted signup2.php explain why the memberships insert never runs — and they’re easy to fix.

First, two definite bugs visible in the code: the SELECT that should check for an existing username is prepared and bound but never executed, and you then test an undefined variable $resultCheck. That means the “user taken” check never actually runs. Second, you call implode() on $_POST['subscriptionplan'] without first checking that key exists / is an array, which can yield an empty string or warnings. Finally, prepare/execute failures are reported only as the generic "SQL error", which hides the real MySQL error (and also hides things like unique/FK constraint failures). was right to point at the DB — check table constraints and column names too (does memberships expect a user_id or a user_uid? a UNIQUE key on user_uid will block duplicate inserts).

Immediate fixes (apply while debugging):

Guard the multi-select input before imploding:

$subscriptionplan = '';
if (!empty($_POST['subscriptionplan']) && is_array($_POST['subscriptionplan'])) {
$subscriptionplan = implode(',', $_POST['subscriptionplan']);
}

Actually run the SELECT and get a row count:

if (mysqli_stmt_prepare($stmt, $sql)) {
mysqli_stmt_bind_param($stmt, 's', $uid);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);
if ($resultCheck > 0) { /* handle username taken */ }
} else {
die('Prepare failed: ' . mysqli_error($conn));
}

Debugging checklist

Turn on errors while testing: ini_set('display_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); — then check mysqli_stmt_error() / mysqli_error() after failures.
After each INSERT, inspect mysqli_stmt_affected_rows() and mysqli_errno() to catch constraint/permission errors.
Verify memberships table schema (DESCRIBE memberships or SHOW CREATE TABLE memberships) — confirm column names, NOT NULLs, defaults, UNIQUE or FK constraints. If memberships links by an auto-increment id, use mysqli_insert_id() for the FK.
Consider wrapping user + membership inserts in a transaction so you don’t end up with a user row and no membership row on failure.

Small usability notes: use the submitted email for mailing (don’t hard-code), and only enable detailed error display during development. These steps should reveal the actual DB error and get the memberships insert working.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

benanamen 69 Light Poster · Answer 1 · 2018-06-04T21:13:20+00:00

As you have been told by many on the other forum you crossposted on, you need to fix your Database before you do anything.