Display data from db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tsalaki
Forum Newbie
Posts: 19
Joined: Sun Jan 23, 2011 11:54 am

Display data from db

Post by tsalaki »

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
User avatar
phazorRise
Forum Contributor
Posts: 134
Joined: Mon Dec 27, 2010 7:58 am

Re: Display data from db

Post by phazorRise »

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

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();
above code will display the database contents in tabular form.
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';  
                }		
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.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Display data from db

Post by McInfo »

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>
Post Reply