Page 1 of 1

Help with mass deletion from mySQL DB...

Posted: Fri Dec 17, 2010 10:17 am
by phpFTW
Hi everyone,

I'm new to the board and fairly new to PHP/mySQL as well. I'm currently in the middle of finishing a final project that's due at 1pm EST for a dynamic scripting course and I'm kinda stuck. I came here hoping there might be some gurus available for some quick help. Basically, I'm dynamically adding html elements to a page that lists a person's dvd collection. Beside each entry, there is a checkbox. I need to be able to select a bunch of checkboxes and click on a delete button to wipe the entries from the DB. Here's what I think is the relevant bits of code:

Code: Select all

////////  PRE HTML
		//declare variables to store input values
		$feedback = '';
		$title = addslashes(trim($_POST['title']));
		$genre = addslashes(trim($_POST['genre']));
		$year = addslashes(trim($_POST['year']));
		$username = addslashes(trim($_SESSION['username']));
		
		//get the user's list from the DB
		$query = "SELECT id, title, genre, year, username
						FROM mydvds
						WHERE username = '" . $username . "'
						ORDER BY id";
					
		$rs = mysqli_query($dbc, $query);		

////////  HTML / PHP
	<form id="showList" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
		<div class="formBox">
			<label for="dvdHeadings" class="dvdHeadings">Title</label>
		</div>
		<div class="formBox">
			<label for="dvdHeadings" class="dvdHeadings">Genre</label>
		</div>
				<div class="formBox">
			<label for="dvdHeadings" class="dvdHeadings">Year</label>
		</div>
			
		<?php
			if($rs){
				//loop through the record set we SELECTed above
					while($row = mysqli_fetch_array($rs)){
	
					//implode the array to end up with comma separated values
	
					$csv = implode(",", $rs);
	
					echo '<div class="formBox large">
									<input type="text" maxlength="60" class="dvdEntry large" name="dvdEntry" value="' . $row['title'] . '"></input>
								</div>
								<div class="formBox med">									
									<input type="text" maxlength="40" class="dvdEntry med" name="dvdEntry" value="' . $row['genre'] . '"></input>
								</div>
								<div class="formBox small">									
									<input type="text" maxlength="4" class="dvdEntry small" name="dvdEntry" value="' . $row['year'] . '"></input>
								</div>
								<div class="btnFormBox">
									<input type="submit" value="Delete" name="btnDeleteEntry" id="btnDeleteEntry" class="buttonSm"></input>
									<input type="checkbox" value="deleteBox" name="deleteBox[]" id="' . $row['id'] . '" class="buttonSm"></input>
								</div>';
								
				if(isset($_POST['btnDeleteEntry'])){
					$arrDeleteBox = $_POST['deleteBox'];
					$query = "DELETE FROM mydvds WHERE id IN ('" . arrDeleteBox . "')";
					$rs3 = mysqli_query($dbc, $query);
				}
				};
		};
		?>	
	</form>

So there's probably a ton of coding faux pas' in here but, at this point, I just need to get the functionality working so whatever that takes... Currently there is a delete button beside every entry (in the while loop) but they're remnants of a previous attempt to get this to work and will be changed to a single delete button. I've decided that the checkbox idea is probably more intuitive/easier to code. The main issue I'm having is building the right $query using the IDs from the input elements. Hopefully I've posted enough information and, if there is an easier way this can be done, I'm all ears!

Thanks in advance,

Cheers!
--Mike

Re: Help with mass deletion from mySQL DB...

Posted: Fri Dec 17, 2010 10:24 am
by klevis miho
Change:
<input type="checkbox" value="deleteBox" name="deleteBox[]" id="' . $row['id'] . '" class="buttonSm">

to
<input type="checkbox" value="'.$row['id'].'" name="deleteBox[]" id="' . $row['id'] . '" class="buttonSm">


Then change the if like this:

Code: Select all

if(isset($_POST['btnDeleteEntry'])){
		$arrDeleteBox = $_POST['deleteBox'];
		foreac($arrDeleteBox as $delete_id) {
			$query = "DELETE FROM mydvds WHERE id = $delete_id";
			$rs3 = mysqli_query($dbc, $query);
		}
}

Re: Help with mass deletion from mySQL DB...

Posted: Fri Dec 17, 2010 10:40 am
by phpFTW
Thank you so much for the hasty reply. Works like a charm!

Cheers!