Jump to content

Archived

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

All4172

Deleting / MODIFYING data from MYSQL

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]

Share this post


Link to post
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

Share this post


Link to post
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?

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
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 :)

Share this post


Link to post
Share on other sites
$id=$_GET['ID'];   

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

Share this post


Link to post
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?

Share this post


Link to post
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";

}
?>

Share this post


Link to post
Share on other sites
Thanks for the help.  I'll play around with it for a bit now :)

Share this post


Link to post
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]

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
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? :(

Share this post


Link to post
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.


Share this post


Link to post
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 :)

Share this post


Link to post
Share on other sites

×

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.