Using PHP to print out MySQL table with update and delete options.

J

justin82

Limp Gawd
Joined
Aug 8, 2004
Messages
404
Hello, I have a question involving mysql and php. I currently have the page where it will print out and format the contents of a table, However I would like to have update and delete buttons for each row. Currently the page prints out each row with the php echo command. The problem is, with that Im only able to just read the table and have no way of knowing what row each is to delete it.

for example
Code: 
Name     Address                 Phone
-----------------------------------------------------------------------------
John      43 Road, Atlanta     (xxx)xxx-xxxx  [delete]  [update]
Jane      23 Atlanta Road      (yyy)yyy-yyyy  [delete]  [update]

I guess the question is how can I have the delete and update buttons know what row they are supposed to operate on.

Forgive me if my question is worded a bit confusing or odd, Ive had a 37hr day and am just about to hit the sack.

Thanks for any help.
 
Well, each entry in your database table has to have a unique identifier, you use that identifier (encrypted/decrypted or something if you're a super security freak) as an argument in the delete link so the next page called knows what to delete.
ie.

2394734 John 43 Road, Atlanta (xxx)xxx-xxxx <a href=delete.php?row=2394734>[delete]</a> [update]
2347238 Jane 23 Atlanta Road (yyy)yyy-yyyy <a href=delete.php?row=2347238>[delete]</a> [update]

Where 2394734 and 2347238 would be the unique keys for the corresponding entries in the database. Obviously you dont have to print the number in the html page table output, just did that for the clarity of the data in the table.

Also, if you're assuming the phone numbers are unique in the table, you could use those as the unique identifier and save the key column space... but that could cause problems if you have 2 people as separate entries but with the same phone number.
 
Ok, I don't mean to jack the thread but I've got a similar question concerning php and mysql. Say that you have a php program that displays a mysql table to the screen but your users want the ability to sort the displayed data on any of the colums. In other words, using the op's example, say a user wants to sort the displayed table by phone number after it's already been displayed or maybe by address or if there was a lastname field or whatever.

I know it's fairly easy to make each colum heading be a link but how do you use that link to resort the displayed data and redisplay it? Is there a way to maybe sort by two or more columns?

I'm wondring this because on a couple of the pages of the site I currently have to maintain, some of the users have asked for this ability and currently I have no idea how to do this.
 
just my 2 cents to throw in...having a delete button for each entry I think would be a little much. I'd say put a check box in there and if checked...delete with a single button. I think it would look cleaner.

As for updating, make the name a hyperlink w/ the unique identifier as a passed "_GET" variable.
 
Paithar said:
Ok, I don't mean to jack the thread but I've got a similar question concerning php and mysql. Say that you have a php program that displays a mysql table to the screen but your users want the ability to sort the displayed data on any of the colums. In other words, using the op's example, say a user wants to sort the displayed table by phone number after it's already been displayed or maybe by address or if there was a lastname field or whatever.

I know it's fairly easy to make each colum heading be a link but how do you use that link to resort the displayed data and redisplay it? Is there a way to maybe sort by two or more columns?

I'm wondring this because on a couple of the pages of the site I currently have to maintain, some of the users have asked for this ability and currently I have no idea how to do this.
Click to expand...

Easiest way is probably to just make hyperlinks for [Sort by xxxx] and have the link include the column you want to sort by.

<a href="xxxx.php?method=sort&column=address">Sort by Address</a>

This is assuming you're using php sessions and have the values saved to be able to recreate the SQL Query statement that you used to originally display the table. If you're not using sessions, you'd also need to pass the values that you used to create that SQL query.
Then in the .php file you toss in a way for method=sort to call a sort(column) function to run that SQL query with the added SORT BY XXXXX in the query.
Throw in &type=asc or &type=desc to your hyperlink and you can tell it to go by ascending or descending values of the sort.

If you wanted to avoid having to refetch the data from the database, you can make use of sessions to store all the fetched table values in an array variable and write a sort for that. But this is probably a bad way to do things, the SQL sort is fast and pre-written for you.
 
Well, I'm not using Sessions but not because I don't want to, it's because the guy who originally wrote the whole site doesn't think sessions are very secure and won't let me switch over to using them (so instead we pass hidden variables via a form to every single page).

I've only been doing web development and PHP programming for about 14 months now. My background is coding in COBOL and C on Tandem systems for about 10 years. Now, on those systems when you have an sql query in your code and you execute the query what really happens is the program goes ahead and gets all of the rows it needs to get for that query, holding them in memory (unless of course the return set is too large). Is it the same for queries in PHP and MySQL?

If so, would it be possible then to take that result set and, before you display it, put it into an array of some sort? Then have a function that sorts arrays based on whatever column you pass it? I'm not exactly sure how to do this if it is possible to do it this way. I might have to try some things out.
 
Paithar said:
Well, I'm not using Sessions but not because I don't want to, it's because the guy who originally wrote the whole site doesn't think sessions are very secure and won't let me switch over to using them (so instead we pass hidden variables via a form to every single page).
Click to expand...

I'm no security expert but isn't this the exact opposite of the truth? As I understand it, all session data is server stored and is never transmitted (unless of course the php you are executing explicitly tries to) while form data is going to be transmitted back and forth with every page request/load.

Paithar said:
If so, would it be possible then to take that result set and, before you display it, put it into an array of some sort? Then have a function that sorts arrays based on whatever column you pass it? I'm not exactly sure how to do this if it is possible to do it this way. I might have to try some things out.
Click to expand...

Yeah, but why not just make sql do all the heavy lifting? No need to write a sorting algorithm when you can already use sort by in the sql statement itself.
 
Robizzle01 said:
I'm no security expert but isn't this the exact opposite of the truth? As I understand it, all session data is server stored and is never transmitted (unless of course the php you are executing explicitly tries to) while form data is going to be transmitted back and forth with every page request/load.
Click to expand...
There are ways where an attacker can gain the session ID and thus have access to all session information.
http://www.net-security.org/article.php?id=925
I'd go with using sessions...and just harden the server....otherwise, like you said, anyone looking at the page source can view the information.
 
Paithar said:
Well, I'm not using Sessions but not because I don't want to, it's because the guy who originally wrote the whole site doesn't think sessions are very secure and won't let me switch over to using them (so instead we pass hidden variables via a form to every single page).
Click to expand...

that has to be the most retarded thing i've heard in a long time. A simple view-source will allow anyone to see all the "hidden" form values. hijacking a session value requires a LOT more work. But i'm guessing with that kind of thinking, he probably has no clue how to take the simple steps necessary to prevent XSS attacks and the like, so it figures why he would think sessions are not very secure.
 
OK - sessions aren't secure. Don't rely on them for security & don't keep anything that needs to be secure in them - shit like how somebody wants tables sorted or what page of results they're viewing don't need to be secure.

Granted, if you're working for idiots, doing the smart thing isn't always an option.
 
You must log in or register to reply here.
Back
Top