Jump to content


Photo

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


  • Please log in to reply
79 replies to this topic

#61 Ben

Ben

    Administrator

  • Administrators
  • 5,682 posts
  • LocationGainesville, Florida

Posted 27 October 2014 - 07:57 AM

Do you have the correct number of "?" in your query? That's the first thing I would check, and likely where you are going wrong.


  • 0
Benjamin Falk
Falken Creative : Twitter

#62 Dononius

Dononius

    New member

  • New Members
  • 1 posts

Posted 24 November 2014 - 09:27 PM

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?


  • 0

#63 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 06 February 2015 - 05:28 AM

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


  • 0

#64 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 06 February 2015 - 07:31 AM

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();
?>

  • 0

#65 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 09 February 2015 - 04:28 AM

Hi

 

Sorry just a update, got it working


  • 0

#66 scampbell70

scampbell70

    New member

  • Member
  • 10 posts

Posted 14 February 2015 - 05:05 PM

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, 14 February 2015 - 07:54 PM.

  • 0

#67 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 16 February 2015 - 08:42 AM

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


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#68 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 11 October 2015 - 06:50 AM

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


  • 0

#69 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 11 October 2015 - 08:42 AM

Sorry got it sorted, I forgot to add the id to the form tag


  • 0

#70 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 28 October 2015 - 10:38 AM

Yep. Most of the time, the problems are small little things.


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#71 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 04 November 2015 - 08:32 AM

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


  • 0

#72 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 04 November 2015 - 08:57 AM

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


  • 0

#73 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 23 November 2015 - 05:57 AM

Hi

 

Is this forum still active as have a quick question about this script


  • 0

#74 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 28 November 2015 - 08:18 PM

Forum activity is slow these days. Once the new courses come out, things will pick up again.

Stef


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#75 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 02 December 2015 - 12:59 PM

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


  • 0

#76 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 13 December 2015 - 11:27 AM

The error is probably in your SQL code. 

 

... If you can connect to the database. Have you been checking for errors in your code?

Stef


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#77 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 03 February 2016 - 05:08 PM

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>" . '&#163;' . $row->job_cost . "</td>";
                echo "<td>" . '&#163;' . $row->part_cost . "</td>";
                echo "<td>" . '&#163;' . $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


  • 0

#78 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 06 February 2016 - 10:55 PM

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


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#79 ianhaney

ianhaney

    Member

  • Member
  • PipPip
  • 20 posts

Posted 11 July 2016 - 09:52 AM

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


  • 0

#80 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,326 posts
  • LocationMontreal Canada

Posted 15 July 2016 - 10:53 AM

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


  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb




3 user(s) are reading this topic

0 members, 3 guests, 0 anonymous users

★★★★★ 5 Star Rated Web Developer Course - check it out now!