Jump to content

retrieve id number

ianhaney

By ianhaney
in PHP Coding Help

 Share

Recommended Posts

ianhaney Advanced Member

ianhaney

Posted
Posted

Hi

 

I have added in a email script so that after the data is stored in the mysql database, a email is sent out to the customer, it all works perfect apart from one little bit hopefully

 

I can't seem to get the $id number from the database to display within the email, it just returns Repair ID:

 

the coding I have is below

if($stmt)

$header = "From: enquiries@it-doneright.co.uk\n"
. "Reply-To: enquiries@it-doneright.co.uk\n";
$subject = "Booked Repair Information";
$email_to = "$customer_email";
$message = "Below is your repair details to track the repair status\n\n"
. "Your Name: $customer_name\n"
. "Your Email: $customer_email\n"
. "Your Phone Number: $customer_phone\n"
. "PC/Laptop Make: $computer_make\n"
. "PC/Laptop Model: $computer_model\n"
. "Assigned to Technician: $technician\n"
. "Current Repair Status: $status\n"
. "Expected Start Date: $exstdate\n"
. "Expected Start Time: $exstime\n"
. "Expected Repair Date: $exrdate\n"
. "Expected Repair Time: $exrtime\n"
. "Dropoff or Pickup: $deltype\n"
. "Comments: $comments\n"
. "Repair Cost: $cost\n\n"
. "Repair ID: $id\n\n"
. "<a href='http://www.it-doneright.co.uk/track-my-repair.php'>Track your repair</a>";
mail($email_to, $subject ,$message ,$header ) ;

I guess I need to add $_GET or $_POST somewhere before the email part to retrieve the $id and display in the email, is that right?

Link to comment
https://forums.phpfreaks.com/topic/299501-retrieve-id-number/
Share on other sites
ianhaney Advanced Member

ianhaney

Posted
  • Author
Posted

Hi Barand

 

Thank you for the reply

 

Yeah the ID is unique, below is the whole coding I have

<?php
	/*
		Allows the user to both create new records and edit existing records
	*/

	// connect to the database
	include("connect-db.php");

	// creates the new/edit record form
 	// since this form is used multiple times in this file, I have made it a function that is easily reusable
	function renderForm($customer_name = '', $customer_email = '', $customer_phone = '', $computer_make = '', $computer_model = '', $technician = '', $status = '', $exrdate = '', $exrtime = '', $exstdate = '', $exstime = '', $deltype = '', $comments = '', $cost = '', $error = '', $id = '')
	{ ?>
		<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
		<html>
			<head>	
				<title>
					<?php if ($id != '') { echo "Edit Repair Tracking"; } else { echo "New Repair Tracking"; } ?>
				</title>
				<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
                
                <link rel="stylesheet"href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.2/themes/blitzer/jquery-ui.css"/>
				<script src="//code.jquery.com/jquery-1.10.2.js"></script>
                <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
                
                <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />

<script src="js/jquery.ui.timepicker.js"></script>
<link rel="stylesheet" type="text/css" media="screen" href="css/jquery.ui.timepicker.css" />

<script>
  $(function() {
    $("#exrdate").datepicker({
	showButtonPanel: true,
	dateFormat: "yy/mm/dd",
	showOn:"both"
	});
  });
  
  $(function() {
	$("#exstdate").datepicker({
	showButtonPanel: true,
	dateFormat: "yy/mm/dd",
	showOn:"both"
	}); 
});
  </script>

<script>
$(document).ready(function() {
                  $('#exrtime').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: true,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
  				});
				
				$('#exstime').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: true,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
  				});
});
</script>

<script src="//cdn.ckeditor.com/4.5.5/full/ckeditor.js"></script>

			</head>
			<body>
            
            <div id="logo">
<img src="images/logo/it-done-right.jpg" alt="" title="">
</div>

