Page 1 of 2

User "confirm-event" switch

Posted: Sun May 18, 2008 9:27 pm
by jason87
I have a small site for a wedding band and I'm trying to make a php admin "cms" with, so far, ability to add gigs, blog, add songs to the repertoire. The login is a simple session with the id being the name of the user (only three people need to use this site). Now I wanna make a "module" where we can arrange practice dates. basically, a user suggests a date by creating a new date, which by default is "not confirmed" by the band members. Now to my question: I want the users to be able to confirm or cancel the potential date. Once someone cancels it, it is deleted from the database. What I've done so far is create a table for each practice date containing three rows, one for each user with an INT value set default to 0 which, when confirmed, changes to 1 which causes the browser to echo e.g. "Tobias has confirmed" to the other users, and maybe "You have confirmed" to himself. I can't help but think there is a smarter way to accomplish what I want. I have the session_id until they log out, but I can't figure out quite how to get there (i'm new and self-taught). I would really appreciate it if someone could give me some pointers in the right direction. I understand the language pretty well, I just can't work out the logic + there are lots of functions in both php and mysql I don't know yet. Any help will be appreciated!

Re: User "confirm-event" switch

Posted: Sun May 18, 2008 10:54 pm
by dbemowsk
Something like this can get to be pretty complex. When you start talking modules, it could possibly require some kind of framework to run it. Depending how new you are to PHP, this could take you some time to code. If you want to add gig information, have a blog, and create song lists, you will need to figure out all your database tables and what information you need to store. From there you will figure out the PHP code around the tables. I suggest taking this task on in pieces. Create everything for showing your gig information, then move on to your blog, and then your song lists. Then you can figure out how to pull all the information together in your page.

I would suggest using templates for your pages so that you can keep a uniformed look to things. I have a beginners tutorial on using templates on my website www.phpwebscripting.com.

Hope some of this gets you a start on your project.

Re: User "confirm-event" switch

Posted: Mon May 19, 2008 5:19 am
by jason87
Hi,
Actually, I have already created the cms with the functions: gig information, songlist and blog including image upload and youtube embedding and it is up and running on our site, we use it daily. What I'm working on now is the "practice dates" bit where we can add dates that every user needs to "confirm". And my question is how to make this "confirm" function the smartest. When a user is displayed a date, it should either say "confirm date" OR "you have confirmed this date" but I just don't see a clever way to do this.

Re: User "confirm-event" switch

Posted: Mon May 19, 2008 12:37 pm
by dbemowsk
Do you want any one to be able to enter the dates (i.e. yourself and the band members), or just yourself? If you want to be the only one to enter dates, you will need a few extra user checks in your code.

This does not seem like a difficult task. The way I would do it would be to have a table that stores your dates with a little description for each. The table would consist of 3 columns, your date_id, gig_date and gig_description. If you have your site set up with usernames and user IDs, you could have a second table for confirmations. This table would have 3 columns, the date_id, the user_id and a confirmation field which could be a boolean tinyint field for 1 or 0 telling weather they confirm or deny that date. You only mentioned confirming, but if a date doesn't work for them they can acknowledge that they looked at that date and it doesn't work for them.

Once you have all that, loop through your database list of dates from the first table to get the information to display for the date and then for each date_id, query your second table whether there is an entry for the date_id and for the current logged in user. If the query returns no result, display the confirm and deny buttons. If a result is returned, have it display "You have confirmed this date" or "You have denied this date" based on the 1 or 0 in the confirmation field.

Let me know if this was helpful...

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 4:02 am
by jason87
I would definitely like users to be able to deny as well as confirm dates. And I want all users to be able to add dates. The "add date" form will be on the same page as "echo dates" so there doesn't need to be a "duplicate date warning" if you know what I mean.
The only thing I'm unsure about is the looping through the db. I guess firstly, I would have something like this(?):

Code: Select all

 
// Get next practice date
$get_next_date = mysql_query("SELECT * FROM practice_dates WHERE date > NOW() LIMIT 1");
$date_array = mysql_fetch_array($get_next_date);
 
What I don't know is how I'm going to make the next query, where I look for the specific date in the other table, the one with confirmations. I know I'm supposed to get the id from the array I've just extracted from the db, but I don't know how to put this id into my db query?

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 4:28 am
by jason87
I've never done "dynamic" mysql queries before, but I could only imagine that it's possible. Can something like this be done?

Code: Select all

 
$id = 2;
$query = mysql_query("SELECT * FROM dates WHERE id = $id");
 

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 5:29 am
by dbemowsk
You are on the right track. What I would actually do is something like this:

Code: Select all

 
//This assumes you have a logged in user with a $_SESSION['user_id'] set for them
 
//I usually use the unix time when storing my dates in a database
//If you use dates in another format you may need to adjust this accordingly
$date_results = mysql_query("SELECT * FROM dates WHERE date > unix_timestamp ORDER BY dates ASC");
if($date_results) {
  //I typically use the associative results array so I can reference the fields like $date_row['date_id'] or $date_row['user_id']
  while($date_row = mysql_fetch_assoc($date_results)) {
    $conf_results = mysql_query("SELECT * FROM confirmations WHERE date_id = $date_row['date_id'] and user_id = $_SESSION['user_id']");
    if ($conf_results) {
      while($conf_row = mysql_fetch_assoc($conf_results)) {
        //a result was found for the user and the date
        //the user confirmed or denied which will be either a 1 or 0
        if ($conf_row['confirmation']) {
          //the user confirmed, add code to show confirmation
        } else {
          //The user denied, add code to show denied
        }
      }
    } else {
      //no result was found for the user and date
      //add your code to place the confirm or deny buttons
    }
  }
}
 
That should get you a start.

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 7:26 am
by jason87
That seems pretty logic actually and without any tricky parts.
The code you've written echoes whether the user watching himself has confirmed, denied or needs to respond to the date. To show what the other users have said (e.g. "Tobias has confirmed") I would do something like this, right?

Code: Select all

 
$conf_results_all = mysql_query("SELECT * FROM confirmations WHERE date_id = $date_row['date_id']");
while($conf_row_all = mysql_fetch_assoc($conf_results_all)) {
echo ""$conf_row_all['user_id']" has";
 
if ($conf_row_all['confirmation'] = 1) {
echo "confirmed";
    elseif ($conf_row_all['confirmation'] = 2) {
    echo "denied";
        else {
        echo "not replied";
        }
    }
}
 
} // END WHILE
 
As I wrote the above, I wondered if I could maybe just call the confirmation statuses in one query (like above) and within the WHILE brackets create an if statement

Code: Select all

 
if ("$conf_row_all['user_id']" = $_SESSION['user_id'])
 
telling it to either show "YOU have confirmed / denied" or show "confirm/deny buttons" This wouldn't seem difficult and would save me writing pretty much the same code twice. This is possible, right?
Now I think I only have one more doubt regarding this script:
The confirm/deny buttons themselves. I'm not quite sure what they're supposed to say. I guess an easy way would be to make them submit buttons with hidden form-fields containing user id and confirm/deny ready to put into the db via $_POST vars. I just think it will look better with regular links styled to my css, but have never done the "index.php?newdate=somethingsomething"-style links before.
The way I understand is that whatever you put after the "?" in the link is a variable. So I could maybe write?

Code: Select all

 
echo '<a href="practice_dates.php?confirm=1&date_id=$date_row['date_id']">Confirm date</a>';
echo '<a href="practice_dates.php?confirm=2&date_id=$date_row['date_id']">Deny date</a>';
// And then on the page practice_dates.php make the following
$addconf = mysql_query("INSERT INTO confirmations (confirmation, user_id, date_id) VALUES ('$confirm', '$_SESSION['user_id']', '$date_id')") or die(mysql_error());
 
I have a suspicion that links can't look like that and still be valid, so how do I get the date in question in the link?
I'm sorry about the extremely long posts, I really appreciate all your help!

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 12:24 pm
by califdon
You're well on the way, Jason. Your links should work just fine, just remember that the PHP script that it is sent to must use $_GET variables, not $_POST variables, as it does with form data received with the POST method. Alternately, you can use $_REQUEST variables, which looks for either GET or POST variables. The code is just easier to read if you treat them separately. In other words, your practice_dates.php script would begin:

Code: Select all

<?php
if(isset($_GET['confirm']) $confirm=$_GET['confirm'];
if(isset($_GET['date_id']) $date_id=$_GET['date_id'];
...
A comment on your table structure(s) and your display arrangements: if you might expand this to many more users, I would definitely recommend a relational database structure, but if you are pretty sure that you only need 3 users--now and forever--then a simple, single table would work. Of course, I'm kidding about the "forever", but my point is, sometimes it might not be worth investing the design time to manage relational tables to achieve a correct design that would be expandable, if you only intend to use this with 3 people and by the time it expands to 10 or 20, you would probably scrap the system and start all over again. On the other hand, if you're interested in learning how to design relational databases, this could be a great opportunity to really do it the right way the first time. dbemowsk has given you some good suggestions. On the display, I suggest that, with only 3 users, you just display each date, followed by 3 columns of colored "flags", one for each user, showing perhaps a bold green "Y" for confirmed, a bold red "N" for denied, a black "?" for no response. The users' names could be the headers for the columns. This would be easy, using an HTML table. Again, this isn't very flexible, in case you add a 4th member (a trombone, maybe?). ;) This is where the difference between a hobby application and a professional application makes a difference.

Finally, a thought on deleting date records. You might be able to do it automatically, using logic something like this: each time your main script is run, have a routine that goes through and deletes all records for dates earlier than the current date, unless you want to save those where everyone confirmed, for history, or whatever.

And no need to apologize for the long posts. I don't mind long posts when they make sense and it's obvious that the poster is actually using the information supplied by the others.

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 2:42 pm
by jason87
Hi califdon, dbemowsk and anyone else reading this,
Thanks for the tip on GET vars, didn't know that, I'm very new at this. I will put the whole code together later and post my result, be it triumph or plentiful bugs ;-)
My take on this is to create one table for dates and one for confirmations like dbemowsk suggested.
I would like to hear more about relational databases, although I'm not exactly sure what this term covers. In theory, I can see this system, albeit simple, working with many users, but I would like to know why it is not optimal. Will my current db structure get slow or are there just some rules-of-thumb regarding databases that I'm not complying to?
The only thing I, myself, have wondered, and I may very well be nitpicking, is that the confirmations table will contain confirmations in no particular order from all users, which doesn't seem very systematic?

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 6:00 pm
by dbemowsk
Jason,

It's a little off the topic, but since you are working on a music/band website you may appreciate this. The link below will take you to the first site I ever coded with PHP. It was over 3 years ago that I coded the site and I was very new to PHP, and I bet if I looked back at the code now I'd say "MAN i'd write that code so much different today". I look back on the site once and a while to see if the people that run it have changed to a different format, but it still remains the same.

http://www.localmusicbomb.com/lmb/

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 7:27 pm
by jason87
I'm dragging my feet here if that was your first ever php site. A lot of functions it seems! But maybe you started out with fewer. And hey, there's no need to fix what is not broken, but I still want to learn the proper ways of doing things.
I have put the code together and aside from minor setbacks and frustrations, I've made the basic page which shows your own (the logged in user) status (says confirmed or displays confirm/deny links) and shows the other users' statuses. The only problem I've encountered is that the links I made earlier in a post don't work. I get the white page when I put the links in, so they must have faults in them. I appreciate all your guidance and hope you can see what is wrong with my links. I'm close to achieving the application as I want it and I owe it all to your great help. Thank you both!

It's 2:25 am here in Denmark, better get to bed. See you.

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 9:36 pm
by califdon
jason87 wrote:Hi califdon, dbemowsk and anyone else reading this,
Thanks for the tip on GET vars, didn't know that, I'm very new at this. I will put the whole code together later and post my result, be it triumph or plentiful bugs ;-)
My take on this is to create one table for dates and one for confirmations like dbemowsk suggested.
I would like to hear more about relational databases, although I'm not exactly sure what this term covers. In theory, I can see this system, albeit simple, working with many users, but I would like to know why it is not optimal. Will my current db structure get slow or are there just some rules-of-thumb regarding databases that I'm not complying to?
The only thing I, myself, have wondered, and I may very well be nitpicking, is that the confirmations table will contain confirmations in no particular order from all users, which doesn't seem very systematic?
Ah! You touched my hot button, Jason! Relational Databases! :D I'll give you several references at the end of this post, but let me try to get your interest by explaining a few things very briefly:

