deleting mysql rows

[11Tami]

Hello, for mysql on the server. Is there an easy or another way to delete rows? Right now I'm checkmarking the row I want to delete and then clicking on the red x for each one. It takes absolutely forever. Is there a better way? Please let me know, thank you very much.

[sanfly]

Write a script.....

 

DELETE FROM tablename WHERE somefield = 'somevalue';

 

Mysql DELETE syntax

[trq]

Run a query manually. either via the command line, or through php if you like.

 

How many rows do you want to remove? To remove all rows from a table simply use...

 

DELETE FROM tbl;

 

To remove only rows with an id between 10 and 50.

 

DELETE FROM tbl WHERE id > 10 && id < 50;

 

There is heaps of different ways of doing it. Maybe you need to take a look at the mysql manual.

[Glyde]

Look at the IN clause for a DELETE statement.  You can do something such as:

 

DELETE FROM tbl WHERE id IN('1', '2', '3', '4')

 

Which will delete all items from tbl where the column id is 1 2 3 or 4.  This is easily implemented when using checkboxes and PHP.

[11Tami]

Fantastic I'm glad there's a way. I've heard about entering queries manually before and I've looked at the guide but need some advice. It sounds like there is a way to enter in a command to the database when youre at the database. So it can be entered there and not from a php form that you make. Where are these codes entered there? At the database? Please get back to me, thanks.

[sanfly]

Are you using phpMyAdmin?

 

If so, there is a button that says "SQL"

 

Click on that and type the query you want in the textbox

 

HOWEVER I would recommend backing up your database using the EXPORT button before doing this, especially if you dont know much mysql

[11Tami]

Thanks a lot. Yes thats where I would like to learn how to do it. I have tried to back up the database before. I exported it as a sql file. Then I tried to upload it to a brand new database just to make sure it would import back again but it wouldn't work. So I still don't know the proper way to export yet. Any good tips on the proper way to export? Thanks.

[11Tami]

OK I've got this to try to delete a field from the table named add. Whatever field is entered into the form is the data row to be deleted. I'm getting this error on the page when the page first loads, before anything is even entered in the form. Anyone know why? Thanks.

 

Error with queryNot unique table/alias: 'data'

 

<?php

$link = mysql_connect('mysqladdress', 'database', 'password');

if (!$link) {

die('Not connected : ' . mysql_error()); }

$db_selected = mysql_select_db('database', $link);

if (!$db_selected) {

die ('Can\'t use mbdirectory : ' . mysql_error());

}

?>

<form action="<?php $_SERVER['PHP_SELF'] ;?>" method="post">

<span>Enter the name of the row you want to delete from database.</span>

<input type="text" name="data" />

<input type="submit" value="Delete data" /></form>     

<?php

$delete = mysql_real_escape_string($_POST['data']); 

$query = "Delete data FROM add WHERE data = '$delete'";

$result = mysql_query($query)  or die('Error with query' . mysql_error());

{ if (mysql_num_rows($result) == 0)

{echo "Row not found. Click back button and try again.";  }

else {echo "Row deleted successfully!! To delete another, just click the back button.";}

}

?>

[sanfly]

Okay, what you need to do here depends on exactly what you want to do.

 

Firstly, so is there a field in your table called 'data'?  if so, there are certain resevered words in mysql that you should avoid for table names, and data and add are both included here.  If you do insist on using it, you probably need to encase it in backticks wherever you use it in your query, eg: `data` or `add`. 

 

That aside, are you wanting to delete the whole row or just reset the `data` field to empty?

 

If you want to delete the whole row, check out the DELETE syntax in the MySQL manual.  Your query should be more like this:

 

$query = "DELETE FROM `add` WHERE `data` = '$delete'";

 

If you want to just set the data field to empty, the you need to do an update, not a delete

 

$query = "UPDATE `add` SET `data` = '' WHERE data = '$delete'";

 

Also, I would recommend making the code so the query only gets executed if the submit button has been pushed, otherwise it will delete any rows where the data field is empty, eg:

 

<?php
if($_POST['submit']){
$delete = mysql_real_escape_string($_POST['data']); 
$query = "DELETE FROM `add` WHERE `data` = '$delete'";
$result = mysql_query($query)  or die('Error with query' . mysql_error());
if (mysql_num_rows($result) == 0){
	echo "Row not found. Click back button and try again.";  
}
else {
	echo "Row deleted successfully!! To delete another, just click the back button.";
}
}
?>

 

Oh, and there were un-needed { and } in your code

[trq]

Anyone know why?

 

You need to wrap your query within an if() so it will only execute if the form has been submitted. Your execution code is a little off aswell.

 

