Jump to content

Deleting / MODIFYING data from MYSQL


All4172

Recommended Posts

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]
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

[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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

[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 :)
Link to comment
Share on other sites

[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?
Link to comment
Share on other sites

<?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";

}
?>
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites



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.


Link to comment
Share on other sites

[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 :)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.