Using PHP to shift data between mySQL tables

[dmad]

Hi All

Forgive me if I have got some of my terminology mixed up there - go easy on me, I'm a total beginner. I'm trying to build a simple page which will allow you to go to a site, register what time you're interested in attending a 'meeting' (in this case a game of tennis), and then leave. Ideally the final product would allow you to logon and only you could add your own name to whichever time you like, but i'm trying to get a concept working before all that.

I've uploaded a dummy example to - where you can see two boxes, for a 6pm 'meet' or 'game' and 7pm 'meet'. You look for your name on the list and click the >> to register your preference, and submit.

But thats all it does. It doesn't save the data to anything in the back-end so it's completely live and not stored.

So I'm using xhtml and css to present the page, a little javascript to work the buttons and lists and PHP to render it all and do the server stuff. Most of this code is sourced from tutorials so I'm still learning what most of it actually is doing.....

So I have a mySQL installation and i've done some bits at the CLI to enable me to build a db, create fields and what have you. But now I'm stuck at taking my front end and using the DB to fill up the list of people and store when people have assigned themselves to a table.

I'm thinking i'd have a table for 5pm, 6pm, 7pm etc and an "Undecided" table, which is where all the names are defaulted to. When someone logs on, the PHP shifts a name from the undecided table to one of the time tables and keeps it there until further notice.

I'm essentially in a rut and looking for some tips....!?

[edited by: jatar_k at 2:42 pm (utc) on Nov. 13, 2008]
[edit reason] no urls thanks [/edit]

[supermanjnk]

I would just keep everything in one table and have a separate column for the time frame in that table. Then you can just update the record with the correct time instead of moving it from one table to another.

[dmad]

Ok, I see what you mean - I guess there is more than one way to make an omlette - but do you have any examples of this in action? I'm most interested in some example PHP code which stores and retrieves these changes to the db....

Thanks!

[andrewsmd]

If you have PEAR installed then download the DB.php file. Otherwise take a look at the php mysql functions. As far as the login stuff since this is a simple page the easiest way to see if a user is logged in would be to set a session variable. You will need at least two tables because you will need one to store usernames and passwords and one to store your tennis data. So when they login you check your database for the username if that exists check the password they entered against the password in the database. If it matches then set a session variable like $_SESSION['logged'] = $username where $username is the data they entered in the textbox. Then, in your other pages you can check like this
if(!(isset($_SESSION['logged']))){
header("location: login.php");
}
basically this if is saying if the session variable is not set meaning they haven't logged in then we will redirect them to the login page.
once they login, go into the page and select everything from the database where their username is. What I mean is say username jonsmith registered for the tennis match at 2:00 pm. In your tennis table you would have something like this.
Id ¦ username ¦ date ¦ match
now you could have more columns this is just a basic one. Anyways, when he logs in and registers you would store him in the tennis table like this (note: ID should just be an auto increment)
insert into tennisTable values ("", "jonsmith", "11-13-08", "match 1"); (note: match 1 is just a made up thing)
Now when he logs in you would select * from tennisTable where username = 'jonsmith'; and then print all of the output and that would only print things for him. I know this is just pseudo but start coding and post it when you run into bugs and I can help you more. I've done almost the exact same thing you are trying to do so let me know.