Help Please! Delete option from my View records page

The_MA

Hi there, I started experimenting and learning PHP a month ago - I'm doing okay I think and I'm really enjoying the challenge but I'm really frustrated with this delete script. I have a list of things I need to do with my database - but this is my next hurdle - the delete query within my view records page. I want to be able to click on a button or link to delete a record in a table. I was envisaging that each record would have a delete button or link next to it and clicking that would remove it. Here is the script I have created to view "Devices": I have commented parts I am unsure about or wanted to clarify my intention about

[<html><head><title>View Devices</title></head>
<body>

<?php

#THIS IS JUST A SESSION I HAVE GOING TO ENSURE THE USER IS LOGGED IN ETC.

session_start();

if (!isset($_SESSION['loggedin']) 
|| $_SESSION['loggedin'] !== true) {

header('Location: login.php');
exit;
}

#I TRIED USING THE 3 LINES BELOW TO AID MY DELETE QUERY?

$x10_channel = $_POST['x10_channel'];
$device = $_POST['device'];
$row = $_POST['row'];

$conn=@mysql_connect( "localhost", "alexm", "rugby" )
or die( "Err:Conn" );

$db = @mysql_select_db( "rugby_project", $conn) or die( "Err:Db" );

$sql = "select device, x10_channel from Devices order by x10_Channel";

$rs = @mysql_query( $sql,$conn);
$rows = mysql_num_rows($rs);

echo( "<table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"3\"><font size=\"1\" face=\"Verdana\">
<center>Devices View</center></font></td></tr>" );
echo( "<tr><td><font size=\"1\" face=\"Verdana\"><strong>X10 Channel</td>" );
echo( "<td><font size=\"1\" face=\"Verdana\"><strong>Device</td>" );
echo( "<td><font size=\"1\" face=\"Verdana\"><i><center>Delete</td></tr>" );
if(mysql_num_rows($rs))
{

while( $row = mysql_fetch_array( $rs ) )


{

$x10_channel=$row["x10_channel"];
$device=$row["device"];

echo( "<tr><td>\n<font size=\"2\" face=\"Verdana\">" . $row["x10_channel"] . "</td>" );
echo( "<td><font size=\"2\" face=\"Verdana\">" . $row["device"] . "</td>" );

#THIS LINE BELOW IS WHERE I AM TRYING TO PLACE THE DELETE OPTION IN - 
#I TRIED USING A SUBMIT BUTTON TOO BUT STILL COULDN'T GET IT TO WORK - 
#A SUBMIT BUTTON WOULD BE MY PREFERED CHOICE - 
#I figured I needed to redirect to another page like delete.php 
#to carry out the query but Ideally I would like to incorporate 
#it within the same viewdevices.php script

echo( "<td><font size=\"2\" face=\"Verdana\"><center><a href=\"Delete.php?cmd=\delete&device=\$device\">x</a></td></tr>" );

}

echo( "</table><p><table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" 

bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>There are $rows records in this table</center></font></td></tr></table>" );

echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );

}
else
{
echo "This table has no records yet";
} 


?>

I was thinking the following may work to actually do the deletion in a seperate delete.php linked from this viewdevices.php by the delete button next to each record - but I REALLY would like it all to be within one script if that is possible?!

<?php

#connect etc.

if($_GET["cmd"]=="delete")
{
$sql = "DELETE FROM devices WHERE device=$device";
$result = mysql_query($sql);
echo "$Device deleted!";
}
else
{
echo "Not Working";
} 
?>

I would REALLY appreciate someone suggesting the correct code for this! Sorry for being an embarassing noob to PHP... Thanks so much in advance!!!!

x

Mark.

herve

Well, you had already mostly everything in your hands 🙂

<?php

#connect etc.

if($_GET["cmd"]=="delete")
{
    // Do the delete stuff here

}
else
{
    // So the 'cmd' was not set or is equal to something else
    // than "delete" ? Ok, so let's print the (initial) form !

print "<html>";

// Loop of records here, with hyperlink pointing back 
// to this script with the "?cmd=delete" parameter


print "</html>";
}
?>

Roger_Ramjet

In one operation? Then you need to use a checkbox next to each record.

<?php

#THIS IS JUST A SESSION I HAVE GOING TO ENSURE THE USER IS LOGGED IN ETC.

session_start();

if (!isset($_SESSION['loggedin'])
|| $_SESSION['loggedin'] !== true) {

header('Location: login.php');
exit;
}

#I TRIED USING THE 3 LINES BELOW TO AID MY DELETE QUERY?

$x10_channel = $_POST['x10_channel'];
$device = $_POST['device'];
$row = $_POST['row'];

$conn=@mysql_connect( "localhost", "alexm", "rugby" )
or die( "Err:Conn" );

$db = @mysql_select_db( "rugby_project", $conn) or die( "Err:Db" );

$sql = "select device, x10_channel from Devices order by x10_Channel";

$rs = @mysql_query( $sql,$conn);
$rows = mysql_num_rows($rs);

echo( "<table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\">
<td colspan=\"3\"><font size=\"1\" face=\"Verdana\">
<center>Devices View</center></font></td></tr>" );
echo( "<tr><td><font size=\"1\" face=\"Verdana\"><strong>X10 Channel</td>" );
echo( "<td><font size=\"1\" face=\"Verdana\"><strong>Device</td>" );
echo( "<td><font size=\"1\" face=\"Verdana\"><i><center>Delete</td></tr>" );
if(mysql_num_rows($rs))
{

echo '<form name="delform" method="post" action="delete.php">';

while( $row = mysql_fetch_array( $rs ) ){
   $x10_channel=$row["x10_channel"];
   $device=$row["device"];
   echo '<tr><td>\n<font size="2" face="Verdana">' . $x10_channel . '</td>';
   echo '<td><font size="2" face="Verdana">' . $device . '</td>';
   echo '<td><input type="checkbox" name="delboy[]" value="' . $device . '" /></td></tr>';
}

echo '<tr><td colspan="3"><input type="submit" name="delete" value="delete" /><input type="reset" name="reset" value="reset" /></td></tr></form>';
echo '</table><p><table width="20%" border="1" align="center" cellpadding="4" cellspacing="1"

bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\">
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>There are $rows records in this table</center></font></td></tr></table>" );

echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );

}
else
{
echo "This table has no records yet";
}


?>

Now you are going to get all the checkboxes ticked posted to delete.php in an array caled delboy, and you process them this way


if (isset($_POST['delete']) && isset($_POST['delboy'])) {
   $delstring = '';
   foreach ($_POST['delboy'] as $val) {
      $delstring .= "'" $val . "',";
   }
   $delstring = rtrim($delstring, ',');
   $delquery = "DELETE FROM Devices WHERE device IN (" . $delstring . ")";
   $result = mysql_query($delquery);
   if ($result) { echo 'records deleted'; }
}

Notice how I use the single quote for echo strings, saves having to uise all those \ escapes in the html.

The_MA

Thanks - sorry - I am at boarding school and the internet cuts off at 11 so I couldn't reply earlier...

Anyway - thankyou - I have incorporated the Delete.php and viewdevices.php together which is great but the query still doesn't work... here is the code:

<?php

session_start();

if (!isset($_SESSION['loggedin']) 
   || $_SESSION['loggedin'] !== true) {

   header('Location: login.php');
   exit;
}

$self = $_SERVER['PHP_SELF'];
$x10_channel = $_POST['x10_channel'];
$device = $_POST['device'];

$conn=@mysql_connect( "localhost", "alexm", "rugby" )
		 	or die( "Err:Conn" );

$db = @mysql_select_db( "rugby_project", $conn) or die( "Err:Db" );

if($_GET["cmd"]=="delete" & !empty($_GET["device"]))

{

$x10_channel = $_GET['x10_channel'];
$device = $_GET['device'];

$sql = "DELETE FROM devices WHERE device=$device, x10_channel=$x10_channel";
$result = @mysql_query($sql,$conn)
	        or die("Could not delete device");
if($result)
  {
    echo( "<table width=\"30%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>$device on $x10_channel has been deleted</center></font></td></tr></table>" );

echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"viewdevices.php\">Back</a>&nbsp;&nbsp;|&nbsp;
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );
  }

}
else
{

$sql = "select device, x10_channel from devices order by x10_Channel";

$rs = @mysql_query( $sql,$conn);
$rows = mysql_num_rows($rs);

   echo( "<table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"3\"><font size=\"1\" face=\"Verdana\">
<center>Devices View</center></font></td></tr>" );
   echo( "<tr><td><font size=\"1\" face=\"Verdana\"><strong>X10 Channel</td>" );
   echo( "<td><font size=\"1\" face=\"Verdana\"><strong>Device</td>" );
   echo( "<td><font size=\"1\" face=\"Verdana\"><i><center>Delete</td></tr>" );
if(mysql_num_rows($rs))
{

while( $row = mysql_fetch_array( $rs ) )


{
      $x10_channel=$row["x10_channel"];
      $device=$row["device"];

   echo( "<tr><td>\n<font size=\"2\" face=\"Verdana\">" . $row["x10_channel"] . "</td>" );
   echo( "<td><font size=\"2\" face=\"Verdana\">" . $row["device"] . "</td>" );

echo( "<td><font size=\"2\" face=\"Verdana\"><center><a href='$self?

cmd=delete&device=$device&x10_channel=$x10_channel'>x</a></td></tr>" );

}

   echo( "</table><p><table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" 

bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>There are $rows records in this table</center></font></td></tr></table>" );

   echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );

}
else
{
echo( "<table width=\"30%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>This table has no records yet</center></font></td></tr></table>" );
} 
}

?>

Where is it going wrong - I get the echo - Could not delete device

Also - if I want to replace the href line:

echo( "<td><font size=\"2\" face=\"Verdana\"><center><a href='$self?cmd=delete&device=$device&x10_channel=$x10_channel'>x</a></td></tr>" );

with a submit button, how do I do this so that the device and channel and posted? Would this work in replacement of the above line?:

echo( "<td><form action='$self' method=post><input type=hidden name=cmd value='delete'><input type=hidden name=device value='$device'><input type=hidden name=x10_channel value='$x10_channel'><input type=submit name=delete value='x'>" );

And with this submit button what code do I need to put in place of the delete query itself (something like:?)

if(isset($_POST['delete']))  {

$x10_channel = $_GET['x10_channel'];
$device = $_GET['device'];

$sql = "DELETE FROM devices WHERE device=$device, x10_channel=$x10_channel";
$result = @mysql_query($sql,$conn)
	        or die("Could not delete device");
if($result)
  {
    echo( "<table width=\"30%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>$device on $x10_channel has been deleted</center></font></td></tr></table>" );

I have tried all this but none of it works properly - Thanks very much again in advance for your help!

As for the delete check boxes this looks great but I can't even get the script to work at all because I do not understand the $val: (it comes up in debugging via command prompt as unexpected T_variable here is the script using your suggestion (I think - it must be wrong somewhere????!!!)

<?php

session_start();

if (!isset($_SESSION['loggedin']) 
   || $_SESSION['loggedin'] !== true) {

   header('Location: login.php');
   exit;
}

$self = $_SERVER['PHP_SELF'];
$x10_channel = $_POST['x10_channel'];
$device = $_POST['device'];

$conn=@mysql_connect( "localhost", "alexm", "rugby" )
		 	or die( "Err:Conn" );

$db = @mysql_select_db( "rugby_project", $conn) or die( "Err:Db" );

if (isset($_POST['delete']) && isset($_POST['delboy'])) { 
   $delstring = ''; 
   foreach ($_POST['delboy'] as $val) { 
      $delstring .= "'" $val . "',"; 
   } 
   $delstring = rtrim($delstring, ','); 
   $delquery = "DELETE FROM Devices WHERE device IN (" . $delstring . ")"; 
   $result = mysql_query($delquery) or die("Could not delete device"); 

if($result)
  {
    echo( "<table width=\"30%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>$device on $x10_channel has been deleted</center></font></td></tr></table>" );

echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"viewdevices.php\">Back</a>&nbsp;&nbsp;|&nbsp;
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );
  }

}
else
{

$sql = "select device, x10_channel from devices order by x10_Channel";

$rs = @mysql_query( $sql,$conn);
$rows = mysql_num_rows($rs);

   echo( "<table width=\"20%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"3\"><font size=\"1\" face=\"Verdana\">
<center>Devices View</center></font></td></tr>" );
   echo( "<tr><td><font size=\"1\" face=\"Verdana\"><strong>X10 Channel</td>" );
   echo( "<td><font size=\"1\" face=\"Verdana\"><strong>Device</td>" );
   echo( "<td><font size=\"1\" face=\"Verdana\"><i><center>Delete</td></tr>" );
if(mysql_num_rows($rs))
{

echo '<form name="delform" method="post" action="$self">'; 

while( $row = mysql_fetch_array( $rs ) ){ 
   $x10_channel=$row["x10_channel"]; 
   $device=$row["device"]; 
   echo '<tr><td>\n<font size="2" face="Verdana">' . $x10_channel . '</td>'; 
   echo '<td><font size="2" face="Verdana">' . $device . '</td>'; 
   echo '<td><input type="checkbox" name="delboy[]" value="' . $device . '" /></td></tr>'; 
} 

echo '<tr><td colspan="3"><input type="submit" name="delete" value="delete" /><input type="reset" name="reset" value="reset" /></td></tr></form>'; 
echo '</table><p><table width="20%" border="1" align="center" cellpadding="4" cellspacing="1" bordercolor="#000000"> 
<tr bgcolor="#CCCCCC"><td colspan="2"><font size="1" face="Verdana"><center>There are $rows records in this table</center></font></td></tr></table>'; 


   echo( "<center><p><font size=\"1\" face=\"Verdana\">
<a href=\"control.php\">Return to Control Panel</a>&nbsp;&nbsp;|&nbsp;
<a href=\"logout.php\">Log out<p></a>
</body></html>" );

}
else
{
echo( "<table width=\"30%\" border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bordercolor=\"#000000\">
<tr bgcolor=\"#CCCCCC\"> 
<td colspan=\"2\"><font size=\"1\" face=\"Verdana\">
<center>This table has no records yet</center></font></td></tr></table>" );
} 
}

?>

Roger_Ramjet

Well your query syntax is wrong

$sql = "DELETE FROM devices WHERE device=$device AND x10_channel=$x10_channel";

The_MA

Nice - I've chucked that in but still get the result DIE

$result = mysql_query($delquery) or die("Could not delete device");

Roger_Ramjet

The unexpected t-var message means that you are missing a closing bracket or suchlike. Start with the line number given in the error message and work UPWARDS untill you find the place unmatched bracket. using indenting for fis and the like makes that much easier to spot. The message could also be caused by other syntax errors so you just have to work your way through the code untill you spot it.

Hint: nect time you post about an error messaeg, include the complete error message as displayed and mark the line number in your code. You can't expect us to scan everything for you, or to count line numbers if your don't mark the place for us.

Roger_Ramjet

Looking at your delete query I can see that mine for the checkboxes will need to be amended with the xchannel. Now that is making things all too complex. You must have a primary key on the table so you need to extract that along with the device and xchannel fields. You can then just use that for the checkbox value and then in the query id=$id instead of having device=$device AND xchannel=$xchannel

The_MA

line 25 - thanks I will try that!!! brb

Roger_Ramjet

Ah! Missing a full stop in line 24

      $delstring .= "'" $val . "',"; 
// should be
      $delstring .= "'" . $val . "',";

Sorry, but my code is always full of typos, especially when I'm just bashing it out in and answer.

Like I said, when you get thet error message it means you have something wrong with your syntax in the staements preceeding the line number. Next time you'll know what to do.

The_MA

OMG YOU LEGENDARY BEAST

FanTASTIC

It works like gloriousness ah FABULOUS

thankyou SO much -WONDERFUL WONDERFUL

I shall return no doubt with more problems with another script, probably an edit script or a cross table query but hell - here's one hurdle done with and by god you're brilliant - thank you.

Mark.