Deleting / MODIFYING data from MYSQL

[All4172]

Could anyone point me to a good tutorial on using the DELTE / UPDATE features of MYSQL?  Basically I'm doing a DB pull, it shows stuff like the ID #, FIELD1, and FIELD2...What I'm now trying do is to put in a link say on the left side, so if I click the link it will delete the ID# FIELD1 and FIELD2 (without clicking delete 3 seperate times).

I know to delete one field I would do something like:

[code]
mysql_query("DELETE FROM table WHERE field1='something'")
[/code]

[redarrow]

Sometimes we may wish to get rid of records from a table. To do so, we can use the DELETE FROM command. The syntax for this is

DELETE FROM "table_name"
WHERE {condition}

It is easiest to use an example. Say we currently have a table as below:

Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999


and we decide not to keep any information on Los Angeles in this table. To accomplish this, we type the following SQL:

DELETE FROM Store_Information
WHERE store_name = "Los Angeles"

Now the content of table would look like,

Table Store_Information
store_name Sales Date
San Diego $250 Jan-07-1999
Boston $700 Jan-08-1999

[All4172]

Thanks for that, that does answer alot of the questions I have. 

Lets say I want to put a link that when clicked it would do the deletion as set forth in your post.  Would I do it like:

[code]
$delete = DELETE FROM Store_Information WHERE store_name = "Los Angeles"
echo '<a href="/script.php?delete=1">Delete</a>
[/code]

Or how would I word it?

[.josh]

DELETE FROM tablename WHERE columnname IN (value1, value2, value3, ...)

[All4172]

[quote author=Crayon Violent link=topic=99348.msg391301#msg391301 date=1151986422]
DELETE FROM tablename WHERE columnname IN (value1, value2, value3, ...)
[/quote]

So how would I put that in a link form, so all I would need to do is to click the link to delete if I wanted to delete?

[.josh]

what does your form look like? are the checkboxes created dynamically? what are you using for the name="??"  is it an array like this? name="list[]" ?

if so, then you can do this:

[code]
<?php
  $list = implode(',',$_POST['list']);
  $sql = "DELETE FROM tablename WHERE id IN ($list)";
  mysql_query($sql);
?>
[/code]

[All4172]

[quote author=Crayon Violent link=topic=99348.msg391336#msg391336 date=1151997906]
what does your form look like?
[/quote]

Well right now I am just pulling all the entries.  My database pull right now displays ID KEYWORD TERM and here is the code:

[code]
<?php

$db_host  = 'localhost';
$db_user  = 'root';
$db_pass  = 'pw';
$db_name  = 'table1';

$db_table = 'joa';

$conn = mysql_connect($db_host,$db_user,$db_pass);

if ($conn == true) {

  mysql_select_db($db_name,$conn); 

  $result = mysql_query("SELECT * from $db_table",$conn);

        $tmp .= "ID  : $row->ID  <br>\n"; 
        $tmp .= "Keyword : $row->keyword <br>\n";   
        $tmp .= "Definition  : $row->definition  <br><hr>\n";   

    }

} else {
    echo 'could not connect to database : '. mysql_error();
}

print $tmp;

$_GET['ID'];

mysql_close( $conn );

?>
[/code]

What I'm now trying to do is say to add a DELTE link say above ID or beside it, so if I want to, I can just click delete and BAM that whole entry is gone :)

[redarrow]

$id=$_GET['ID'];   

$sql = "DELETE FROM tablename WHERE id ='$id'";

[All4172]

[quote author=redarrow link=topic=99348.msg391381#msg391381 date=1152011115]
$id=$_GET['ID']; 

$sql = "DELETE FROM tablename WHERE id ='$id'";
[/quote]

So wuld that mean that I would put:

[code]
<a href="$sql">Delete</a>
[/code]

Or did I totally wiff on that one?

[redarrow]

<?php

$db_host  = 'localhost';
$db_user  = 'root';
$db_pass  = 'pw';
$db_name  = 'table1';

$db_table = 'joa';

$conn = mysql_connect($db_host,$db_user,$db_pass);

if ($conn == true) {

  mysql_select_db($db_name,$conn); 

  $result = mysql_query("SELECT * from $db_table",$conn);

        $tmp .= "ID   : $row->ID   <br>\n";   
        $tmp .= "Keyword : $row->keyword <br>\n";     
        $tmp .= "Definition  : $row->definition  <br><hr>\n";   

    }

} else {
    echo 'could not connect to database : '. mysql_error();
}

print $tmp;

$id=$_GET['ID'];

echo"<a href='delete_data.php?&id=$id&del=deleted'>Delete data</a>";

mysql_close( $conn );

?>


delete_data.php

<?

database connection

if($_GET["del"]=="deleted") {

$sql = "DELETE FROM tablename WHERE id ='$id'";
$resul=mysql_query($sql);

header("location: whatever_page_to_send_user.php");

}else{

echo"sorry no data deleted";

}
?>

[All4172]

Thanks for the help.  I'll play around with it for a bit now :)

[redarrow]

any time mate good luck.

[.josh]

[b]redarrow[/b] your code does nothing to make checkboxes for him to check several at once and delete at the same time.

[code]
<?php

$db_host  = 'localhost';
$db_user  = 'root';
$db_pass  = 'pw';
$db_name  = 'table1';

$db_table = 'joa';

$conn = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$conn); 

