I've got a database right, I've displayed a table on a page (Login.php) and have the necessary Edit Link's on each database record ->
echo "<td><a href='../DAL/edit_consultant.php?ID=$arrRows[Consultant_Id]'>Edit</a>";
I cannot find the reason why the records won't get updated?! E.g When I click the submit new details button. It will refresh the page and then display the the input fields that are empty.
If someone could have a look at the code, that'd be great. I've had trouble with this for a while now.
What is working:
- The columns from the database are put into the
edit_consultant.phpform, depending on the Consultant_Id passed in the edithref=''
What isn't working:
- When I click "submit new details" It won't update the database table. It will instead do nothing
db_functions.php:
<?php
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
error_reporting(-1);
//Database connection Variables
$localhost = "localhost";
$user = "root";
$password = "root";
$db = "Dalton";
$dsn = "mysql:host=$localhost;dbname=$db";
//Declare Global Variables
$dbConnection = NULL;
$stmt = NULL;
$numRecords = NULL;
//This connect database function can be used to connect anywhere
function connect(){
//These are variables from the other file (dblibary) - global allows access to these variables
global $user, $password, $dsn, $dbConnection; //Required to access the global variables.
try{
$dbConnection = new PDO($dsn, $user, $password);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $error){
//display error message if connection doesnt work
echo "The following error occured: " . $error->getMessage();
}
}
function insertContact(){
global $dbConnection, $stmt, $strName, $strEmail, $strPhone, $strTitle, $strGender, $booOk;
// global $booname, $booemail, $boophone, $bootitle,$boogender, $booOk;
connect();
$sqlStr = "INSERT INTO D_customer values('".$strName."','".$strEmail."','".$strPhone."','".$strTitle."','".$strGender."');";
try{
$stmt = $dbConnection->exec($sqlStr);
}
catch(PDOException $error){
$errinfo = ($dbConnection->errorInfo());
if($errinfo[1] == 1062){
echo "This Customer ID already Exists!";
}
else{
echo "An Error occured: " . $error->getMessage();
}
$booOk = 0;
}
//Close the DB connection
$dbConnection = NULL;
}
/*INSERT INTO DATABASE FUNCTION (CONTACT FORM)-------------------------*/
function updateContact(){
global $dbConnection, $stmt, $booOk;
global $booname, $booemail, $boophone, $bootitle,$boogender, $booOk;
global $strName, $strEmail, $strNumber, $strQuery, $strGender;
connect();
$sqlStr = "INSERT INTO D_contact values('".$strName."','".$strEmail."','".$strNumber."','".$strTitle."','".$strGender."');";
try{
$stmt = $dbConnection->exec($sqlStr);
}
catch(PDOException $error){
$errinfo = ($dbConnection->errorInfo());
if($errinfo[1] == 1062){
echo "This Customer ID already Exists!";
}
else{
echo "An Error occured: " . $error->getMessage();
}
$booOk = 0;
}
//Close the DB connection
$dbConnection = NULL;
}
/*INSERT INTO DATABASE FUNCTION (CONTACT FORM)-------------------------*/
/*READ THE DATABASE-------------------------------------------*/
function readQuery($table){
global $numRecords, $dbConnection, $stmt;
connect();
$sqlStr = "SELECT * FROM " . $table.";";
// $sqlStr = 'SELECT First_Name, Last_Name,Email FROM ' . $table . '";"';
// $sqlStr = "SELECT 'First_Name', 'Last_Name', 'Email' FROM " . $table .";";
try{
$stmt = $dbConnection->query($sqlStr);
if($stmt === false){
die("Error executing the qquery: $sqlStr");
}
}
catch(PDOException $error){
echo "An Error occured: " . $error->getMessage();
}
$numRecords = $stmt->rowCount();
//Close the DB connection
$dbConnection = NULL;
}
/*READ THE DATABASE-------------------------------------------*/
function readQuerySingle($table, $column, $colValue, $colType)
{
global $numRecords, $dbConnection, $stmt;
connect(); //Run connect function
$sqlStr = NULL; //Initialise Variable to hold query
if($colType === "numeric")
{
//Select Individual Record
$sqlStr = "SELECT * FROM ".$table." WHERE ".$column." = ".$colValue.";";
}
else //If Data-Type is non numeric
{
//Select Individual Record
$sqlStr = "SELECT * FROM ".$table." WHERE ".$column." = '".$colValue."';";
}
//Run Query
try
{
$stmt = $dbConnection->query($sqlStr);
if($stmt === false)
{
die("Error executing the query: $sqlStr");
}
}
catch(PDOException $error)
{
//Display error message if applicable
echo "An Error occured: ".$error->getMessage();
}
//How many records are there?
$numRecords = $stmt->rowcount();
//Close the database connection
$dbConnection = NULL;
}
function updateConsultant(){
global $dbConnection, $stmt, $booOk;
global $strConsultant_Id, $strFirst_Name, $strLast_Name, $strHome_Phone;
global $strMobile, $strEmail, $strDate_Commenced, $strDOB ,$strStreet_Address ;
global $strSuburb, $strPost_Code;
connect();
// $sqlStr = "INSERT INTO D_Consultant values('".$strConsultant_Id."','".$strFirst_Name."','".$strLast_Name."','".$strHome_Phone."','".$strMobile."','".$strEmail."','".$strDate_Commenced."','".$strDOB."','".$strStreet_Address."','".$strSuburb."','".$strPost_Code."');";
$sqlStr = "UPDATE D_Consultant SET ";
$sqlStr .= "Consultant_Id = '".$strConsultant_Id."',";
$sqlStr .= "First_Name = '".$strFirst_Name."',";
$sqlStr .= "Last_Name = '".$strLast_Name."',";
$sqlStr .= "Home_Phone = '".$strHome_Phone."',";
$sqlStr .= "Mobile = '".$strMobile."',";
$sqlStr .= "Email = '".$strEmail."',";
$sqlStr .= "Date_Commenced = '".$strDate_Commenced."',";
$sqlStr .= "DOB = '".$strDOB."',";
$sqlStr .= "Street_Address = '".$strStreet_Address."',";
$sqlStr .= "Suburb = '".$strSuburb."',";
$sqlStr .= "Post_Code = '".$strPost_Code."' ";
$sqlStr .= "WHERE Consultant_Id = '" . $strConsultant_Id ."';";
try{
$stmt = $dbConnection->exec($sqlStr);
}
catch(PDOException $error){
$errinfo = ($dbConnection->errorInfo());
if($errinfo[1] == 1062){
echo "This Consultant ID already Exists!";
}
else{
echo "An Error occured: " . $error->getMessage();
}
$booOk = 0;
}
//Close the DB connection
$dbConnection = NULL;
}
function insertConsultant(){
global $dbConnection, $stmt, $booOk;
global $strConsultant_Id, $strFirst_Name, $strLast_Name, $strHome_Phone;
global $strMobile, $strEmail, $strDate_Commenced, $strDOB, $strStreet_Address;
global $strSuburb, $strPost_Code, $boo0k;
connect();
$sqlStr = "INSERT INTO D_Consultant values('".$strConsultant_Id."','".$strFirst_Name."','".$strLast_Name."','".$strHome_Phone."','".$strMobile."','".$strEmail."','".$strDate_Commenced."','".$strDOB."','".$strStreet_Address."','".$strSuburb."','".$strPost_Code."');";
try{
$stmt = $dbConnection->exec($sqlStr);
}
catch(PDOException $error){
$errinfo = ($dbConnection->errorInfo());
if($errinfo[1] == 1062){
echo "This Consultant ID already Exists!";
}
else{
echo "An Error occured: " . $error->getMessage();
}
$booOk = 0;
}
//Close the DB connection
$dbConnection = NULL;
}
?>
edit_consultant.php:
<?php
include "../PHP/head.php";
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
error_reporting(-1);
require_once("db_functions.php");
require_once("../BLL/validate_form.php");
// $strConsultant_Id = $strFirst_Name = $strLast_Name = $strHome_Phone = "";
// $strMobile = $strEmail = $strDate_Commenced = $strDOB = $strStreet_Address = "";
// $strSuburb = $strPost_Code = "";
$booDate_Commenced = $booConsultant_Id = $booLast_Name = $booHome_Phone = $booMobile = 0;
$booEmail = $booDOB = $booStreet_Address = $booFirst_Name = $booSuburb = $booPost_code = 0;
$booOk = 1;
echo "<br />";
echo "<br /><br /><br />";
if(isset($_POST["submit"])){
updateConsultant();
}
else{
if(isset($_GET["ID"])) $strConsultant_Id = $_GET["ID"];
readQuerySingle("D_Consultant", "Consultant_Id", $strConsultant_Id, "NonNumeric");
//If there is a record continue
if($numRecords == 0){
echo "<span class='error'>No Matching Branch Found!</span><br /><br />";
}
else{
$arrRows = NULL;
//Get first and only result from database
$arrRows = $stmt->fetch(PDO::FETCH_ASSOC);
$strConsultant_Id = $arrRows['Consultant_Id'];
$strFirst_Name = $arrRows['First_Name'];
$strLast_Name = $arrRows['Last_Name'];
$strHome_Phone = $arrRows['Home_Phone'];
$strMobile = $arrRows['Mobile'];
$strEmail = $arrRows['Email'];
$strDate_Commenced = $arrRows['Date_Commenced'];
$strDOB = $arrRows['DOB'];
$strStreet_Address = $arrRows['Street_Address'];
$strSuburb = $arrRows['Suburb'];
$strPost_Code = $arrRows['Post_Code'];
}
}
echo "<form action='edit_consultant.php' method='post'><table id='mavis'>";
echo "<tr><th>Consultant Id</th><td><input type='text' name='strConsultant_Id' size='20' value='".$strConsultant_Id."' /></td>";
if($booConsultant_Id) echo "<td>Please enter a Consultant Id</td>";
echo "<tr><th>First Name</th><td><input type='text' name='strFirst_Name' size='20' value='".$strFirst_Name."' /></td>";
if($booFirst_Name) echo "<td>Please Enter your First Name</td>";
echo "<tr><th>Last Name</th><td><input type='text' name='strLast_Name' size='20' value='".$strLast_Name."' /></td>";
if($booLast_Name) echo "<td>Please enter a Last Name</td>";
echo "<tr><th>Home Phone</th><td><input type='text' name='strHome_Phone' size='20' value='".$strHome_Phone."' /></td>";
if($booHome_Phone) echo "<td>Please enter a Home Phone</td>";
echo "<tr><th>Mobile Number</th><td><input type='text' name='strMobile' size='20' value='".$strMobile."' /></td>";
if($booMobile) echo "<td>Please enter a Mobile Number</td>";
echo "<tr><th>Email</th><td><input type='text' name='strEmail' size='20' value='".$strEmail."' /></td>";
if($booEmail) echo "<td>Please enter an Email!</td>";
echo "<tr><th>Date Commenced</th><td><input type='text' name='strDate_Commenced' size='20' value='".$strDate_Commenced."' /></td>";
if($booDate_Commenced) echo "<td>Please enter a Date Commenced!</td>";
echo "<tr><th>DOB</th><td><input type='text' name='strDOB' size='20' value='".$strDOB."' /></td>";
if($booDOB) echo "<td>Please enter a DOB Number!</td>";
echo "<tr><th>Street_Address</th><td><input type='text' name='strStreet_Address' size='20' value='".$strStreet_Address."' /></td>";
if($booStreet_Address) echo "<td>Please enter a Street_Address!</td>";
echo "<tr><th>Street_Address</th><td><input type='text' name='strSuburb' size='20' value='".$strSuburb."' /></td>";
if($booSuburb) echo "<td>Please enter a Suburb!</td>";
echo "<tr><th>Post Code</th><td><input type='text' name='strPost_Code' size='20' value='".$strPost_Code."' /></td>";
if($booPost_code) echo "<td>Please enter a Post Code !</td>";
echo "<tr><td></td><td><input type='submit' name='submit' value='Submit New Details' /></td></tr></table></form>";
include "../PHP/footer.php";
?>
login.php:
<?php include "head.php"; ?>
<body>
<div id="particles-js">
<div class='open-text'>
<div class='open-text-wrap login-wrap'>
<form method='post'>
<input type='text' placeholder="Login Code" name='strCode' />
<input type='submit' name='submit' value='Login' />
</form>
<?php
$display = false;
function checkCode()
{
}
if(isset($_POST["submit"])){
if($_POST["strCode"] === "admin"){
$display = true;
}
else{
echo "<div class='error-login'>Authentication Failed</div>";
}
}
?>
</div>
</div>
</div>
<main>
<div class='container section-one-position cont-colour'>
<hr />
<div class='section-one'>
<h1>Consultant List</h1>
</div>
<hr />
</div>
<br />
<div class='full-container'>
<!-- DISPLAY THE TABLE FROM DATABASE -->
<?php
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
error_reporting(-1);
require_once("../DAL/db_functions.php");
if($display == true)
{
//Run query on branch table
readQuery("D_Consultant");
//If there are any details in branch table continue
if($numRecords === 0){
echo "<p>No Branches Found!</p>";
}
else{
$arrRows = NULL;
//Create table and headings
echo "<table id='dalton' border='0'>";
echo "<tr>";
// echo "<th>Prospect_No</th>";
echo "<th>Consultant ID</th>";
echo "<th>Surname</th>";
echo "<th>First Name</th>";
echo "<th>Home Phone</th>";
echo "<th>Mobile</th>";
// echo "<th>Phone</th>";
echo "<th>Email</th>";
echo "<th>Date Commenced</th>";
echo "<th>DOB</th>";
echo "<th>Street Address</th>";
echo "<th>Suburb</th>";
echo "<th>Post code</th>";
echo "<th></th>";
//echo "<th>Email</th>";
echo "</tr>";
while($arrRows = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr>";
// echo "<td>".$arrRows['Consultant_Id']."</td>";
echo "<td>".$arrRows['Consultant_Id']."</td>";
echo "<td>".$arrRows['Last_Name']."</td>";
echo "<td>".$arrRows['First_Name']."</td>";
echo "<td>".$arrRows['Home_Phone']."</td>";
echo "<td>".$arrRows['Mobile']."</td>";
// echo "<td>".$arrRows['Home_Phone']."</td>";
echo "<td>".$arrRows['Email']."</td>";
echo "<td>".$arrRows['Date_Commenced']."</td>";
echo "<td>".$arrRows['DOB']."</td>";
echo "<td>".$arrRows['Street_Address']."</td>";
echo "<td>".$arrRows['Suburb']."</td>";
echo "<td>".$arrRows['Post_Code']."</td>";
echo "<td><a href='../DAL/edit_consultant.php?ID=$arrRows[Consultant_Id]'>Edit</a>";
echo "<br /><a href='../DAL/delete_confirm.php?TYPE=Consultant&ID=$arrRows[Consultant_Id]'>Delete</a></td></tr>";
}
echo "</table>";
}
}
else{
echo "<p>Please enter your login code to view our list of consultants</p>";
}
?>
</div>
<br />
<div class='container section-one-position cont-colour'>
<?php echo "<form action='../DAL/add_branch.php' method='post'>";
echo "<input type='submit' value='Add a consultant' />";
echo "</form>";
echo "<p></P><P>$numRecords Records Returned</P>";?>
</div>
<!-- CONSULTANT LIST ABOVE _________________________________ -->
<div class='container section-one-position cont-colour'>
<hr />
<div class='section-one'>
<h1>Projects List</h1>
</div>
<hr />
</div>
<br />
<div class='full-container'>
<!-- DISPLAY THE TABLE FROM DATABASE -->
<?php
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
error_reporting(-1);
require_once("../DAL/db_functions.php");
if($display == true)
{
//Run query on branch table
readQuery("D_Project");
//readQuery("D_Projects");
//If there are any details in branch table continue
if($numRecords === 0){
echo "<p>No Branches Found!</p>";
}
else{
$arrRows = NULL;
//Create table and headings
echo "<table id='dalton' border='0'>";
echo "<tr>";
// echo "<th>Prospect_No</th>";
echo "<th>Project_No</th>";
echo "<th>Project_Name</th>";
echo "<th>Project_Description</th>";
echo "<th>Project_Manager</th>";
echo "<th>Start_Date</th>";
// echo "<th>Phone</th>";
echo "<th>Finish_Date</th>";
echo "<th>Budget</th>";
echo "<th>Cost_To_Date</th>";
echo "<th>Tracking_Statement</th>";
echo "<th>Client_No</th>";
echo "<th></th>";
//echo "<th>Email</th>";
echo "</tr>";
while($arrRows = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr>";
// echo "<td>".$arrRows['Consultant_Id']."</td>";
echo "<td>".$arrRows['Project_No']."</td>";
echo "<td>".$arrRows['Project_Name']."</td>";
echo "<td>".$arrRows['Project_Description']."</td>";
echo "<td>".$arrRows['Project_Manager']."</td>";
echo "<td>".$arrRows['Start_Date']."</td>";
// echo "<td>".$arrRows['Home_Phone']."</td>";
echo "<td>".$arrRows['Finish_Date']."</td>";
echo "<td>".$arrRows['Budget']."</td>";
echo "<td>".$arrRows['Cost_To_Date']."</td>";
echo "<td>".$arrRows['Tracking_Statement']."</td>";
echo "<td>".$arrRows['Client_No']."</td>";
echo "<td><a href='../DAL/edit_Project.php?ID=$arrRows[Project_No]'>Edit</a>";
echo "<br /><a href='../DAL/delete_confirm.php?TYPE=Consultant&ID=$arrRows[Project_No]'>Delete</a></td></tr>";
}
echo "</table>";
}
}
else{
echo "<p>Please enter your login code to view our list of consultants</p>";
}
?>
<br />
</div>
<div class='container section-one-position cont-colour'>
<?php echo "<form action='../DAL/add_branch.php' method='post'>";
echo "<input type='submit' value='Add a consultant' />";
echo "</form>";
echo "<p></P><P>$numRecords Records Returned</P>";?>
</div>
</main>
<?php include "footer.php"; ?>
