prefill ID number on form

ianhaney50 · October 29, 2015

Hi

I have created a form that stores data in a MySQL database and all works perfect but was wondering if there is a way to pre fill the ID number from the MySQL database so for example the last ID number is 8 so when I go the form next time to add new data, the ID number would already be pre filled for me and would be 9

Is that possible to do?

I tried doing a Google search but could not find anything related to what I need

Thank you in advance

Ian

mac_gyver · October 29, 2015

you can display the next probable id, but your code or the visitor cannot use this value, because if more than one instance of your form/page is being displayed at the same time, the value being shown will be same on all the instances of the form/page, but the actual value that gets inserted by the database will be different.

so, the question is, why do you want to do this as it will just confuse the visitor with a piece of information that he doesn't need to know at the time the form is being displayed, if at all?

ianhaney50 · October 29, 2015

Hi

Thank you for the quick reply, appreciate it

Oh right, what we want is to auto generate repair numbers rather than ourselves having to put each one in ourselves and to avoid duplication as well as we might forget what the last repair number was

budimir · October 29, 2015

Hi

Thank you for the quick reply, appreciate it

Oh right, what we want is to auto generate repair numbers rather than ourselves having to put each one in ourselves and to avoid duplication as well as we might forget what the last repair number was

I guess what you need is not ID of inserted row, because that is not relevant to user. I would suggest that you create a column called document number or repair number or whatever. When you create new repair you call a query on you're database, get max repair number and add one or whatever you want it to be. That way you will always have automatic repair number.

ID in database you should set to autoincrement and add primary key to it.

Is that what you need?

ianhaney50 · October 29, 2015

I have got the column invid column created so I just need bit of php to add +1 to the invid column each time

just unsure how to pre fill the repair number from the database

budimir · October 29, 2015

I have got the column invid column created so I just need bit of php to add +1 to the invid column each time

just unsure how to pre fill the repair number from the database

SELECT MAX(invid) as last_inserted
FROM table_name

Php part

$data = mysqli_fetch_row($query);
$last_inserted = $data["last_inserted"];

$new_repair = $last_inserted + 1;

That's a simple solution, if that is what you need.

mac_gyver · October 29, 2015

you cannot get the current value of either an auto-increment or your own maintained column value, add one to it and use that value. you will end up with concurrent instances of your code that will use the same value.

if you want to do this and have the displayed value be the one that gets used in the database, you will need to pre-insert a row, using an auto-increment field, get that row's id, then display that id and use it to UPDATE the row when the form is actually submitted. this will have an affect of empty rows/wasted ids if anyone doesn't complete the form.

the correct method would be to NOT try to display this information in the form. after the form gets submitted and the data is inserted, you would then have the actual id that you can display/email to serve as a confirmation number.

Edited October 29, 2015 by mac_gyver

ianhaney50 · October 29, 2015

Think I have got that right and put in, how does the field get pre filled, below is the coding I have

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Status System Administration</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.2/jquery-ui.min.js"></script>
<script src="jquery.ui.timepicker.js"></script>
<script src="repairstatus-funcs.js"></script>
<link rel="stylesheet"href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.2/themes/smoothness/jquery-ui.css"/>
<link rel="stylesheet"href="jquery.ui.timepicker.css"/>

</head>

<?
// check php version
if (strnatcmp(phpversion(),'5.2.0') >= 0)
{
}
else
{
 die("This script requires PHP version 5.2 or higher to run.");
}

if(isset($_GET['debug']))
{
error_reporting(E_ALL ^ E_NOTICE);
}

if(isset($_GET['phpinfo']))
{
	phpinfo();
	die;
}

if(isset($_POST['btnSubmit']))
	{
	    switch ($_POST['btnSubmit'])
	    {
	        case "Delete Invoice":
	            delInvoice();
	            break;
			case "Add Invoice";
				addInvoice();
				break;
			case "Change Invoice";
				chgInvoice();
				break;
			case "Delete All Invoices";
				delallInvoices();
				break;
			case "Delete Range";
				delrange();
				break;
			case "Delete Date Range";
				deldaterange();
				break;
					    }
	}
