Jump to content

Displaying the dynamically added data to existing table

Senthilkumar
 Share
Go to solution Solved by Senthilkumar,

Recommended Posts

Senthilkumar Advanced Member

Senthilkumar

Posted
Posted

Dear team,

I have a table which is used to display the customers assigned to me.My table code is  

<form method="post" action="" id="myForm">
				<div class="row" style="width:100%;margin-bottom:10px; margin-left:2px">

					<div class="col-md-2 border rounded" style="padding: 5px; box-shadow: rgba(0, 0, 0, 0.4) 0px 2px 4px, rgba(0, 0, 0, 0.3) 0px 7px 13px -3px, rgba(0, 0, 0, 0.2) 0px -3px 0px inset; ">
						<label style="font-weight:bold">Month</label>
						<input type="month" id="Month" name="Month" onchange="viewData()" class="form-control" />
					</div>
					<div class="alert-warning col-md-9 hidde" id="toast">
						Note:
						<br />1) Visit Interval, Visit Plan, Type of Visit & Visit Date is Mantory. Without that the data will not submit.
						<br />2) You can choose No Due under Type of Visit if the Visit Interval is in Quarters and you don't have any Due for this month. In that case, the date field will become inactive.
						<br />3) You must choose Not Visited under Type of Visit if you have not contacted the Customer. The date field will become inactive.
					</div>
					<div class="col f-right">
						<button type="button" class="btn btn-info align-middle f-right" id="info"><i class="fa-solid fa-circle-info" style="font-size:larger;"></i></button>
					</div>
				</div>

				<script>
					$("#info").on("click", function () {
						$('#toast').show();
						setTimeout(function () {
							document.getElementById('toast').style.display = 'none';
						}, 10000); // 10000ms = 10s
					});
				</script>



				<div class="row tbl_fixed" style="margin-top: 20px; box-shadow: rgba(0, 0, 0, 0.25) 0px 0.0625em 0.0625em, rgba(0, 0, 0, 0.25) 0px 0.125em 0.5em, rgba(255, 255, 255, 0.1) 0px 0px 0px 1px inset; width: 100% ">

					<table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;">

						<thead class="bg-blue text-center" style="white-space:nowrap">
							<tr>
								<th style="width:5%">S.No</th>
								<th style="width:25%">Customer Name</th>
								<th style="width:15%">Customer Location</th>
								<th style="width:10%">Visit Interval <span class="text-danger" style="font-size:15px"> *</span></th>
								<th style="width:10%">Visit Plan <span class="text-danger" style="font-size:15px"> *</span></th>
								<th style="width:10%">Type of Visit <span class="text-danger" style="font-size:15px"> *</span></th>
								<th style="width:10%">Visit Date <span class="text-danger" style="font-size:15px"> *</span></th>
								<th style="width:15%">Remarks </th>
								<th style="width:15%">Remarks1 </th>
							</tr>
						</thead>
						<tbody>
							<?php

							$n = 1;
							$sql = "select * from machinemaster  AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'";
							$result = mysqli_query($conn, $sql);
							while ($row = mysqli_fetch_assoc($result)) {

								$RowID = $row['RowID'];
								$CustomerName = $row['CustomerName'];
								$CustomerCity = $row['CustomerCity'];
								$VisitInterval = $row['VisitInterval'];
								$VisitPlan = $row['VisitPlan'];
								$VisitType = $row['VisitType'];
								?>
								<tr>
									<td align='center'>
										<?php echo $n++; ?>
									</td>
									<td>
										<?php echo $CustomerName; ?>
									</td>
									<td>
										<?php echo $CustomerCity; ?>
									</td>
									<td hidden><input type="text" name="CustomerName[]" id="CustomerName<?php echo $RowID; ?>" class="form-control" /></td>
									<td hidden><input type="text" name="CustomerCity[]" id="CustomerCity<?php echo $RowID; ?>" class="form-control" /></td>
									<td align='center' hidden>
										<input type="number" name="RowID[]" id="RowID<?php echo $RowID; ?>" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" />
									</td>
									<?PHP if ($VisitInterval !== '') { ?>
										<td align='center'>
											<?php echo $VisitInterval; ?>
										</td>
										<td align='center' hidden>
											<input type="text" name="interval[]" id="interval<?php echo $RowID; ?>" value="<?php echo $VisitInterval; ?>" class="form-control border border-success" autocomplete="off" />
										</td>
									<?PHP } else { ?>
										<td>
											<select name='interval[]' id='interval<?php echo $RowID; ?>' class='form-control interval' style="width:auto">
												<option value=''>Choose</option>
												<option value='Monthly'>Monthly</option>
												<option value='Bymonth'>Bi-Monthly</option>
												<option value='Quarterly'>Quarterly</option>
											</select>
										</td>
									<?PHP } ?>

									<td>
										<?PHP if ($VisitInterval == 'Monthly') { ?>
											<select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto">
												<option value=''>Select Interval</option>
												<option value='1st Week'>1st Week</option>;
												<option value='2nd Week'>2nd Week</option>;
												<option value='3rd Week'>3rd Week</option>;
												<option value='4th Week'>4th Week</option>;
											</select>
										<?PHP } elseif ($VisitInterval == 'Bymonth') { ?>
											<select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto">
												<option value=''>Select Interval</option>
												<option value='1st Month'>1st Month</option>;
												<option value='2nd Month'>2nd Month</option>;
											</select>
											?PHP } elseif ($VisitInterval == 'Quarterly') { ?>
											<select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto">
												<option value=''>Select Interval</option>
												<option value='1st Month'>1st Month</option>;
												<option value='2nd Month'>2nd Month</option>;
												<option value='3rd Month'>3rd Month</option>;
											</select>
										<?PHP } else { ?>
											<select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto">
												<option value=''>Select Interval</option>
												<option value='1st Week'>1st Week</option>;
												<option value='2nd Week'>2nd Week</option>;
												<option value='3rd Week'>3rd Week</option>;
												<option value='4th Week'>4th Week</option>;
												<option value='1st Month'>1st Month</option>;
												<option value='2nd Month'>2nd Month</option>;
												<option value='3rd Month'>3rd Month</option>;
											</select>
										<?PHP } ?>
									</td>

									<td>
										<select name='type[]' id='type<?php echo $RowID; ?>' onchange="getSelectedValue(<?php echo $RowID; ?>, this.value)" class='form-control form-select type' style="width:auto">
											<option value=''>Choose</option>
											<option value='Direct'>Direct</option>
											<option value='Telephone'>Telephone</option>
											<option value='Not Visited'>Not Visited</option>
											<option value='No Due'>No Due</option>
										</select>
									</td>
									<td align='center'>
										<input type="date" name="SubmitedDate[]" id="SubmitedDate<?php echo $RowID; ?>" value="" class="form-control border border-success SubmitedDate" autocomplete="off" required/ />
									</td>
									<td align='center'>
										<input type="text" name="Remarks[]" id="Remarks<?php echo $RowID; ?>" value="" class="form-control border border-success Remarks" autocomplete="off" />
									</td>
									<td>
										<input type="number" name="lang[]" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" />
									</td>

								</tr>
								<?php
							}

							?>

						</tbody>
					</table>
				</div>


				<div class="col-sm-12 text-center" style="margin-top:10px;margin-bottom:10px" id="button">
					<button type="button" id="addRow" class="btn btn-sm btn-primary btn-md center-block fw-bold" style="width: 100px;">Add Row</button>
					<button type="button" id="Save" name="Save" value="Save" class="btn btn-sm btn-secondary btn-md center-block fw-bold" style="width: 100px;">Save</button>
					<button type="button" id="Submit" name="Submit" value="Submit" class="btn btn-sm btn-success btn-md center-block fw-bold" style="width: 100px">Submit</button>
				</div>
