How to combine 2 Table queries and INSERT into a new table?

Beginners - Learning PHP
#1

Hi Gurus, please help me again, I have a single database(db) with 3 tables:

  • equipment
  • employee
  • calrecords

SQL1: from Equipment, I would like to search for an Equipment Number,
-> Equip_id, Equip_no, Description

SQL2: From Employee, I would like to search for Employee Number
->Emp_ID, Employee_no, FirstName, LastName

From SQL1 and SQL2 queries, I would like to combine the following:

Equip_no, Description + Employee_no + current_date and put them all together to
table: calrecord.

May I know how will be sequence? Thanks a lot in advance

#2

First, we need to make sure the DB design is correct. Post an SQL dump of your DB with a few sample records.

#3

Your user interface (UI) should let you select, via a select/option menu, from existing choices. If you have a large number of choices for either of those values, you need to have some type of filter to present a smaller number of choices. Two ways of doing this is, either by themselves or combined, is to have specific search filters that you can select, check, or enter values for and 2) to use ajax to implement ‘auto-complete’ to query for and display data matching what has already been typed.

Once you have selected an employee and a piece of equipment, your form should submit only the employee id and the equipment id. After validating the two submitted values, you would insert these two values, and the current date, in the calibration records table.

#4

There is no reason to do this. You already have the information needed in the tables, you shouldn’t combine them into a table because it’s easier, what it is is a stupid way to duplicate data and bloat a database.

#5

Hi astonecipher,
basically i have table_a and table_b which has unique contents which I want to store into table_c. table_c is the transaction list.

for the html page, I need both table_a and table_b on a drop down list and after all the columns of table_a and b has been displayed, I can click on a button and they will be saved on table_c.

Please help me how to do this? Thanks

#7

Your answer is here…

#8

I still don´t know how to get the columns I need and send it to calrecord table, Please help.

<?php


require "../config.php";
require "../common.php";


// This is to view and for me to select the Equipment Number


if (isset($_POST['search'])) {
  if (!hash_equals($_SESSION['csrf'], $_POST['csrf'])) die();

  try  {
    $connection = new PDO($dsn, $username, $password, $options);

    $sql = "SELECT * 
            FROM equipment
      WHERE mach_no LIKE :mach_no";

    $mach_no = $_POST['mach_no'];
    $statement = $connection->prepare($sql);
    $statement->bindParam(':mach_no', $mach_no, PDO::PARAM_STR);
    $statement->execute();

    $result = $statement->fetchAll();
  } catch(PDOException $error) {
      echo $sql . "<br>" . $error->getMessage();
  }
}
?>
<?php require "templates/equip_header.php"; ?>
        
<?php  
if (isset($_POST['search'])) {
  if ($result && $statement->rowCount() > 0) { ?>
    <h2>Results</h2>

    <table>
      <thead>
        <tr>
          <th>Equip ID</th>
          <th>Machine/Equipment No.</th>
          <th>Brand</th>
          <th>Description</th>
          <th>Serial Number</th>
          <th>Cal Interval</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach ($result as $row) : ?>
        <tr>
          <td><?php echo escape($row["equip_id"]); ?></td>
          <td><?php echo escape($row["mach_no"]); ?></td>
          <td><?php echo escape($row["brand"]); ?></td>
          <td><?php echo escape($row["description"]); ?></td>
          <td><?php echo escape($row["serial_no"]); ?></td>
          <td><?php echo escape($row["cal_interval"]); ?></td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>

    <?php } else { ?>
      <blockquote>No results found for <?php echo escape($_POST['mach_no']); ?>.</blockquote>
    <?php } 
} ?> 

<h2>Find Equipment based on Machine Number</h2>

<form method="post">
  <input name="csrf" type="hidden" value="<?php echo escape($_SESSION['csrf']); ?>">
  <label for="mach_no">Equipment/Machine Number</label>
  <input type="text" id="mach_no" name="mach_no">
  <input type="submit" name="search" value="View Search">
</form>

<?php
// this is where I view Employees
if (isset($_POST['submit'])) {
  if (!hash_equals($_SESSION['csrf'], $_POST['csrf'])) die();

  try  {
    $connection = new PDO($dsn, $username, $password, $options);

    $sql = "SELECT * 
            FROM users
      WHERE employeenumber LIKE :employeenumber";

    $employeenumber = $_POST['employeenumber'];
    $statement = $connection->prepare($sql);
    $statement->bindParam(':employeenumber', $employeenumber, PDO::PARAM_STR);
    $statement->execute();

    $result = $statement->fetchAll();
  } catch(PDOException $error) {
      echo $sql . "<br>" . $error->getMessage();
  }
}
?>
<?php require "templates/users_header.php"; ?>
        