?>

<!-- DIV hide/show code -->
<script type="text/javascript">
 $(function TimeShow () {
     $('#delivertype').change(function () {
         $('#DTTime').hide();
         if (this.options[this.selectedIndex].value == 'DELIVER TO CUSTOMER') {
             $('#DTTime').show();
         }
     });
 });
 	 $(function TimeShow2 () {
     $('#delivertype2').change(function () {
         $('#DTTime2').hide();
         if (this.options[this.selectedIndex].value == 'DELIVER TO CUSTOMER') {
             $('#DTTime2').show();
         }
     });
});
</script>

<!-- Settings for Google Calendar widget -->

<script type="text/javascript">
$(function() {
	$( "#rpdate" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
$(function() {
	$( "#rpdate2" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
$(function() {
	$( "#exdate" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
$(function() {
	$( "#exdate2" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
$(function() {
	$( "#range3" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
$(function() {
	$( "#range4" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
$(function() {
	$( "#invdate" ).datepicker({
		showButtonPanel: true,
		dateFormat:"dd-mm-yy",
		showOn:"both",
		buttonImage:"cal.gif"
	}); 
});
});
</script>

<!-- Settings for Tab Widget -->
<script type="text/javascript">
$(function() {
	$( "#Tabs1" ).tabs(); 
});
</script>

<!-- Settings for Time Picker -->
<script type="text/javascript">
              $(document).ready(function() {
                  $('#rptime').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.rptime_button'
  				});
				    $('#extime').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.extime_button'
  				});
				  $('#rptime2').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.rptime2_button'
  				});
				$('#extime2').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.extime2_button'
  				});
				$('#delivertime').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.delivertime_button'
  				});
				$('#delivertime2').timepicker({
					defaultTime: '12:00',
  					showLeadingZero: false,
					showNowButton: true,
  				    showCloseButton: true,
  	    			showDeselectButton: true,
  					showOn: 'both',
       				button: '.delivertime2_button'
  				});
              });
          </script>
          
<!-- Script for Tooltips -->

<script type="text/javascript">
$(function() {
$( document ).tooltip();
});
</script>

<body>

<?
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></center>";
  echo "</div>";
 }
}
?>

<h1><center><strong><font face="Tahoma, Verdana, Arial, Helvetica, sans-serif">Repair Status Administration</font></strong></center></h1>
<div id="Tabs1">
  <ul>
    <li><a href="#tabs-1">Add Invoice</a></li>
    <li><a href="#tabs-2">Edit Invoice</a></li>
    <li><a href="#tabs-3">Delete Invoice</a></li>
    <li><a href="#tabs-4">Mass Actions</a></li>
    <li><a href="#tabs-5">About...</a></li>
  </ul>
  <div id="tabs-1">
    <form id="add" name="add" method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
      <p>
        <label for="textfield">Repair ID Number:</label>
        <input name="invid" type="text" id="invid" size="3" onKeyDown="limitText(this.form.invid,this.form.countdown,3);" onKeyUp="limitText(this.form.invid,this.form.countdown,3);"; />
      </p>
      
      <?
	  require "repairadmin-config.php";
	if($allowdatechange == 1) {
       echo "<p>";
       echo "<label for=\"textfield\">Date Created:</label>";
	   echo "<input name=\"invdate\" type=\"text\" id=\"invdate\" size=\"10\" value=\"". DATE('d-m-Y'). "\"". "/ >";
       echo "</p>";
	}
  	require("repairstatus-config.php");
	if($enablesecurity == 1) {
		echo "<p>";
		echo "<label for=\"pincode\">4 Digit PIN Code <b>(REQUIRED)</b>:</label>";
		echo "<input type=\"text\" size=\"4\" name=\"pincode\" id=\"pincode\" title=\"Numbers only\" onKeyDown=\"limitText(this.form.pincode,this.form.countdown,4);\" onKeyUp=\"limitText(this.form.pincode,this.form.countdown,4);\"; />";
  		echo "</p>";
	}
	?>
    
      <p>
      <label for="customer_name">Customer Name</label>
      <input name="customer_name" type="text" id="customer_name">
      </p>
      
      <p>
      <label for="customer_email">Customer Email</label>
      <input name="customer_email" type="email" id="customer_email" size="40">
      </p>
      
      <p>
      <label for="customer_phone">Customer Phone Number</label>
      <input name="customer_phone" type="tel" id="customer_phone">
      </p>
    
      <p>
        <label for="textfield">Assigned to Tech:</label>
        <?
require "repairadmin-config.php";
echo "<select name=\"tech\">";
foreach($techslist as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
	echo "</select>";
?>
      </p>
      <p>
        <label for="select">Status:</label>
        <?
require "repairadmin-config.php";
echo "<select name=\"status\">";
foreach($statustypes as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
	echo "</select>";
?>
      </p>
      <p>
        <label for="rpdate">Expected Repair Date:</label>
        <input type="text" name="rpdate" id="rpdate">
      </p>
      <p>
        <label for="rptime">Expected Repair Time:</label>
        <input name="rptime" type="time" id="rptime" style="width: 70px;" title="Must be in 24 hour format - example: 23:30">
        
        <input type="button" class="rptime_button" value="..." title="Must be in 24 hour format - example: 23:30">
      </p>
      <p>
        <label for="exdate">Expected Start Date </label>
        <label for="exdate"> (applicable if In Queue or Delayed):</label>
        <input type="text" name="exdate" id="exdate" >
      </p>
      <p>
        <label for="extime">Expected Start Time (applicable if In Queue or Delayed):</label>
        <input name="extime" type="extime" id="extime" title="Must be in 24 hour format - example: 23:30">
        <input type="button" class="extime_button" value="..." title="Must be in 24 hour format - example: 23:30">
      </p>
      <p>
        <label for="deliverytype">Delivery Type:</label>
        <select name="delivertype" id="delivertype" onChange="TimeShow();">
        <?
require "repairadmin-config.php";
foreach($delivertypes as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
?>
</select>
      </p>
      <div id="DTTime" style="display:none">
      <p>
        <label for="delivertime">Estimated Delivery Time:</label>
        <input name="delivertime" type="text" id="delivertime" title="Must be in 24 hour format - example: 23:30" >
        <input type="button" class="delivertime_button" value="..." title="Must be in 24 hour format - example: 23:30">
      </p>
      </div>
      <p>
        <label for="comments">Comments:</label>
        <br>
        <textarea name="comments" cols="50" rows="3" id="comments"></textarea>
      </p>
      <input type="submit" name="btnSubmit" id="btnSubmit" value="Add Invoice">
    </form>
  </div>
  <div id="tabs-2">
    <p><b>NOTE: Leave field(s) blank to preserve existing data</b></p>
    <form id="change" name="change" method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
      <p> Repair ID Number:
        <?
  // Create connection
require "repairadmin-config.php";
$cn=mysqli_connect($servername, $username, $password, $dbname) or die($cn->connect_error);
$sql = "SELECT invid, datecreated FROM repairs";
$rs = $cn->query($sql) or die(mysqli_error($cn));
echo "<select name=\"invid2\">";
while($row = mysqli_fetch_array($rs)){
echo "<option value='".$row["invid"]."'>".$row["invid"]. " (Created on ".$row['datecreated'].")</option>";
}
$rs->free_result();
$rs->close();
echo "</select>";
?>
      </p>
      <?
  	require("repairstatus-config.php");
	if($enablesecurity == 1) {
		echo "<p>";
		echo "<label for=\"pincode2\">4 Digit PIN Code <b>(Leave blank for no change)</b>:</label>";
		echo "<input type=\"text\" size=\"4\" name=\"pincode2\" id=\"pincode2\" >";
  		echo "</p>";
	}
	?>
    
    <p>
    <label for="customer_name">Customer Name</label>
    <input name="customer_name" type="text" id="customer_name">
    </p>
    
    <p>
    <label for="customer_email">Customer Email</label>
    <input name="customer_email" type="email" id="customer_email" size="40">
    </p>
    
      <p>
      <label for="customer_phone">Customer Phone Number</label>
      <input name="customer_phone" type="tel" id="customer_phone">
      </p>
    
      <p>
        <label for="textfield">Assigned to Tech:</label>
        <?
// Create connection
require "repairadmin-config.php";
echo "<select name=\"tech2\">";
echo "<option value=\"\"></option>";
foreach($techslist as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
	echo "</select>";
?>
      </p>
      <p>
        <label for="status2">Status:</label>
        <?
require "repairadmin-config.php";
echo "<select name=\"status2\">";
echo "<option value=\"\"></option>";
foreach($statustypes as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
	echo "</select>";
?>
      </p>
      <p>
        <label for="datetime">Expected Repair Date:</label>
        <input type="text" name="rpdate2" id="rpdate2">
      </p>
      <p>
        <label for="time">Expected Repair Time:</label>
        <input type="time" name="rptime2" id="rptime2" title="Must be in 24-hour format - example: 23:30">
        <input type="button" class="rptime2_button" value="..." title="Must be in 24-hour format - example: 23:30">
      </p>
      <p>
        <label for="datetime2">Expected Start Date </label>
        <label for="time6"> (applicable if In Queue or Delayed):</label>
        <input type="text" name="exdate2" id="exdate2">
      </p>
      <p>
        <label for="time2">Expected Start Time </label>
        <label for="time7"> (applicable if In Queue or Delayed):</label>
        <input type="time" name="extime2" id="extime2" title="Must be in 24-hour format - example: 23:30">
        <input type="button" class="extime2_button" value="..." title="Must be in 24-hour format - example: 23:30">
      </p>
      <p>
        <label for="delivertype2">Delivery Type:</label>
        <select name="delivertype2" id="delivertype2" onChange="TimeShow2();">
        <?
require "repairadmin-config.php";
echo "<option value=\"\"></option>";
foreach($delivertypes as $item){
  echo "<option value='". $item . "'>". $item ."</option>";
}
?>
</select>
      </p>
      <div id="DTTime2" style="display:none">
        <p>
        <label for="delivertime">Estimated Delivery Time:</label>
        <input type="text" name="delivertime2" id="delivertime2" title="Must be in 24 hour format - example: 23:30" >
        <input type="button" class="delivertime2_button" value="..." title="Must be in 24 hour format - example: 23:30">
      </p>
      </div>
      <p>
        <label for="comments2">Comments:</label>
        <textarea name="comments2" cols="50" rows="3" id="comments2"></textarea>
      </p>
      <p>
        <input type="submit" name="btnSubmit" id="btnSubmit" value="Change Invoice">
      </p>
    </form>
  </div>
  <div id="tabs-3">
    <form id="delete" name="delete" method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
      <p>
        <label for="textfield">Invoice ID: </label>
        <?
// Create connection
require "repairadmin-config.php";
$cn=mysqli_connect($servername, $username, $password, $dbname) or die($cn->connect_error);
$sql = "SELECT MAX(invid) as last_inserted FROM repairs";

$data = mysqli_fetch_row($query);
$last_inserted = $data["last_inserted"];
 
$invid = $last_inserted + 1;

$rs = $cn->query($sql) or die(mysqli_error($cn));
echo "<select name=\"inviddel\">";
while($row = mysqli_fetch_array($rs)){
echo "<option value='".$row["invid"]."'>".$row["invid"]."</option>";
}
$rs->free_result();
$rs->close();
echo "</select>";
?>
      </p>
      <p>
        <input type="submit" name="btnSubmit" id="btnSubmit" value="Delete Invoice">
      </p>
    </form>
  </div>
  <div id="tabs-4">
    <p><strong>Database Administration</strong></p>
    <form id="dbadmin" name="dbadmin" method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
      <p>Purge Invoice Numbers in Range:
        <input name="range1" type="text" id="range1" size="5">
        to
        <input name="range2" type="text" id="range2" size="5">
        <br>
        <input name="btnSubmit" type="submit" id="btnSubmit" value="Delete Range">
      </p>
      <p>Purge Invoice Numbers in Date Range:
        <input name="range3" type="text" id="range3" size="10">
to
<input type="text" name="range4" id="range4" size="10">
<br>
<input name="btnSubmit" type="submit" id="btnSubmit" value="Delete Date Range">
      </p>
            <p> <strong><font color="red">WARNING: This action will delete ALL Invoices in the Database. Once you click the button, you cannot recover the Invoices.</font></strong><br>
        <input name="btnSubmit" type="submit" id="btnSubmit" title="WARNING: WILL DELETE ALL INVOICES!" value="Delete All Invoices">
      </p>
    </form>
  </div>
  <!--<div id="tabs-5">
  <center>
    <p><b>Repair Status Script for PC Repair Shops
      Version 1.5 (branch-1.5_gold)
      </b></p>
    <p>Copyright © 2014, Chris Formeister. All Rights Reserved.
    </p>
    <p>Download latest version from <a href="https://github.com/azchrisf/StatusScript/">https://github.com/azchrisf/StatusScript/</a></p>
    <p>Post bugs and questions to GitHub</p>
    <p>Email the author at <a href="mailto:azchrisf@gmail.com">azchrisf@gmail.com</a></p>
    <p><strong>CREDITS:</strong></p>
    <p>Thanks to Eduardo Mendez for additional code<br>
    </p>
    <p>Uses JQuery UI Time Picker, © François Gélinas.<br>
      Licensed & Distributed under MIT/GPL License
    </p>
  </center>
  </div>-->
</div>
</body>
</html>
<?php

function addInvoice() {
	
require "repairadmin-config.php";

if(empty($invid)) {
	echo "<b><font color=\"red\">You must enter a Invoice ID to add!</b></font><hr>";
	return;
}
if (!is_numeric($invid)) {
        echo "<b><font color=\"red\">The Invoice ID must be numeric only!</b></font><hr>";
		return;
    }
if (strlen($invid) < 3) { 
		echo(strlen($invid));
		echo "<b><font color=\"red\">The Invoice ID must be 5 digits!</b></font><hr>";
		return;
}

require "repairstatus-config.php";

if($enablesecurity == 1) {
if(empty($pincode)) {
	echo "<b><font color=\"red\">You must enter a PIN ID to add!</b></font><hr>";
	return;
}
if (!is_numeric($pincode)) {
        echo "<b><font color=\"red\">The PIN ID must be numeric only!</b></font><hr>";
		return;
    }
if (strlen($pincode) < 4) { 
		echo "<b><font color=\"red\">The PIN ID must be 5 digits!</b></font><hr>";
		return;
}
}

$cn=mysqli_connect($servername, $username, $password, $dbname) or die($cn->connect_error);
$sql = "SELECT invid FROM repairs";
$rs = $cn->query($sql) or die(mysqli_error($cn));
while($row = mysqli_fetch_array($rs)){
	if ($row[invid] == $invid) {
		echo "<b><font color=\"red\">Invoice ID already exists - choose a new ID!</b></font><hr>";
		return;
	}
}
$cn->close(); 

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("<b><font color=\"red\">Connection failed: " . $conn->connect_error . "</b></font><hr>");
}

// sql to add a record

if($allowdatechange == 1) {
	$datecreated = $invdate;
} else {
	$datecreated = DATE('d-m-Y');
}

$sql = "INSERT INTO repairs (invid, customer_name, customer_email, customer_phone, status, tech, rpdate, rptime, exdate, extime, comments, delivertype, delivertime, pincode, datecreated)
VALUES ('$invid', '$customer_name', '$customer_email', '$customer_phone', '$status', '$tech', '$rpdate', '$rptime', '$exdate', '$extime', '$comments', '$delivertype', '$delivertime', '$pincode', '$datecreated')";

if ($conn->query($sql) === TRUE) {
    echo "<b><font color=\"green\">Invoice added successfully!</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error adding Invoice: " . $conn->error . "</b></font><hr>";
}
$conn->close(); 
}

function chgInvoice() {
	
require "repairadmin-config.php";

if(empty($invid2)) {
	echo "<b><font color=\"red\">You must select a non-empty Invoice ID to change!</b></font><hr>";
	return;
}

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("<b><font color=\"red\">Connection failed: " . $conn->connect_error . "</b></font><hr>");
}

// sql to change a record
if(!empty($invid2)){
	if(!empty($status2)){
     $update_fields[] = "status='$status2'";
    }
	if(!empty($customer_name)){
     $update_fields[] = "customer_name='$customer_name'";
    }
	if(!empty($customer_email)){
     $update_fields[] = "customer_email='$customer_email'";
    }
	if(!empty($customer_phone)){
     $update_fields[] = "customer_phone='$customer_phone'";
    }
    if(!empty($tech2)){
     $update_fields[] = "tech='$tech2'";
    }
    if(!empty($rpdate2)){
     $update_fields[] = "rpdate='$rpdate2'";
    }
    if(!empty($rptime2)){
     $update_fields[] = "rptime='$rptime2'";
    }
    if(!empty($exdate2)){
     $update_fields[] = "exdate='$exdate2'";
    }
    if(!empty($extime2)){
     $update_fields[] = "extime='$extime2'";
    }
	if(!empty($comments2)){
     $update_fields[] = "comments='$comments2'";
    }
	if(!empty($delivertype2)){
     $update_fields[] = "delivertype='$delivertype2'";
    }
	if(!empty($delivertime2)){
     $update_fields[] = "delivertime='$delivertime2'";
    }
	if(!empty($pincode2)){
     $update_fields[] = "pincode='$pincode2'";
    }
    if(count($update_fields) > 0){
      $nonempty_fields = implode(", ", $update_fields);
      $sql = "UPDATE repairs SET $nonempty_fields WHERE invid='$invid2'";
	}
    else{
     echo "<b><font color=\"red\">Cannot update the Invoice because all fields are empty!</b></font><hr>";
	 return;
    }
}

if ($conn->query($sql) === TRUE) {
    echo "<b><font color=\"green\">Invoice changed successfully</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error changing Invoice: " . $conn->error . "</b></font><hr>";
}
$conn->close(); 
}


function delInvoice() {

require "repairadmin-config.php";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("<b><font color=\"red\">Connection failed: " . mysqli_connect_error(). "</b></font><hr>");
}

// sql to delete a record
$sql = "DELETE FROM repairs WHERE invid = '$inviddel'";

if (mysqli_query($conn, $sql)) {
    echo "<b><font color=\"green\">Invoice deleted successfully</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error deleting Invoice: " . mysqli_error($conn) . "</b></font><hr>";
}

$conn->close(); 
}
function delallInvoices() {

require "repairadmin-config.php";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("<b><font color=\"red\">Connection failed: " . mysqli_connect_error(). "</b></font><hr>");
}

// sql to delete a record
$sql = "TRUNCATE TABLE repairs";

if (mysqli_query($conn, $sql)) {
    echo "<b><font color=\"green\">All Records deleted successfully</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error deleting all records: " . mysqli_error($conn) . "</b></font><hr>";
}

$conn->close(); 
}

function delrange() {

require "repairadmin-config.php";

if(empty($range1)) {
	echo "<b><font color=\"red\">You must enter a starting Invoice range</b></font><hr>";
	return;
}
if(empty($range2)) {
	echo "<b><font color=\"red\">You must enter a ending Invoice range</b></font><hr>";
	return;
}

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("<b><font color=\"red\">Connection failed: " . mysqli_connect_error(). "</b></font><hr>");
}

// sql to delete a record
$sql = "DELETE FROM repairs WHERE invid BETWEEN ". $range1 ." AND ". $range2;

if (mysqli_query($conn, $sql)) {
    echo "<b><font color=\"green\">Selected Range deleted successfully</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error deleting record range: " . mysqli_error($conn) . "</b></font><hr>";
}

$conn->close(); 
}

function deldaterange() {
	
require "repairadmin-config.php";

if(empty($range3)) {
	echo "<b><font color=\"red\">You must enter a starting date range</b></font><hr>";
	return;
}
if(empty($range4)) {
	echo "<b><font color=\"red\">You must enter a ending date range</b></font><hr>";
	return;
}

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("<b><font color=\"red\">Connection failed: " . mysqli_connect_error(). "</b></font><hr>");
}

// sql to delete a record
$sql = "DELETE FROM repairs WHERE datecreated BETWEEN '". $range3 ."' AND '". $range4 . "'";

if (mysqli_query($conn, $sql)) {
    echo "<b><font color=\"green\">Selected Range deleted successfully</b></font><hr>";
} else {
    echo "<b><font color=\"red\">Error deleting record date range: " . mysqli_error($conn) . "</b></font><hr>";
}

$conn->close(); 
}
?>

budimir · October 29, 2015

<label for="textfield">Repair ID Number:</label>
        <input name="invid" type="text" value="<?php echo '".$new_repair."'; ?>" id="invid" size="3" onKeyDown="limitText(this.form.invid,this.form.countdown,3);" onKeyUp="limitText(this.form.invid,this.form.countdown,3);"; />

But before that you need to use the select query to get the max invid you have in database.

Do you know how invid is setup in you're DB? Is that autoincrement field? Who setup the database?

mac_gyver · October 29, 2015

there are at least three problems with your code -

1) if you are doing this for real, you would not delete any data. you would keep the data for reference.

2) you would not create a database connection, run the code within one function, and close the database connection. in fact, your code is including/requiring the same files multiple times and creating database connections in a ton of places.

3) you apparently missed post #7 in this thread. you CANNOT get the highest value from a database table, add one to it, and USE that value. you will end up with concurrent instances of your code using the same value.

