CODEWITHSUNDEEP
phpsqlpdo
Carlos Perez
Carlos Perez

Reputation: 435

How to insert same value into to tables sql, php

can someone help me with this, Im using a simple form to add user into a database, my goal is to add the user into one table, but at the same time add the (userid) into a different table, the user id is made automatically by the database, it is possible to do this ?

Thank you

 <?php 
    require("coneccion.php"); 

    if(!empty($_POST))
    { 

      $query = "INSERT INTO students (fname, lname, studentcode) values (:fname, :lname, :studentcode)";
      $query_params = array(':fname' => $_POST['fname'], ':lname' => $_POST['lname'], ':studentcode' => $_POST['studentcode']);

      try
      {
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);
      }
      catch(PDOException $ex)
      {
        die("Error" . $ex->getMessage());
      }
      $name = $_SESSION['user']['username'];
      $id = $_SESSION['courseid'];
      $query = "INSERT INTO idtables(courseid, studentid) values ('$id', (SELECT id FROM users WHERE username = '$name'))";
      try
      {
        $stmt = $db->prepare($query);
        $stmt->execute();
      }
      catch(PDOExecute $ex)
      {
        die("Error" . $ex->getMessage());
      }
      $id = $_SESSION['courseid'];
      header("Location: index.php?id=$id");
      die("Rendirecting to index.php?id=$id");

    }
    ?>

    <?php
    $id = $_SESSION['courseid'];
    echo $id;
    ?>

    <form action="add.php" method="post">
      First Name: <input type="text" name="fname" value=""><br>
      Last Name: <input type="text" name="lname" value=""><br>
      Stude Code: <input type="text" name="studentcode" value=""><br>
      <input type="submit" value="Add Student">
    </form>

Upvotes: 0

Views: 398

Answers (3)

Barmar
Barmar

Reputation: 780909

Change the second query to:

      $query = "INSERT INTO idtables(courseid, studentid) values ('$id', LAST_INSERT_ID())";

LAST_INSERT_ID is a built-in MySQL function that returns the last inserted ID.

Upvotes: 1

RobP
RobP

Reputation: 9522

It's not safe just to find the student ID by checking names. Does your table structure allow two students with the same name? Above all you need to make sure that your students table is set with a studentid column that is the primary key and is AUTOINCREMENT. Then you use lastInsertId() and you don't need to do another SELECT in the second (insert) query at all. Just this:

  $query = "INSERT INTO students (fname, lname, studentcode) values (:fname, :lname, :studentcode)";
  $query_params = array(':fname' => $_POST['fname'], ':lname' => $_POST['lname'], ':studentcode' => $_POST['studentcode']);

  try
  {
    $stmt = $db->prepare($query);
    $result = $stmt->execute($query_params);
    if($result) $newStudentID = $db->lastInsertId();
  }
  catch(PDOException $ex)
  {
    die("Error" . $ex->getMessage());
  }
  $name = $_SESSION['user']['username'];
  $id = $_SESSION['courseid'];
  $query = "INSERT INTO idtables(courseid, studentid) values ('$id', $newStudentID)";

Upvotes: 1

Heimindanger
Heimindanger

Reputation: 127

You can get the id of the row you added like this :

$studentId = $db->lastInsertId();

See docs

Upvotes: 1

Related Questions