<?
session_start();
if($_SESSION['user']==''){
 header("Location:../index.php");
}else{
 include("../config.php");
 $sql=$dbh->prepare("SELECT * FROM users WHERE id=?");
 $sql->execute(array($_SESSION['user']));
 while($r=$sql->fetch()){
  echo "<div class='home-content'>";
  echo "<center><h2>Hello, ".$r['username']."</h2>";
  echo "<a href='../logout.php'>Log Out</a>
  <br><br>
  <a href='../index.php'>Home</a></center>";
  echo "</div>";
 }
}
?>
            
				<h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
				<?php if ($error != '') {
					echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
						. "</div>";
				} ?>
				
				<form action="" method="post" class="basic-grey">
				<div>
					<?php if ($id != '') { ?>
						<input type="hidden" name="id" value="<?php echo $id; ?>" />
						<p>Repair ID: <?php echo $id; ?></p>
					<?php } ?>
                    
					<br>
					<strong>Customer Name:</strong> <input type="text" name="customer_name"
						value="<?php echo $customer_name; ?>"/>
                        <br/>
					<strong>Customer Email:</strong> <input type="text" name="customer_email"
						value="<?php echo $customer_email; ?>"/>
                        <br>
                        <strong>Customer Phone:</strong> <input type="text" name="customer_phone"
						value="<?php echo $customer_phone; ?>"/>
                        <br>
                        <strong>Computer Make:</strong> <input type="text" name="computer_make"
						value="<?php echo $computer_make; ?>"/>
                        <br>
                        <strong>Computer Model:</strong> <input type="text" name="computer_model"
						value="<?php echo $computer_model; ?>"/>
                        <br>
                        <strong>Assigned to Technician:</strong>
                        <select name="technician">
                        <option value="Phil Roskams">Phil Roskams</option>
                        <option value="Ian Haney">Ian Haney</option>
                        </select>
                        <br>
                        <strong>Repair Status:</strong>
                        <select name="status">
                        <option value="In Queue">In Queue</option>
                        <option value="Working on">Working on</option>
                        <option value="Awaiting Parts">Awaiting Parts</option>
                        <option value="Ready for Collection/Delivery">Ready for Collection/Delivery</option>
                        <option value="Complete">Complete</option>
                        </select>
                        <br>
                        <strong>Expected Start Date:</strong> <input type="text" name="exstdate" value="<?php echo $exstdate; ?>" id="exstdate" />
                        <br><br>
     					<strong>Expected Start Time:</strong> <input type="time" name="exstime" value="<?php echo $exstime; ?>" id="exstime"/>
                        <br><br>
                        <strong>Expected Repair Date:</strong> <input type="text" name="exrdate" value="<?php echo $exrdate; ?>" id="exrdate"/>
                        <br><br>
     					<strong>Expected Repair Time:</strong> <input type="time" name="exrtime" value="<?php echo $exrtime; ?>" id="exrtime"/>
                        <br><br>
                        <strong>Delivery Type:</strong>
                        <select name="deltype">
                        <option value="Customer Pickup">Customer Pickup</option>
                        <option value="Delivery">Delivery</option>
                        </select>
                        <br>
                        <strong>Comments:</strong>
                        <br>
                        <textarea name="comments" class="ckeditor"><?php echo $comments; ?></textarea>
                        <br>
                        <strong>Repair Cost:</strong> <input type="text" name="cost"
						value="<?php echo $cost; ?>"/>
                        <br>
					<input type="submit" name="submit" value="Add/Update Repair Tracking" />
				</div>
				</form>
			</body>
		</html>
		
	<?php }



        /*

           EDIT RECORD

        */
	// if the 'id' variable is set in the URL, we know that we need to edit a record
	if (isset($_GET['id']))
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// make sure the 'id' in the URL is valid
			if (is_numeric($_POST['id']))
			{
				// get variables from the URL/form
				$id = $_POST['id'];
				$customer_name = htmlentities($_POST['customer_name'], ENT_QUOTES);
				$customer_email = htmlentities($_POST['customer_email'], ENT_QUOTES);
				$customer_phone = htmlentities($_POST['customer_phone'], ENT_QUOTES);
				$computer_make = htmlentities($_POST['computer_make'], ENT_QUOTES);
				$computer_model = htmlentities($_POST['computer_model'], ENT_QUOTES);
				$technician = htmlentities($_POST['technician'], ENT_QUOTES);
				$status = htmlentities($_POST['status'], ENT_QUOTES);
				$exrdate = htmlentities($_POST['exrdate'], ENT_QUOTES);
				$exrtime = htmlentities($_POST['exrtime'], ENT_QUOTES);
				$exstdate = htmlentities($_POST['exstdate'], ENT_QUOTES);
				$exstime = htmlentities($_POST['exstime'], ENT_QUOTES);
				$deltype = htmlentities($_POST['deltype'], ENT_QUOTES);
				$comments = htmlentities($_POST['comments'], ENT_QUOTES);
				$cost = htmlentities($_POST['cost'], ENT_QUOTES);
				
				// check that firstname and lastname are both not empty
				if ($customer_name == '' || $customer_phone == '' || $computer_make == '' || $computer_model == '' || $comments == '')
				{
					// if they are empty, show an error message and display the form
					$error = 'ERROR: Please fill in all required fields!';
renderForm($customer_name, $customer_phone, $computer_make, $computer_model, $comments, $error, $id);
				}
				else
				{
					// if everything is fine, update the record in the database
	if ($stmt = $mysqli->prepare("UPDATE repairs SET customer_name = ?, customer_email = ?, customer_phone = ?, computer_make = ?, computer_model = ?, technician = ?, status = ?, exrdate = ?, exrtime = ?, exstdate = ?, exstime = ?, deltype = ?, comments = ?, cost = ?
						WHERE id=?"))
					{
	$stmt->bind_param("ssssssssssssssi", $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $cost, $id);
						$stmt->execute();
						$stmt->close();
					}
					// show an error message if the query has an error
					else
					{
						echo "ERROR: could not prepare SQL statement.";
					}
					
					// redirect the user once the form is updated
					header("Location: view-repairs-tracking.php");
				}
			}
			// if the 'id' variable is not valid, show an error message
			else
			{
				echo "Error!";
			}
		}
		// if the form hasn't been submitted yet, get the info from the database and show the form
		else
		{
			// make sure the 'id' value is valid
			if (is_numeric($_GET['id']) && $_GET['id'] > 0)
			{
				// get 'id' from URL
				$id = $_GET['id'];
				
				// get the recod from the database
				if($stmt = $mysqli->prepare("SELECT id, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, exrdate, exrtime, exstdate, exstime, deltype, comments, cost FROM repairs WHERE id=?"))
				{
					$stmt->bind_param("i", $id);
					$stmt->execute();
					
					$stmt->bind_result($id, $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $cost);
					$stmt->fetch();
					
					// show the form
					renderForm($customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $cost, NULL, $id);
					
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "Error: could not prepare SQL statement";
				}
			}
			// if the 'id' value is not valid, redirect the user back to the view.php page
			else
			{
				header("Location: view-repairs-tracking.php");
			}
		}
	}
	
        /*

           NEW RECORD

        */
		
	// if the 'id' variable is not set in the URL, we must be creating a new record
	else
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// get the form data
			$customer_name = htmlentities($_POST['customer_name'], ENT_QUOTES);
			$customer_email = htmlentities($_POST['customer_email'], ENT_QUOTES);
			$customer_phone = htmlentities($_POST['customer_phone'], ENT_QUOTES);
			$computer_make = htmlentities($_POST['computer_make'], ENT_QUOTES);
			$computer_model = htmlentities($_POST['computer_model'], ENT_QUOTES);
			$technician = htmlentities($_POST['technician'], ENT_QUOTES);
			$status = htmlentities($_POST['status'], ENT_QUOTES);
			$exrdate = htmlentities($_POST['exrdate'], ENT_QUOTES);
			$exrtime = htmlentities($_POST['exrtime'], ENT_QUOTES);
			$exstdate = htmlentities($_POST['exstdate'], ENT_QUOTES);
			$exstime = htmlentities($_POST['exstime'], ENT_QUOTES);
			$deltype = htmlentities($_POST['deltype'], ENT_QUOTES);
			$comments = htmlentities($_POST['comments'], ENT_QUOTES);
			$cost = htmlentities($_POST['cost'], ENT_QUOTES);
			
			// check that firstname and lastname are both not empty
			if ($customer_name == '' || $computer_make == '' || $computer_model == '' || $comments == '')
			{
				// if they are empty, show an error message and display the form
				$error = 'ERROR: Please fill in all required fields!';
	renderForm($customer_name, $computer_make, $computer_model, $comments, $error);
			}
			else
			{
				// insert the new record into the database

				if ($stmt = $mysqli->prepare("INSERT repairs (customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, exrdate, exrtime, exstdate, exstime, deltype, comments, cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
				{
					$stmt->bind_param("ssssssssssssss", $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $cost);
					$stmt->execute();
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "ERROR: Could not prepare SQL statement.";
				}
				
				if($stmt)

$header = "From: enquiries@it-doneright.co.uk\n"
. "Reply-To: enquiries@it-doneright.co.uk\n";
$subject = "Booked Repair Information";
$email_to = "$customer_email";
$message = "Below is your repair details to track the repair status\n\n"
. "Your Name: $customer_name\n"
. "Your Email: $customer_email\n"
. "Your Phone Number: $customer_phone\n"
. "PC/Laptop Make: $computer_make\n"
. "PC/Laptop Model: $computer_model\n"
. "Assigned to Technician: $technician\n"
. "Current Repair Status: $status\n"
. "Expected Start Date: $exstdate\n"
. "Expected Start Time: $exstime\n"
. "Expected Repair Date: $exrdate\n"
. "Expected Repair Time: $exrtime\n"
. "Dropoff or Pickup: $deltype\n"
. "Comments: $comments\n"
. "Repair Cost: $cost\n\n"
. "Repair ID: $id\n\n"
. "<a href='http://www.it-doneright.co.uk/track-my-repair.php'>Track your repair</a>";
mail($email_to, $subject ,$message ,$header ) ;
				
				// redirec the user
				header("Location: view-repairs-tracking.php");
			}
			
		}
		
		// if the form hasn't been submitted yet, show the form
		else
		{
			renderForm();
		}
	}
	
	// close the mysqli connection
	$mysqli->close();
?>

Sorry to post the whole code but thought would all be important and related

Link to comment
https://forums.phpfreaks.com/topic/299501-retrieve-id-number/#findComment-1526563
Share on other sites
Ch0cu3r Newbie

Ch0cu3r

Posted
Posted (edited)

Your code is sending the email for when a new record has been inserted into the database,  at this point the $id variable is not defined. It is only defined if you are performing the edit action.

 

I assume the id column in your repairs table is set to auto increment? It is this id value you want to display in your email, in that case you will need to use mysqli insert id after the query has inserted the new to get the (auto increment) id value

Edited by Ch0cu3r
  • Like 1
Link to comment
https://forums.phpfreaks.com/topic/299501-retrieve-id-number/#findComment-1526575
Share on other sites
ianhaney Advanced Member

ianhaney

Posted
  • Author
Posted

Hi Barand

 

I thought about using the mysqli_insert_id and tried to add it to the php email content but didn't display, I will try it again and try and work it out

 

Sorry there is just one ID column in the repairs table, sorry was my fault not being clear, it is just called id in the repairs table and is auto_inc

Link to comment
https://forums.phpfreaks.com/topic/299501-retrieve-id-number/#findComment-1526581
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.