Delete row using MysQl / PHP with checkboxes

  • (2 Pages)
  • +
  • 1
  • 2

29 Replies - 8659 Views - Last Post: 06 June 2011 - 10:49 AM Rate Topic: -----

#1 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 03:57 AM

Hi All,
I am hoping someone can point me in the right direction. I have tried various permutations and receiving the same error each time of "Error: Query was empty". I have to tell you that I am not in anyway a programmer and just trying to adapt an existing application on my own. I will be happy to provide a live location for viewing the site layout and what I am trying to achieve. (pm me for the site location and login details if you want to view the site)...

Essentially the site allows small guesthouses to register their accommodation establishment and to manage bookings etc from there. One of the things they can do is to enter their rates. The application never had any functionality for them to delete any rates and this is what I am attempting to insert, functionality that will allow a vendor to select a rate for deletion using a checkbox.

My code is currently as follows. On clicking the delete button I receive an error:Query was empty. I have a feeling that I am going wrong on marrying the checkbox id to the record id but for the life of me I cant get it right...
Hope someone can assist... am sure it is staring me in the face but after two days of trying still cannot see the wood for the trees...
p.s. Some of the code lines are commented out... this is because it was various things I was trying out and getting errors from but kept them there for later referral if need be

<?php
  
  
  $rates = dbGetRows("rates", "venueid = '".$_SESSION['venueid']."' ORDER BY roomtype, datefrom");
  //$result=mysql_query($rates);
  //$count=mysql_num_rows($result);
  
  $roomtypes = dbGetRows("roomcategories", "venueid = '".$_SESSION['venueid']."'");
  $id = dbGetRows("rates", "id = '".$_SESSION['venueid']."'");
 

//while ($row = mysql_fetch_assoc($result)){ 
 
  if($_POST['delete']) // from button name="delete"
	 {
 $checkbox = $_POST['checkbox']; //from name="checkbox[]"
		 $countCheck = count($_POST['checkbox']);
 
 for($i=0;$i<$countCheck;$i++)
		 {
			 $del_id  = $checkbox[$i];
 
 $sql = "DELETE FROM rates where rates.id = $del_id";
 $result = mysql_query($query) or die( "Error: " . mysql_error() );
 
		 }
			 if($result)
		 {	
				 header('Location: index.php?section=rates');
			 }
			 else
			 {
 echo "Error: ".mysql_error();
			 }
	 }

?> 

<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td class="small"> <font size="3"><b><?php echo $venue['venuename']." "; ?> - Rates Setup</b></font><br>
      The rate data entered here is used to determine the cost of a guest's accommodation 
      at your establishment.<br>
      If you wish to discount your rates to certain groups, clicking on 'Manage 
      Rate Groups' will set this up.<br>
      <br>
      <a href="javascript:;" onclick="window.open('rategroups.php', '_blank', 'width=500,height=350,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');">[Manage 
      Rate Groups]</a> <br>
      <br>
      <a href="javascript:;" onclick="<?php if( mysql_num_rows($roomtypes) == 0 ) echo "alert('Rates information cannot be entered without room/unit categories existing.\\nPlease setup your rooms or units first');"; else echo "window.open('newratewizard.php', '_blank', 'width=700,height=500,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');";?>">[Add New Rate]</a>- <a href="javascript:;" onclick="window.open('delrates.php', '_blank', 'width=900,height=350,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');">[Delete Rates]</a> <br>
      <br>
      <form id="form1" name="form1" method="post" action="">
        <p>
          <?php
        $oldroomtype = "";

        while( $rate = mysql_fetch_array($rates, MYSQL_ASSOC) ) {
          $roomtype = $rate['roomtype'];
          $id = $rate['id'];
		  
          if( $roomtype != $oldroomtype ) {
            $roomcategories = dbGetRows("roomcategories", "id = '".$roomtype."'");
            $roomcategory = mysql_fetch_array($roomcategories, MYSQL_ASSOC);
            $roomtypename = $roomcategory['roomname'];
            
            if( $oldroomtype != "" ) echo "</table><br><br>";
            echo "<b>".strtoupper($roomtypename)."</b>
             <table width=\"600\" border=\"0\" cellspacing=\"1\" cellpadding=\"3\" style=\"border: 1px solid #EEEEEE;\">
                <tr bgcolor=\"#DDDDDD\">
                  <td class=\"small\" align=\"center\"><b>DESCRIPTION</b></td>
                  <td class=\"small\" align=\"center\"><b>PERIOD</b></td>
                  <td class=\"small\" colspan=\"56\" align=\"center\"><b>RATES</b></td>
				  </tr>
                <tr>
                  <td class=\"small\">&nbsp;</td>
                  <td class=\"small\">&nbsp;</td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Single</b></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Double</b></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Child</b></td>
				  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Infant</b></td>
                  <td class=\"small\" align=\"right\" width=\"1\" bgcolor=\"#E4E4E4\"></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Base Rate</b></td>
                  </tr>";
              $oldroomtype = $roomtype; 
          }
            
          echo "<tr bgcolor=\"#F5F5F5\">
                  <td class=\"small\">".$rate['ratename']."</td>
                  <td class=\"small\" class=\"small\" align=\"center\">".date("d M Y", myDateTophp($rate['datefrom']))." - ".date("d M Y", myDateTophp($rate['dateto']))."</td>
                  <td class=\"small\" align=\"center\">".($rate['single'] > 0 ? $rate['single'] : "")."</td>
                  <td class=\"small\" align=\"center\">".($rate['double'] > 0 ? $rate['double'] : "")."</td>
				  <td class=\"small\" align=\"center\">".($rate['child'] > 0 ? $rate['child'] : "")."</td>
                  <td class=\"small\" align=\"center\">".($rate['infant'] > 0 ? $rate['infant'] : "")."</td>
                  <td class=\"small\" align=\"center\" width=\"1\" bgcolor=\"#E4E4E4\"></td>
                  <td class=\"small\" align=\"center\">".($rate['base'] > 0 ? $rate['base'] : "")."</td>
				  <td class=\"small\" align=\"center\">".($rate['id'] > 0 ? $rate['id'] : "")."</td>
				  <td><input type='checkbox' name='checkbox[]' id='checkbox[]'  value=$del_id </td>
				  
             </tr>";
			}       
                
           if( mysql_num_rows($rates) > 0 ) echo " </table> ";
		   
        ?>
        </p>
        <p><label><input type="submit" name="delete" id="button" value="Delete Selected" /></label></p>
      </form>      </td>
  </tr>