</form>

In this table, I will update the Visit Interval, Visit Plan, Type of Visit & Visit Date & Remarks field and I will save the data as soon as entered and I will submit the data finally once all the fields are entered.

For save and submit I will use the below code 

				<?php

				if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == "Save") {

					$Month = $_POST['Month'];

					if ($EmpNo == '') {
						echo "<script>
								alert('The time for your session has ended. You'll return to the login page.');
								window.location.href='../logout-user.php';
								</script>";
					} else {

						if (!empty($_POST['lang'])) {
							foreach ($_POST['lang'] as $ID => $VAL) {
								$customerName = mysqli_real_escape_string($conn, $_POST['CustomerName'][$ID]);
								$customerCity = mysqli_real_escape_string($conn, $_POST['CustomerCity'][$ID]);
								$interval = mysqli_real_escape_string($conn, $_POST['interval'][$ID]);
								$plan = mysqli_real_escape_string($conn, $_POST['plan'][$ID]);
								$type = mysqli_real_escape_string($conn, $_POST['type'][$ID]);
								$SubmitedDate = mysqli_real_escape_string($conn, $_POST['SubmitedDate'][$ID]);
								$RowID = mysqli_real_escape_string($conn, $VAL); // Use VAL as RowID
				
								// Insert or update the record
								$insertquery = "INSERT INTO sbms.customerdata (Month, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, RowID, EmpID, Branch, Status)
                                VALUES('$Month', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$SubmitedDate', '$RowID', '$EmpNo','$Branch', '0')
                                ON DUPLICATE KEY UPDATE
                                    CustomerName = VALUES(CustomerName),
                                    CustomerCity = VALUES(CustomerCity),
                                    VisitInterval = VALUES(VisitInterval),
                                    VisitPlan = VALUES(VisitPlan),
                                    VisitType = VALUES(VisitType),
                                    VisitDate = VALUES(VisitDate),
                                    Status = VALUES(Status)";
								$result = mysqli_query($conn, $insertquery);
							}

							if ($result) {
								echo "<script>
								alert('Data Saved Sucessfully');
								window.location.href='Visit_Data.php';
								</script>";
							} else {
								echo "<script>
								alert('Data Not Saved. Please check the details entered');
								</script>";
							}
						}
					}
				}
				?>

				<?php

				if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == "Submit") {

					$Month = $_POST['Month'];

					if ($EmpNo == '') {
						echo "<script>
								alert('The time for your session has ended. You'll return to the login page.');
								window.location.href='../logout-user.php';
								</script>";
					} else {


						if (!empty($_POST['lang'])) {
							foreach ($_POST['lang'] as $ID => $VAL) {
								$interval = $_POST['interval'][$ID];
								$plan = $_POST['plan'][$ID];
								$type = $_POST['type'][$ID];
								$SubmitedDate = $_POST['SubmitedDate'][$ID];
								$Remarks = $_POST['Remarks'][$ID];
								$RowID = $_POST['RowID'][$ID];

								$insertquery = "INSERT INTO sbms.customerdata (Month, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, RowID, EmpID, Branch, Status)
                                VALUES('$Month', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$SubmitedDate', '$RowID', '$EmpNo','$Branch', '0')
                                ON DUPLICATE KEY UPDATE
                                    CustomerName = VALUES(CustomerName),
                                    CustomerCity = VALUES(CustomerCity),
                                    VisitInterval = VALUES(VisitInterval),
                                    VisitPlan = VALUES(VisitPlan),
                                    VisitType = VALUES(VisitType),
                                    VisitDate = VALUES(VisitDate),
                                    Status = VALUES(Status)";
								$result = mysqli_query($conn, $insertquery);

							}
							if ($result) {
								$CurrentTime = date('Y/m/d h:i:s', time());
								$Submit = "INSERT INTO submitteddata (EmpID, Month, Branch, Production, Production_Submitted, Production_App1, Production_App1_Submitted, Production_App2, Production_App2_Submitted, Production_Remarks, Marketingpromotion, Mrkt_Submitted, Mrkt_App1, Mrkt_App1_Submitted, Mrkt_App2, Mrkt_App2_Submitted, Mrkt_Remarks, CustVisit, CustVisit_Submitted, CustVisit_APP1, CustVisit_APP1_Submitted, CustVisit_App2, CustVisit_APP2_Submitted, CustVisit_Remarks, status)
											VALUES('$EmpNo', '$Month', '$Branch', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '$CurrentTime', '$FirstapproverID', '', '', '', '', '1')
											ON DUPLICATE KEY UPDATE CustVisit='1', CustVisit_Submitted='$CurrentTime', CustVisit_APP1='$FirstapproverID'";
								$result = mysqli_query($conn, $Submit);

								$monthformat = date("M-Y", strtotime($Month));



								echo "<script>
								alert('Data Submitted Sucessfully');
								window.location.href='Visit_Data.php';
								</script>";
							} else {
								echo "<script>
								alert('Data Not Saved. Please check the details entered');
								</script>";
							}
						}
					}
				}
				?>

My requirement is I want to save the customer's details which is visited additionally apart from this list. For that I want to add extra column and enter the values and save. I am using the below script for add the additional rows 

				<script>
					$(document).ready(function() {
						var n = <?php echo isset($n) ? $n : 1; ?>; // Counter for rows

						$("#addRow").click(function() {
							var newRow = `<tr>
								<td align='center'>${n++}</td>
								<td><input type="text" name="CustomerName[]" class="form-control" /></td>
								<td><input type="text" name="CustomerCity[]" class="form-control" /></td>
								<td>
									<select name="interval[]" class="form-control form-select">
										<option value="">Choose</option>
										<option value="Monthly">Monthly</option>
										<option value="Quarterly">Quarterly</option>
									</select>
								</td>
								<td>
									<select name="plan[]" class="form-control form-select">
										<option value="">Select Interval</option>
										<option value="1st Week">1st Week</option>
										<option value="2nd Week">2nd Week</option>
										<option value="3rd Week">3rd Week</option>
										<option value="4th Week">4th Week</option>
										<option value="1st Month">1st Month</option>
										<option value="2nd Month">2nd Month</option>
										<option value="3rd Month">3rd Month</option>
									</select>
								</td>
								<td>
									<select name="type[]" class="form-control form-select">
										<option value="">Choose</option>
										<option value="Direct">Direct</option>
										<option value="Telephone">Telephone</option>
									</select>
								</td>
								<td align='center'>
									<input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" />
								</td>
								<td align='center'>
									<input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" />
								</td>
								<td >
									<input type="number" name="lang[]" value="${n}" class="form-control border border-success" autocomplete="off" />
								</td>
							</tr>`;
							$("#product_data tbody").append(newRow);
						});
					});
				</script>

This script is adding additional rows and I am able to fill the data I visited additionally apart from the list.

Once I save the data I want to retrieve and display the data on the same table for that particular month. For that, I am using the bellow ajax script 

<script>

				function viewData() {
					var Month = document.getElementById("Month").value;
					var ID = '<?php echo $EmpNo; ?>';

					$.ajax({
						type: 'get',
						dataType: 'JSON',
						url: 'Customer_Data_View.php',
						data: 'Month=' + Month + '&employeeno=' + ID,
						success: function (response) {

							if (!$.trim(response)) {
								$('.SubmitedDate').val('')

							} else {

								$('.SubmitedDate').val('')

								var len = response.length;

								for (var i = 0; i < len; i++) {

									var id = response[i].id;
									var CustomerName = response[i].CustomerName;
									var CustomerCity = response[i].CustomerCity;
									var VisitInterval = response[i].VisitInterval;
									var VisitPlan = response[i].VisitPlan;
									var VisitType = response[i].VisitType;
									var VisitDate = response[i].VisitDate;
									var Remarks = response[i].Remarks;

									document.getElementById('CustomerName' + id).value = CustomerName;
									document.getElementById('CustomerCity' + id).value = CustomerCity;
									document.getElementById('interval' + id).value = VisitInterval;
									document.getElementById('plan' + id).value = VisitPlan;
									document.getElementById('type' + id).value = VisitType;
									document.getElementById('SubmitedDate' + id).value = VisitDate;
									document.getElementById('Remarks' + id).value = Remarks;

									if (VisitType == 'Not Visited' || VisitType == 'No Due') {
										document.getElementById("SubmitedDate" + id).readOnly = true;
									} else {
										document.getElementById("SubmitedDate" + id).readOnly = false;
									}
								}

							}
						}
					});


					$.ajax({
						type: 'get',
						dataType: 'JSON',
						url: 'Customer_Data_Button.php',
						data: 'Month=' + Month + '&employeeno=' + ID,
						success: function (response) {
							if (response['success']) {
								$('#button').hide();
							} else {
								$('#button').show();
							}

						}
					});
				};

</script>

Once I submit the data, I cannot modify it further because the buttons will get hidden automatically.

My Customer_Data_View.php is  

<?PHP

session_start();
error_reporting(0);
ini_set('display_errors', 'Off');

include '../connection.php';
if (isset($_GET['Month'])) {
	$Month = $_GET['Month'];
	$Employeeid = $_GET['employeeno'];

	// Check if there is any data for the given month and employee
	$check = "SELECT * FROM customerdata WHERE EmpID = '$Employeeid' AND Month ='$Month'";
	$result = mysqli_query($conn, $check);

	if (mysqli_num_rows($result) == 0) {
		// If no data, get data from the machinemaster and customerassign tables
		$query = "SELECT c.* FROM sbms.machinemaster AS a
                    INNER JOIN sbms.customerassign AS c ON c.RowID = a.id
                    WHERE c.EmpID = '$Employeeid' AND a.MachineStatus='A'";
		$result = mysqli_query($conn, $query);

		while ($row = mysqli_fetch_array($result)) {
			$VisitInterval = $row['VisitInterval'];
			$VisitPlan = $row['VisitPlan'];
			$VisitType = $row['VisitType'];
			$id = $row['RowID'];
			$VisitDate = "";
			$Remarks = "";
			$CustomerName = "";
			$CustomerCity = "";

			$return_arr[] = array(
				"VisitInterval" => $VisitInterval,
				"VisitPlan" => $VisitPlan,
				"VisitType" => $VisitType,
				"id" => $id,
				"VisitDate" => $VisitDate,
				"Remarks" => $Remarks,
				"CustomerName" => $CustomerName,
				"CustomerCity" => $CustomerCity

			);
		}
	} else {
		// If data exists for the month, retrieve it
		while ($row = mysqli_fetch_assoc($result)) {
			$return_arr[] = $row;
		}
	}

	// Return the data as JSON
	echo json_encode($return_arr);
}


?>

When I am fetching the data it is displaying only the data available in the table. it is not display the data which is dynamically added. 

 

Can anyone please suggest to me how to display the dynamically added rows of data in the table along with the existing data

mac_gyver Prolific Member

mac_gyver

Posted
Posted

the most immediate problem is you are mixing a normal get request for a page, for a Create operation, with an ajax request for existing data, for an Update operation. the number of form fields being displayed is determined by the query for the machinemaster/customerassign data. when you are Updating existing data, the number of form fields must match the existing data. you also have a secondary problem related to this in that if there is a validation error for the submitted form data, which can include dynamically added fields for the Create operation, you need to repopulate the correct number of form fields with the submitted form data, not the initial data.

so, two things - don't use ajax for this, and organize the code for the page in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. html document

you would put the code to query for the initial data, either for the machinemaster/customerassign data (Create operation) or the customerdata data (Update operation) in item #3 on this list, and only execute it if the form has never been submitted. the way to accomplish this logic is to use a php array variable to hold a trimmed working copy of the form data. you would assign this variable a trimmed copy of the $_POST data inside the post method form processing code, then use elements in this array variable throughout the rest of the code. for the item #3 code, if this php array variable is empty (assumes there is at least one correctly coded field in the form), you would query to get the initial data and fetch it into this array variable, that then gets used throughout the rest of the code.

Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted

I had modified the code. Now the dynamic data are saving the database and displaying the table while retrieving it. The modified code is  

<?PHP

include("../connection.php");
ini_set('memory_limit', '-1');
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);
session_start();
date_default_timezone_set("Asia/Kolkata");

$id = $_SESSION['id'];
if ($id == '') {
	session_destroy();
	header("location:../logout-user.php"); //redirect to index.php
}


$EmpNo = $_SESSION['EmpNo'];
$Email = $_SESSION['email'];
$Name = $_SESSION['Name'];
$Branch = $_SESSION['Branch'];
$Department = $_SESSION['Department'];

$mailselect = "SELECT Fname, Email, EmpNo FROM sbms.usertable WHERE Branch = '$Branch' AND Role='4' AND Department='$Department'";
$mailresult = mysqli_query($conn, $mailselect);
$mailrow = mysqli_fetch_assoc($mailresult);
//$Firstapprovermail = $mailrow['Email'];
//$Firstapprovername = $mailrow['Fname'];
//$FirstapproverID = $mailrow['EmpNo'];
?>


<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0" />
	<title>Customer Visit Data</title>
	<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" />
	<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
</head>
<body>

	<form method="post" action="" id="myForm">
		<div class="row" style="width:100%;margin-bottom:10px; margin-left:2px">
			<div class="col-md-2 border rounded" style="padding: 5px; box-shadow: rgba(0, 0, 0, 0.4) 0px 2px 4px, rgba(0, 0, 0, 0.3) 0px 7px 13px -3px, rgba(0, 0, 0, 0.2) 0px -3px 0px inset; ">
				<label style="font-weight:bold">Month</label>
				<input type="month" id="Month" name="Month" onchange="viewData()" class="form-control" />
			</div>
			<div class="alert-warning col-md-9 hidde" id="toast">
				Note:
				<br />1) Visit Interval, Visit Plan, Type of Visit & Visit Date is Mandatory. Without that the data will not submit.
				<br />2) You can choose No Due under Type of Visit if the Visit Interval is in Quarters and you don't have any Due for this month. In that case, the date field will become inactive.
				<br />3) You must choose Not Visited under Type of Visit if you have not contacted the Customer. The date field will become inactive.
			</div>
			<div class="col f-right">
				<button type="button" class="btn btn-info align-middle f-right" id="info"><i class="fa-solid fa-circle-info" style="font-size:larger;"></i></button>
			</div>
		</div>

		<script>
			$("#info").on("click", function () {
				$('#toast').show();
				setTimeout(function () {
					document.getElementById('toast').style.display = 'none';
				}, 10000); // 10000ms = 10s
			});
		</script>

		<div class="row tbl_fixed" style="margin-top: 20px; box-shadow: rgba(0, 0, 0, 0.25) 0px 0.0625em 0.0625em, rgba(0, 0, 0, 0.25) 0px 0.125em 0.5em, rgba(255, 255, 255, 0.1) 0px 0px 0px 1px inset; width: 100% ">

			<table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;">
				<thead class="bg-blue text-center" style="white-space:nowrap">
					<tr>
						<th style="width:5%">S.No</th>
						<th style="width:25%">Customer Name</th>
						<th style="width:15%">Customer Location</th>
						<th style="width:10%">Visit Interval <span class="text-danger" style="font-size:15px"> *</span></th>
						<th style="width:10%">Visit Plan <span class="text-danger" style="font-size:15px"> *</span></th>
						<th style="width:10%">Type of Visit <span class="text-danger" style="font-size:15px"> *</span></th>
						<th style="width:10%">Visit Date <span class="text-danger" style="font-size:15px"> *</span></th>
						<th style="width:15%">Remarks </th>
						<th style="width:15%">Remarks1 </th>
					</tr>
				</thead>
				<tbody>
					<?php
					$n = 1; // Default to 1 if no rows are found					


					$sql = "SELECT * FROM machinemaster AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'";
					$result = mysqli_query($conn, $sql);
					while ($row = mysqli_fetch_assoc($result)) {
						$RowID = $row['RowID'];
						$CustomerName = $row['CustomerName'];
						$CustomerCity = $row['CustomerCity'];
						$VisitInterval = $row['VisitInterval'];
						$VisitPlan = $row['VisitPlan'];
						$VisitType = $row['VisitType'];
						?>
						<tr>
							<td align='center'><?php echo $n++; ?></td>
							<td><?php echo $CustomerName; ?></td>
							<td><?php echo $CustomerCity; ?></td>
							<td hidden><input type="text" name="CustomerName[]" id="CustomerName<?php echo $RowID; ?>" class="form-control" value="<?php echo $CustomerName; ?>" /></td>
							<td hidden><input type="text" name="CustomerCity[]" id="CustomerCity<?php echo $RowID; ?>" class="form-control" value="<?php echo $CustomerCity; ?>" /></td>
							<td align='center'>
								<select name='interval[]' id='interval<?php echo $RowID; ?>' class='form-control interval' style="width:auto">
									<option value=''>Choose</option>
									<option value='Monthly'
										<?php if ($VisitInterval == 'Monthly')
											echo 'selected'; ?>>Monthly</option>
									<option value='Bymonth'
										<?php if ($VisitInterval == 'Bymonth')
											echo 'selected'; ?>>Bi-Monthly</option>
									<option value='Quarterly'
										<?php if ($VisitInterval == 'Quarterly')
											echo 'selected'; ?>>Quarterly</option>
								</select>
							</td>
							<td>
								<select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto">
									<option value=''>Select Interval</option>
									<option value='1st Week'
										<?php if ($VisitPlan == '1st Week')
											echo 'selected'; ?>>1st Week</option>
									<option value='2nd Week'
										<?php if ($VisitPlan == '2nd Week')
											echo 'selected'; ?>>2nd Week</option>
									<option value='3rd Week'
										<?php if ($VisitPlan == '3rd Week')
											echo 'selected'; ?>>3rd Week</option>
									<option value='4th Week'
										<?php if ($VisitPlan == '4th Week')
											echo 'selected'; ?>>4th Week</option>
									<option value='1st Month'
										<?php if ($VisitPlan == '1st Month')
											echo 'selected'; ?>>1st Month</option>
									<option value='2nd Month'
										<?php if ($VisitPlan == '2nd Month')
											echo 'selected'; ?>>2nd Month</option>
									<option value='3rd Month'
										<?php if ($VisitPlan == '3rd Month')
											echo 'selected'; ?>>3rd Month</option>
								</select>
							</td>
							<td>
								<select name='type[]' id='type<?php echo $RowID; ?>' class='form-control form-select type' style="width:auto" onchange="toggleDateField(<?php echo $RowID; ?>)">
									<option value=''>Choose</option>
									<option value='Direct'
										<?php if ($VisitType == 'Direct')
											echo 'selected'; ?>>Direct</option>
									<option value='Telephone'
										<?php if ($VisitType == 'Telephone')
											echo 'selected'; ?>>Telephone</option>
									<option value='Not Visited'
										<?php if ($VisitType == 'Not Visited')
											echo 'selected'; ?>>Not Visited</option>
									<option value='No Due'
										<?php if ($VisitType == 'No Due')
											echo 'selected'; ?>>No Due</option>
								</select>
							</td>
							<td align='center'>
								<input type="date" name="SubmitedDate[]" id="SubmitedDate<?php echo $RowID; ?>" value="" class="form-control border border-success SubmitedDate" autocomplete="off" required />
							</td>
							<td align='center'>
								<input type="text" name="Remarks[]" id="Remarks<?php echo $RowID; ?>" value="" class="form-control border border-success Remarks" autocomplete="off" />
							</td>
							<td>
								<input type="text" name="lang[]" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" />
							</td>
						</tr>
						<?php
					}
					?>
				</tbody>
			</table>
		</div>

		<div class="col-sm-12 text-center" style="margin-top:10px;margin-bottom:10px" id="button">
			<button type="button" id="addRow" class="btn btn-sm btn-primary btn-md center-block fw-bold border"><i class="fa-solid fa-plus"></i> Add New Row</button>
			<button type="button" id="save" name="save" class="btn btn-success btn-md center-block fw-bold border" onclick="saveData()">Save</button>
		</div>
	</form>


	<script>
		$(document).ready(function () {

			var n = <?php echo $n; ?>; // Counter for rows

			$("#addRow").click(function () {
				var newRow = `<tr>
								<td align='center'>${n}</td>
								<td><input type="text" name="CustomerName[]" class="form-control" /></td>
								<td><input type="text" name="CustomerCity[]" class="form-control" /></td>
								<td>
									<select name="interval[]" class="form-control form-select">
										<option value="">Choose</option>
										<option value="Monthly">Monthly</option>
										<option value="Quarterly">Quarterly</option>
									</select>
								</td>
								<td>
									<select name="plan[]" class="form-control form-select">
										<option value="">Select Interval</option>
										<option value="1st Week">1st Week</option>
										<option value="2nd Week">2nd Week</option>
										<option value="3rd Week">3rd Week</option>
										<option value="4th Week">4th Week</option>
										<option value="1st Month">1st Month</option>
										<option value="2nd Month">2nd Month</option>
										<option value="3rd Month">3rd Month</option>
									</select>
								</td>
								<td>
									<select name="type[]" class="form-control form-select" onchange="toggleDateField('new_${n}')">
										<option value="">Choose</option>
										<option value="Direct">Direct</option>
										<option value="Telephone">Telephone</option>
										<option value="Not Visited">Not Visited</option>
										<option value="No Due">No Due</option>
									</select>
								</td>
								<td align='center'>
									<input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" />
								</td>
								<td align='center'>
									<input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" />
								</td>
								<td>
									<input type="text" name="lang[]" value="new_${n++}" class="form-control border border-success" autocomplete="off" />
								</td>
							</tr>`;
				$("#product_data tbody").append(newRow);
			});
		});

		function toggleDateField(rowId) {
			var visitType = $('#type' + rowId).val();
			if (visitType === 'Not Visited' || visitType === 'No Due') {
				$('#SubmitedDate' + rowId).prop('disabled', true);
			} else {
				$('#SubmitedDate' + rowId).prop('disabled', false);
			}
		}

		function viewData() {
			var month = $('#Month').val();
			var employeeno = '<?php echo $EmpNo; ?>'; // Adjust this to retrieve the correct employee number

			$.ajax({
				url: 'Customer_Data_View.php',
				type: 'GET',
				data: { Month: month, employeeno: employeeno },
				dataType: 'json',
				success: function (response) {
					var tbody = $('#product_data tbody');
					tbody.empty(); // Clear existing rows
					var len = response.length;
					var n = 0;
					$.each(response, function (index, data) {
						var newRow = `<tr>
										<td align='center'>${n + 1}</td>
										<td >${data.CustomerName}</td>
										<td >${data.CustomerCity}</td>
										<td hidden><input type="text" name="CustomerName[]" value="${data.CustomerName}" class="form-control" /></td>
										<td hidden><input type="text" name="CustomerCity[]" value="${data.CustomerCity}" class="form-control" /></td>
										<td>
											<select name="interval[]" class="form-control form-select">
												<option value="">Choose</option>
												<option value="Monthly" ${data.VisitInterval === 'Monthly' ? 'selected' : ''}>Monthly</option>
												<option value="Quarterly" ${data.VisitInterval === 'Quarterly' ? 'selected' : ''}>Quarterly</option>
											</select>
										</td>
										<td>
											<select name="plan[]" class="form-control form-select">
												<option value="">Select Interval</option>
												<option value="1st Week" ${data.VisitPlan === '1st Week' ? 'selected' : ''}>1st Week</option>
												<option value="2nd Week" ${data.VisitPlan === '2nd Week' ? 'selected' : ''}>2nd Week</option>
												<option value="3rd Week" ${data.VisitPlan === '3rd Week' ? 'selected' : ''}>3rd Week</option>
												<option value="4th Week" ${data.VisitPlan === '4th Week' ? 'selected' : ''}>4th Week</option>
												<option value="1st Month" ${data.VisitPlan === '1st Month' ? 'selected' : ''}>1st Month</option>
												<option value="2nd Month" ${data.VisitPlan === '2nd Month' ? 'selected' : ''}>2nd Month</option>
												<option value="3rd Month" ${data.VisitPlan === '3rd Month' ? 'selected' : ''}>3rd Month</option>
											</select>
										</td>
										<td>
											<select name="type[]" class="form-control form-select" onchange="toggleDateField('${data.id}')">
												<option value="">Choose</option>
												<option value="Direct" ${data.VisitType === 'Direct' ? 'selected' : ''}>Direct</option>
												<option value="Telephone" ${data.VisitType === 'Telephone' ? 'selected' : ''}>Telephone</option>
												<option value="Not Visited" ${data.VisitType === 'Not Visited' ? 'selected' : ''}>Not Visited</option>
												<option value="No Due" ${data.VisitType === 'No Due' ? 'selected' : ''}>No Due</option>
											</select>
										</td>
										<td align='center'>
											<input type="date" name="SubmitedDate[]" value="${data.VisitDate}" class="form-control border border-success SubmitedDate" autocomplete="off" ${data.VisitType === 'Not Visited' || data.VisitType === 'No Due' ? 'disabled' : ''} />
										</td>
										<td align='center'>
											<input type="text" name="Remarks[]" value="${data.Remarks}" class="form-control border border-success Remarks" autocomplete="off" />
										</td>
										<td>
											<input type="text" name="lang[]" value="${data.RowID}" class="form-control border border-success" autocomplete="off" />
										</td>
									</tr>`;
						tbody.append(newRow);
						n++;
					});
				},
				error: function (xhr, status, error) {
					console.error(xhr.responseText);
				}
			});
		}

		function saveData() {
			var formData = $('#myForm').serialize();

			$.ajax({
				url: 'save_data.php',
				type: 'POST',
				data: formData,
				success: function (response) {
					alert('Data saved successfully!');
				},
				error: function (xhr, status, error) {
					console.error(xhr.responseText);
					alert('Failed to save data.');
				}
			});
		}
	</script>



