How To Manage Database Entries?

[FoxRocks]

Hello,

 

Thank you for taking a look at my question.

 

I have a question about managing database entries with respect to a content management system. I am currently using one solution that I believe is a terrible way to handle things and my hope here is to find a better, more professional way to do things.

 

So far I have tried different ways of coding a solution, but every time I just get a bunch of gibberish that doesn't make any sense. I've also tried to look for answers on the web, but I've had no luck, I suspect I don't know how to word my question properly.

 

Anyways, let me explain what it is I am trying to accomplish.

 

I have a Staff Page on a website that displays information for each staff member. Each entry includes a photo, biography and work location for each member. This information is stored in a MySQL database and is displayed on the web page using php.

 

I want to be able to list all of the entries from the database and then choose which ones to delete or update as well as have the option to add a new staff member.

 

Currently this is what I'm doing:

 

On the CMS side of things I have an html form that has one section for each staff member (8 employees = 8 sections) that is populated with the entries in the database. To update any information I just edit the page and when I click submit, it updates the database.

 

Now you know why I called it a terrible solution.

 

To date this method has "done the trick" because I am the one managing all of the entries. If there is a new employee I just add another section to the page, or if an employee leaves I just clear the entry and hit submit. The main problem is that none of my customers could ever manage this part of their website, which prevents me from giving them a proper content management system.

 

Right now I feel like I have all the pieces to the puzzle, but I just don't know how to put them all together.

 

I would really appreciate some help with two things:

 

1) Some direction to get me started on a solution, I like to work through things on my own as much as possible, I learn more that way.

 

2) Help with wording the title of this thread properly, I like to try and make it so it's not misleading and also if someone has the same problem later on, they can find a solution.

 

Thank you for your time,

 

~FOX~

Edited October 1, 2012 by FoxRocks

[White_Lily]

As for the MySQL Database setup, it is easier the have all employee information in say a "Employees" table, so for example,

 

You have a registration system, every user that enters information and hits submit will then be added to a database table named "Members" or "Users".

 

As for the forms themselves they are as simple as you make them, how the forms are used and handle the data is down to the way you code the CMS, i also am developing a CMS at the moment, that other users will be able to "plug-into" their site, and use accordingly.

[Christian F.]

The way I'd do it, is to set up a "employees list" page, and one "profile (edit)" page.

Have the list page show a quick listing of all the employees, with two buttons/links: "edit" and "delete". The "edit" link should lead to the profile page, where it'll allow the employee him/herself and the admin to change any details necessary. The "delete" button should lead to a page where you ask for confirmation.

 

The trick is having the employees list page only available to the admin, and the edit page should check the user ID and privilege level for the accessing user.

 

Take a look at the "Members" page for any forum, for hints on how to do this.

[White_Lily]

Quick help with Chiristian's method:

 

Use "User Types" e.g: 0 for normal user, 1 for moderators, 2 for admin, 3 for webmaster.

 

The code is basic:

 

<?php

$query = mysql_query("SELECT * FROM employees");
$fetch = mysql_fetch_assoc($query);

if($fetch["user_level"] < 0){

//display this

}else{

//display this

}
?>

[PFMaBiSmAd]

For real-life applications, data is normally never actually deleted. You would just have a 'status' field that is set to a value that indicates the data is inactive or use something like an end_date column and you only select active data with an end_date that is not in the past.

[FoxRocks]

Hi,

 

I just wanted to drop in real quick to let you all know that I have seen the replies, but I've been busy at my day job and haven't had time to work on this.

 

Also, in reply to PFMaBiSmAd, wouldn't that just make the database grow and grow and grow over time? I didn't really consider that option because I figured that would just make everything slow down.

 

Thank you all for the replies, I hope to get to work on this very soon!

 

Cheers,

 

~FOX~

[White_Lily]

The point of PFMaBiSmAd's method, is that should you need to go back to the entry, you would only have to change one field, this is usually an "active" column, 1 ofr active, 0 for inactive - i use it, it works very well with my CMS

[FoxRocks]

White_Lily,

 

Thanks, that makes sense now, and after rereading what PFMaBiSmAd said, I see what he means.

 

Do you happen to have a Live CMS model running that I could see? I hope that's not out of line to ask. The reason I ask is that I'm interested to know what the industry best practices are for managing content. I have a mixed bag of ideas in my head and I'm sure I'm missing some things that I should have. I apologize if I'm out of line.

 

Cheers,

 

~FOX~

[White_Lily]

Your not out of line - once ive finished developing it and its fully pre-live checked and post-live checked i will post a link

 

A CMS should be capable of development throughout its life-time, this means that you should be about to take away features and add features depending on your sites needs. This is the one reason why i hate pre-built CMS's such as WordPress, and Joomla. Very difficult to customise those. (sets my teeth on edge just mentioning them >_<)

[FoxRocks]

Very cool, Thanks White_Lily

[White_Lily]

Hi Fox, I have a basic CMS system set up, there is just a few little things to change. Once the changes are made and i have created a basic website template - and all this is live, i will post the link for you - sorry for it taking so long

[FoxRocks]

No worries, I've got all the time in the world

[White_Lily]

I have PM'ed you the link(s) and login details.