Relational database theory was developed in the late 1960's by Dr. E. F. ("Ted") Codd, a mathematician at IBM, and Chris Date (I took a seminar from him once) and it remains the underlying principle on which nearly every database system and software is based, including MySQL, Access, Oracle, etc. Without that theory, we wouldn't have web search engines, airline reservation systems, financial systems, etc. etc.

We refer to Relational DataBase Management Systems, or RDBMS's. The idea behind an RDBMS is that one database may include data stored in multiple tables, which have "key" fields that enable them to be related, in simple or complex ways. But in order to work properly and reliably, there are quite strict rules that apply to what tables are needed and what fields must be in each table. When these rules are followed, the mathematical theory behind RDBMS absolutely guarantees certain desirable features, such as the consistency of the data in your database. Quite often people will say something like, Well, I didn't follow those rules, and my database is working just fine! That may be true, but when some unplanned transaction occurs, or you want to make some changes in your data structure, you will have no guarantee that it will continue to work. A properly designed relational database can enforce rules to assure data integrity under all conditions. I'm talking about operations like editing or deleting entries in a table which would then leave inconsistent or "orphan" data in another table.

To begin with, a database application should always begin with off-the-computer planning about the data model. You can think of every database as a model of some part of the real world, such as students in college classes, or financial transactions, or reservations on air flights, etc. etc. The very first consideration should be to define the scope of your data model; will this model cover just your classes, or all the students, or all the departments in the school, or what? You will need to identify what entities will be represented in your data model. An entity is like a noun in grammar--a person, a place, an object, a transaction, an event, etc. For example, the entities for a hotel reservation system might be Rooms, Guests and Reservations. This is a critical decision, because each entity requires one table in the database.