</body>
</html>

save_data.php code is 

<?php

include("../connection.php");
ini_set('memory_limit', '-1');
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);
session_start();
date_default_timezone_set("Asia/Kolkata");

$id = $_SESSION['id'];
if ($id == '') {
	session_destroy();
	header("location:../logout-user.php"); //redirect to index.php
}


$EmpNo = $_SESSION['EmpNo'];
$Email = $_SESSION['email'];
$Name = $_SESSION['Name'];
$Branch = $_SESSION['Branch'];
$Department = $_SESSION['Department'];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
	$month = $_POST['Month'];
	$customerNames = $_POST['CustomerName'];
	$customerCities = $_POST['CustomerCity'];
	$intervals = $_POST['interval'];
	$plans = $_POST['plan'];
	$types = $_POST['type'];
	$dates = $_POST['SubmitedDate'];
	$remarks = $_POST['Remarks'];
	$ids = $_POST['lang'];

	$Employeeid = $_GET['employeeno'];

	foreach ($ids as $index => $id) {
		$customerName = $customerNames[$index];
		$customerCity = $customerCities[$index];
		$interval = $intervals[$index];
		$plan = $plans[$index];
		$type = $types[$index];
		$date = $dates[$index];
		$remark = $remarks[$index];
		$RowID = $ids[$index];

		if ($type == 'Not Visited' || $type == 'No Due') {
			$date = null; // No date for 'Not Visited' and 'No Due'
		}

	

		$sql = "INSERT INTO customerdata (EmpID, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, Remarks, Month, Branch, RowID, Status)
				VALUES ('$EmpNo', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$date', '$remark', '$month', '$Branch', '$RowID', '0')
				ON DUPLICATE KEY UPDATE
				CustomerName = VALUES(CustomerName),
				CustomerCity = VALUES(CustomerCity),
				VisitInterval = VALUES(VisitInterval),
				VisitPlan = VALUES(VisitPlan),
				VisitType = VALUES(VisitType),
				VisitDate = VALUES(VisitDate),
				Remarks = VALUES(Remarks)";

		if (!mysqli_query($conn, $sql)) {
			echo "Error: " . mysqli_error($conn);
		}
	}
}
?>

