Edit, Delete records from a table

[WKevco]

Hi all.

I built an online finance tracking thingy for myself and I got it to work using the following database table and PHP code. I didn't write the PHP code, rather, I edited it to suit my needs.

It works great for my needs. I have a way to enter records and view the last 180 records in a table. The thing is, while viewing the table, I would lke to be able to edit and delete records. I don't know enough to be able to do this.

Can it be done? I realize my PHP code would probably, for the most part, be totally different.

Thanks for any help with this.

 

Here is my DB table structure:

DROP TABLE IF EXISTS `money`;
CREATE TABLE IF NOT EXISTS `money` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`type` varchar(18) NOT NULL,
`checking` decimal(5,2) NOT NULL DEFAULT '0.00',
`cash` decimal(5,2) NOT NULL DEFAULT '0.00',
`description` varchar(25) NOT NULL,
`who` varchar(25) NOT NULL,
`note` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

And here is the PHP:

<?php


$result = mysql_query ('SELECT date, type, checking, cash, who, description, note'
        . ' FROM money'
        . ' ORDER BY date DESC LIMIT 0,180')
or die(mysql_error());  

echo "<table border='1' cellpadding='5'>";


echo "<tr><th>Date</th><th>Type</th><th>Checking</th><th>Cash</th><th>Who?</th><th>Description</th><th>Note</th></tr>";

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['date'];
echo "</td><td>";
        echo $row['type'];
echo "</td><td>";  
echo $row['checking'];
echo "</td><td>"; 
        echo $row['cash'];
echo "</td><td>"; 
echo $row['who'];
echo "</td><td>"; 
        echo $row['description'];
        echo "</td><td>"; 
        echo $row['note'];
echo "</td></tr>";
} 

echo "</table>";
?>

 

 

[revraz]

Sure it can be done.

[seany123]

Firstly there is a couple of things i would suggest;

 

1. having a id value in your db table, make sure its auto increment and primary... this will give each row a unique number.

 

2. this is more of a general thing i do and its maybe not right, but instead of...

 

$result = mysql_query ('SELECT date, type, checking, cash, who, description, note'

        . ' FROM money'....blah blah blah

 

i would instead have SELECT * FROM money.....blah blah blah

 

 

Okay now to your question...

 

to delete rows you will first need to create a button or link...

 

html:

<a href="delete.php?id=" .$row['id']. "">Delete row</a>

 

php:

<?php
	if($_GET['id'])
	{
		//its always advised set all $_GETS and $_POSTS to variables.
                        $id = $_GET['id'];

                        //this is the mysql query which deletes the row, using the id of the row.
		$delete = mysql_query ("DELETE FROM money WHERE id='$id'");

                        //redirect to a location in this example i redirected to index.php
		header("Location: index.php");
	}
?>

 

i would highly suggest having other safe guards to stop other members deleting any row they life...

 

i would not suggest just copying this and using it live... but it gives you a example on a way to delete the rows.

 

 

 

i thought i would also give an example on how you can edit the information:

 

i dont know what information you wish to be able to edit but ill use description as an example.

 

HTML:

 

<form method="POST">
     <input type="text" name="description" value"" . $row['description'] . "">
     <input type="submit" name="edit" value="Edit">
</form>

 

PHP:

 

<?php
     if($_POST['edit'])
    {
     $POST['description'] = $description;

                        //this is the mysql query which updates the row, using the id of the row.
		$update = mysql_query ("UPDATE money SET description='$description'");
    }
?>

[Pikachu2000]

2. this is more of a general thing i do and its maybe not right, but instead of...

 

$result = mysql_query ('SELECT date, type, checking, cash, who, description, note'

        . ' FROM money'....blah blah blah

 

i would instead have SELECT * FROM money.....blah blah blah

 

It isn't right. The proper thing is to always explicitly name the fields in the query string. The only time I use a wildcard SELECT * is when I'm actually going to use the values from every field of every record that gets retrieved, and even then it's questionable.

[seany123]

2. this is more of a general thing i do and its maybe not right, but instead of...

 

$result = mysql_query ('SELECT date, type, checking, cash, who, description, note'

        . ' FROM money'....blah blah blah

 

i would instead have SELECT * FROM money.....blah blah blah

 

It isn't right. The proper thing is to always explicitly name the fields in the query string. The only time I use a wildcard SELECT * is when I'm actually going to use the values from every field of every record that gets retrieved, and even then it's questionable.

 

 

as i said i didnt think it was right its just generally my preference and it makes life a little easier (especially for beginners).

[Pikachu2000]

It's always easier to learn something the right way first, rather than learning the wrong way, then unlearning the wrong way and re-learning it the right way.

[seany123]

It's always easier to learn something the right way first, rather than learning the wrong way, then unlearning the wrong way and re-learning it the right way.

 

tbh, i don't see any major reasons to why select * is the wrong way, its more personal preference, there is no major vulnerability.

[JasonLewis]

I wouldn't say it's wrong, because it's not really. It's just not the preferred way of doing things.

[Pikachu2000]

SELECT * is a resource hog. Why would you want to tie up the memory with all of the fields from a table if the data isn't going to be used? It's even worse when the database and web servers are on two physically separate machines, and that data has to be sent over a network.

[Pikachu2000]

Anyhow, back to the original question. Yes that can be done fairly easily. With each record that's echoed from the DB when building your list, you can add a link to another script, and pass the record's primary key id, along with the action you want to take on the record. Then in that script, use those values to process the request.

 

For example you'd change your query string to also select the `id`field, and in your while() loop, add the links to the second script (alter,php, for argument's sake), with the variables appended to the URL.

echo "<td><a href=\"alter.php?action=delete&id={$row['id']}\">Delete</a> | <a href=\"alter.php?action=edit&id={$row['id']}\">Edit</a></td>";

 

In the alter.php script, read the incoming GET vars, and make the determination how to handle the request. An example of the logic would be:

<?php
$valid_actions = array('edit', 'delete'); // define the only 2 valid actions for the script

if( isset($_GET['action']) && in_array($_GET['action'], $valid_actions) ) { // validate that the action is valid with in_array()
$action = $_GET['action'];

if( isset($_GET['id']) && ctype_digit($_GET['id']) && intval($_GET['id']) > 0 ) { // validate that the id is set and is a number with a value > 0
	$id = (int) $_GET['id']; // assign $_GET['id'] to $id, cast as an integer

	if( $action == 'delete' ) {
		// execute a "DELETE FROM `money` WHERE id =$id LIMIT 1" query

	} elseif( $action == 'edit' ) {
		// Exexute a SELECT query and display all the values in a form using the query results as the value= attribute of the input tags.
		// when the form submits, and UPDATE query is executed.

	} else {
		echo "Action not valid.";
		// could also use a header() redirect here
	}

} else {
	echo "No valid record id found.";
	// Again, a header() redirect could be used
}

} else {
echo "No valid action to be taken was specified.";
// Again, a header() redirect could be used
}
?>

 

Have a go at it and if you get stuck on anything just say so.

[litebearer]

IF you are using this strictly for your own personal use, you might look into phpMyEdit

http://www.phpmyedit.org/

[WKevco]

IF you are using this strictly for your own personal use, you might look into phpMyEdit

http://www.phpmyedit.org/

Thanks, litebearer. For now, this is a big help.

Pikachu, thank you for your replies as well. I will study that code and learn from it.

I came to the right place!

This question has been solved.