Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Delete MySQL Field - HTML Table

mysql

  • Please log in to reply
8 replies to this topic

#1 Zizzy

Zizzy

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 102 posts

Posted 26 February 2012 - 02:58 PM

Hello all of CodeCall reading this;

Introduction
First of all, I'd like to apologize if this is a tutorial that may have already been posted in the past, for I have not read back through the pages. If so, in my defense, it may be good to have another recent post for this, for new users not having to search/go through many, many pages. This is also my first tutorial, so I hope it's easy to understand and it helps if you were looking for something like this, or it would be of use to you. :) NOW, on to the tutorial:

Notepad and Notepad++ - Your choice
First off, I'd recommend downloading the latest version of Notepad++ so you can follow along with the tutorial, and not just copy/paste it into your page. Doing it this way, you will learn to write PHP - not learn how to copy and paste other code.

If you'd rather not download and install, you can do this in Notepad ( XP: Start > Run > Notepad, Vista+: Start -> Search bar -> Notepad). If you'd like to use Notepad++, you can download it from the official website here, by clicking this link.

On To The Code
As we go through the code, I will explain what I am doing, use comments in the code, explaining and breaking down what every piece does :)

Connect to the database (database.php)

<?php
$connect = mysql_connect("localhost","db_user","dbpassword"); // this is selecting which database you want to use, with a variable (for later in the code), and using the mysql_connect function to let the code know you want to connect

if (!$connect) // using and if statement to see if the connection is incorrect, and display a mysql_error() message, which lets the user know what the issue is, and then exit the code with die()
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("database_name", $connect); // this is selecting the database itself from $connect... which is the variable we created at the beginning of the script... make sure you replace "database_name" with your database, as well as the information provided in $connect

// finally, we end the PHP script for database.php
?>


display.php - displaying the results

<?php
include("database.php"); // include the database file with the include() function.... make sure if you make an includes folder (for those who do) to set the path: include("/path/database.php");

$infoGrab = mysql_query("SELECT * FROM your_table"); // this is taking everything (*) from whichever table you want, from the selected database in database.php, which we included.

echo "<p><b>Information to Delete</b></p>"; // displaying a title (optional), above the table
echo "<table border = '1' cellpadding = '10'>"; //starting off the table
echo "<tr><b><i><th><font color='purple'>Field One:</font></b></i></th> <th><b><i><font color='blue'>Field Two:</font></b></i></th> <th><b><i><font color='green'>Field Three:</font></b></i></th> <th><b><i><font color='red'>Delete File</b></font></i></th></tr></b></i>"; // this is setting the table labels (above the data), with style/color (<b>, <i>, <font> are optional tags)

while ($Row = mysql_fetch_array($infoGrab)) { //while variable $Row = getting data from $infoGrab, which was selecting everything from your selected table
$ID = $Row['id']; // use the ID (should be PRIMARY and AI - auto increment) for specific deleting
$secondVariable = $Row['field_two']; // the second field and variable (after the ID variable)
$thirdVariable = $Row['field_three']; //the third field
$fourthVariable = $Row['field_four']; // the fourth variable

echo "<tr>
<td><b><i><font color='purple'>{$secondVariable}</font></b></i></td> // showing the field after ID
<td><b><i><font color='blue'>{$thirdVariable}</font></b></i></td> //showing the next field
<td><b><i><font color='green'>{$fourthVariable}</font></b></i></td>"; //showing the next field
echo "<td><a href='deleted.php?id='{$ID}'><img src='images/delete.png' width='25' height='25' /></a></td>"; //showing the delete button (feel free to use the delete icon attached, or use plain text instead of <img src>

}

//end the script
?>


deleted.php - deleting the item

if (isset($_GET['id']) && is_numeric($_GET['id'])) { // if the ID is set (isset) and is a number (is_numeric)
$ID = $_GET['id']; // variable ID is getting the ID from the form

$Delete = mysql_query("DELETE * FROM table WHERE id='{$ID}'"); // deleting the specific ID ($ID)

// next you can pretty much echo (display) any successful delete message you want...

echo "<p>Deleted successfully.</p>"; // any message
echo "<p><a href='../index.php'>Return Home</a></p>"; // link or text optional


} else { //something goes wrong(?), display error
echo "<p>Error code #7</p>"; // you can use any error message or 'code' you want

// ending the if and else so we don't get a buggy unexpected $end
}
}

// ending the script
?>

Thanks, readers!
I hope you enjoyed the tutorial, and I hope it comes of use to what you may be looking for. :)

-Zizz


  • 1
-Zizz

#2 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 04 March 2012 - 10:41 AM

Not bad. Though in delete.php you need to be really careful with validating what you are deleting. In the current code the user can delete ANYTHING just by changing the id number. This is a security flaw, that people could use to really mess up your website by deleting ALL YOUR CONTENT.

