Jump to content
Killersites Community
falkencreative

Basic PHP System: View, Edit, Add, Delete records with MySQLi

Recommended Posts

I have one question what i really want to know  ^_^

 

In the record.php code on the form and the rest of the code, how do we add an <textarea> style to the form and modify the code so we do not send blanco text?

 

 

 

I tried it with an <textarea> in the form but because it is empty and can't hold value, only between <textarea> TEXT </textarea>
 

AND in the code you are saying that you can't have empty boxes for input in the database...

 

 

 

How do i deal with that?

Share this post


Link to post
Share on other sites

Hi

 

I was just wondering how would I make two radio buttons in the add record form, one will be yes and the other will be no and need when either one clicked to save in the database, was just wondering how to set up the database to store the radio button values and what the PHP would look like

 

I did similar for another site but was in MySQL not MySQLi

 

Thank you in advance

 

Ian

Share this post


Link to post
Share on other sites

Hi

 

I have got it sort of working, it is saving the values in the database but is not editing them when I change it from yes to no, is it ok if my code is checked over by someone please as can't see the issue, my code for the edit-customer.php is below, I think it is to do with the line below but not 100%

$introemail = (int)$_POST['introemail'];
<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

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

	// connect to the database
	include("connect-db.php");
?>
	<?php 
$title = "Edit Customer";

include ( 'includes/header.php' );

