Retrieve auto increment
last entered auto increments data. In my case the owner_ID of the previously entered record into the mySQL database.
<?php
require "formfunctions.inc.php";
require "connect.inc.php";
startPage("Add details");
connectAndSelect(); //Uses my include php script functions to logon to mySQL using my credentials or die
if(isset($_POST['addDetails'])) //isset means Is Set, so this makes clicking the submit button true
{
errorCheckDetails();
}
else
{
showAddFormDetails();
}
endPage();
?>
Here is the functions include file:
<?php
/*This is a PHP script containing the majority of my forms, which I have made into functions. It keeps all the back-end data together, meaning the other PHP
files are a lot simpler, as I only need to call the functions if I need to use any of them, less code repitition meaning maximum efficency.
Again it is a required script so files relying on this will have it being required and will not parse if this file cannot be parsed by the server.*/
//******************************THIS FUNCTION IS CALLED AT THE BEGINNING OF EVERY PAGE, IT SETS UP THE DOCTYPE, DEFAULT COMMENT, NAVBAR, AND DIV TAGS TO BE DISPLAYED***************
function startPage($title = '')
{
echo "<!doctype html>
<!--NICK LITTLE
WEB 2 ASSIGNMENT 2- THE MOTOR VEHICLE REGISTER DATABASE-->
<html>
<head>
<meta charset=utf-8>
<title>$title</title>
<link href='css.css' rel='stylesheet' type='text/css'>
</head>
<body>
<div class='header'>
<img src='header.jpg'/>
<ul id='list-nav'>
<li><a href='frontend.php'>Home</a></li>
<li><a href='search.php'>Search/Delete</a></li>
<li><a href='addowner.php'>Add New Owner & Vehicle</a></li>
<li><a href='addvehicle.php'>Add Vehicle</a></li>
</ul>
</div>
<div class='container'>
<div class='content'>";
}
//**********SIMILAR TO THE START PAGE, CLOSES OFF DIV DAGS AND HTML BODY AND ENDING**********************************************************************************
function endPage()
{
echo "
</div>
</div>
</body>
</html>";
}
//****************THIS FORM IS DISPLAYED ON THE ADD OWNER/VEHICLE PAGE. IT JUST DISPLAYS THE FORM FOR USER INPUT**************************************************************************
function showAddFormDetails()
{
$self=$_SERVER['PHP_SELF']; //making the form self referring
//start form display - EVERYTHING IS DISPLAYED IN A NICE LOOKING FORM
echo "<form action='$self' method='POST'>
<center>
<table id='searchForm'>
<thead>
<tr>
<th colspan ='2'>Owner</th>
<th colspan ='2'> Vehicle</th>
</tr>
</thead>
<tfoot>
<tr>
<td></td>
<td></td>
<td><input type='submit' name='addDetails' value='Add Details'/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>Title:</td>
<td><input type='text' name='title' size='5px'/></td>
<td>Make:</td>
<td><input type='text' name='make' size='20px'/></td>
</tr>
<tr>
<td>Name:</td>
<td><input type='text' name='name' size='25px'/></td>
<td>Model</td>
<td><input type='text' name='model' size='20px'/></td>
</tr>
<tr>
<td>Address:</td>
<td><input type='text' name='address' size='48px'/></td>
<td>Year:</td>
<td><input type='text' name='year' size='10px'/></td>
</tr>
<tr>
<td>Phone:</td>
<td><input type='text' name='phonenumber' size='10px'/></td>
<td>Registration:</td>
<td><input type='text' name='rego' size='10px'/></td>
</tr>
<tr>
<td></td>
<td></td>
<td>Kilometres:</td>
<td><input type='text' name='kms' size='10px'/></td>
</tr>
</tbody>
</table>
</form>";
}
//**********FUNCTION TO DISPLAY FORM TO ADD A VEHICLE TO AN EXISTING OWNER*******************************************************************************************************
function showAddFormVehicle() //SELECT NAME FROM THE OWNER ID TO PERFORM AN SQL QUERY ON
{
$self=$_SERVER['PHP_SELF']; //making the form self referring
//start form display - EVERYTHING IS DISPLAYED IN A NICE LOOKING FORM
echo"Please fill out the required fields to add a vehicle to the system<br /><br />
Please note: A vehicle must be added to an <strong>existing owner</strong><br /><br />
Click here to <a href='addowner.php'> add a new owner and vehicle</a>
<form action='$self' method='POST'>
<center>
<table id='searchForm'>
<thead>
<tr>
<th colspan ='2'> Vehicle</th>
</tr>
</thead>
<tfoot>
<tr>
<td></td>
<td></td>
<td><input type='submit' name='addVehicle' align='right' value='Add Vehicle'/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>Owner:</td>
<td><select name='owner_ID'><option value='-1' selected='selected'>Please select an owner...</option>";
$selectString ="SELECT DISTINCT owner_ID, name FROM tblOwner";
$result=mysql_query($selectString);
while($row = mysql_fetch_assoc($result))
{
$owner_ID = $row['owner_ID'];
$owner_name = $row['name'];
echo "<option value='$owner_ID'>$owner_name</option>";
}
echo"</select>
<tr>
<td>Make:</td>
<td><input type='text' name='make' size='20px'/></td>
</tr>
<tr>
<td>Model:</td>
<td><input type='text' name='model' size='20px'/></td>
</tr>
<tr>
`<td>Year:</td>
<td><input type='text' name='year' size='10px'/></td>
</tr>
<tr>
<td>Registration:</td>
<td><input type='text' name='rego' size='10px'/></td>
</tr>
<tr>
<td>Kilometres:</td>
<td><input type='text' name='kms' size='10px'/></td>
</tr>
</tbody>
</table>
</form>";
}
//************FUNCTION TO ERROR CHECK THE INPUT ON THE ADDING A VEHICLE PAGE - SLIGHT VARIATION ON ADDING NEW VEHICLE AND OWNER SIMULTANEOUSLY**************************************************
function delete()
{
/*foreach($_POST as $field => $value)
{
/*echo "$value<br>
$field<br>";
}*/
$vehicleArray = $_POST['deleteButton'];
$size = sizeOf( $vehicleArray);
/*echo "SIZE is: $size<br>"; //SIZE returns a '1'
echo "ARRAY is: $vehicleArray";
echo" $vehicleArray[0]";
//First index of array is a 'D'*/
for ($i = 0; $i < $size; $i++) //LOOP THROUGH THE SIZE AND ARRAY
{
$num = $vehicleArray[$i];
$query = "DELETE FROM tblVehicle WHERE vehicle_ID='$num'";
//echo"$query";
$result = mysql_query($query);
echo "Thank you, the selected vehicle(s) were removed from the system <br />
<i> Please note the OWNER of the vehicle will remain in the system </i>";
}
}
//*****************************THIS FUNCTION ERROR CHECKS USER INPUT WHEN ATTEMPTING TO INSERT AN OWNER**************************************************************************
function errorCheckDetails()
{
//assigning variables to the fields filled in, creates variables and assigns to 'NAME' form input value------
//----------owner variables----------------//
$ownerTitle = mysql_real_escape_string($_POST['title']);
$ownerName = mysql_real_escape_string($_POST['name']);
$ownerAddress = mysql_real_escape_string($_POST['address']);
$ownerPhone = mysql_real_escape_string($_POST['phonenumber']);
//--------vehicle variables------------//
$vehicleMake = mysql_real_escape_string($_POST['make']);
$vehicleModel = mysql_real_escape_string($_POST['model']);
$vehicleYear = mysql_real_escape_string($_POST['year']);
$vehicleRego = mysql_real_escape_string($_POST['rego']);
$vehicleKms = mysql_real_escape_string($_POST['kms']);
$allFilled = true;
//checking to see that all individual fields are filled in:
if (empty($_POST['title'])==0)
$allFilled = false; //If a specefic form field is empty, it is set to true, or else it is false
if (empty($_POST['name'])==0)
$allFilled = false;
if (empty($_POST['address'])==0)
$allFilled = false;
if (empty($_POST['phonenumber'])==0)
$allFilled = false;
if (empty($_POST['make'])==0)
$allFilled = false;
if (empty($_POST['model'])==0)
$allFilled = false;
if (empty($_POST['year'])==0)
$allFilled = false;
if (empty($_POST['rego'])==0)
$allFilled = false;
if (empty($_POST['kms'])==0)
$allFilled = false;
//providing if all of the fields are filled in, insert user's data into owner table, all required fields
if ($allFilled)
{
//*********************************************mySQL queries**********************************************************
$insertOwnerQuery="INSERT INTO 开发者_开发技巧tblOwner(title,name,address,phone)
VALUES ('$ownerTitle','$ownerName','$ownerAddress','$ownerPhone')";
$result=mysql_query($insertOwnerQuery);
$aOwner = mysql_insert_id(); //Assign variable to mySQL function, returns last known user id input, so as to determine auto_inc for owner_ID
$insertVehicleQuery="INSERT INTO tblVehicle(owner_ID,make,model,year,rego,kms)
VALUES ('$aOwner','$vehicleMake','$vehicleModel','$vehicleYear','$vehicleRego','$vehicleKms')";
$result=mysql_query($insertVehicleQuery);
echo "Thank you, your entry has been added to the system"; //Echo to screen to inform user owner has been added successfully.
}
else
{
//error messages that appear for each individual field that is not filled in:
if (empty($_POST["title"]))
echo"<p>The 'Owner Title' field must be filled in.</p>";
if (empty($_POST["name"]))
echo "<p>The 'Name' field must be filled in.</p>";
if (empty($_POST["address"]))
echo "<p>The 'Address' field must be filled in.</p>";
if (empty($_POST["phonenumber"]))
echo "<p>The 'Phone Number' field must be filled in.</p>";
if (empty($_POST["make"]))
echo "<p>The 'Vehicle Make' field must be filled in.</p>";
if (empty($_POST["model"]))
echo "<p>The 'Vehicle Model' field must be filled in.</p>";
if (empty($_POST["year"]))
echo "<p>The 'Vehicle Year' field must be filled in.</p>";
if (empty($_POST["rego"]))
echo "<p>The 'Vehicle Registration' field must be filled in.</p>";
if (empty($_POST["kms"]))
echo "<p>The 'Vehicle Kilometers' field must be filled in.</p>";
}
/*echo '<form action = '$self' method='POST'>
<input type='submit' name='returnAddOwner' value='Return to Adding an Owner'>
</form>';*/
}
//************FUNCTION TO ERROR CHECK THE INPUT ON THE ADDING A VEHICLE PAGE - SLIGHT VARIATION ON ADDING NEW VEHICLE AND OWNER SIMULTANEOUSLY**************************************************
function errorCheckVehicle()
{
//assigning variables to the fields filled in, creates variables and assigns to 'NAME' form input value------
//----------owner variables----------------//
$owner_ID = $_POST['owner_ID']; //NEED 2 FIGURE OUT HOW TO DETECT OPTION VALUE FOR OWNER NAME
//--------vehicle variables------------//
$vehicleMake = $_POST['make'];
$vehicleModel = $_POST['model'];
$vehicleYear = $_POST['year'];
$vehicleRego = $_POST['rego'];
$vehicleKms = $_POST['kms'];
$allFilled = true;
//checking to see that all individual fields are filled in:
if ($vehicleMake == "") $allFilled = false;
if ($vehicleModel == "") $allFilled = false;
if ($vehicleYear == "") $allFilled = false;
if ($vehicleRego == "") $allFilled = false;
if ($vehicleKms == "") $allFilled = false;
//providing if all of the fields are filled in, insert user's data into owner table, all required fields
if ($allFilled)
{
$insertVehicleQuery="INSERT INTO tblVehicle(owner_ID,make,model,year,rego,kms)
VALUES ('$owner_ID','$vehicleMake','$vehicleModel','$vehicleYear','$vehicleRego','$vehicleKms')";
$result=mysql_query($insertVehicleQuery);
echo "Thank you, the vehicle has been added to the system"; //Echo to screen to inform user owner has been added successfully.
}
//error messages that appear for each individual field that is not filled in:
else
{
if (empty($_POST["make"]))
echo "<p>The 'Vehicle Make' field must be filled in.</p>";
if (empty($_POST["model"]))
echo "<p>The 'Vehicle Model' field must be filled in.</p>";
if (empty($_POST["year"]))
echo "<p>The 'Vehicle Year' field must be filled in.</p>";
if (empty($_POST["rego"]))
echo "<p>The 'Vehicle Registration' field must be filled in.</p>";
if (empty($_POST["kms"]))
echo "<p>The 'Vehicle Kilometers' field must be filled in.</p>";
}
}
//*********************************************************DISPLAY SEARCH FORM ON PAGE************************************************************************
function showSearchForm()
{
$self=$_SERVER['PHP_SELF']; //making the form self referring
echo "
<form action='$self' method='POST'>
<center>
<table id='searchForm'>
<thead>
<tr>
<th colspan='2'>Vehicle</th>
<th colspan='2'>Owner</th>
</tr>
</thead>
<tfoot>
<tr>
<td></td>
<td></td>
<td class='searchSubmitButtons'><input type='submit' name='search' value='Search Records' /></td>
<td class='searchSubmitButtons'><input type='submit' name='search' value='List all database entries' /></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>Make:</td>
<td><input type='text' name='vehiclemake' size='20' /></td>
<td>Name:</td>
<td><input type='text' name='ownername' size='25' /></td>
</tr>
<tr>
<td>Model:</td>
<td><input type='text' name='vehiclemodel' size='20' /></td>
<td>Address:</td>
<td><input type='text' name='owneraddress' size='48' /></td>
</tr>
<tr>
<td><label for='vehicleyear'>Year:</label></td>
<td><input type='text' name='vehicleyear' id='vehicleyear' size='10' /></td>
<td>Phone:</td>
<td><input type='text' name='ownerphone'/></td>
</tr>
<tr>
<td>Registration:</td>
<td><input type='text' name='vehiclerego' size='10' /></td>
</tr>
<tr>
<td>Km's:</td>
<td><input type = 'text' name='vehiclekms' size='10'/></td>
</tr>
</tbody>
</table>
</center>
</form>";
}
function showRecords()
{
$self=$_SERVER['PHP_SELF']; //making the form self referring
//assigning variables to the fields filled in:
//$owner_ID = $_POST['ownerID'];
//$title = $_POST['ownertitle'];
$name = $_POST['ownername'];
$address = $_POST['owneraddress'];
$phone = $_POST['ownerphone'];
$make = $_POST['vehiclemake'];
$model = $_POST['vehiclemodel'];
$year = $_POST['vehicleyear'];
$rego = $_POST['vehiclerego'];
$kms = $_POST['vehiclekms'];
//print search results from both tables - patients and owners (unnecessary fields or duplicates excluded) % is the like function, so could put 'at' to get cat:
$selectString = "SELECT vehicle_ID,make,model,year,rego,kms,name,phone
FROM tblVehicle,tblOwner
WHERE (tblVehicle.owner_ID = tblOwner.owner_ID
AND make LIKE '%$make%'
AND model LIKE '%$model%'
AND rego LIKE '%$rego%'
AND kms LIKE '%$kms%'
AND name LIKE '%$name%'
AND address LIKE '%$address%'
AND phone LIKE '%$phone%')";
$result = mysql_query($selectString);
//vehicle_ID, tblOwner.owner_ID,
echo"<form action = '$self' method='POST'>
<table border='1' cellpadding='8'>
<tr>
<th>Vehicle ID</th>
<th>Vehicle Make</th>
<th>Vehicle Model</th>
<th>Vehicle Year</th>
<th>Vehicle Registration</th>
<th>Vehicle KM</th>
<th>Owner Name</th>
<th>Owner Phone</th>
<th>Delete</th>
</tr>";
while($row = mysql_fetch_row($result))
{
echo '<tr>';
foreach($row as $field => $value)
{
if( $field == 0)
$vehicle_ID=$value;
echo "<td>$value</td>";
}
echo"<td><input type ='checkbox' name = deleteButton[] value='$vehicle_ID'></td>";
echo '</tr>';
}
echo "</table>
<br><br>
<input type='submit' name='returnSearch' value='Return to Searching Records'>
<input type='submit' name='del' align='right' value='Delete Selected Records'>
</form>";
}
?>
Sorry they are big files. Im new here :( Don't know how to post big amounts of code.
Cheers
you are answering your own question
in your code
$aOwner = mysql_insert_id(); //Assign variable to mySQL function, returns last known user id input, so as to determine auto_inc for owner_ID
so just print the $aOwner
You can use
SELECT LAST_INSERT_ID();
or in php
mysql_query("INSERT ... ");
$increment = mysql_insert_id();
@genesis I believe you want to display the next incremement
owner_ID
where a user know, what will be his/her owner_ID.
If i am right read below information
1] retrive the owner_ID from tblVehicle table,
2] take the highest number,
3] increment the highest number with 1,
4] then go with the insertion code,
And i tested with queries listed above
SELECT from table_name LAST_INSERT_ID()
or
SELECT LAST_INSERT_ID();
these two queries didnt work out, i provides only '0' values.
better try up with code insertion
$insertVehicleQuery="INSERT INTO tblVehicle(make,model,year,rego,kms) VALUES ('$vehicleMake','$vehicleModel','$vehicleYear','$vehicleRego','$vehicleKms')";
just remove the *owner_ID* from the above query
mysql reference
$result = mysql_query(SELECT from table_name LAST_INSERT_ID())";
echo $result;
php reference
mysql_query("watever query");
echo mysql_insert_id();
you can get this by
$r = mysql_query("SHOW TABLE STATUS LIKE 'table_name' ");
$row = mysql_fetch_array($r);
$Auto_increment = $row['Auto_increment'];
mysql_free_result($r);
加载中,请稍侯......
精彩评论