Then you must decide how much data you need to collect concerning each entity. Any entity has almost unlimited possible characteristics, but you only need to collect a small number of them. For the hotel system, for example, you need to know the Room Number, how many beds and what size, whether it is Smoking or Nonsmoking, maybe other characteristics, but you probably don't need to store the dimensions or color, etc.--although there might be some hotels that advertise special amenities such as a view of the ocean from some rooms, so it depends entirely on what is needed. But whether a room is occupied or has been reserved on a certain date are NOT characteristics of the entity Room, which is a point often confused by beginners. That information is part of an entirely different entity, the Reservation, and will be in that table, not the Rooms table. It is this sort of analysis that leads to a solid schema for a database, that is, the structure of the tables. My major point is this: designing a schema is a careful and disciplined consideration of the data model and should never be merely someone's opinion of what they think might work, despite what you will often read in forums like this one.

If you start with a properly designed schema, your PHP (or other language) routines will be simpler because most of the database related operations can then be handled with the standard features of SQL, without elaborate processing within the scripting or programming language. And your database will probably be simple to expand with additional data, features or to cope with new conditions, because of the logical consistency of your data.

So, if you like those concepts, here are some online tutorials I recommend:
http://www.tekstenuitleg.net/en/article ... tutorial/1
http://www.utexas.edu/its/archive/windo ... rview.html
http://jegsworks.com/Lessons/lesson1-2/ ... tabase.htm
http://www.phlonx.com/resources/nf3/
http://www.roseindia.net/jdbc/relationa ... epts.shtml
http://www.quackit.com/database/tutoria ... design.cfm
http://www.databasedesigning.com/Relati ... Model.html

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 9:45 pm
by califdon
jason87 wrote:Will my current db structure get slow or are there just some rules-of-thumb regarding databases that I'm not complying to?
No, it probably won't affect performance, but it could affect the way you write your PHP script and could limit the flexibility to make changes later, under some conditions. I'll just comment, though, that a good relational database designer will absolutely never resort to "rules-of-thumb"--as I said in my lengthy post above, there are very strict rules, most notoriously, the 5 "normal forms" of data in a table, which you will read about if you pursue the subject of relational databases.
jason87 wrote:The only thing I, myself, have wondered, and I may very well be nitpicking, is that the confirmations table will contain confirmations in no particular order from all users, which doesn't seem very systematic?
It's not nitpicking, but there's a basic consideration in relational database theory that says you must never assume any particular order of data within a table. This is because of the mathematical basis of relational database theory and database engine design. Sequencing of data is always done by the database engine, responding to SQL queries, and is totally independent of any physical sequence of records on a storage device.