Also from a database design perspective, most of the time you shouldn't be deleting data unless it is temporary data, like autosaves (like in the codecall forum posts). Instead you should just have a bit field IsDeleted that you filter on and update when deleting. This lets you maintain data for integrity and auditing purposes. What if you want to confirm that one of your users said they did something when they said they didn't? If the data is gone you cannot confirm this. If you keep the data (and have a DateDeleted field) you can verify what your user did. It's for a just in-case scenario.
  • 0

#3 Zizzy

Zizzy

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 102 posts

Posted 04 March 2012 - 10:52 AM

Thanks for the input, chili5. However, on mine I do have a permissions setting, so that only administrators can even access that page. If no access (with $_SESSION), nothing can happen. Fortunately, there are no other administrators, except my father whom I am developing the website for.

I was actually debating whether or not to put in the level check for the tutorial, but I didn't want to include setting up a database for the users in this tutorial. My code looks (something) like this:


$level = $_SESSION['level_field'];

if ($level < 4) {
echo "<p>You do not have access.</p>";
} else {
// code continues
}
?>


Anything you can think of to help with this part? Maybe a security issue? Thanks for the input.

As for the delete, that DeleteData field is a good idea - I may add that in. :)

(extra: the level permission is on the form (display.php))

-Zizz
  • 0
-Zizz

#4 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 04 March 2012 - 11:00 AM

I'm not familar with security issues associated with sessions.

But if that page is only accessible to administrators you should make sure that there is no possible way for a non-administrator to accidentely access the page.

The level check might be an interesting tutorial you could write at some point. ;) What would be interesting to mess around with is making a user-customizable permissions system.

Does the delete.php also include the level check? So that someone doesn't try to bypass the display page.

And yes, the DeleteDate and IsDeleted fields are a good idea. It is VERY rare when you should actually use DELETE (unless it's temporary data) that you can live without.
  • 0

#5 Zizzy

Zizzy

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 102 posts

Posted 04 March 2012 - 11:57 AM

Ah, alright.

And that is (in a way) a user-customizable permission system, because it has multi-levels. As well as the administrator can update, change, delete, etc) members, as well as promote to Member, Student, Teacher, Family Member or Administrator - if that's what you mean. If not, what do you mean by user-customizable?

Yes, the delete.php has the same permissions on it - so someone who isn't an administrator cannot access any page to delete anything (even if changing the ID in the URL).

It is temporary data, technically. All that I am deleting for my father's website (him deleting, mostly) is his little two man, folk music band he plays with the neighbor, as well as for his classes he teaches at CU Boulder (in Colorado), where he uploads word documents, pdf's, etc).

The rest of the deleting (on a separate page) is for images for family photos, which only the Family Member permissions can access (not CU Student or CU Teacher).

So pretty much none of it has to stay in the database if it's just him deleting... but it still is a good idea just in case.

-Zizz
  • 0
-Zizz

#6 Imless

Imless

    CC Regular

  • Member
  • PipPipPip
  • 44 posts
  • Location:CDOC, Philippines
  • Learning:C, Java, C++, C#, PHP, (Visual) Basic, JavaScript, PL/SQL, Visual Basic .NET, Transact-SQL, VBScript

Posted 18 May 2012 - 12:31 PM

Great tutorial, cleared my confusions! But is it possible in deleting there is only one button for the delete and then datas retrieve from database to the table will have links. Can have it sir? So the basis is the link itself. :laugh:
  • 0

#7 Imless

Imless

    CC Regular

  • Member
  • PipPipPip
  • 44 posts
  • Location:CDOC, Philippines
  • Learning:C, Java, C++, C#, PHP, (Visual) Basic, JavaScript, PL/SQL, Visual Basic .NET, Transact-SQL, VBScript

Posted 21 May 2012 - 12:30 PM

Can you have confirmation dialog code for the deletion sir? xD
  • 0

#8 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 21 May 2012 - 01:00 PM

But is it possible in deleting there is only one button for the delete and then datas retrieve from database to the table will have links. Can have it sir? So the basis is the link itself. :laugh:


Not sure what you mean?

Can you have confirmation dialog code for the deletion sir? xD


Sure, this is simple. When you click on 'delete' link to a page that asks the user if they want to delete the item... and if they click 'yes' the item will be deleted. If they click 'no' the item is not deleted.
  • 0

#9 Imless

Imless

    CC Regular

  • Member
  • PipPipPip
  • 44 posts
  • Location:CDOC, Philippines
  • Learning:C, Java, C++, C#, PHP, (Visual) Basic, JavaScript, PL/SQL, Visual Basic .NET, Transact-SQL, VBScript

Posted 21 May 2012 - 01:33 PM

Not sure what you mean?



Sure, this is simple. When you click on 'delete' link to a page that asks the user if they want to delete the item... and if they click 'yes' the item will be deleted. If they click 'no' the item is not deleted.





Ok sir. Ahm can you have sample codes for the confirmation dialog sir? If it's okay. xD
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download