</table>


Is This A Good Question/Topic? 0
  • +

Replies To: Delete row using MysQl / PHP with checkboxes

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,436
  • Joined: 23-August 08

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 04:38 AM

$sql = "DELETE FROM rates where rates.id = $del_id";
$result = mysql_query($query) or die( "Error: " . mysql_error() );


What the variable containing the query now?

Moved to PHP, as this is not really a MySQL question. Also, there's no need to qualify the id in the query with the table name here.
Was This Post Helpful? 0
  • +
  • -

#3 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 04:47 AM

View PostJackOfAllTrades, on 29 May 2011 - 04:38 AM, said:

$sql = "DELETE FROM rates where rates.id = $del_id";
$result = mysql_query($query) or die( "Error: " . mysql_error() );


What the variable containing the query now?

Moved to PHP, as this is not really a MySQL question. Also, there's no need to qualify the id in the query with the table name here.


Hi JackOfAllTrades..
Many thanks for your reply. My apologies for posting in the wrong forum. Was not sure if it was to go into the MySql one or PHP and of course I chose the wrong one :)

I have removed the qualifying (rates.)but am sorry, I don't quite understand your question regarding the variable? Are you want me to ech the variable to check the contents?
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,436
  • Joined: 23-August 08

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 04:58 AM

Is the query you're trying to run in the variable named $query, or is it in the variable named $sql?
Was This Post Helpful? 0
  • +
  • -

#5 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 05:03 AM

AH!... It was in $sql.. changed it to $query and now getting "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
Was This Post Helpful? 0
  • +
  • -

#6 japanir  Icon User is offline

  • jaVanir
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,025
  • Joined: 20-August 09

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 05:06 AM

Try to print the query and see if you can find the problem, or post it here.
Was This Post Helpful? 0
  • +
  • -

#7 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 05:15 AM

hi japanir,

I tried to print the query but getting all sorts of errors. I apologise, this must frustrate people like yourselves when dealing with us that know next to nothing and trying to learn...

All the code I have in that one page is listed in my first posting so don't really know what else I can supply that may help you to help me...
Was This Post Helpful? 0
  • +
  • -

#8 japanir  Icon User is offline

  • jaVanir
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,025
  • Joined: 20-August 09

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 06:11 AM

just use
var_dump($query)

if echo\print gives you errors.

This post has been edited by japanir: 29 May 2011 - 06:11 AM

Was This Post Helpful? 0
  • +
  • -

#9 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 06:22 AM