budimir · October 29, 2015



Can you give us table structure?

Edited October 29, 2015 by budimir

ianhaney50 · October 29, 2015

I have now added the sql query and connection to the DB above that coding you mentioned above

The invid is set up as INT(11), it is not an auto increment field

I set the database up but got all the coding/script from Google from the following link

https://github.com/azchrisf/StatusScript/

budimir · October 29, 2015

I have now added the sql query and connection to the DB above that coding you mentioned above

The invid is set up as INT(11), it is not an auto increment field

I set the database up but got all the coding/script from Google from the following link

https://github.com/azchrisf/StatusScript/

OK, so you could use my code I gave you, but as mac_gyver said you could end up with the same invid on more records if few users open that form at same time. If you want to continue with that concept you should check if invid you're inserting already exists in DB before storing data in DB.

ianhaney50 · October 29, 2015

The script does already check if it already exists which is good, I figured a way around it which seems to work

I created a new line of code called last repair ID used and then echoed the $invid and then under that got the input field to enter the next repair ID number

seems to work

Just want to say thank you for all the help and replies, really do appreciate it

budimir · October 29, 2015

The script does already check if it already exists which is good, I figured a way around it which seems to work

I created a new line of code called last repair ID used and then echoed the $invid and then under that got the input field to enter the next repair ID number

seems to work

Just want to say thank you for all the help and replies, really do appreciate it

Excellent, glad we could help!

Sign In

prefill ID number on form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information