Skip to content
Advertisement

Sending and email with the auto Increment number attached to the email using PDO/MySQL

Hello my favorite people!

I am trying to send an email after submitting a form, with the AUTO INCREMENT number attached to the email because the AUTO INCREMENT number is the clients Job Card Reference Number. So far i have successfully created the insert script which inserts the data into the database perfectly, and also sends the email too. But does not attach the AUTO INCREMENT number into the email. The INT(11) AUTO INCREMENT primary key is “job_number” in my MySQL database.

Here is my insert page:

<form action="addnewrepairprocess.php" method="POST">
         
         <div class="form-group">
      <label for="date">Date</label>
      <input type="date" name="date" id="date" class="form-control" placeholder="Job Card Date">
    </div>


         <div class="form-group">
      <label for="client_full_name">Client Full Name</label>
      <input type="text" name="client_full_name" class="form-control" id="client_full_name" placeholder="Mr. Laptop Man">
    </div>
    
     <div class="form-group">
      <label for="client_email">Client Email Address</label>
      <input type="email" name="client_email" class="form-control" id="client_email" placeholder="example@live.co.za">
    </div>
    
    <div class="form-group">
      <label for="client_phone">Client Phone Number</label>
      <input type="text" name="client_phone" class="form-control" id="client_phone" placeholder="071 984 5522">
    </div>
    
     <div class="form-group">
     <label for="item_for_repair">Item For Repair</label>

<select name="item_for_repair" id="item_for_repair">
  <option value="Laptop">Laptop</option>
  <option value="Desktop">Desktop</option>
  <option value="Television">Television</option>
  <option value="Washing Machine">Washing Machine</option>
   <option value="Tumble Dryer">Tumble Dryer</option>
    <option value="Dishwasher">Dishwasher</option>
     <option value="Microwave">Microwave</option>
      <option value="Fridge">Fridge</option>
       <option value="Printer">Printer</option>
       <option value="Other">Other</option>
</select>
    </div>
    
      <div class="form-group">
      <label for="repair_description">Repair Description</label>
      <input type="text" name="repair_description" class="form-control" id="repair_description" placeholder="Laptop is dead...">
    </div>
    
      <div class="form-group">
      <label for="hardware_details">Hardware Details</label>
      <input type="text" name="hardware_details" class="form-control" id="hardware_details" placeholder="Black Lenovo Laptop with Charger">
    </div>
    
      <div class="form-group">
      <label for="diagnostic_fee">Diagnostic Fee</label>
      <input type="text" name="diagnostic_fee" class="form-control" id="diagnostic_fee">
    </div>
    
     <div class="form-group">
     <label for="tech_assigned">Technician Assigned</label>

<select name="tech_assigned" id="tech_assigned">
  <option value="Not Assigned Yet">Not Assigned Yet</option>
  <option value="Brendon">Brendon</option>
  <option value="Gabriel">Gabriel</option>
  <option value="Tapiwa">Tapiwa</option>
   <option value="Conrad">Conrad</option>
  </select>
    </div>
    
     <div class="form-group">
     <label for="current_status">Current Status</label>

<select name="current_status" id="current_status">
  <option value="Pending">Pending</option>
  <option value="In Progress">In Progress</option>
  <option value="On Hold Spares Required">On Hold Spares Required</option>
  <option value="On Hold Other Fault">On Hold Other Fault</option>
   <option value="Repair Completed">Repair Completed</option>
  </select>
    </div>
    
      <div class="form-group">
      <label for="technician_notes">Technician Notes</label>
      <input type="text" name="technician_notes" class="form-control" id="technician_notes">
    </div>
    
       <div class="form-group">
      <label for="admin_notes">Admin Notes</label>
      <input type="text" name="admin_notes" class="form-control" id="admin_notes">
          </div>
          
          <div class="form-group">
     <label for="invoice_status">Invoice Status</label>

<select name="invoice_status" id="invoice_status">
  <option value="Client Not Yet Invoiced">Client Not Yet Invoiced</option>
  <option value="Client Invoiced">Client Invoiced</option>
   </select>
    </div>
    
     <div class="form-group">
      <label for="invoice_number">Invoice Number</label>
      <input type="text" name="invoice_number" class="form-control" id="invoice_number">
          </div>
<input type="submit" id="btn_create" name="btn_create" class="btn btn-primary" value="Create Job Card">

    </form>

My Form Action Page:

<?php

require_once "connection.php";

