Filter/Update Multiple Records

Question

enim213 1 Light Poster

16 Years Ago

can anyone give me a hand.. any idea how to do the following requirements.. i can't start please help..
i have two tables to compare.. it has so called rates.. its buying rate and selling rate(diff table), before i update new buyingrate.. it has to check selling rate (must be always below selling rate)
i already have my multiple update using checkboxes.. but the following requirements.. i couldn't start any..

1.) filter multiple records selected through chechbox..
if there were conflicts>>catch those conflict values, view it in a pop up window , enter new selling rate
2.) how can i update records with the same name but diff id in one text box(for buyrate)..>>
please help..

php

2 Contributors
2 Replies
264 Views
2 Days Discussion Span
Latest Post 16 Years Ago Latest Post by enim213

digital-ether 399 Nearly a Posting Virtuoso

16 Years Ago

can anyone give me a hand.. any idea how to do the following requirements.. i can't start please help..
i have two tables to compare.. it has so called rates.. its buying rate and selling rate(diff table), before i update new buyingrate.. it has to check selling rate (must be always below selling rate)
i already have my multiple update using checkboxes.. but the following requirements.. i couldn't start any..
1.) filter multiple records selected through chechbox..
if there were conflicts>>catch those conflict values, view it in a pop up window , enter new selling rate
2.) how can i update records with the same name but diff id in one text box(for buyrate)..>>
please help..

Hello,

Could you please post the code you're working on so someone can help you out from there.

nb: Please give your thread a descriptive title, not "Please help" etc. It will help others to find your thread when having similar problems.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

enim213 1 Light Poster · Answer 1 · 2008-08-16T11:35:16+00:00

<?php // i have two tables, buyingrate and sellingrate. this code for viewing buyingrate records. this is where i have to select records to update using checkbox. 
?>
<?php include "dbconn.php";?>
<html>
	<head>
		<script language="javascript">
			function Check(chk)
				{
				if(document.updateForm.Check_ctr.checked==true){
				for (i = 0; i < chk.length; i++)
				chk[i].checked = true ;
				}else{

				for (i = 0; i < chk.length; i++)
				chk[i].checked = false ;
				}
			}
			
		</script>
	</head>
	<body>
<?php 
			if(!$dbconn)
				{
				echo "There was an error on connection.";
				}
				
			$sql  = "select * from buyingratetable";	
			$result = mysql_query($sql, $dbconn);
			
			$count  = mysql_num_rows($result);
			echo $count;
			
			if($count == 0)
			{
				echo "there was no record.";
			}
			else
			{
?>			
			<center><table width="900">
				<tr>
					<td>			
						<fieldset>
							<legend>Multiple Update</legend>
							<center>
								<table border="0" cellpadding="1" cellspacing="2">
									<form name="updateForm" action="update.php" method="post">
										<tr style="color: #ffffff; background-color: #000000">
											<th><input type="checkbox" name="Check_ctr" value="yes" onClick="Check(document.updateForm)"></th>
											<th>DESTINATION</th>
											<th>DIALPREFIX</th>
											<th>BUYRATE</th>
											<th>NEW RATE</th>
											<th>EFFECTIVITY DATE</th>
										</tr>

<?php			
											$c=0;
											while($row = mysql_fetch_array($result))
											{
												$edate = strtotime($row['breffectivitydate']);
												$pedate = date("F j Y H:i",$edate);
					
												if( (($c++)%2) )
												{
													$color = "silver";
												}
												else
												{
													$color = "#ffffff";
												}
?>				
					
										<tr style="background-color: <?php echo $color;?>">
											<td><input type='checkbox' name='checkbox[]' id='checkbox[]' value='<?php echo $row['idbuying']; ?>'/></td>
											<td width="150" align="center"><?php echo $row['destination'];?></td>
											<td width="40" align="center"><?php echo $row['dialprefix']; ?></td>
											<td width="100" align="center"><?php echo $row['currentbuyrate']; ?></td>
											<td width="100" align="center"><?php echo $row['newbuyrate']; ?></td>
											<td width="150" align="center"><?php echo $pedate; ?></td>
										</tr>
<?php										}
			}
?>
										<tr>
											<td colspan="3"><input type="submit" name="update" value="update"/></td>
										</tr>
									</form>
								</table>
							</center>
						</fieldset>	
					</td>
				</tr>
			</table></center>
	</body>
</html>

<?php //upon submit.. it will pass array variable to another page(update.php) .. here's the code so far..i will view here the selected records(checked) and update newrate with effectivitydate..using join i will compare table data of currentsellingrate..?>

