CODEWITHSUNDEEP
phpmysqlpdo
Jack
Jack

Reputation: 95

How to carry over an ID to a different table when inserting using PDO and MySQL PHP

I am trying to carry over the userID from the Student table to the assessment table when the student adds a new subject so their subjects remain unique to them from the userID they have.

However i can't seem to ask the right question to get the answer i am looking for so i am assuming i have to get them a unique session when they log in that is their userID.

I then proceeded to add a hidden input so when the form is submitted MySQL is getting that userID so it can be added in however it doesn't seem to work*...

*Everything works as it should however the userID field in the assessment table remains 0

Surely there is a more effective way around this..

Here is what i have done below..

Once logged in i create a session - 

$_SESSION['userID'] = $_POST['userID'];

My PDO + MySQL - 

  $assessName = $_POST["assessName"];
  $assessSubject = $_POST['assessSubject'];
  $duedate = $_POST['duedate'];
  $userID = $_POST['userID'];


      $sql = "INSERT INTO assessment (assessName, assessSubject, duedate, userID) VALUES (:assessName, :assessSubject, :duedate, :userID)";
      $statement = $conn->prepare($sql);
      $statement->bindValue(':assessName', $assessName);
      $statement->bindValue(':assessSubject', $assessSubject);
      $statement->bindValue(':duedate', $duedate);
      $statement->bindValue(':userID', $userID);
      $result = $statement->execute();
      $statement->closeCursor();


      header("location: ../view/success.php");
      return $result;

Form - 

  <form action="<?php echo $_SERVER["PHP_SELF"];?>" method="post">
                <div class="form-group">
                <label>Assessment Name</label>
                <input type="text" class="form-control" id="assessName" name="assessName" placeholder="Enter Name" required>
                </div>
                <div class="form-group">
                <label>Subject</label>
                  <input type="text" class="form-control" id="assessSubject" name="assessSubject" placeholder="Enter Subject" required>
                <span class="error"><?php echo $Variable_error;?></span>
                </div>
                <div class="form-group">
                  <div class="dateBox">
                <label>Date</label>
                <input type="text" class="form-control" id="datepicker" name="duedate" placeholder="Choose Due Date" autocomplete="off" required>
              </div>
              <input type="hidden" name="userID" value="<?php $_SESSION['userID'] ?> "/>
                </div>
                <input type="submit" name="submit" value="Submit" class="btn btn-primary"></input>

        </form>

Thanks!

EDIT -

Student Table

    $sql = 'SELECT * FROM student WHERE username = :username AND password = 
:password';
$statement = $conn->prepare($sql);
$statement->bindValue(':username', $username);
$statement->bindValue(':password', $password);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
$count = $statement->rowCount();
return $count;

Upvotes: 1

Views: 88

Answers (1)

Jack
Jack

Reputation: 95

So my issue laid in my Student Table i needed to add 

$_SESSION['uid'] = $result[0]['studentid'];

Before my $count variable was used...

$sql = 'SELECT * FROM student WHERE username = :username AND password = :password';
$statement = $conn->prepare($sql);
$statement->bindValue(':username', $username);
$statement->bindValue(':password', $password);
$statement->execute();

$result = $statement->fetchAll();
$_SESSION['uid'] = $result[0]['studentid'];
$statement->closeCursor();
$count = $statement->rowCount();
return $count;

Upvotes: 1

Related Questions