?>
<?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($name = '', $company = '', $officenumber = '', $mobilenumber = '', $website = '', $email = '', $addressline1 = '', $addressline2 = '', $town = '', $county = '', $postcode = '', $notes = '', $customertype = '', $businesscategory = '', $introemail = '', $error = '', $id = '')
	{ ?>
		
			<body>
				<h1>Edit Customer</h1>
				<?php if ($error != '') {
					echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
						. "</div>";
				} ?>
				
				<form action="" method="post">
				<div class="left-side">
					<?php if ($id != '') { ?>
						<input type="hidden" name="id" value="<?php echo $id; ?>" />
						<p>ID: <?php echo $id; ?></p>
					<?php } ?>
					
    <strong>Name: *</strong> <input type="text" name="name" value="<?php echo $name; ?>"/>
   <br/>
   <strong>Company: *</strong> <input type="text" name="company" value="<?php echo $company; ?>"/>
   <br>
   <strong>Office Number: *</strong> <input type="text" name="officenumber" value="<?php echo $officenumber; ?>"/>
   <br>
   <strong>Mobile Number:</strong> <input type="text" name="mobilenumber" value="<?php echo $mobilenumber; ?>"/>
   <br>
   <strong>Website: *</strong> <input type="text" name="website" value="<?php echo $website; ?>"/>
   <br>
   <strong>Email: *</strong> <input type="text" name="email" value="<?php echo $email; ?>"/>
   </div>
   
   <div class="right-side">
   <strong>Address Line 1: *</strong> <input type="text" name="addressline1" value="<?php echo $addressline1; ?>"/>
   <br>
   <strong>Address Line 2:</strong> <input type="text" name="addressline2" value="<?php echo $addressline2; ?>"/>
   <br>
   <strong>Town: *</strong> <input type="text" name="town" value="<?php echo $town; ?>"/>
   <br>
   <strong>County: *</strong> <input type="text" name="county" value="<?php echo $county; ?>"/>
   <br>
   <strong>Postcode: *</strong> <input type="text" name="postcode" value="<?php echo $postcode; ?>"/>
   <br>
   <strong>Notes:</strong>
   <br>
   <textarea type="text" name="notes" value="<?php echo $notes; ?>"/><?php echo $notes; ?></textarea>
   </div>
   
					<div class="far-right-side">
                    <strong>Customer Type: *</strong> <input type="text" name="customertype" value="<?php echo $customertype; ?>"/>
                    <br>
                    <strong>Business Category:</strong> <input type="text" name="businesscategory" value="<?php echo $businesscategory; ?>"/>
                    <br>
                    <strong>Intro Email Sent:</strong>
                    <label style="color: #000000;">Yes<input type="radio" name="introemail" value="1" <?php if($introemail == 1) echo 'checked="checked"'; ?> ></label>
                    <br>
                    <label style="color: #000000;">No<input type="radio" name="introemail" value="0"<?php if($introemail == 0) echo 'checked="checked"'; ?> ></label>
                    
                    <p>* required</p>
					<input type="submit" name="submit" value="Save Changes" />
				</div>
				</form>
			
            <?php include( 'includes/footer.php' ); ?>
            
        <?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'];
				$name = htmlentities($_POST['name'], ENT_QUOTES);
				$company = htmlentities($_POST['company'], ENT_QUOTES);
				$officenumber = htmlentities($_POST['officenumber'], ENT_QUOTES);
				$mobilenumber = htmlentities($_POST['mobilenumber'], ENT_QUOTES);
				$website = htmlentities($_POST['website'], ENT_QUOTES);
				$email = htmlentities($_POST['email'], ENT_QUOTES);
				$addressline1 = htmlentities($_POST['addressline1'], ENT_QUOTES);
				$addressline2 = htmlentities($_POST['addressline2'], ENT_QUOTES);
				$town = htmlentities($_POST['town'], ENT_QUOTES);
				$county = htmlentities($_POST['county'], ENT_QUOTES);
				$postcode = htmlentities($_POST['postcode'], ENT_QUOTES);
				$notes = htmlentities($_POST['notes'], ENT_QUOTES);
				$customertype = htmlentities($_POST['customertype'], ENT_QUOTES);
				$businesscategory = htmlentities($_POST['businesscategory'], ENT_QUOTES);
				$introemail = (int)$_POST['introemail'];
				
				// check that firstname and lastname are both not empty
				if ($name == '' || $company == '' || $officenumber == '' || $website == '' || $email == '' || $addressline1 == '' || $town == '' || $county == '' || $postcode == '' || $notes == '' || $customertype == '')
				{
					// if they are empty, show an error message and display the form
					$error = 'ERROR: Please fill in all required fields!';
					renderForm($name, $company, $officenumber, $mobilenumber, $website, $email, $addressline1, $addressline2, $town, $county, $postcode, $notes, $customertype, $businesscategory, $introemail, $error, $id);
				}
				else
				{
					// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE customers SET name = ?, company = ?, officenumber = ?, mobilenumber = ?, website = ?, email = ?, addressline1 = ?, addressline2 = ?, town = ?, county = ?, postcode = ?, notes = ?, 
customertype = ?, businesscategory = ? introemail = ? WHERE id=?"))
					{
	$stmt->bind_param("sssssssssssssssi", $name, $company, $officenumber, $mobilenumber, $website, $email, $addressline1, $addressline2, $town, $county, $postcode, $notes, $customertype, $businesscategory, $introemail, $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-paginated.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 * FROM customers WHERE id=?"))
				{
					$stmt->bind_param("i", $id);
					$stmt->execute();
					
					$stmt->bind_result($id, $name, $company, $officenumber, $mobilenumber, $website, $email, $addressline1, $addressline2, $town, $county, $postcode, $notes, $customertype, $businesscategory, $introemail);
					$stmt->fetch();
					
					// show the form
					renderForm($name, $company, $officenumber, $mobilenumber, $website, $email, $addressline1, $addressline2, $town, $county, $postcode, $notes, $customertype, $businesscategory, $introemail, 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-paginated.php");
			}
		}
	}
	// if the form hasn't been submitted yet, show the form
		else
		{
			renderForm();
		}
	
	// close the mysqli connection
	$mysqli->close();
?>

Share this post


Link to post
Share on other sites


I am getting the following error and I am not sure how to fix it. Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /Applications/XAMPP/xamppfiles/htdocs/test/records.php on line 135


<?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($first = '', $middle = '', $last = '', $ClientID = '', $Diagnosis = '', $Gender = '', $LevelCare = '', $Counselor = '', $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 Record"; } else { echo "New Record"; } ?>
                </title>
                <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
            </head>
            <body>
                <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">
                <div>
                    <?php if ($id != '') { ?>
                        <input type="hidden" name="id" value="<?php echo $id; ?>" />
                        <p>id: <?php echo $id; ?></p>
                    <?php } ?>

                    <strong>First Name: *</strong> <input type="text" name="FirstName"
                        value="<?php echo $first; ?>"/><br/>
                        <strong>Middle Name: *</strong> <input type="text" name="MiddleName"
                        value="<?php echo $middle; ?>"/>
                    <strong>Last Name: *</strong> <input type="text" name="LastName"
                        value="<?php echo $last; ?>"/>
                        <strong>Client ID: *</strong> <input type="text" name="ClientID"
                        value="<?php echo $ClientID; ?>"/>
                        <strong>Diagnosis: *</strong> <input type="text" name="Diagnosis"
                        value="<?php echo $Diagnosis; ?>"/>
                        <strong>Gender: *</strong> <input type="text" name="Gender"
                        value="<?php echo $Gender; ?>"/>
      <strong>Level of Care: *</strong> <input type="text" name="LevelCare"
                        value="<?php echo $LevelCare; ?>"/>
                        <strong>Counselor: *</strong> <input type="text" name="Counselor"
                        value="<?php echo $Counselor; ?>"/>
                    <p>* required</p>
                    <input type="submit" name="submit" value="Submit" />
                </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'];
                $FirstName = htmlentities($_POST['FirstName'], ENT_QUOTES);
                $MiddleName = htmlentities($_POST['MiddleName'], ENT_QUOTES);
                $LastName = htmlentities($_POST['LastName'], ENT_QUOTES);
                $ClientID = htmlentities($_POST['ClientID'], ENT_QUOTES);
    $Diagnosis = htmlentities($_POST['Diagnosis'], ENT_QUOTES);
    $Gender = htmlentities($_POST['Gender'], ENT_QUOTES);
    $LevelCare = htmlentities($_POST['LevelCare'], ENT_QUOTES);
    $Counselor = htmlentities($_POST['Counselor'], ENT_QUOTES);

                // check that FirstName and LastName are both not empty
                if ($FirstName == '' || $MiddleName == '' || $LastName == '' || $ClientID == '')
                {
                    // if they are empty, show an error message and display the form
                    $error = 'ERROR: Please fill in all required fields!';
                    renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor, $error, $id);
                }
                else
                {
                    // if everything is fine, update the record in the database
                    if ($stmt = $mysqli->prepare("UPDATE clients SET FirstName = ?, MiddleName = ?, LastName = ?, ClientID = ?, Diagnosis = ?, Gender = ?, LevelCare = ?, Counselor = ?
                        WHERE id=?"))
                    {
                        $stmt->bind_param("sssisss", $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $counselor, $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.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 * FROM clients WHERE id=?"))
                {
                    $stmt->bind_param("i", $id);
                    $stmt->execute();

                    $stmt->bind_result($id, $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor);
                    $stmt->fetch();

                    // show the form
                    renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor, 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.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
            $FirstName = htmlentities($_POST['FirstName'], ENT_QUOTES);
            $MiddleName = htmlentities($_POST['MiddleName'], ENT_QUOTES);
            $LastName = htmlentities($_POST['LastName'], ENT_QUOTES);
            $ClientID = htmlentities($_POST['ClientID'], ENT_QUOTES);
   $Diagnosis = htmlentities($_POST['Diagnosis'], ENT_QUOTES);
   $Gender = htmlentities($_POST['Gender'], ENT_QUOTES);
   $LevelCare = htmlentities($_POST['LevelCare'], ENT_QUOTES);
   $Counselor = htmlentities($_POST['Counselor'], ENT_QUOTES);

            // check that FirstName and LastName are both not empty
            if ($FirstName == '' || $MiddleName == '' || $LastName == '' || $ClientID == '')
            {
                // if they are empty, show an error message and display the form
                $error = 'ERROR: Please fill in all required fields!';
                renderForm($FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $LevelCare, $Counselor, $error);
            }
            else
            {
                // insert the new record into the database
                if ($stmt = $mysqli->prepare("INSERT clients (FirstName, MiddleName, LastName, ClientID, Diagnosis, Gender, LevelCare, Counselor) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"))
                {
                    $stmt->bind_param("ssssssss", $FirstName, $MiddleName, $LastName, $ClientID, $Diagnosis, $Gender, $LevelCare, $Counselor);
                    $stmt->execute();
                    $stmt->close();
                }
                // show an error if the query has an error
                else
                {
                    echo "ERROR: Could not prepare SQL statement.";
                }

                // redirec the user
                header("Location: view.php");
            }

        }
        // if the form hasn't been submitted yet, show the form
        else
        {
            renderForm();
        }
    }

    // close the mysqli connection
    $mysqli->close();
?>

 

Edited by scampbell70

Share this post


Link to post
Share on other sites

I have one question what i really want to know  ^_^

 

In the record.php code on the form and the rest of the code, how do we add an <textarea> style to the form and modify the code so we do not send blanco text?

 

 

 

I tried it with an <textarea> in the form but because it is empty and can't hold value, only between <textarea> TEXT </textarea>

 

AND in the code you are saying that you can't have empty boxes for input in the database...

 

 

 

How do i deal with that?

 

Ok, I think you are dealing with two issues here, first, in your php, for stmt must have a spot (if you will) for each database field/column. Does that make sense? So if you are capturing from a form the following:

 

- name-first

- name-last

- comment

- date

 

... You must have the same columns in your database and you must have the same spots in the stmt ... inside the $mysqli->prepare ...

 

Stef

Share this post


Link to post
Share on other sites

Hi

 

I need bit of help if ok with this script

 

I have put all the coding in and seems to be all ok but on the records.php file, I want to add in a select menu and then show extra text input fields based on what the user selects in the select menu but the extra fields don't show when I select a option value

 

below is my coding for the records.php file with the select menu part and it's javascript

<form action="" method="post" class="basic-grey">
				<div>
					<?php if ($id != '') { ?>
						<input type="hidden" name="id" value="<?php echo $id; ?>" />
						<p>ID: <?php echo $id; ?></p>
					<?php } ?>
					<strong>Plan Name: *</strong>
                    <select name="support_plans" id="plan">
                    <option>Select the support plan</option>
                    <option value="Domestic">Domestic</option>
                    <option value="Domestic+">Domestic+</option>
                    <option value="Domestic Gold">Domestic Gold</option>
                    <option value="Domestic+ Gold">Domestic+ Gold</option>
                    <option value="Commercial">Commercial</option>
                    </select>
                        <br/>
                        <div id="extra" style="display:none">
					<strong>PC/Laptop 1: *</strong> <input type="text" name="machine1"
						value="<?php echo $machine1; ?>"/>
                        <br>
                        <strong>PC/Laptop 2: *</strong> <input type="text" name="machine2"
						value="<?php echo $machine2; ?>"/>
                        <br>
                        <strong>PC/Laptop 3: *</strong> <input type="text" name="machine3"
						value="<?php echo $machine3; ?>"/>
                        <br>
                        <strong>PC/Laptop 4: *</strong> <input type="text" name="machine4"
						value="<?php echo $machine4; ?>"/>
                        <br>
                        <strong>PC/Laptop 5: *</strong> <input type="text" name="machine5"
						value="<?php echo $machine5; ?>"/>
                        </div>
                        <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>Date Plan Purchased: *</strong> <input type="text" name="date_plan_purchased"
						value="<?php echo $date_plan_purchased; ?>"/>
                        <br>
					<p>* required</p>
					<input type="submit" name="submit" value="Submit" />
				</div>
				</form>
                
                <script>	
	$(document).ready(function () {
  $('#support select[name="support_plans"]').change(function () {
    val = $('#support select[name="support_plans"] option:selected').val();
	if (val == 'Domestic+' || val == 'Domestic+ Gold' || val == 'Commercial') {
        $('#extra').css('display', 'inline');
    } else {
        $('#extra').css('display', 'none');
    }
  });  
	});
</script>

Can someone help me please as can't work it out and I have also added in the jquery 1.10.1 from googleapi

 

Thank you in advance

 

Ian

Share this post


Link to post
Share on other sites

Hi

 

I am having a couple more issues with the script

 

I have added in some extra fields and all works apart from two of the extra fields, I have made them date input fields and the mysql data type is date and am using datepicker but for some reason, the date is being added into the mysql table as 00/00/0000

 

Can't work out why, the coding is below

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?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 ='', $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: "dd/mm/yy",
	showOn:"both"
	});
  });
  
  $(function() {
	$( "#exstdate" ).datepicker({
	showButtonPanel: true,
	dateFormat: "dd/mm/yy",
	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>

			</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>
                        </select>
                        <br>
                        <strong>Expected Repair Date:</strong> <input type="date" 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>Expected Start Date:</strong> <input type="date" 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>Delivery Type:</strong>
                        <select name="deltype">
                        <option value="Customer Pickup">Customer Pickup</option>
                        <option value="Delivery">Delivery</option>
                        </select>
                        <br>
                        <strong>Comments:
                        <br>
                        <textarea name="comments">
                        <?php echo $comments; ?>
                        </textarea>
                        <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);
				
				// 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 = ?
						WHERE id=?"))
					{
						$stmt->bind_param("sssssssssssssi", $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $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 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);
					$stmt->fetch();
					
					// show the form
					renderForm($customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, 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);
			
			// 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);
			}
			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) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
				{
					$stmt->bind_param("sssssssssssss", $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments);
					$stmt->execute();
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "ERROR: Could not prepare SQL statement.";
				}
				
				// 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();
?>

Hope someone can help me please

Share this post


Link to post
Share on other sites

I know it is the datepicker confusing the mysql as the standard format is YYYY-MM-DD but datepicker is changing it to DD/MM/YY so just not sure how to have it as DD/MM/YY but make it still add into the database as YYYY/MM/DD or convert to DD/MM/YYYY in the database

 

Hope that makes sense

Share this post


Link to post
Share on other sites

Hi Stef

 

Oh right ok cool

 

I need bit of help with a update issue, I am using this script and for some reason, the data is not being updated. I only want to be able to update three columns within the database table but don't seem to be doing it

 

I have put the coding in a pastebin link below

 

http://pastebin.com/mK9eAHQ9

Share this post


Link to post
Share on other sites

Hi, I need some help with this and creating a search form that allows me to filter dates so I can see data between two dates but I keep getting no results to display even though I should have 1 set displayed based on the dates I am entering, below is my code

 

search-data.php

<form method="post" action="data-results.php">
    <label>From Date : </label><input type="text" name="exrdate" id="fromdate" />
    <br/><br/>
    <label>To Date   : </label><input type="text" name="exrdate" id="todate" />
    <br/><br/>
    <input type="submit" name="submit" value="Submit" />
    </form>

data-results.php

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php

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

$per_page=5;
if (isset($_GET["page"])) {
    $page = $_GET["page"];
} else {
    $page=1;
}

// Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page;

// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{

$fromdate = mysqli_real_escape_string($mysqli, $_POST['exrdate']);
$todate = mysqli_real_escape_string($mysqli, $_POST['exrdate']);

// get the records from the database
    if ($result = $mysqli->query("SELECT id, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, DATE_FORMAT(exrdate,'%d/%m/%Y') AS exrdate, exrtime, DATE_FORMAT(exstdate,'%d/%m/%Y') AS exstdate, exstime, deltype, comments, job_cost, part_cost, profit FROM repairs WHERE exrdate between '$fromdate' and '$todate' ORDER BY id LIMIT $start_from, $per_page"))

    {
		// display records if there are records to display
        if ($result->num_rows > 0)
        {
			// display records in a table
            echo "<table class='view-repairs'>";

            // set table headers
            echo "<tr><th>Repair ID</th>
                <th>Customer Name</th>
                <th>Customer Email</th>
                <th>Customer Phone</th>
                <th>Computer Make</th>
                <th>Computer Model</th>
                <th>Technician</th>
                <th>Status</th>
                <th>Expected Start Date</th>
                <th>Expected Start Time</th>
                <th>Expected Repair Date</th>
                <th>Expected Repair Time</th>
                <th>Delivery Type</th>
                <th>Comments</th>
                <th>Job Repair Cost</th>
                <th>Part(s) Cost</th>
                <th>Profit</th>
                <th colspan='2'>Actions</th>
                </tr>";									
             
			 while ($row = $result->fetch_object())
            {
				// set up a row for each record
                echo "<tr>";
                echo "<td><a href='view-specific-repair.php?id=" . $row->id . "'>".$row->id . "</a></td>";
                echo "<td>" . $row->customer_name . "</td>";
                echo "<td>" . $row->customer_email . "</td>";
                echo "<td>" . $row->customer_phone . "</td>";
                echo "<td>" . $row->computer_make . "</td>";
                echo "<td>" . $row->computer_model . "</td>";
                echo "<td>" . $row->technician . "</td>";
                echo "<td>" . $row->status . "</td>";
                echo "<td>" . $row->exstdate . "</td>";
                echo "<td>" . $row->exstime . "</td>";
                echo "<td>" . $row->exrdate . "</td>";
                echo "<td>" . $row->exrtime . "</td>";
                echo "<td>" . $row->deltype . "</td>";
                echo "<td>" . substr($row->comments, 0, 25) . "</td>";
                echo "<td>" . '£' . $row->job_cost . "</td>";
                echo "<td>" . '£' . $row->part_cost . "</td>";
                echo "<td>" . '£' . $row->profit . "</td>";
                echo "<td><a href='repairs-tracking.php?id=" . $row->id . "'>Edit</a></td>";
                echo "<td><a href='delete-repair.php?id=" . $row->id . "'>Delete</a></td>";
                echo "</tr>";
            }
			
			echo "</table>";
        }
        // if there are no records in the database, display an alert message
        else
        {
            echo "No results to display!";
        }
    }

// show an error if there is an issue with the database query
else
{
    echo "Error: " . $mysqli->error;
}
                                        
$query = "select * from repairs";
$result = mysqli_query($mysqli, $query);

// Count the total records
$total_records = mysqli_num_rows($result);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);

//Going to first page
echo "<center><a href='view-repairs-tracking.php?page=1'>".'First Page'."</a> ";

for ($i=1; $i<=$total_pages; $i++) {

    echo "<a href='view-repairs-tracking.php?page=".$i."'>".$i."</a> ";
};
// Going to last page
echo "<a href='view-repairs-tracking.php?page=$total_pages'>".'Last Page'."</a></center> ";

// close database connection
$mysqli->close();
}
?>

Thank you in advance

 

Ian

Share this post


Link to post
Share on other sites

The problem is in your SQL ... well that is most likely. 

 

I would suggest to construct an SQL string, and just try in a console until you get it working. Sometimes just writing an SQL statement directly makes it much easier to debug your SQL. 

 

Stefn

Share this post


Link to post
Share on other sites

Hi

 

I need some help or advice, I want to upload and download a file along with adding other data like name and email using the form but am not sure how to do it if someone can help me please

 

Thank you in advance

 

Ian

Share this post


Link to post
Share on other sites

Hi, 

 

Looks like a typo ... basically that error implies you are not matching the resultset field count with the prepared statement.

 

... Is my sentence too nerd?

 

:huh:

 

So review your code. 

Stef

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×