//if submit button was clicked and there is something to delete
if ($_POST['submit'] && $_POST['delete_list']) {
  $delete_list = implode(',',$_POST['delete_list']);
  $sql = "DELETE FROM $db_table WHERE ID IN ($delete_list)";
  mysql_query($sql);
}

//select everything from table
$sql = "select * from $db_table";
$result = mysql_query($sql);

//example form
$form = "<form action = '{$_SERVER['PHP_SELF']}' method = 'post'>";
while($info = mysql_fetch_array($result)) {
  $form.= "<input type='checkbox' name='delete_list[]' value='{$info['ID']}'> delete<br>";
  $form.= "ID : {$info['ID']} <br>"; 
  $form.= "Keyword : {$info['keyword']} <br>";
  $form.= "Definition  : {$info['definition']}<br><br>";   
}
$form.="<input type='submit' value='delete' name='submit'>";

echo $form;

?>
[/code]

[All4172]

I ended up adjusting it to semi-work like this:

[code]
<?php

$db_host  = 'localhost';
$db_user  = 'root';
$db_pass  = 'pw';
$db_name  = 'table1';

$db_table = 'joa';

$conn = mysql_connect($db_host,$db_user,$db_pass);

if ($conn == true) {


  mysql_select_db($db_name,$conn); 


  $result = mysql_query("SELECT * from $db_table",$conn);

    while($row = mysql_fetch_object($result)) {


        $tmp .= "<a href='/mysql/delete_data.php?&ID=$row->ID&del=deleted'>Delete</a><br>\n";
        $tmp .= "ID  : $row->ID  <br>\n"; 
        $tmp .= "Keyword : $row->keyword <br>\n";   
        $tmp .= "Definition  : $row->definition  <br><hr>\n";   

    }

} else {
    echo 'could not connect to database : '. mysql_error();
}

print $tmp;

$_GET['ID'];

mysql_close( $conn );

?>

DELETE_DATA.PHP

<?php

$db_host  = 'localhost';
$db_user  = 'root';
$db_pass  = 'pw';
$db_name  = 'joa';

$db_table = 'joa';

$conn = mysql_connect($db_host,$db_user,$db_pass);

$id = $_GET["ID"];
if($_GET["del"]=="deleted") {

$sql = "DELETE FROM joa WHERE ID='$id'";
$result=mysql_query($sql);

header("location: /index.php");

}else{

echo"sorry no data deleted";

}

mysql_close( $conn );

?>
[/code]

The table displays everything how I want it to and I've verified the ID=$id points to the right ID tag.  Also when clicking on it, the page does load at the new header location so I know its going through that.  However when I click delete button, nothing from the database is deleted.  I've check that the table has the correct name and so forth.  Is there anything obviously wrong with the above that would make it go through:

[code]
$id = $_GET["ID"];

if($_GET["del"]=="deleted") {

$sql = "DELETE FROM joa WHERE ID='$id'";
$result=mysql_query($sql);

header("location: /index.php");
[/code]

But not do the DELTE function, but obeying the new header location?

[SharkBait]

Not sure if anyone showed you how to use an UPDATE statement.....

[code]
<?php
$myID = 34;  // Some value in the table that is unique to that line item

//  The UPDATE query as a string - Easier to troubleshoot
// myTable - Name of the table you wish to do the update in
// myName, myLocation, myAge - field names within MyTable
// id - unique field identifer

$strQry = "UPDATE myTable SET myName = 'Batman', myLocation = 'BatCave', MyAge = '282' WHERE id = '{$myID}'";

// Execute the query, kill the script if the query errors. Display such error.
$query = mysql_query($strQry) or die("MySQL Error: <br /> {$strQry} <br />". mysql_error());

// Get a value for the update that was executed
$num = mysql_affected_rows();


if ($num > 0) {
  echo "Line Item has been Updated";
} else {
  echo "There was an error with your submission";
}
?>
[/code]

[All4172]

Thanks, I will defantly be using that code once I get the DELETE function fully working :)

Any ideas on why my delete isn't working? :(

[SharkBait]

When you do $row->ID in an echo by itself. Does it show up properly?

Try putting them in curly braces like:

<a href='/mysql/delete_data.php?&ID={$row->ID}&del=deleted'>Delete</a>

Also on your query set it up like

[code]
<?php
$sql = "DELETE FROM joa WHERE ID='{$id}'";
$result=mysql_query($sql) or die("I ERRORED: <br /> ". mysql_error());
?>
[/code]
That way if you are getting a MySQL error, it will print it out to the screen.

[All4172]

[quote author=SharkBait link=topic=99348.msg391577#msg391577 date=1152041897]
When you do $row->ID in an echo by itself. Does it show up properly?
[/quote]

Because the original way, in the browser window it would show up as "&id=&del=deleted".  With me putting in $row->ID it now shows up as &id=2&del=deleted. 

Thanks for the advise about displaying the error.  By doing that it revealed I failed to put in:

[code]
mysql_select_db($db_name,$conn);
[/code]

So ultimately it was not connecting to a DB so it couldn't delete :)