Customer_Data_View.php is 

<?php

session_start();
error_reporting(0);
ini_set('display_errors', 'Off');

include '../connection.php';
if (isset($_GET['Month'])) {
	$Month = $_GET['Month'];
	$Employeeid = $_GET['employeeno'];

	// Retrieve all data for the given month and employee, including newly added rows
	$query = "SELECT * FROM customerdata WHERE EmpID = '$Employeeid' AND Month ='$Month'";
	$result = mysqli_query($conn, $query);

	$return_arr = [];
	while ($row = mysqli_fetch_assoc($result)) {
		$return_arr[] = $row;
	}

	// If no data, retrieve from default tables
	if (empty($return_arr)) {
		$query = "SELECT * FROM sbms.machinemaster AS a
                  INNER JOIN sbms.customerassign AS c ON c.RowID = a.id
                  WHERE c.EmpID = '$Employeeid' AND a.MachineStatus='A'";
		$result = mysqli_query($conn, $query);

		while ($row = mysqli_fetch_array($result)) {
			$VisitInterval = $row['VisitInterval'];
			$VisitPlan = $row['VisitPlan'];
			$VisitType = $row['VisitType'];
			$RowID = $row['RowID'];
			$VisitDate = "";
			$Remarks = "";
			$CustomerName = $row['CustomerName'];
			$CustomerCity = $row['CustomerCity'];

			$return_arr[] = array(
				"VisitInterval" => $VisitInterval,
				"VisitPlan" => $VisitPlan,
				"VisitType" => $VisitType,
				"RowID" => $RowID,
				"VisitDate" => $VisitDate,
				"Remarks" => $Remarks,
				"CustomerName" => $CustomerName,
				"CustomerCity" => $CustomerCity
			);
		}
	}

	echo json_encode($return_arr);
}
?>