<?php include "dbconn.php"; ?>
<html>
	<head>
		<script>
			<!--
			function thePopupWindows(windowsname){
				var win = window.open('', windowsname, 'width=580,height=300');
				return true;
				}
			//-->
		</script>
	</head>
	<body>
<?php
	//code for multiple update
		if($_POST['update'])
		{
			$checkbox = $_POST['checkbox'];
			$count = count($_POST['checkbox']);
		
			if($count == 0)
			{
				echo "<script type='text/javascript'>";
				echo "alert('There was no item selected!')";
				echo "</script>";
?>				<meta http-equiv="refresh" content="0;URL=multipleupdate.php"/>
<?php
			}
			else
			{
		
				for($i=0;$i<$count;$i++)
				{
					$month = "";
					$day = "";
					$year = "";
					$time = "";
			
					$up_id = $checkbox[$i];
					$sql= "select * from buyingratetable
						left join sellingratetable
						on buyingratetable.dialprefix = sellingratetable.dialprefix
						where idbuyrate = '$up_id' order by buyingratetable.destination asc";
				
					$result = mysql_query($sql, $dbconn);
					$rows = mysql_fetch_array($result);
					
					//chop date here
					$cmonth = strtotime($rows['breffectivitydate']);
					$pcmonth = date("m",$cmonth);
						//echo $pcmonth;
					$cday = strtotime($rows['breffectivitydate']);
					$pcday = date("j",$cday);
						//echo $pcday;
					$cyear = strtotime($rows['breffectivitydate']);
					$pcyear = date("Y",$cyear);
						//echo $pcyear;
					$ctime = strtotime($rows['breffectivitydate']);
					$pctime = date("H",$ctime);
			
					//month
					$month_names = array("January","February","March","April","May","June","July","August","September","October","November","December");
					$month .= "<select name='month[]'>";
								
						for($x=0;$x<12;$x++)
							{
								$v = $x+1;
									if($v==$pcmonth)
									{
										$month .="<option value='$v' selected='selected'>$month_names[$x]</option>";
									}
									else
									{
										$month .="<option value='$v'>$month_names[$x]</option>";
									}
							}
								
					$month .="</select>";
					
					//day
					$day .= "<select name='day[]'>";
						
						for($z=1;$z<32;$z++)
							{
									if($z==$pcday)
									{
										$day .= "<option value='$z' selected='selected'>$z</option>";
									}
									else
									{
										$day .= "<option value='$z'>$z</option>";	
									}
							}
							
					$day .= "</select>";
						
					//year
					$year .= "<select name='year[]'>";
							
						for($y=date("Y");$y<=2020;$y++)
						{
									if($y==$pcyear)
									{
										$year .= "<option value='$y'>$y</option>";
									}
									else
									{
										$year .= "<option value='$y'>$y</option>";	
									}
						}
							
					$year .= "</select>";
						
					//time
					$time .= "<select name='time[]'>";
						
						$timev = array("12:00 AM","01:00 AM","02:00 AM","03:00 AM","04:00 AM","05:00 AM","06:00 AM","07:00 AM"
										,"08:00 AM","09:00 AM","10:00 AM","11:00 AM","12:00 PM","01:00 PM","02:00 PM","03:00 PM"
										,"04:00 PM","05:00 PM","06:00 PM","07:00 PM","08:00 PM","09:00 PM","10:00 PM","11:00 PM");
						for($w=0;$w<24;$w++)
						{
							$v = $w.":00:00";
								//if($v==date("h:i:s A"))
								if($v==$pctime)
								{
									$time .= "<option value='$v' selected='selected'>$timev[$w]</option>";
								}
								else
								{
									$time .= "<option value='$v'>$timev[$w]</option>";
								}
						}
							
					$time .= "</select>";	
			
			
					$update .= "<tr align='center'>
									<td><input type='checkbox' name='checkbox[]' checked='checked' value='".$rows['idbuyrate']."'/></td>
										<input type='hidden' name='id[]' value='".$rows['idbuyrate']."'/>
									<td><input type='text' size='8' name='csr[]' value='".$rows['currentsellingrate']."'/></td>
									<td><input type='text' readonly='readonly' name='name[]' value='".$rows['destination']."'/></td>
									<td><input type='text' size='8' name='current[]' value='".$rows['currentbuyrate']."'/></td>
									<td><input type='text' size='8' name='new[]' value='".$rows['newbuyrate']."'/></td>
									<td>".$month."".$day."".$year."".$time."</td>
								</tr>";
				}
		
?>
	<center><table width="900">
		<tr>
			<td>
				<fieldset>
					<legend>Selected Rows</legend>
						<center><table>
							<tr style="color: #ffffff; background-color: #000000" align="center">
								<th>&nbsp;</th>
								<th>CSR</th>
								<th>DESTINATION</th>
								<th>BUY RATE</th>
								<th>NEW RATE</th>
								<th>EFFECTIVITY DATE</th>
							</tr>
								<form action="update2.php" method="post" target="thepopup" onsubmit="return thePopupWindows(this.target);">
							
										<?php echo $update; ?>
								
							<tr>
								<td colspan="2"><input type="submit" name="up" value="Apply Changes" /></td>
							</tr>
						</table></center>
								</form>
				</fieldset>
			</td>
		</tr>
	</table></center>	
<?php	
			}
		}	