<form method="post">
<span>Enter the name of the row you want to delete from database.</span>
<input type="text" name="data" />
<input type="submit" name="submit" value="Delete data" /></form>     
<?php

  if (isset($_POST['submit'])) { // check the form has been submitted.
    $link = mysql_connect('mysqladdress', 'database', 'password') or die('Not connected : ' . mysql_error()); }
    $db_selected = mysql_select_db('database', $link) or die ('Can\'t use mbdirectory : ' . mysql_error());
    $delete = mysql_real_escape_string($_POST['data']); 
    $query = "DELETE FROM add WHERE data = '$delete'";
    if ($result = mysql_query($query)) {
      if (mysql_affected_rows()) { // use mysql_affected_rows() not mysql_num_rows().
        echo "Row deleted successfully!! To delete another, just click the back button.";
      } else {
        echo "Row not found. Click back button and try again.";
      }
    } else {
      echo 'Error with query' . mysql_error();
    }
  }

?>

[11Tami]

OK got it!! I used a combination of all of these, thanks a lot I'll send you something. Last question, I changed it to do by id. Is there anyway to enter more than one id so I can delete several at the same time? Its going to take a lot of time to do one at a time. Here's the working code doing one at a time.

 

<?php

$con = mysql_connect("mysql","database","password")

OR die('Could not connect: ' . mysql_error());   

mysql_select_db("database", $con) OR die(mysql_error()); 

?>

<form action="<?php $_SERVER['PHP_SELF'] ;?>" method="post">

<span>Enter the id you want to delete.</span>

<input type="text" name="id" />

<input type="submit" name="submit" value="submit" /></form> 

<?PHP

if (isset($_POST['submit'])) {$delete = mysql_real_escape_string($_POST['id']);

$query = "DELETE FROM `addid` WHERE `id` = '$delete'";

$delete = mysql_real_escape_string($_POST['id']);

  $result = mysql_query($query)  or die('Error with query' . mysql_error());

if (($result) == 0){echo "Row not found. Click back button and try again."; }

else {echo "Row deleted successfully!! To delete another, just click the back button.";}else {

      echo 'Error with query' . mysql_error();

    }

}

?>

[11Tami]

So for instance could you adjust this somehow? $delete = mysql_real_escape_string($_POST['id']); So that it takes the id number you enter into the form plus the 4 rows after the id, to delete 5 rows instead of 1? I already looked in the manual under limits and order by etc. and couldn't see how to delete more than one row. Thanks.

 

[sanfly]

You would need to separate the info out into an array

 

So, you could put your data into the text box like this, with each number separated by a comma

 

4,5,6,7,8,9

 

Then, when you retrieve the data onto the POST page, put it into an array using explode().  After that you use foreach() to cycle through each value

 

<?php
$con = mysql_connect("mysql","database","password") or die('Could not connect: ' . mysql_error());   
mysql_select_db("database", $con) OR die(mysql_error());   
?>
<form action="<?php $_SERVER['PHP_SELF'] ;?>" method="post">
<span>Enter the ids you want to delete, separate each with a comma.</span>
<input type="text" name="id" />
<input type="submit" name="submit" value="submit" /></form> 
<?php
if (isset($_POST['submit'])) {
$delete = mysql_real_escape_string($_POST['id']);
   	$array = explode(',', $delete);
foreach($array as $value){
	$value = trim($value); // to remove any whitespace if you enter spaces in the textbox
	$query = "DELETE FROM `addid` WHERE `id` = '$value'";
   		$result = mysql_query($query) or die('Error with query: ' . mysql_error());
   		if ($result == 0){ echo "Row $value not found. Click back button and try again<br>."; }
   		else{ echo "Row deleted successfully!! To delete another, just click the back button<br>."; }
}
}
?>

 

This should work but obviously havent tested it, let me know if there are any problems

 

Ive also tidied up your code a little.  In the If/else statement after you executed your query, you had two else{} statements.  Your second one was unnecessary as you already have the mysql_error() echoed in the die statement.  Also, if you have more than one else option, you need to write like this:

 

<? 
if($something == true){
// do this
}
elseif($someOtherThing == true){
// do some other thing
}
else{
// do yet another thing
} ?>

[11Tami]

Perfect, I love it. I'll send you something.

[Glyde]

I would HIGHLY discourage the use of nested queries like this.  For such a simple operation, it is simple to delete all of the rows with one query.  The way you seem to have itcauses a lot of overhead and server stress.

[11Tami]

What part of it don't you like?

[Glyde]

The fact that you have a query inside a for loop that could potentially be very long.  Take advantage of the MySQL "IN" clause, something like:

<?php
$id = $_GET['id'];

// Make sure it's only numbers and commas
$tmpArray = explode(",", $id);
foreach ($tmpArray as $key=>$item)
     if (!is_int($item)) unset($tmpArray[$key]);
$id = implode(",", $tmpArray);

// Query the DB
$query = "DELETE FROM `addid` WHERE `id` IN ($id)";
$result = mysql_query($query) or die('Error with query: ' . mysql_error());
?>

Also, having an "or die" in your mysql_query command while also putting an if ($result) just below it, is absolutely ridiculous considering how redundant it is.  They do the exact same thing.  I think what you would want it to check mysql_affected_rows.  That will tell you how many were deleted by your query.  Using ($result) just tells you if the query was sent to the DB and it had no problems, it won't tell you if it actually did anything.