updating page based on user status

[capurt_341]

Hi all,

 

I was wondering if there is a way to have a page update itself based on the status of the user. Right now, I have a mysql database where the users' username, pw, group, and week are stored. Overall, there are three groups and each group will have access to different materials. This year, one group (grp13) will have access to more handouts each week. I am planning on going into the database and changing each week manually once they have completed their training. I was wondering, if I have all the necessary info on the page, could I write a php code that once I change the status of the user in the database, the page would show the next week's materials. I tried to look up some tutorials but am not sure what this type of coding is called. Right now I have a page like this right now:

 

<?php 
require_once('../../includes/logged_in/li_overall/li_mid.php'); 
?>

<!--Class of 2013 Welcome Message......Show for class of 2013  -->                   
<div id="right_middle">
   <h1>Welcome y <?php echo $user_data['first_name'];?>!</h1>                    
   <p>You have been assigned to Class of 2013....</p>
</div>

<!--Class of 2014 Welcome Message......Show for class of 2014 -->                   
<div id="right_middle">
   <h1>Welcome y <?php echo $user_data['first_name'];?>!</h1>                    
   <p>You have been assigned to Class of 2014....</p>
</div>

<!--Class of 2015 Welcome Message......Show for class of 2015 -->                   
<div id="right_middle">
   <h1>Welcome y <?php echo $user_data['first_name'];?>!</h1>                    
   <p>You have been assigned to Class of 2015....</p>
</div>
                
<?php require_once('../../includes/logged_in/li_overall/li_footer.php'); ?>

 

Does anyone have any suggestions? I'm sorry if this is an easy question, I'm very new to php!

Thanks in advance!

[jcbones]

You should have a separate table in your database with your handouts info.  This should also have a week number, and a group number.  Then join the data together, and get the current handouts.  <-it may not be real clear, I'm losing it lately

 

--
-- Table structure for table `handouts`
--

CREATE TABLE IF NOT EXISTS `handouts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `img_location` varchar(20) NOT NULL,
  `group` varchar(5) NOT NULL,
  `week` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) 

You can join this table to your user table with a query similar to this:

$sql = "SELECT a.name AS handout_title  FROM `handouts` AS a JOIN user AS b ON a.group = b.group AND a.week = b.week WHERE b.username = '$user_data[first_name]'";

 

You could take it a lot further than that, and your database should be normalized (or it WILL run into problems), as data shouldn't be redundant.  It will lead to collisions and produce undesired results.

 

Things you could do.

Use a base time subtracted from the current time to automatically find the week number.

If you stored the group numbers as class numbers (2013) instead of a varchar (grp13), then you could get rid of the redundant blocks you have written and change it to:

<?php 
require_once('../../includes/logged_in/li_overall/li_mid.php'); 
?>

<!--Class Welcome Message...... -->                   
<div id="right_middle">
   <h1>Welcome y <?php echo $user_data['first_name'];?>!</h1>                    
   <p>You have been assigned to Class of <?php echo $user_data['group']; ?>....</p>
</div>                
<?php require_once('../../includes/logged_in/li_overall/li_footer.php'); ?>

 

I know I went beyond what you asked for, so if I confuse the situation, I apologize.

[capurt_341]

First of all thanks for replying !! This is much more complicated than I imagined! So once a user logs on, they have access to five different pages that I would need the content to be changed depending on what week and group they are in. The five pages are going to be answer keys, handouts, training progress, their homepage, and calendar. So then I would need to create a separate database for all the separate pages and link them each to the user table ?  Also, in the table, would I then be storing the data that I would want displayed on the page then ? Thanks !!

[Christian F.]

No, you'd need one table for each group of items. Which that's one table for all of the handouts, one for the calenders, one for the answer keys (possibly one for the questions too, depends upon the structure of your data), and so on.

In addition to that you might need a secondary table, to join some of the tables together with the user table. In particular I suspect that the handout table will require this, as you might want to have multiple users for each handout and multiple handouts for each user. This is called a "many-to-many" relation, and there's lots of information and guides on it on the net.

 

You might also want to check out the

, as they explain quite nicely how to go about designing your database properly.

[capurt_341]

Okay, so I've made all my tables. Now I am not quite sure how to pull the data from the tables. So for handouts, I have a table with handout_name, handout_path, week, and training_group. What I want to show in the handouts page is something like this:

 

For week 1, all people in training_group 13 and in week 1 would see this:

<h1>Handouts</h1>
<h4>Week 1</h4>
<ul>
<li>Session 1: Study Guide</li>
 <li>Handout 3: Gathering Information</li>
 <li>Handout 4: CPRT Time-Based Preference Asst</li>
 <li>Handout 5: CPRT Paired-Choice Preference Asst</li>
 <li>Session 2: Study Guide</li>
</ul>

For week 2, all people in training_group 13 and in week 2 would see this:

<h4>Week 1</h4>
<ul>
<li>Session 1: Study Guide</li>
 <li>Handout 3</li>
 <li>Handout 4</li>
 <li>Handout 5</li>
 <li>Session 2: Study Guide</li>
</ul>
<h4>Week 2</h4>
<ul>
 <li>Handout 8</li>
 <li>Handout 12</li>
 <li>Handout 15</li>
 <li>Session 3: Study Guide</li>
</ul>

and so forth. If it would be easier, I can also have all of the handout names listed and then as each week goes by, the links will show up as they have completed their training.

 

So on the handouts page, I would join the tables users and handouts together, using what jcbones provided:

 $sql = "SELECT a.handouts AS handout_name FROM `handouts` AS a JOIN user AS b ON a.users = b.users AND a.week = b.week WHERE b.username = '$user_data[username]'"; 

and then pull the data? Would I be using something like

<?php echo $handouts['handout_name'];?>

and then somehow link that to handout_path?

 

Thanks in advance!

[Christian F.]

If I understood your question correctly, you'll have to order by week and handout name. Then when you're building the HTML table you'll need a variable to hold the week number from the previous row, check it up with the week number of the current row, and if different add a new section.

 

All you need, in addition to the regular loop, is one variable and one IF-test at the top of the looping block.

 

$oldWeek = 0;
while ($row = $result->fetch_array ()) {
   if ($oldWeek != $row['week']) {
       $oldWeek = $row['week'];
       // Add new header to the HTML code.
   }
   // Add row data to output.
}

Edited September 25, 2012 by Christian F.