Jump to content

[SOLVED] MySQL DELETE using auto-populated Drop Down


Hyperjase

Recommended Posts

I'm wanting to remove rows from the database, using an auto-populated drop down.

 

I'm having issues tying up which variable I need to pull from the drop down section as to what needs deleting in the database ... here's the drop down code:

 

<?php
if (isset($_GET['removecat'])) { 
		?>
		<p class="admintxtl">Remove Category</p>
            <FORM ENCTYPE="multipart/form-data" ACTION="<?php echo($_SERVER['PHP_SELF']); ?>" METHOD='POST'>
            <p><span class="admintxt">Remove Category</span><br />
<?php
$query="SELECT * FROM category ORDER by 'catname'";
$result = mysql_query ($query);
echo "<select name='removecat'>";
while($nt=mysql_fetch_array($result))
{
	echo "<option value=$nt[id]>$nt[catname]</option>";
}
echo "</select>";
?>
<input type="submit" name="removecat" value="Remove Category" class="admintxt" />
            </form>
            <br />
              <br />
            </p><?php }
		?>

 

And here's the code for the actual DB DELETE command:

 

<?php
if (isset($_POST['removecat'])) {
	$result = "DELETE from category WHERE id = ('{$_POST['removecat']}')";
	mysql_query( $result ) or die (mysql_error());
	echo "<html><head><meta http-equiv='refresh' content='5;url=admin.php?main'></head>";
	echo "<p class='bigtext'>".$_POST['category']." removed from the database".$proceed;
	exit;
	}
?>

 

I've done plenty of searching, much testing but I can't suss it out, I'm almost certain it's something simple I'm missing!

 

Cheers

 

Jason

Use the multiple="multiple" option on your select element which allows you to select multiple categories at once

 

<?php
if (isset($_GET['removecat'])) { 
         ?>
         <p class="admintxtl">Remove Category</p>
            <form enctype="application/x-www-form-urlencoded" action="<?php echo($_SERVER['PHP_SELF']); ?>" method="post">
            <p><span class="admintxt">Remove Category</span><br />
<?php
   $query='SELECT * FROM category ORDER BY \'catname\'';
   $result = mysql_query ($query);
   echo '<select name="removecats[]" multipe="multiple">';
   while($nt=mysql_fetch_array($result)) {
      echo "<option value=\"{$nt['id']}\">{$nt['catname']}</option>";
   }
   echo '</select>';
?>
<input type="submit" name="removecat" value="Remove Category" class="admintxt" />
            </form>
            <br />
              <br />
            </p><?php }
         ?>

 

<?php
if (isset($_POST['removecat'])) {
      $query = 'DELETE from category WHERE id IN ('. implode(', ', $_POST['removecats']) .')';
      echo $query;// debug
      mysql_query( $query ) or die (mysql_error());
      echo "<html><head><meta http-equiv='refresh' content='5;url=admin.php?main'></head>";
      echo "<p class='bigtext'>".$_POST['category']." removed from the database".$proceed;
      exit;
      }
?>

 

Your problem was that you named your submit button the same as your select element tag. I have renamed your select name="removecats" (plural)

 

P.S. Your code is a mess. I strongly advice separating your html from your php as much as possible.

Thanks for the help, I do understand it's a tad messy, I'm still learning what does what, hence the mess and quite a few questions!

 

One more thing;  the final echo (echo "<p class='bigtext'>".$_POST['category']." removed from the database".$proceed;) attempts to pull the name of the category that has just been deleted, how would I go about this?  I was going to use the removecats variable, but thats only the category id number.  Any ideas?

 

On a side note how would i move the html outside the php in this?  I wasn't sure how to do that.

 

Thanks,

 

Jason

An extra SQL Statement.

<?php
if (isset($_POST['removecat'])) {
      $query = 'SELECT category_name FROM category WHERE id IN ('. implode(', ', $_POST['removecats']) .')';
      $result = mysql_query($query);
      $removed = array();
      while ($row = mysql_fetch_assoc($result)) {
             $removed[] = $row['category_name']; // change this to be the column name
      }

      $query = 'DELETE from category WHERE id IN ('. implode(', ', $_POST['removecats']) .')';
      echo $query;// debug
      mysql_query( $query ) or die (mysql_error());
      echo "<html><head><meta http-equiv='refresh' content='5;url=admin.php?main'></head>";
      echo "<p class='bigtext'>". implode(', ', $removed) ." removed from the database".$proceed;
      exit;
}
?>

Thanks ... although I get an error when I remove a category now:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/stea7024/public_html/anthony/admin.php on line 93

 

Not entirely sure what that means, I can't see anything obvious which is out of place

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.