?>		
	</body>
</html>

<?php //upon submit.. it will go to another page update2.php, where filter should be done newbuyrate should always be less than currentsellrate, if newbuyrate is greater, conflict records should filter and will view to pop up window. what i cant imagine is what if you select and update records ie. 5 records.. 3 rows have conflict and 2 rows are ok.how should filter should be done? following codes are as follows for update2.php  ?>

<?php include "dbconn.php"; ?>
<html>
	<head>
	</head>
	<body>
<?php	
		if($_POST['insert'])
		{
			$checkbox = $_POST['checkbox'];
			$count = count($_POST['checkbox']);
			echo $count;
			$sell_id = $_POST['sell_id'];
			$newsr = $_POST['newsr'];
			
			for($k=0;$k<$count;$k++)
			{
			$newsrid = $checkbox[$k];
			$sqlInsert = "update cc_sellingrate set newsellingrate = '$newsr[$k]' where id_sellingrate = '$newsrid'";
			$resultInsert = mysql_query($sqlInsert, $dbconn) or die ("Result Error: ".mysql_error());
			}
				if($resultInsert)
				{
					echo "successful";
				}
				else
				{
					echo "Error: ".mysql_error();
				}
			
		}
		if($_POST['up'])
		{

			$qmonth = $_POST['month'];
			$qday = $_POST['day'];
			$qyear = $_POST['year'];
			$qtime = $_POST['time'];
			
			$csr = $_POST['csr'];
			$current = $_POST['current'];
			$new = $_POST['new'];
			$effect = $_POST['effect'];
			$name = $_POST['name'];
			$checkbox = $_POST['checkbox'];
			$count = count($_POST['checkbox']);	
	
				if($new >= $csr)
				{
					for($j=0;$j<$count;$j++)
					{
					$con_id = $checkbox[$j];
					$sqlCon = "select buyingratetable.destination, buyingratetable.dialprefix,currentbuyrate, currentsellingrate from buyingratetable
							left join sellingratetable
							on buyingratetable.dialprefix = sellingratetable.dialprefix
							where idbuyrate = $con_id";
					$resultCon = mysql_query($sqlCon, $dbconn) or die("Error: ".mysql_error());		
					
					$row = mysql_fetch_array($resultCon);
					
					$con .= "<tr align='center'>
								<input type='hidden' name='sell_id' value='".$row['idsellrate']."'/>
								<input type='hidden' name='checkbox[]' checked='checked' value='".$row['idsellrate']."'/>
								<td>".$row['destination']."</td>
								<td>".$row['dialprefix']."</td>
								<td>".$row['currentsellingrate']."</td>
								<td><input type='text' name='newsr' value='".$row['newsellingrate']."'/></td>
							</tr>";
					}									
						
				}
				else
				{
					for($i=0;$i<$count;$i++)
					{
						$upid = $checkbox[$i];
						$sql = "update buyratetable SET destination = '$name[$i]', currentbuyrate = '$current[$i]', newbuyrate = '$new[$i]', breffectivitydate = '".$qyear[$i]."-".$qmonth[$i]."-".$qday[$i]." ".$qtime[$i]."' WHERE idbuyrate='$upid'";
						$result = mysql_query($sql, $dbconn);

					}
						if($result)
						{
							echo "successful.";
						}
						else
						{
							echo "error.".mysql_error();
						}	
				}
		}
		
?>
		<center><table border="0" cellpadding="1" cellspacing="2">
			<tr style="color: #ffffff; background-color: #000000">
				<th>Destination</th>
				<th>Dialprefix</th>
				<th>Current Selling Rate</th>
				<th>New Selling Rate</th>
			</tr>
			<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
			<?php echo $con;?>
			<tr>
				<td colspan="2"><input type="submit" name="insert" value="Insert"/></td>
			</tr>
			</form>
		</table></center>

	</body>	
</html>

if there were suggestions with different approach, that will be so appreciated too!
thanks in advance.. :)