Updating multiple rows within a custom WordPress Database


#1

Hi,
I am new to this forum and was wondering if anybody would be able to shed some light on a problem that I am having with a WordPress plugin I am creating.

Basically I need to show 6 records from a custom database and then update each of the records within 1 form and 1 submit button. At the moment my code can show the 6 records but when I update the form it only updates the last record?

Here is my code.

//Find PL Forecasts
public function findPLforcasts(){

		//HTML to create table for form
		echo '</br>';
		echo '</br>';
		echo '<h1>PL Forecasts</h1>';
		echo '<div class="limiter">';
		echo '<div class="container-table100">';
		echo '<div class="wrap-table100">';
		echo '<div class="table100 ver1 m-b-110">';
		echo '<table data-vertable="ver1">';
		echo '<thead>';
		echo '<tr class="row100 head">';
		echo '<th class="column100 column1" data-column="column1">ID</th>';
		echo '<th class="column100 column1" data-column="column1">Week</th>';
		echo '<th class="column100 column2" data-column="column2">Week Ending</th>';
		echo '<th class="column100 column3" data-column="column3">Sun</th>';
		echo '<th class="column100 column4" data-column="column4">Mon</th>';
		echo '<th class="column100 column5" data-column="column5">Tues</th>';
		echo '<th class="column100 column6" data-column="column6">Wed</th>';
		echo '<th class="column100 column7" data-column="column7">Thurs</th>';
		echo '<th class="column100 column8" data-column="column8">Fri</th>';
		echo '<th class="column100 column9" data-column="column9">Sat</th>';
		echo '<th class="column100 column10" data-column="column10">Total</th>';
		echo '<th class="column100 column11" data-column="column11">Budget</th>';
		echo '<th class="column100 column12" data-column="column12">Labour</th>';
		echo '</tr>';
		echo '</thead>';
		echo '<tbody>';
		echo '<div class="pltable"> </div>';
		
		global $wpdb;
			
			
		$PLWeekNumber = ( $_POST['PLWeekNumber'] );
		$PLUnitNumber = ( $_POST['PLUnitNumber'] );
		$cssw = 1;
		$cssd = 1;
		$upcss = 0;
		$id_num = 0;
		$form_id =0;
		
		$result = $wpdb->get_results( "SELECT * FROM wp_pl_actuals WHERE unit =$PLUnitNumber AND week >= $PLWeekNumber  LIMIT 1"  );
		
		
		foreach ( $result as $print )   {
		
			echo '<tr class="row100">';
			echo '<td class="column100 column1 week" data-column="column1"><input type="text" name="ly_id" id="ly_id'. $cssw++ .'" value="' . $print->id .'" readonly /></td>';
			echo '<td class="column100 column1 week" data-column="column1"><input type="text" name="ly_week" id="ly_week_'. $cssw++ .'" value="' . $print->week .'" readonly /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_week_ending_date" id="ly_week_ending_date_'. $cssd++ .'" value="' . $print->week_ending_date  .'" readonly /></td>';
			echo '<td class="column100 column2 price" data-column="column2" ><input type="text" name="ly_sun_net_sales" id="ly_sun_net_sales" value="' . $print->sun_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_mon_net_sales" id="ly_mon_net_sales" value="' . $print->mon_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_tues_net_sales" id="ly_tues_net_sales" value="' . $print->tues_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_wed_net_sales" id="ly_wed_net_sales" value="' . $print->wed_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_thurs_net_sales" id="ly_thurs_net_sales" value="' . $print->thurs_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_fri_net_sales" id="ly_fri_net_sales" value="' . $print->fri_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_sat_net_sales" id="ly_sat_net_sales" value="' . $print->sat_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_total_net_sales" id="ly_total_net_sales" value="' . $print->total_net_sales  .'" readonly /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_budget_net_sales" id="ly_budget_net_sales" value="' . $print->budget_net_sales  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input type="text" name="ly_total_labour" id="ly_total_labour" value="' . $print->total_labour  .'" /></td>';
			echo '<td class="column100 column2" data-column="column2" ><input value="Update" name="update_1" id="update_1" type="button"></td>';
			echo '</tr>';
			
		}  
		
		echo '</tbody>';
		echo '</table>';
		echo '</div>';
	
	}
					
	/**
	 * Ajax Form action
	 */
	public function ajax_form_action() { 
		if (isset($_POST)) {
		
		// unserialize the data
		parse_str($_POST["data"], $_POST);

		if (!$errNotice) { 
			
			global $wpdb; // this is how you get access to the database
			$table_name = $wpdb->prefix . "pl_actuals";
			$id = $_POST['ly_id'];
			$unit = $_POST['venue'];
			$week = $_POST['ly_week'];
			$week_ending_date = $_POST['ly_week_ending_date'];
			$sun_net_sales = $_POST['ly_sun_net_sales'];
			$mon_net_sales = $_POST['ly_mon_net_sales'];
			$tues_net_sales= $_POST['ly_tues_net_sales'];
			$wed_net_sales= $_POST['ly_wed_net_sales'];
			$thurs_net_sales= $_POST['ly_thurs_net_sales'];
			$fri_net_sales= $_POST['ly_fri_net_sales'];
			$sat_net_sales= $_POST['ly_sat_net_sales'];
			$total_net_sales= $_POST['ly_total_net_sales'];
			$total_budget_net_sales= $_POST['ly_budget_net_sales'];
			$total_labour= $_POST['ly_total_labour'];
				
			$rows_affected = $wpdb->update( $table_name, array( 
			
			'sun_net_sales' => $sun_net_sales,
			'mon_net_sales' => $mon_net_sales,
			'tues_net_sales' => $tues_net_sales,
			'wed_net_sales' => $wed_net_sales,
			'thurs_net_sales' => $thurs_net_sales,
			'fri_net_sales' => $fri_net_sales,
			'sat_net_sales' => $sat_net_sales,
			'total_net_sales' => $total_net_sales,
			'total_budget_net_sales' => $total_budget_net_sales,
			'total_labour' => $total_labour
			),array(
			'id' => $id));
					
			if($rows_affected==1){
				echo "Your Forecast has been submitted'. $id .'";
			} else {
				
				exit( var_dump( $wpdb->last_error ));
			}

			$wpdb->flush();
			}						
		} else {
			echo $errNotice; 
			
			// stop executing script
			die();
		}
	} // end if

#2

Name all form fields as arrays - example: https://davidwalsh.name/checkbox-form-input-arrays

loop through the submitte data