Jump to content


Photo

Deleting / MODIFYING data from MYSQL


  • Please log in to reply
17 replies to this topic

#1 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 03 July 2006 - 09:44 PM

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:

mysql_query("DELETE FROM table WHERE field1='something'") 


#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 03 July 2006 - 09:55 PM

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


Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 12:10 AM

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:

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

Or how would I word it?



#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 04:13 AM

DELETE FROM tablename WHERE columnname IN (value1, value2, value3, ...)
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 06:51 AM

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


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?

#6 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 07:25 AM

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:

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

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#7 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 10:57 AM

what does your form look like?


Well right now I am just pulling all the entries.  My database pull right now displays ID KEYWORD TERM and here is the 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 );

?> 

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 :)

#8 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 04 July 2006 - 11:05 AM

$id=$_GET['ID'];   

$sql = "DELETE FROM tablename WHERE id ='$id'";
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#9 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 11:12 AM

$id=$_GET['ID']; 

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


So wuld that mean that I would put:

<a href="$sql">Delete</a>

Or did I totally wiff on that one?

#10 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 04 July 2006 - 11:24 AM

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

}
?>
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#11 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 11:33 AM

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

#12 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 04 July 2006 - 11:46 AM

any time mate good luck.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#13 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 05:59 PM

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

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

?>

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#14 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 06:39 PM

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

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

?>

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:

$id = $_GET["ID"];

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

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

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

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

#15 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 04 July 2006 - 06:49 PM

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

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


#16 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 07:06 PM

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? :(

#17 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 04 July 2006 - 07:38 PM



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

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




#18 All4172

All4172
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 July 2006 - 07:59 PM

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


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:

mysql_select_db($db_name,$conn);

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users