<?php  
if (isset($_POST['submit'])) {
  if ($result && $statement->rowCount() > 0) { ?>
    <h2>Results</h2>

    <table>
      <thead>
        <tr>
          <th>#</th>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Email Address</th>
          <th>Employee Number</th>
          <th>Department</th>
          <th>Date</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach ($result as $row) : ?>
        <tr>
          <td><?php echo escape($row["id"]); ?></td>
          <td><?php echo escape($row["firstname"]); ?></td>
          <td><?php echo escape($row["lastname"]); ?></td>
          <td><?php echo escape($row["email"]); ?></td>
          <td><?php echo escape($row["employeenumber"]); ?></td>
          <td><?php echo escape($row["department"]); ?></td>
          <td><?php echo escape($row["date"]); ?> </td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
    <?php } else { ?>
      <blockquote>No results found for <?php echo escape($_POST['employeenumber']); ?>.</blockquote>
    <?php } 
} ?> 

<h2>Find user based on Employee Number</h2>

<form method="post">
  <input name="csrf" type="hidden" value="<?php echo escape($_SESSION['csrf']); ?>">
  <label for="employeenumber">Employee Number</label>
  <input type="text" id="employeenumber" name="employeenumber">
  <input type="submit" name="submit" value="View Results">
</form>

?>

<?php

// I need then to 

if (isset($_POST['submit'])) {
  if (!hash_equals($_SESSION['csrf'], $_POST['csrf'])) die();

  try  {
    $connection = new PDO($dsn, $username, $password, $options);
    
    $new_calrec = array(
      "equip_id" => $_POST['equip_id'],
      "brand"  => $_POST['brand'],
      "description"     => $_POST['description'],
      "employeenumber" => $_POST['employeenumber'],
      "cal_interval"  => $_POST['cal_interval']
   
    );

    $sql = sprintf(
      "INSERT INTO %s (%s) values (%s)",
      "calrecord",
      implode(", ", array_keys($new_calrec)),
      ":" . implode(", :", array_keys($new_calrec))
    );
    
    $statement = $connection->prepare($sql);
    $statement->execute($new_calrec);
  } catch(PDOException $error) {
      echo $sql2 . "<br>" . $error->getMessage();
  }
}
?>
<?php require "templates/calrec_header.php"; ?>

  <?php if (isset($_POST['submit']) && $statement) : ?>
    <blockquote><?php echo escape($_POST['equip_id']); ?> successfully added.</blockquote>
  <?php endif; ?>

  <h2>Add a Calibration Record</h2>

  <form method="post">
    <input name="csrf" type="hidden" value="<?php echo escape($_SESSION['csrf']); ?>">
    <label for="mach_no">Machine/Equipment Number</label>
    <input type="text" name="equip_id" id="equip_id">
    <input type="submit" name="submit" value="Search">
    <label for="brand">Brand</label>
    <input type="text" name="brand" id="brand">
    <label for="description">Description</label>
    <input type="text" name="description" id="description">
    <label for="employeenumber">Employee Number</label>
    <input type="text" name="employeenumber" id="employeenumber">
    <label for="cal_interval">Cal Interval</label>
    <input type="text" name="cal_interval" id="cal_interval">
    <input type="submit" name="submit" value="Submit">
  </form>




<?php
// this is to view the Transaction of Calibrations
try {
  $connection = new PDO($dsn, $username, $password, $options);

//open database View of calrecord
$sql = "SELECT * FROM Next_Update";



  $statement = $connection->prepare($sql);
  $statement->execute();

  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
?>

<h2>Calibration Record</h2>

<table>
    <thead>
        <tr>
            <th>Calibration ID</th>
            <th>Machine/Equipment No.</th>
            <th>Brand</th>
            <th>Description</th>
            <th>Employee Number</th>
            <th>Calibration Interval</th>
            <th>Calibration Date</th>
            <th>Next Calibration</th>
            <th>Edit</th>
        </tr>
    </thead>
    <tbody>
    <?php 
    foreach ($result as $row) : ?>

        <tr>
            <td><?php echo escape($row["cal_id"]); ?></td>
            <td><?php echo escape($row["mach_no"]); ?></td>
            <td><?php echo escape($row["brand"]); ?></td>
            <td><?php echo escape($row["description"]); ?></td>
            <td><?php echo escape($row["employeenumber"]); ?></td>
            <td><?php echo escape($row["cal_interval"]); ?></td>
            <td><?php echo escape($row["cal_date"]); ?> </td>
            <td><?php echo escape($row["next_cal"]); ?> </td>
            <td><a href="update-single-calrec.php?id=<?php echo escape($row["cal_id"]); ?>">Edit</a></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>
#9

You are over thinking this. You need a join table, if that is calrecords that’s fine. That table needs a primary key,
equipment_id,
employee_id,
date_added

Also, this won’t work,

Change it to,

    $mach_no = '%' . $_POST['mach_no'] . '%';
    $statement = $connection->prepare($sql);
    $statement->bindParam(':mach_no', $mach_no, PDO::PARAM_STR);

Don’t make this dynamic.

Sponsor our Newsletter | Privacy Policy | Terms of Service