Page 1 of 1

Deleting rows of data from a table

Posted: Thu Feb 26, 2004 6:01 am
by struggling_student
My code sucessfully extracts rows of data from a MySQL table and displays it on a page. What I want to do now is insert checkboxes at the beginning of each row and have a delete button underneath the table which will delete all selected rows. Can anyone give me some pointers??!

Thanks!

Posted: Thu Feb 26, 2004 6:44 am
by twigletmac
What does your code currently look like? Basically to do this you just need to add the required HTML at the appropriate point - code for checkboxes when you're looping through the rows and the delete button once all the looping has finished.

Mac

Posted: Thu Feb 26, 2004 7:06 am
by struggling_student
Hi....my code is as follows.... i assume i put the code for the checkboxes in just after this point :

Code: Select all

for ($i = 0; $i < mysql_num_rows($result); $i++){
                		echo "<tr>";
and put the delete button in after i've finished that for loop. But how will the delete button know which rows to delete? I apologise for being a bit slow... I've been working on this site for a week and i think it's getting to me!!!

Code: Select all

<?
mysql_connect($host,$username,$password);
mysql_select_db($database);
	$query = "select * from $table";
 	$result = mysql_query($query);
        echo "<hr>";

   	if ($result == 0){
      		echo("<b>Error " . mysql_errno() . ": " . mysql_error() . "</b>");
	}

   	elseif (@mysql_num_rows($result) == 0){
      		echo("<b>There is no data in this table at present.</b><br>");
	}

	else{	
		echo "<div style='width:600px;height:300px;overflow:auto;'>
    		<table border='1' bordercolor = '#FFFFFF'>
           	<thead>
           	<tr bordercolor = 'black'>";
		$row_count = 0;

             		for ($i = 0; $i < mysql_num_fields($result); $i++){
		                 echo("<th>" . mysql_field_name($result,$i) . "</th>");
             		}

    		echo "</tr>
           	</thead>
           	<tbody>";

             		for ($i = 0; $i < mysql_num_rows($result); $i++){
                		echo "<tr>";
                		$row = mysql_fetch_row($result);

                			for ($j = 0; $j < mysql_num_fields($result); $j++){
            					 echo"<td bgcolor='$row_color'>" . $row[$j] . "</td>";
               				}
                		echo "</tr>";
             		}

             	echo "</tbody>
                </table>
		</div>";

   	}

?>

Keep It Simple

Posted: Thu Feb 26, 2004 5:16 pm
by Rhino_dna
Why use a check box and a form at all? Try this;
________________________________________________________
<?
mysql_connect("$DBHost","$DBUser","$DBPass");
mysql_select_db("$DBName") or die( "Unable to select database");

if ($task == 'delete'){ mysql("$DBName","DELETE FROM MyTable WHERE Record_ID='$RID'"); }

?>
<html>
<head>
<title>thispage.php</title>
</head>

<body>
<table>
<?
$result= mysql_query( "SELECT * from MyTable ");
while($row = @mysql_fetch_array($result)) {
$RID = $row[0];
$Name= $row[1];

?>
<tr>
<td><? echo "$Name"; ?></td>
<td><a href='thispage.php?RID=<? echo "$RID"; ?>&task=delete'>delete</a> </td>
</tr>

<?
}
?>

</table></body></html>

------------------------------------------------------
Now the Record_ID field MUST BE unique to each record (of course), I just set the field to autoincrement and pass a null value to it each time I add a record.

This method deletes your record as it reloads the page and its short and sweet enough to appear that the page does not reload at all. But, if you are in love with the form/checkbox/submit method - let me know and Ill post that for ya too.

Posted: Thu Feb 26, 2004 6:45 pm
by struggling_student
Thanks for that! I get the theory behind it but PHP seems to have issues with task in this line:

if ($task == 'delete'){ mysql("$DBName","DELETE FROM MyTable WHERE Record_ID='$RID'"); }


just says its a undefined variable....

Posted: Thu Feb 26, 2004 7:11 pm
by Weirdan
obviously you have register_globals off and rhino have it on. replace $task with $_GET['task'] and $RID with $_GET['RID'] in 'delete part' of the script.

also you have to check if $_GET['task'] is set at all:

Code: Select all

if(!empty($_GET['task'])&&$_GET['task']=='delete'){
  //delete code goes here
}
And make sure that user do not pass something like
' or 1=1 --
in RID variable - or you may loose your table contents :D

Posted: Thu Feb 26, 2004 7:31 pm
by struggling_student
I definately have register_globals on which is why this is confusing me. Also, i see how this code works but the page which displays the tables is passed different table names depending on which table the user selects to look at.

A problem I can see with the piece of code below this is that there is no way of knowing which table, with which fields will be passed through so how can I output all fields for each row? :?

Does this actually make any sense or am I just missing the point?!?!

while($row = @mysql_fetch_array($result)) {
$RID = $row[0];
$Name= $row[1];