Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Editing MySQL Database from a Webpage

Futhark

Futhark

macrumors 65816
Original poster
Jun 12, 2011
1,238
179
Northern Ireland
I have a MySQL Database that holds different kinds of Hazards in my local area, I created a very basic webpage that allows me to add entries to the MySQL Database easily but i would love to create buttons that will allow me to display the content of the database or a button to allow me to remove or edit an entry that has maybe changed or is no longer a hazard, but i'm not sure how i can go about this?

Any help would be much appreciated :)
 
Sounds like you want a handy way to update content, not a deep admin interface.

Are you not already close? If you have already built a page that creates new records you may want to take the time to add an edit page. I've done this myself quite a bit. There's something to be said about building your own interface that looks and behaves exactly the way you want it.
 
Matt. +1 for this suggestion but be careful.

If your host is exposed to the internet at general, (mine is), and you regularly review your web server logs, you will not believe the the amount of hack attempts you get for it.

As nice as this tool is, I can not strongly recommend enough that you lock it down as tight as possible, should you choose to expose it to the Internet at large.



slightly said:
The usual solution is PHPMyAdmin. It's very easily installed, and just requires PHP running on your server. Pretty much every web host I've used has had PHPMyAdmin available for administering MySQL databases.

http://www.phpmyadmin.net/home_page/index.php

Matt
Click to expand...
 
TalkAboutApple said:
Sounds like you want a handy way to update content, not a deep admin interface.

Are you not already close? If you have already built a page that creates new records you may want to take the time to add an edit page. I've done this myself quite a bit. There's something to be said about building your own interface that looks and behaves exactly the way you want it.
Click to expand...

Yeah i'm pretty close here, the page works really well but i just can't figure out how to edit or delete an entry from the page itself? and it doesn't necessarily have to be the last entry either i should be able to pick any one.

I'm having to do it via phpMyAdmin at the minute which isn't practical as i don't want people accessing the database this way.
 
mysql_query("UPDATE ")

Futhark said:
Yeah i'm pretty close here, the page works really well but i just can't figure out how to edit or delete an entry from the page itself? and it doesn't necessarily have to be the last entry either i should be able to pick any one.

I'm having to do it via phpMyAdmin at the minute which isn't practical as i don't want people accessing the database this way.
Click to expand...

I struggled with the same issue after creating my own simple CMS (really didn't like wordpress or the others out there). phpMyAdmin is clunky for quick updates.

Think about your structure. My system has a form that sends data to a script which inserts the data into a table. The table is read from the front end and displayed for the visitor in the browser. To update an entry, I created a page that lists the entries (ordered by date). When you choose the entry, it takes you to the same form that you originally filled out, except the fields are pre-filled with the data from the table. You can do this by a mysql query, pull down the data from the table and then call the pieces in the default values of the fields. The user can make changes, then when the user clicks submit, the data is sent to a different script which uses the UPDATE command instead of the INSERT command.

Googles searches will provide plenty of info and examples. This is just what I did. You may be looking for something different, but the method is there. Hope this helped.
 
Futhark said:
I have a MySQL Database that holds different kinds of Hazards in my local area, I created a very basic webpage that allows me to add entries to the MySQL Database easily but i would love to create buttons that will allow me to display the content of the database or a button to allow me to remove or edit an entry that has maybe changed or is no longer a hazard, but i'm not sure how i can go about this?

Any help would be much appreciated :)
Click to expand...

phpMyAdmin and other web pages DB management tools are not intended to be used for everyday content entry and editing. Their primary purpose is to give an experienced admin familiar with relational databases a back end tool to manage the database, including tables, indexes, format, character sets, structure, access and privileges. Rarely does it live on a production server, no matter how tough it is locked down - it's usually on a local sandbox or dev used to create, manage and optimize the database.

Okay, with that said, you can also use it to edit fields in tables simplistically, which means no WYSIWYG editor with preview. Just plain old text. Considering the security concerns and simplicity in the context of managing content only, this is last on my list of suggestions.

Based on your original question what you really need is to create a simple CMS or convert to one of the open source ones which offer back end admin tools for managing content and using WYSIWYG editors such as CKEDITOR or TABULA, etc. These usually come in the form as plugins or modules for the CMS and require very little technical expertise.

Use phpMyAdmin to fix issues directly on your database server, optimize tables, export/import and so on only when necessary. Or to create fields, tables and databases for new modules, etc.

Pick an open source CMS and expand your horizons:
http://www.opensourcecms.com

Or, modify your own code to add in the features you want which means you'll need to learn about delete and update, not just select. It's nothing more than forms for loading and saving data which accomplish that by running queries on the back end and parsing the data as variables. Just make sure you'r familiar with cross-site scripting prevention and SQL injection prevention with whatever language your using for your code. Use phpMyAdmin to confirm load/save and to get the field/table info and allow access, etc. That's where it fits in to development of such features.

If you attempt to do the second option on your own, we can help you if you get stuck once you give it a solid try. Just send us snippets of code related to your issue and any error info, we'll see if we can help.

Hope you found this useful, and not too confusing. I hit on alot of concepts and suggestions from a high level view, take some time to absorb it all and ask if you have further questions.

Cheers.

:cool:
 
Does it have to be a web page? I used to do all the editing with CocoaMySQL. Last years I use PHPmyadmin again, but I would love to have a CocoaMySQL like tool once again. It makes editing a bit like working with a spreadsheet.
 
blanka said:
Does it have to be a web page? I used to do all the editing with CocoaMySQL. Last years I use PHPmyadmin again, but I would love to have a CocoaMySQL like tool once again. It makes editing a bit like working with a spreadsheet.
Click to expand...

Like a spreadsheet? Have we not advanced in the industry enough to escape IDE's like that? No offense, but not for me! :p
 
Please google mysql php crud tutorials.

So far you have the C ( create record ) part complete. Now you need the Read, Update and Delete. There are literally hundreds of tutorials and hundreds of levels of complexity for this.

As mentioned above, opening your database to script bots can be dangerous in the sense that someone ( or thing ) else could edit your records. We use lots of little tricks gleaned over the years to harden our forms, but nothing is perfect. If you can edit it, so can someone else. One thing commonly recommended is to use a soft delete, aka flagging the status of the record as deleted, but not actually deleting it. This way if someone gets access and 'deletes' everything, you can use the database admin tools ( phpmysql ) to remove the delete flag from the status field. Another thing is to do all inserts, never actually update a record. This gives you the ability in the future to edit / view revisions of your records over time.

See how quickly this can get complex?! Just pay careful attention to your login form, it is very easy for a new developer to leave gapping holes in their security as most tutorials rightly so laser focus on the CRUD part and not the security part.

Good luck. If you get stuck post the code here and we will give you some help!
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back