Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    PHP Check box form

    I'm certain that this is easy.... for some one! I have an application which stores user skills for our employees. The user can log in and click on a menu item which will display (results from a query) their unachieved skills, with a check box, the intention of which it to have them check which skills they can perform.

    I can make a checkbox form using values I already know, but I do not know how to to deal with the results of a query being the values. What I would like to have happen is that when a user checks one or more skill check boxes, the field "achieved" in the db would be set or updated to "Yes". Each skill has a unique user_skillID, so that is the field I am looking to "hook up" with its "achieved" field when the user checks the box. I have it going to a changeskillsrecord.php page, but I'm not really sure what to put in there either.

    I hope someone can help me with this, I just can't seem to get my head around it! Currently, the query below displays all the skills that a user would have NOT achieved, for the Access 2007 topic.

    Code:
    <html>
    <head>
    <title>Microsoft Access 2007 Skills Listing</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <link href="Includes/si.css" rel="stylesheet" type="text/css" />
    </head>
    
    <body topmargin="5" leftmargin="0">
    <table width="980" align="center" cellpadding = "0">
    <td>
    <tr>
    <th align="center">
       <b>Microsoft Access 2007 skills for 
    <?php
    echo $_SESSION['fname'];
    echo '&nbsp;';
    echo $_SESSION['lname'];
    echo ":";
    ?> 
    <br/><p>(If no skills appear, there are no skills for you to update.)</p>
    </th>
    </tr>
    </td>
    <tr>
    <td>
    <?php require 'Includes/topicsmenu.php'; ?><br />
    </tr>
    </td>
    </table ><br />
    
    <table width="800" align="center" border="1">
    <td>
    <tr>
    		
    		<th>Skill ID</th>
    		<th>Skill Description</th>
    		<th>Select Achieved Skills</th>
    		
    </tr>
    </td>
    
    <?php
    mysql_select_db($database_skills, $skills);
    
    $query_MySkills = "SELECT user_skills.user_skillID, skills.topic, skills.skill, user_skills.tngdate, user_skills.achieved, users.username, DATE_FORMAT(`tngdate`,'%d-%b-%y') AS tngdate
                   FROM skills, users, user_skills
                   WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'N/A' AND user_skills.achieved !='Yes'
    		AND user_skills.skillID=skills.skillID
    		AND skills.topic = 'Access'
                   ORDER BY topic, skill";
    
    $MySkills = mysql_query($query_MySkills, $skills) or die(mysql_error());
    //$row_MySkills = mysql_fetch_assoc($MySkills);
    $totalRows_MySkills = mysql_num_rows($MySkills);
    
    $result = mysql_query($query_MySkills, $skills);
    
    while($row_MySkills = mysql_fetch_assoc($MySkills))
    		{
    		echo '<tr>';
    		echo '<td align="center">' . ($row_MySkills['user_skillID']).'</td>';
    		echo '<td align="left">&nbsp;&nbsp;' . ($row_MySkills['skill']). '</td>';
    		echo '<td <form method="POST" action="changeskillsrecord.php">
    <p align="center"><input type="checkbox" name="user_skillID[]" value="$row_MySkills[user_skillID]">';	
    		echo '</tr>';
    	}
    ?>
    <br /><br />
    <td>
    <td>
    <td>
    <p align="center"><input type="submit" value="Select"></p>
    </form>
    </td>
    </td>
    </td>
    </table>
    </body>
    </html>

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,456
    Thanks
    8
    Thanked 1,084 Times in 1,075 Posts
    There are three parts to your problem ...

    1) Processing the checkboxes.
    You are using a checkbox array, which is fine.
    When the form is processed, you have to loop through the checkbox array,
    and pick out the values. The array will only contain the checkboxes that
    were checked. So the value needs to be some sort of code so you know
    which one was checked. Don't use the name of your MySQL column, because
    anyone can see that in the HTML source.

    2) Saving the checkbox array into your MySQL table.
    You already know who the person is, so you need to loop through that
    checkbox array and determine which column to update to "YES" for that user.
    I don't know if you have separate columns (variables) for each skill?

    3) Re-displaying your form with the checkboxes checked.
    Each time you display your form, you have to query the database and pull-out
    all of the skill variables. If the variable says "YES", then you have to add "checked"
    in that <input ... checkbox> tag. This will be done in a loop or individually as
    PHP builds your dynamic form.

    hint: instead of using the word "YES" for the value of the skill, use the word "checked".
    then, you can use it directly in your <input ... checkbox> tag.


    I hope all that makes sense, or at least triggers some "aha" moments.



    .
    Last edited by mlseim; 11-15-2010 at 10:57 PM.

  • #3
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you for your reply.

    #1: "When the form is processed, you have to loop through the checkbox array, and pick out the values. The array will only contain the checkboxes that were checked. So the value needs to be some sort of code so you know which one was checked." I was intending to use the "user_skillID", which is a unique number of a particular skill for a user.

    #2: The column to update to "Yes" for the selected user is the "achieved" column in the "user_skills" table. This table uses a many-to-many relationship of users to skills, skills to users, from a table called "skills" which contains the skills, and a table called "users" which contains the users, comprised of foreign keys, creating this table called "user_skills" which contains each user and their required skills (each user can have many skills, and each skill can have many users).

    #3: Don't know how to do this.

    No aha moments here. I'm not that experienced with this - if I can see instructions, I can "plug in" my stuff to make it work, but I have no idea how to create it from scratch.
    Last edited by jseth; 11-16-2010 at 02:54 PM.

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,456
    Thanks
    8
    Thanked 1,084 Times in 1,075 Posts
    So I'm not following the skills thing ...

    Does each user have a MySQL row like this?

    UserID,skill1,skill2,skill3,skill4 .... etc.

    So you have a column for each skill?

    Or do you have a row for each skill ...

    userID,skill1
    userID,skill2
    userID,skill3

  • #5
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    A row for each skill. The link table called user_skills has the following columns:
    user_skillID - the auto increment number of the record
    userID - linked to the users table, this is the userID for each user
    skillID - linked to the skillID in the skills table, this is the skillID for each skill
    tngdate - a date field where the date of training, if needed, can be entered
    achieved - initially, this field is null, can be Yes or N/A

    Looks like:
    user_skillID, userID, skillID, tngdate, achieved

    What I can't seem to fathom is how to get the check box that the user clicks on associated with the user_skillID, so that that user_skillID's achieved field gets updated to "Yes".
    Last edited by jseth; 11-16-2010 at 06:41 PM.

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,456
    Thanks
    8
    Thanked 1,084 Times in 1,075 Posts
    I will have to study it a bit more.

  • #7
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok, I've got it figured out to where the user can check their skills, and it will update the achieved field for that skill record to "Yes"!

    On the form processing page, I have it displaying to the screen which skillIDs the user has selected to update, and also code verifying that they have actually updated.

    Here's my NEW problem! Only one of the records in the array is updating, not all of them. Here's my code for the input form:
    Code:
    ...connect to database...
    
    <table width="800" align="center" border="1">
    <td>
    <tr>
    		
    		<th>Skill ID</th>
    		<th>Skill Description</th>
    		<th>Select Achieved Skills</th>
    		
    </tr>
    </td>
    
    <?php
    mysql_select_db($database_skills, $skills);
    
    $query_MySkills = "SELECT user_skills.user_skillID, skills.topic, skills.skill, user_skills.tngdate, user_skills.achieved, users.username, DATE_FORMAT(`tngdate`,'%d-%b-%y') AS tngdate
                   FROM skills, users, user_skills
                   WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'N/A' AND user_skills.achieved !='Yes'
    		AND user_skills.skillID=skills.skillID
    		AND skills.topic = 'Access'
                   ORDER BY topic, skill";
    
    $result = mysql_query($query_MySkills) or die(mysql_error());
    
    while($row = mysql_fetch_array($result)){
    		
    		echo '<tr>';
    		echo '<td align="center">' . ($row['user_skillID']).'</td>';
    		echo '<td align="left">&nbsp;&nbsp;' . ($row['skill']). '</td>';
    		echo '<td <form method="POST" action="changeskillsrecord.php">
    <p align="center"><input type="checkbox" name="ach_skillID[];" value='. ($row['user_skillID']).'>';	
    		echo '</tr>';
    	}
    
    ?>
    
    <br /><br />
    <td>
    <td>
    <td>
    <p align="center"><input type="submit" name="formSelect" value="Select"></p>
    </form>
    </td>
    </td>
    </td>
    </table>
    </body>
    </html>
    and this is the code for the processing form:

    Code:
    ...connect to database...
    
    <html>
    <head>
    <title>Change My Skills Record Form</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <link href="Includes/si.css" rel="stylesheet" type="text/css" />
    </head>
    
    <body topmargin="5" leftmargin="0">
    
    <table width="980" align="center" border="1">
    <td>
    <tr>
    <th align ="left">
    You have selected the following skill(s) as achieved:<br />
    
    <?php
    
    foreach($ach_skillID as $value){
    echo $value;
    echo "<br />";
    }
    
    ?>
    <?php
    mysql_select_db($database_skills, $skills);
    $result = mysql_query("UPDATE user_skills SET achieved='Yes' WHERE user_skillID = $value") or die(mysql_error());
    
    echo "The following skill(s) have been updated:";
    print "$value";
    
    ?>
    </body>
    </html>
    When I select more than one skill, the output of the foreach statement displays all of the records selected, but only the last skill displays in the print "$value" statement, and indeed, only the last record is actually updated in the database.

    How do you make all of the records update? Thanks!

  • #8
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,292
    Thanks
    4
    Thanked 202 Times in 199 Posts
    Something along these lines. You need to do the updating as you go through the check boxes.

    PHP Code:
    <?php
    mysql_select_db
    ($database_skills$skills);

    foreach(
    $ach_skillID as $value){

    $result mysql_query("UPDATE user_skills SET achieved='Yes' WHERE user_skillID = $value") or die(mysql_error());

    echo 
    "The following skill has been updated: $value <br />";
    }

    ?>
    Dave .... HostMonster for all of your hosting needs

  • Users who have thanked djm0219 for this post:

    jseth (11-17-2010)

  • #9
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hurray!!! Thank you SO much! That's it!!!! Thank you again!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •