Hi everybody,
I want to ask something and I will appreciate any answer. I want to display data from my database in a table which will have an extra "Delete" column which enables user to delete each record. The "Delete" option can be a button which will delete the specific record from the database. What I want to know is if it possible for you to describe me the whole procedure in order to make this thing on my own. Is this an easy task or not? If you have any links which may help me with that it would be great for me!
Thanks a lot
Display data from db
Moderator: General Moderators
- phazorRise
- Forum Contributor
- Posts: 134
- Joined: Mon Dec 27, 2010 7:58 am
Re: Display data from db
I assume you know how to pull data from database and display it on a page.
Your table mush contain a field that will uniquely identify a record. This field will be used to delete that particular record.
So while fetching data, pull out this unique field also. After you got this unique field, you can pass it another page that will delete the record that matches to this passed variable.
Here's piece of code that will give you an idea
above code will display the database contents in tabular form.
and to delete put this in another file which will delete record-
Unique field can be primary key with auto increments such as ID. Depends on your table schema.
Actually, this is rough code just to give you an idea.
You should implement it using class and objects. Filter the query and data passed through query before executing it.
As well as if you know javascript, use Ajax for deleting records.It would be much better and will reduce page redirects.
Your table mush contain a field that will uniquely identify a record. This field will be used to delete that particular record.
So while fetching data, pull out this unique field also. After you got this unique field, you can pass it another page that will delete the record that matches to this passed variable.
Here's piece of code that will give you an idea
Code: Select all
//setup connection to mysql first
// to display the data
$sql="select unique_field,field1,field2 from table_name";
$result=mysql_query($sql) or die('Error'.mysql_error());
if(mysql_num_rows($result)>0){
echo '<table>';
echo '<tr><th>Column Heading 1</th><th>Column Heading 2</th><th> Operation Delete</th></tr>';
while($row=mysql_fetch_assoc($result)){
echo '<tr><td>'.$row['field1'].'</td><td>'.$row['filed2'].'</td><td><a href="delete_file.php?feed='.$row['unique_field'].' ">Delete</a></td></tr>';
}
echo '</table>';
}
else{
echo 'Nothing to display';
}
mysql_close();
and to delete put this in another file which will delete record-
Code: Select all
if(isset($_REQUEST['feed'])){
$feed=trim($_REQUEST['feed']);
$feed=mysql_real_escape_string($feed); // to use this function you need to set up connection with mysql first.
$sql="delete from comment where unique_filed=$feed";
$result=mysql_query($sql) or die('Error'.mysql_error());
if($result){
echo 'Record Deleted Successfully';
}
mysql_close();
}
else{
echo 'Nothing to display';
}
Actually, this is rough code just to give you an idea.
You should implement it using class and objects. Filter the query and data passed through query before executing it.
As well as if you know javascript, use Ajax for deleting records.It would be much better and will reduce page redirects.
Re: Display data from db
Here is more code to play with. It's a stand-alone demo that uses the session as a mock database. It features buttons for deleting rows and restoring the database. It also demonstrates an effective way to filter user input.
Code: Select all
<?php
header('Content-Type: text/html; charset=UTF-8');
// Simulates a database connection
session_start();
// A cheap, unreliable solution to finding the valid id range
define('MIN_RECORD_ID', 1);
define('MAX_RECORD_ID', 5);
// On first run, initializes a mock database.
// For a real database, this would not be necessary.
if (!isset($_SESSION['records'])) {
restoreRecords();
}
function restoreRecords () {
// Loads some sample rows into the mock database
$_SESSION['records'] = array(
0 => array('id' => '1', 'name' => 'aaa'),
1 => array('id' => '2', 'name' => 'bbb'),
2 => array('id' => '3', 'name' => 'ccc'),
3 => array('id' => '4', 'name' => 'ddd'),
4 => array('id' => '5', 'name' => 'eee'),
// If you add records, remember to update MAX_RECORD_ID.
);
}
function getRecords () {
// Equivalent of fetching all rows from the database
return $_SESSION['records'];
}
function deleteRecord ($id) {
// $id is assumed to be query-safe.
// Searches for the matching record and deletes it
foreach ($_SESSION['records'] as $r => $record) {
if ($record['id'] == $id) {
// Removes the row from $_SESSION['records']
unset($_SESSION['records'][$r]);
break;
}
}
// For a real database, deleting a record would be accomplished
// with a DELETE query, not by looping through the rows.
}
// Handles form submission
if ($_POST) {
// Cleans user input. See the manual.
$_POST = filter_input_array(INPUT_POST, array(
'action' => array(
'filter' => FILTER_VALIDATE_REGEXP,
'flags' => FILTER_REQUIRE_SCALAR | FILTER_NULL_ON_FAILURE,
'options' => array(
// Acceptable actions are "delete" and "restore".
'regexp' => '/\A(delete|restore)\z/i',
),
),
'id' => array(
'filter' => FILTER_VALIDATE_INT,
'flags' => FILTER_REQUIRE_SCALAR | FILTER_NULL_ON_FAILURE,
'options' => array(
'min_range' => MIN_RECORD_ID,
'max_range' => MAX_RECORD_ID,
),
),
));
// $_POST items should be query-safe at this point.
// isset() tests if key exists and value is not null.
if (isset($_POST['action'])) {
switch (strtolower($_POST['action'])) {
case 'restore' :
restoreRecords();
break;
case 'delete' :
if (isset($_POST['id'])) {
deleteRecord($_POST['id']);
}
break;
}
} else {
die('Unauthorized POST');
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Demo</title>
</head>
<body>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php foreach (getRecords() as $record) : ?>
<tr>
<td><?php echo $record['id']; ?></td>
<td><?php echo $record['name']; ?></td>
<td>
<form action="" method="post">
<input type="hidden" name="id" value="<?php echo $record['id']; ?>" />
<input type="submit" name="action" value="delete" />
</form>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<form action="" method="post">
<p><input type="submit" name="action" value="restore" /></p>
</form>
<pre><?php echo '$_POST => ', htmlentities(print_r($_POST, true), ENT_QUOTES, 'UTF-8'); ?></pre>
</body>
</html>