Need help with Simple PHP/MYSQL database

[COOKIED89]

Hi everyone,

 

I am currently looking at writing up a simple PHP/MYSQL database for work.. & i'm after some advice as to how I can go about it.

I have installed Xampp etc and I am running the pages I create via http://localhost/something.php.

 

What I am after is a simple login system, where users can register an account, login, and log the amount of hours they do each day.

They do work for a number of different states/terriroties, so they'd have to be able to select from a dropdown box which state they worked for and the amount of hours for each.

 

I had a look online and found some scripts 'PHP Login System with Admin feature' See - ( http://www.evolt.org/PHP-Login-System-with-Admin-Features )

So I've tweaked that a little bit and have it all working/talking to the local mySQL database. What I want is when users login to have their OWN page come up.. where they can enter in their data for the day, modify their data for previous days, but not anyone elses. How hard would it be to get a calander in there, that might make things easier.  A set of tables  that displays the data and generates weekly/monthly totals would also be a good idea.

 

I also want to have the admin feature set up so that an account with administrative rights can login and be able to print out simple tables or export an excel spreadsheet that contains the total number of hours worked for each State by all. (Adds everybody's information together) .. perhaps being able to select "Week ending" Or "For the month of August" .. so I'd obviously have it connecting into the database and running queries to pull this info out.

 

It is so very simple to visualise it but I need help as to how I would construct/setup the tables in mySql and what kind of types they will be! Like Varchart etc. Finally, are there any programs, or web resources/forums that will assist me in generating php code, CSS sheets (I plan to create a simple one, with a nav bar across the top, and use the include feature to make all the pages have the same formatting) ? If anybody would be able to take the time to give me a few pointers so that I have more of an idea about how I am going to tackle this I would really appreciate it. I plan to have this running on one of the servers at work so It has to be easily movable without the need to change *too* many lines in the script.

 

If you need any more information from me just ask! I hope I have posted this in the most appropiate section.

Thanks in advance guys . 

[Barand]

To be honest, there's a bit too much there. I'd have to write a book to answer all that.

 

Best to take it one step at a time and when you get stuck, come back with a specific question.

 

Tables would prob be something like this

[pre]

user                               timesheet                                     state

------                             -----------                                   ---------

username varchar(10) PK  ----+     id int auto_increment not null PK     +-----  state varchar(2) PK

password varchar(32)         +---< username varchar(10)                  |       name  varchar(20)

fname    varchar(15)               entrydate DATE                        |

lname    varchar(15)               state varchar(2)         >------------+

                                   hours decimal(5,2)

[/pre]

[COOKIED89]

Thanks very much for your input. It is okay now, I have worked out the login/authentication system, I am using a simple but very effective/usable script called 'Vauthenticate' Basically it assigns usernames ID's, you can categorize them in groups etc. I have just created pages that are designed to either let the user in if their ID matches, or bounce them back to the main page or an access denied prompt if it doesn't. It's only going to be up on the local network for internal use so security isn't too much of an issue.

 

I currently have two tables set up -

Authteam:

id, INIT(4), NULL:NO, AUTO_INCREMENT

teamname, varchar(25), latin1_swedish_ci, NULL:NO

teamlead, varchar(25), latin1_swedish_ci, NULL:NO

status, varchar(10), latin1_sweedish_ci, NULL:NO

 

"Authuser"

id, int(11)  , NULL:NO,  auto_increment

uname,  varchar(25), NULL:NO

passwd, varchar(32), NULL:NO

team, varchar(25)NULL:NO

level, int(4)

status, varchar(10), NULL:NO

lastlogin, DATETIME, null: YES, Default:Null

logincount, int(11) Null: YES, Default: Null

 

-----

 

So now i'm basically looking at setting up the tables to link in with the above.

Is something similar to what you have said there okay ? I don't know how to link them together like you've got above, (Eg username -------+ (timesheet) +-----< username) . How do I go about setting that up?

 

Each user will have their own protected page now, which as I said is all set up.

So all I really need is the structure for these tables and how to create them., from there i'd just have some simple queries running in the PHP pages, to bring back/display their OWN data etc.. as well as give them the option to create and edit existing entries. On the 'Administrative' page I'd set it up to display all the records and total them up.