Re: User "confirm-event" switch

Posted: Tue May 20, 2008 10:42 pm
by dbemowsk
If you are referring to these two lines

Code: Select all

 
echo '<a href="practice_dates.php?confirm=1&date_id=$date_row['date_id']">Confirm date</a>';
echo '<a href="practice_dates.php?confirm=2&date_id=$date_row['date_id']">Deny date</a>';
 
I do not see a reason why they shouldn't work. I don't know how the rest of your code is set up, so it may be something in there that is causing the white screen. Are you having the page with the links wrap back onto itself? In other words, do you call practice_dates.php to have it show your confirm/deny links which then call the same page? If so, check to see how you are processing the results ($_GET values).

One other thing i might suggest is using 1 and 0 for your confirm var in your href URL instead of 1 and 2. It is easier in the end since you store it as 1 or 0 in the database, not to mention you won't have the extra step of converting the deny value from 2 to 0 before you store it.
----------------------------------------------------------------------------------------------------------------------------------
BTW, califdon, very informative post on RDBMS. Kudos.

Jason, heed califdon's words. If you are going to get into PHP, you are inevitably going to deal with databases at some point, as you are seeing right now. Understand what it means to normalize a set of database tables by breaking them down into smaller logical parts. If you notice how I split your data into two tables, the table for the dates, and the table for the confirmations. If you kept all the data in one table, e.g. date_id, date, description, confirm_person_1, confirm_person_2, confirm_person_3, confirm_person_4, you severely limit your flexibility. Yes, right now you may have 4 band members that need to confirm the dates, but what if you get another member. Now you have 5 members and your database is only set up for 4. By having the data split into the two tables you keep your data logically separated and increase your flexibility at the same time.

If you are a good logical thinker, then database normalization shouldn't be that hard to figure out. It's all about eliminating redundant data. As with databases and eliminating data redundancy, some of these rules can be carried over to PHP to eliminate code redundancy through the use of functions, classes and other things. I have a short article on eliminating code redundancy on my website.

Cheers...