In this code I am facing only one problem is the s.no incremental is not working properly.

For example initially when the page is loading, the $n will be fetched from  

<?php
$n = 1; // Default to 1 if no rows are found					


$sql = "SELECT * FROM machinemaster AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {

For example, the total row is 4. After that, I added 3 rows dynamically. The s.no starts from 5,6,7. So the total saved row is 7. Now, when fetching the data from the saved table, this table displays 7 rows. Now when I click the add row button this s.no again starts from 5 only. Not 8. I don't know where is the mistake . Any one please suggest me how to start the s.no from 8.

  • Solution
Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
  • Solution
Posted

Dear Mr. Mac_gyver,

thanks for your reply. I changed the code as per your suggestion. Count the number of rows in the table body. Now it is working properly 

	<script>
		$(document).ready(function () {

			const tbody = document.querySelector('#product_data tbody');

			// Get all the tr elements within the tbody
			const rows = tbody.getElementsByTagName('tr');

			// Get the count of tr elements
			const rowCount = rows.length;



			$("#addRow").click(function () {

				const tbody = document.querySelector('#product_data tbody');

				// Get all the tr elements within the tbody
				const rows = tbody.getElementsByTagName('tr');

				// Get the count of tr elements
				const rowCount = rows.length;

				var n = rowCount + 1; // Counter for rows
				var newRow = `<tr>
										<td align='center'>${n}</td>
										<td><input type="text" name="CustomerName[]" class="form-control" /></td>
										<td><input type="text" name="CustomerCity[]" class="form-control" /></td>
										<td>
											<select name="interval[]" class="form-control form-select">
												<option value="">Choose</option>
												<option value="Monthly">Monthly</option>
												<option value="Quarterly">Quarterly</option>
											</select>
										</td>
										<td>
											<select name="plan[]" class="form-control form-select">
												<option value="">Select Interval</option>
												<option value="1st Week">1st Week</option>
												<option value="2nd Week">2nd Week</option>
												<option value="3rd Week">3rd Week</option>
												<option value="4th Week">4th Week</option>
												<option value="1st Month">1st Month</option>
												<option value="2nd Month">2nd Month</option>
												<option value="3rd Month">3rd Month</option>
											</select>
										</td>
										<td>
											<select name="type[]" class="form-control form-select" onchange="toggleDateField('new_${n}')">
												<option value="">Choose</option>
												<option value="Direct">Direct</option>
												<option value="Telephone">Telephone</option>
												<option value="Not Visited">Not Visited</option>
												<option value="No Due">No Due</option>
											</select>
										</td>
										<td align='center'>
											<input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" />
										</td>
										<td align='center'>
											<input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" />
										</td>
										<td>
											<input type="text" name="lang[]" value="new_${n++}" class="form-control border border-success" autocomplete="off" />
										</td>
									</tr>`;
				$("#product_data tbody").append(newRow);
			});
		});
		}
	</script>

Please give your feedback

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.