thanks.. that gave me a NULL value together with the error of

Quote

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '


My code now looks like this and takes into account the line number where the Sql syntax error is occuring. The Null appears as soon as I open the page... have I inserted it at the correct location?

if($_POST['delete']) // from button name="delete"
	 {
 $checkbox = $_POST['checkbox']; //from name="checkbox[]"
		 $countCheck = count($_POST['checkbox']);
 
 for($i=0;$i<$countCheck;$i++)
		 {
			 $del_id  = $checkbox[$i];
 
 $sql = "DELETE FROM rates where id = $del_id";
 $result = mysql_query($sql) or die( "Error: " . mysql_error() );
 
		 }
			 if($result)
		 {	
				 header('Location: index.php?section=rates');
			 }
			 else
			 {
 echo "Error: ".mysql_error();
			 }
	 }
var_dump($sql)
?> 

Was This Post Helpful? 0
  • +
  • -

#10 japanir  Icon User is offline

  • jaVanir
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,025
  • Joined: 20-August 09

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 06:29 AM

Better do it here:
for($i=0;$i<$countCheck;$i++)
		 {
			 $del_id  = $checkbox[$i];
 
 $sql = "DELETE FROM rates where id = $del_id";
var_dump($sql);


Check also the $del_id, var_dump($del_id)
Was This Post Helpful? 0
  • +
  • -

#11 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 06:39 AM

That location gives and Unexpected T-Variable error at that line number...
Was This Post Helpful? 0
  • +
  • -

#12 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,436
  • Joined: 23-August 08

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 07:11 AM

Here's a quick example of how to do this. HTML Form:

<html>
<body>
<form id="cbs" name="cbs_test" method="POST" action="checkboxes.php">
<input type="checkbox" name="checkboxes[]" value="1">1</input>
<input type="checkbox" name="checkboxes[]" value="2">2</input>
<input type="checkbox" name="checkboxes[]" value="3">3</input>
<input type="checkbox" name="checkboxes[]" value="4">4</input>
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>



PHP page:
<?php

$checkedBoxes = implode(',', $_POST['checkboxes']);

$query = "DELETE FROM my_table WHERE id IN ($checkedBoxes)";

echo $query;
?>


Was This Post Helpful? 1
  • +
  • -

#13 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 09:55 AM

Gentlemen,
I msut express my sincere gratitude for the time you have taken to help me. I am stuck on the

Quote

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
error so am going to try fix that before I can go any further to see of the code is still broken or whether it works.
Once again, Many thanks
Was This Post Helpful? 0
  • +
  • -

#14 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 944
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 10:03 AM

Did you try what JackOfAllTrades said?

Please post your current/updated code.
Was This Post Helpful? 0
  • +
  • -

#15 garwil  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 29-May 11

Re: Delete row using MysQl / PHP with checkboxes

Posted 29 May 2011 - 10:11 AM

Hi coderada.

I am busy with tha aspect now as well again. I could not use the htm tables as mune are dynamically generated. I did try the php code however but getting implode errors

Quote

Warning: implode() [function.implode]: Invalid arguments passed in C:\wamp\www\lat\venueadmin\rates.php on line 12
DELETE FROM my_table WHERE id IN ()


my code now looks like this. I ahve commented out the old code and am busy with JackofAllTrade's suggestion...

<?php
  
  
  $rates = dbGetRows("rates", "venueid = '".$_SESSION['venueid']."' ORDER BY roomtype, datefrom");
  //$result=mysql_query($rates);
  //$count=mysql_num_rows($result);
  
  $roomtypes = dbGetRows("roomcategories", "venueid = '".$_SESSION['venueid']."'");
  $id = dbGetRows("rates", "id = '".$_SESSION['venueid']."'");
 

$checkedBoxes = implode(',', $_POST['checkboxes']);

$query = "DELETE FROM my_table WHERE id IN ($checkedBoxes)";

echo $query;



//while ($row = mysql_fetch_assoc($result)){ 
 
//  if($_POST['delete']) // from button name="delete"
//	 {
// $checkbox = $_POST['checkbox']; //from name="checkbox[]"
//		 $countCheck = count($_POST['checkbox']);
// 
// for($i=0;$i<$countCheck;$i++)
//		 {
//			 $del_id  = $checkbox[$i];
//
// $sql = "DELETE FROM rates where id = '$del_id'";
// $result = mysql_query($sql) or die( "Error: " . mysql_error() );
// 
//		 }
//			 if($result)
//		 {	
//				 header('Location: rates.php');
//}
//			 else
//			 {
// echo "Error: ".mysql_error();
//			 }
//	 }

?> 


<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td class="small"> <font size="3"><b><?php echo $venue['venuename']." "; ?> - Rates Setup</b></font><br>
      The rate data entered here is used to determine the cost of a guest's accommodation 
      at your establishment.<br>
      If you wish to discount your rates to certain groups, clicking on 'Manage 
      Rate Groups' will set this up.<br>
      <br>
      <a href="javascript:;" onclick="window.open('rategroups.php', '_blank', 'width=500,height=350,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');">[Manage 
      Rate Groups]</a> <br>
      <br>
      <a href="javascript:;" onclick="<?php if( mysql_num_rows($roomtypes) == 0 ) echo "alert('Rates information cannot be entered without room/unit categories existing.\\nPlease setup your rooms or units first');"; else echo "window.open('newratewizard.php', '_blank', 'width=700,height=500,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');";?>">[Add New Rate]</a>- <a href="javascript:;" onclick="window.open('delrates.php', '_blank', 'width=900,height=350,toolbar=0,location=0,status=0,menubar=0,resizable=0,scrollbars=1');">[Delete Rates]</a> <br>
      <br>
      <form id="form1" name="form1" method="post" action="">
        <p>
          <?php
        $oldroomtype = "";

        while( $rate = mysql_fetch_array($rates, MYSQL_ASSOC) ) {
          $roomtype = $rate['roomtype'];
          $id = $rate['id'];
		  
          if( $roomtype != $oldroomtype ) {
            $roomcategories = dbGetRows("roomcategories", "id = '".$roomtype."'");
            $roomcategory = mysql_fetch_array($roomcategories, MYSQL_ASSOC);
            $roomtypename = $roomcategory['roomname'];
            
            if( $oldroomtype != "" ) echo "</table><br><br>";
            echo "<b>".strtoupper($roomtypename)."</b>
             <table width=\"600\" border=\"0\" cellspacing=\"1\" cellpadding=\"3\" style=\"border: 1px solid #EEEEEE;\">
                <tr bgcolor=\"#DDDDDD\">
                  <td class=\"small\" align=\"center\"><b>DESCRIPTION</b></td>
                  <td class=\"small\" align=\"center\"><b>PERIOD</b></td>
                  <td class=\"small\" colspan=\"56\" align=\"center\"><b>RATES</b></td>
				  </tr>
                <tr>
                  <td class=\"small\">&nbsp;</td>
                  <td class=\"small\">&nbsp;</td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Single</b></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Double</b></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Child</b></td>
				  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Infant</b></td>
                  <td class=\"small\" align=\"right\" width=\"1\" bgcolor=\"#E4E4E4\"></td>
                  <td class=\"small\" align=\"center\" bgcolor=\"#E4E4E4\"><b>Base Rate</b></td>
                  </tr>";
              $oldroomtype = $roomtype; 
          }
            
          echo "<tr bgcolor=\"#F5F5F5\">
                  <td class=\"small\">".$rate['ratename']."</td>
                  <td class=\"small\" class=\"small\" align=\"center\">".date("d M Y", myDateTophp($rate['datefrom']))." - ".date("d M Y", myDateTophp($rate['dateto']))."</td>
                  <td class=\"small\" align=\"center\">".($rate['single'] > 0 ? $rate['single'] : "")."</td>
                  <td class=\"small\" align=\"center\">".($rate['double'] > 0 ? $rate['double'] : "")."</td>
				  <td class=\"small\" align=\"center\">".($rate['child'] > 0 ? $rate['child'] : "")."</td>
                  <td class=\"small\" align=\"center\">".($rate['infant'] > 0 ? $rate['infant'] : "")."</td>
                  <td class=\"small\" align=\"center\" width=\"1\" bgcolor=\"#E4E4E4\"></td>
                  <td class=\"small\" align=\"center\">".($rate['base'] > 0 ? $rate['base'] : "")."</td>
				  <td class=\"small\" align=\"center\">".($rate['id'] > 0 ? $rate['id'] : "")."</td>
				  <td><input type='checkboxes' name='checkboxes[]' id='checkboxes[]'  value=$del_id </td>
				  
             </tr>";
			}       
                
           if( mysql_num_rows($rates) > 0 ) echo " </table> ";
		   
        ?>
        <?php echo $del_id;?></p>
        <p><label><input type="submit" name="delete" id="button" value="Delete Selected" /></label></p>
      </form>      </td>
  </tr>
</table>

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2