if(isset($_REQUEST['btn_create']))
{
    $job_number = $_REQUEST['job_number'];
    $date = $_REQUEST['date'];
    $client_full_name = $_REQUEST['client_full_name'];
    $client_email = $_REQUEST['client_email'];
    $client_phone = $_REQUEST['client_phone'];
    $item_for_repair = $_REQUEST['item_for_repair'];
    $repair_description = $_REQUEST['repair_description'];
    $hardware_details = $_REQUEST['hardware_details'];
    $diagnostic_fee = $_REQUEST['diagnostic_fee'];
    $tech_assigned = $_REQUEST['tech_assigned'];
    $current_status = $_REQUEST['current_status'];
    $technician_notes = $_REQUEST['technician_notes'];
    $admin_notes = $_REQUEST['admin_notes'];
    $invoice_status = $_REQUEST['invoice_status'];
    $invoice_number = $_REQUEST['invoice_number'];
  
 if(empty($date)){
  $errorMsg="Please Enter date";
 }
 else if(empty($client_email)){
  $errorMsg="Please Enter Email Address";
 }
 else
 {
  try
  {
   if(!isset($errorMsg))
   {
    $insert_stmt=$db->prepare('INSERT INTO repairs(job_number,date,client_full_name,client_email,client_phone,item_for_repair,repair_description,hardware_details,diagnostic_fee,tech_assigned,current_status,technician_notes,admin_notes,invoice_status,invoice_number) VALUES(:job_number,:date,:client_full_name,:client_email,:client_phone,:item_for_repair,:repair_description,:hardware_details,:diagnostic_fee,:tech_assigned,:current_status,:technician_notes,:admin_notes,:invoice_status,:invoice_number)');      
                $insert_stmt->bindParam(':job_number', $job_number);
                $insert_stmt->bindParam(':date', $date);
                $insert_stmt->bindParam(':client_full_name', $client_full_name);
                $insert_stmt->bindParam(':client_email', $client_email);
                $insert_stmt->bindParam(':client_phone', $client_phone);
                $insert_stmt->bindParam(':item_for_repair', $item_for_repair);
                $insert_stmt->bindParam(':repair_description',$repair_description);
                $insert_stmt->bindParam(':hardware_details', $hardware_details);
                $insert_stmt->bindParam(':diagnostic_fee', $diagnostic_fee);
                $insert_stmt->bindParam(':tech_assigned', $tech_assigned);
                $insert_stmt->bindParam(':current_status', $current_status);
                $insert_stmt->bindParam(':technician_notes', $technician_notes);
                $insert_stmt->bindParam(':admin_notes', $admin_notes);
                $insert_stmt->bindParam(':invoice_status', $invoice_status);
                $insert_stmt->bindParam(':invoice_number', $invoice_number);   
     
    if($insert_stmt->execute())
    {
     $insertMsg="Created Successfully........sending email now"; 
     
    }
   }
  }
  catch(PDOException $e)
  {
   echo $e->getMessage();
  }
 }
}

?>
<?php 
if(isset($_POST['btn_create'])){
    $to = "EMAIL_ADDRESS"; // this is your Email address
    $from = "EMAIL_ADDRESS"; // this is the sender's Email address
    $job_number = $_POST['job_number'];
    $date = $_POST['date'];
    $client_full_name = $_POST['client_full_name'];
    $item_for_repair = $_POST['item_for_repair'];
    $subject = "JC$job_number has been added to ECEMS";
   $message = "Hi Admin. A new job card has been added to ECEMS on the $date for $client_full_name. The item for repair is a $item_for_repair. Please start diagnostics immediately for this item.";
       $headers = "From:" . $from;
    $headers2 = "From:" . $to;
    mail($to,$subject,$message,$headers);
    header("refresh:1;repairs.php"); 
            }
?>

I tried to follow this tut: Send email with PHP from html form on submit with the same script

I have also tried activating errors on this page with no results:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

I am new and have NEVER done a code like this where the AUTO INCREMENT number needs to be sent in an email. Please can someone assist me. I can edit my question if more clarification is needed.

EDIT: Ive done some research and found i can use the lastInsertID (https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id function. Some help implementing this would be so appreciated.

Advertisement

Answer

Ok so i used the MAX method to solve this issue. i added the following to the top of my createnewrepairs.php page:

$stmt = $db->prepare("SELECT MAX(job_number) AS max_id FROM repairs"); $stmt -> execute(); $job_number = $stmt -> fetch(PDO::FETCH_ASSOC); $max_id = $job_number['max_id'];

Then on the same page, i added the following form field

<div class="form-group">
  <label for="job_number">Job Number</label>
  <input type="job_number" name="job_number" id="job_number" class="form-control" value="<?php echo $max_id+1;?>" readonly>
</div>

Then on the form processing page (processnewrepair.php) my code in my original post worked and generated the AUTO INCREMENT NUMBER to send in an email.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement