Help with "Delete from" .$dbname "Where ID ="....

[Pako]

I am very new to this whole PHP/MySQL relationship, but managed to create my first Database, and can even bring up some of the date, as well as add additional records through the use of a form. My problem is trying to figure out, or more so, how to code a "Delete" link which can sit next to each record in the table to allow a admin user to simply hit the "Delete" link and the page would refreash with that particular record gone.

Here is the code I have so far for the dynamic table that's pulling data from my 'event_dates' table.

<table width="90%" border="0" align="center" cellpadding="0" cellspacing="1">
  <tr bgcolor="#000000"> 
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">Record 
      ID</font></td>
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">Event 
      Date</font></td>
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">Venue</font></td>
    <td width="200"><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">Description</font></td>
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">City</font></td>
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">State</font></td>
    <td><font color="#999999" size="2" face="Arial, Helvetica, sans-serif">Starting 
      Time</font></td>
    <td><font color="#999999">Remove</font></td>
  </tr>
  <?php do { ?>
  <tr bgcolor="#CCCCCC"> 
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['counter']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['event_date']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['venue']; ?></font></td>
    <td width="200"><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['description']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['city']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['state']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><?php echo $row_all_records['event_time']; ?></font></td>
    <td><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><a href="update2.php"></a></font></td>
  </tr>
  <?php } while ($row_all_records = mysql_fetch_assoc($all_records)); ?>
</table>

The uploaded file can be found here:

www.blueonion.net/php/private/update2.php

Any and all help would be greatly appreciated. I have spent a week for each extra moment I have combing the web for answers with no result.

 

[GilesGuthrie]

There are two basic ways that you can do this. The first (not at all secure) is to put a "Delete" link on each record, so add a table column and put:

a href="delete.php?id=<?php echo $row_all_records['RecordID']; ?>">Delete</a>

Then in your file "delete.php" you'd have:

$deleted = mysql_query("DELETE FROM TableName WHERE RecordID = '".$id."' LIMIT 1");

The reason that this isn't secure is that it's too easy to see how the code actually deletes records, and therefore malicious users could use this to delete lots of stuff. This would only work if the delete.php file was in a protected directory on the web server.

The second way would be to put a checkbox on each line of the table. You could have PHP name the checkboxes for you, like this:

<input type="checkbox" value="1" name="delete<?php echo $row_all_records['RecordID']; ?>">

Then you would have a single "Delete" button somewhere on the page. Obviously you're using a form here and will need to configure the page accordingly.

Now, you use the array that gets passed from a form to a handler, called $HTTP_POST_VARS. If you use a do...while loop to step through this array, you can get the variable name and value from the form, in a stepwise manner. Check it out:

while(list($Key, $Val)= each($HTTP_POST_VARS)) {
	if($Key == "Submit")
	{
		break;
	}
	echo "Key = ".$Key.", Value = ".$Val."<br>";
	$deleted = mysql_query("DELETE FROM TableName WHERE RecordID = '".$Key."' LIMIT 1");
}

The list($Key, $Val) = each($HTTP_POST_VARS) command takes each posted variable, and sets $Key to be the variable name (i.e. "deletexx") and $Val to be the value of the variable. This will work for any posted form, but the beauty here is that if you do not check a checkbox, then HTML will not pass the checkbox parameters to the form.

If you wish, you could test this before deleting from the database, but it's not necessary - the above code snippet is taken from a working file on giles-guthrie.com.

The if($Key == "Submit") test allows the script to break out of the loop before it tries to parse the Submit button itself.

This way is more secure because all the code that does the deletion is passed as form variables. It's still not magically secure, but it will most likely do.

Oh, and always put "LIMIT 1" at the end of a MySQL deletion query - it helps prevent faulty SQL logic in the rest of your query from wiping out the database!

 

[Pako]

Thanks so much for the detailed post GG! I know how busy you are and greatly appreciate such a detailed explaination. Now if I could just skip work so I could go home and test this little puppy out.

I'll definately let you know as things progress.



BTW - I do have your suggested book on order "PHP/MySQL", as none of the stores in our modest little town carry any PHP/MySQL reference material. I'm really hoping it has n00b howto materials in it.

 

[Pako]

I got it working! YeeHaa.....

It will reside in a directory called 'Private' that is part of the 'Admin' group and is now password protected so I opted for the first example. Now I just need to put in some HTML and a redirect back to the update2.php file and I'll be set!

You Da Man!

On a side note, do you use connect() or pconnect()? I thought that with pconnect() it would keep a db connection open specific to each user. Well, it's just me doing this stuff and I have 'A LOT' of open connections that I have to manually KILL in MySQL. I'm assuming that theses processes are taking up system resources so I need to stay on top of it....

again!

 

[GilesGuthrie]

I use pconnect().

However, I do also tend to use "footer" files, all of which have a mysql_close() at the end.

 

[Acid X]

Theres also an easierway to echo out something without the whole "echo" statement.

<?=$var?>

Really simple and easy to remember.

 

[Pako]

Have you ran accross any good tutorials on uploading images in conjunction with other field data that would need to be uploaded as well?

Then displaying the records, while resizing the image incase it's above the max size you'd want on your site?

I finally got my 'delete' to work with GG's help.

username: test
pw: test

www.blueonion.net/php/private/update.php

Let's assume that I also wanted to upload a image to a predefined directory, and have the db just reference to the filename/location for later retrievel...

Is such an animal possible?

 

[GilesGuthrie]

Originally posted by Pako
Have you ran accross any good tutorials on uploading images in conjunction with other field data that would need to be uploaded as well?

Then displaying the records, while resizing the image incase it's above the max size you'd want on your site?

I finally got my 'delete' to work with GG's help.

username: test
pw: test

www.blueonion.net/php/private/update.php

Let's assume that I also wanted to upload a image to a predefined directory, and have the db just reference to the filename/location for later retrievel...

Is such an animal possible?

Well the example that I used (which I sent you the source code for) uploads several form fields to the form handler.

Having read the various documents that exist on the subject, I would think that you would want to create a record in the database. You would be wanting to use an ID field in the database with Primary, Auto_increment on it, so that each record would get a unique ID.

Then you'd do a mysql_query to get the last ID from the database, which will be the one that contains your just-uploaded information:
$recordid = mysql_query("SELECT ID FROM TableName, WHERE 1 ORDER BY ID desc LIMIT 1");
then you'd create the filename as:
$filename = "file".mysql_result($recordid, 0, "ID");

But I haven't yet done any research on copying files up to the server. I'm beginning to think that the whole copying thing might not be the best plan though, and that it might be easier to stream the data in from the file, and then write a new file to the server, streaming the data back out again. This should circumvent the need for the client to have write permissions to the directory. Again, this is an unsubstantiated statement, so please don't hang me if it's wrong!!

Oh, and welcome Acid X to the PHP forum. It's great to have another coder around! Pako and I have been working together over E-mail, PM, IM and thread posts, it'll be good to bring another person in!

 

[Acid X]

Err.. GG, i've been here a while.. Its just not been very active.

Apparently you didnt know i got a name change, as i used to be gt2_Gs.

Pako, as far as the file storage, you could store it in a directory on your website, or you could store files straight into your database. The only problem i see with putting them on the database is that it could make it rather large, depending on the picture sizes.

Since youre looking to just upload a file to the server, a good tutorial can be found here. It also explains how you can have yoru script resize the image on upload.

Hopefully this helps you out.. It's been a while since i've worked with php, because i havent been around much lately. If i had been around, i would definitely explain to you how to do it, but i just cant remember at the moment.

Anyway, enjoy!

 

[Pako]

Hey,

Thanks Acid X (better known as gt2_gs ) for the heads up! I'll give that a shot tonight!

 

[GilesGuthrie]

Originally posted by Acid X
Err.. GG, i've been here a while.. Its just not been very active.

Apparently you didnt know i got a name change, as i used to be gt2_Gs.

I remember now! Anyway, it's cool to see this forum active again.

Originally posted by Acid X
Pako, as far as the file storage, you could store it in a directory on your website, or you could store files straight into your database. The only problem i see with putting them on the database is that it could make it rather large, depending on the picture sizes.

We were chatting about the different file sizes in another thread. Seems that the received wisdom is to put small files into the database, but if the files are large, then they should go into the server's filesystem.