Help posting data to database

brandonboyce · January 7, 2011

I'm not much of a PHP programmer so please excuse the mess but I'm having a problem with some code. I am able to successfully build a table and assign values to radio buttons based one information in a database. The problem comes when I want to update the database when someone changes a radio button. Everthing works up until the foreach statment. Nothing in the database is being updated, if I replace the sql statement with echo $v; all I get is the word Submit so it's like the rest of the values aren't being included in the form.

<link href="css/demo.css" rel="stylesheet" type="text/css">

<?php
session_start();
// dBase file
        include "dbconfig.php";
if (!$_SESSION["valid_user"])
        {
        // User not logged in, redirect to login page
        Header("Location: index.php");
        }
//Grab GUID passed in the URL
$guid=$_GET['guid'];
// Create query for student name
  $sql = "SELECT FirstName, LastName FROM Students where GUID ='". $guid . "'";
  // Run query
  $result = mssql_query($sql);
  $row= mssql_fetch_assoc($result);
  
// Member only content
//Title and main page information
echo "<div align=\"center\"><p><img src=\"images/EFCTS Logo.jpg\" width=\"600\" height=\"144\" /></p><h1>OCAP DATABASE</h1></div>";
echo "<p><a href=\"logout.php\">Logout</a></p>";
//Build sortable table of student data.
echo "<table id=\"test1\" class=\"sortable-onload-3-reverse rowstyle-alt no-arrow\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">";

//query database for table information
$SQLcommand = "SELECT convert(varchar(36), StudentGUID) as StudentGUID, CompetencyLevel, CompetencyName, CompetencyID, Category, convert(varchar(36), CompetencyGUID) as CompetencyGUID FROM VW_StudentSkills WHERE StudentGUID ='". $guid ."'";
$SQLresult = MsSQL_QUERY($SQLcommand);

//Continue building table
echo "<caption>".$row[FirstName]." ".$row[LastName]."</caption>";
echo "<th style=\"-moz-user-select: none;\" class=\"sortable-numeric fd-column-0\"><a title=\"Sort on \"Competency ID\"\" href=\"#\">Competency ID</a></th>";
echo "<th style=\"-moz-user-select: none;\" class=\"fd-column-1 sortable-text reverseSort\"><a title=\"Sort on \"Catagory\"\" href=\"#\">Catagory</a></th>";
echo "<th style=\"-moz-user-select: none;\" class=\"fd-column-2 sortable-text reverseSort\"><a title=\"Sort on \"Competency Name\"\" href=\"#\">Competency Name</a></th>";
echo "<th style=\"-moz-user-select: none;\" class=\"sortable-numeric fd-column-3\"><a title=\"Sort on \"Competency Level\"\" href=\"#\">Competency Level</a></th>";
echo "</tr></thead>";
//Set variables for radio buttons
$a="";
$b="";
$c="";
$d="";

//count the number of rows returned
$count=mysql_num_rows($SQLresult);
//Start form
echo "<form name=\"form1\" method=\"post\" action=\"".$_SERVER['REQUEST_URI']."\">";

$groupnum = 0;
//Build table with SQL data
while($row2=mssql_fetch_array($SQLresult)) 
{

//find the competency level
$complevel = $row2[CompetencyLevel];
if($complevel=="1") $a="checked";
if($complevel=="2") $b="checked";
if($complevel=="3") $c="checked";
if($complevel=="4") $d="checked";
//build the table
echo "<tr class=\"\"><td>".$row2[CompetencyID]."</td><td>".$row2[Category]."</td>";
echo "<td>".$row2[CompetencyName]."</td>";
echo "<td class=\"lft\"><input type=\"radio\" name=\"".$row2[CompetencyID]."\" value=\"1\"".$a."> 1";
echo "<input type=\"radio\" name=\"".$row2[CompetencyID]."\" value=\"2\"".$b."> 2";
echo "<input type=\"radio\" name=\"".$row2[CompetencyID]."\" value=\"3\"".$c."> 3";
echo "<input type=\"radio\" name=\"".$row2[CompetencyID]."\" value=\"4\"".$d."> 4";
$idarray[]=$row2[CompetencyGUID];
echo "</td></tr>";
$groupnum++;
//clear the variables
$a="";
$b="";
$c="";
$d="";
}

echo "</tbody>";
echo "<input type=\"submit\" name=\"Submit\" value=\"Submit\">";
echo "</form></table>";
echo "<script type=\"text/javascript\" src=\"javascript/tablesort.js\"></script>";

                  if($_POST['Submit'])
                        {
                           echo"submit button detected";
					   foreach($_POST as $k=>$v)
                           {                               
                             if($k!="Submit") { 
                              $sqlUpdate = "UPDATE DATA SET COMPETENCYLEVEL = '".$v."' WHERE COMPETENCYGUID = '".$idarray."' AND STUDENGUID = '".$guid."' ";
                              $resultUpdate = mssql_query($sqlUpdate);
						  }

                           }
                        }
					if($resultUpdate)
                        {    print_r ($_POST); 
						echo $resultUpdate;     
						echo $sqlUpdate;                  
                                                      
                        }
                        else
                        {
                           echo "Your entry is not completed at this time.............";
					   echo $sqlUpdate;
					   echo $resultUpdate;
                        }
                    
// Display Member information
//echo "<p>User ID: " . $_SESSION["valid_id"];
//echo "<p>Username: " . $_SESSION["valid_user"];
//echo "<p>Logged in: " . date("m/d/Y", $_SESSION["valid_time"]);

?>

Pikachu2000 · January 7, 2011

Seeing what the $_POST array actually holds may help shed some light on this. Place this at the beginning of your if($_POST['submit']) { conditional

echo '<pre>';
print_r($_POST);
echo '</pre>';

brandonboyce · January 7, 2011

That part seems to work ok, all of the results were returned. At the end where it checks the results in the IF ELSE statement it is displaying the information in the ELSE section which means the query didn't work, and it is displaying UPDATE DATA SET COMPETENCYLEVEL = '4' WHERE COMPETENCYGUID = 'Array' AND STUDENGUID = '32A0EF05-EECB-4E5A-A4D0-F0ED1EFD35F6'

So it looks like everything is working except for the idarry variable.

kirk112 · January 7, 2011

Hi if you

echo '<pre>';
print_r($idarray);
echo '</pre>';

What output do you get

brandonboyce · January 7, 2011

That outputs the appropriate information as well but thinking about it I am not sure it would work for me doing it the way I am doing it anyway. I think it might work better for me if I put the $row2[CompetencyGUID]; into a hidden input field so that the radio buttons and the hidden field are matched up approprately. Right now there isn't anything that matches a row number with the idarray.

If I did that though, I'm not sure how I would get that information from the hidden field into the sql statment. Or would you recommend another way to accomplish this?

Pikachu2000 · January 7, 2011

Don't really have enough information to say for sure. What is the logical flow of this? Are multiple radio groups in the form? Are they all associated with the same record set, or is more than one record set also in the form?

kirk112 · January 7, 2011

If you change

$idarray[]=$row2[CompetencyGUID];

to

$idarray=$row2[CompetencyGUID];

Then you sql will look like

UPDATE DATA SET COMPETENCYLEVEL = '4' WHERE COMPETENCYGUID = '1' AND STUDENGUID = '32A0EF05-EECB-4E5A-A4D0-F0ED1EFD35F6'

Currently $idarray is being define as an array

brandonboyce · January 7, 2011

Essentially I build a table from values in a database with 4 radio buttons for each row. This allows a rating of 1 - 4, it shows the current rating based on what is in the database. If there is no rating it is blank and I want to update the rating based on the value selected. So far everything works except I need way to match the ID of the row with the rating that was selected. I've attached a screen shot of the end result if it helps make sense of what I am trying to accomplish. Thanks for your help so far, atleast I know I am on the right track.

[attachment deleted by admin]

brandonboyce · January 7, 2011

If you change

$idarray[]=$row2[CompetencyGUID];

to

$idarray=$row2[CompetencyGUID];

Then you sql will look like

UPDATE DATA SET COMPETENCYLEVEL = '4' WHERE COMPETENCYGUID = '1' AND STUDENGUID = '32A0EF05-EECB-4E5A-A4D0-F0ED1EFD35F6'

Currently $idarray is being define as an array

I think it needs to be an array as there are a lot of values that I need it to store. I could be wrong, I'm not much of a programmer, I just kind of got roped into the project because I was in the wrong place at the wrong time.

kirk112 · January 7, 2011

You do need the array, after

 
if($_POST['Submit'])
                        {
                           echo"submit button detected";
echo '<pre>';
print_r($_POST);
echo '</pre>';

echo '<pre>';
print_r($idarray);
echo '</pre>';

and output the results.

Does CompetencyGUID contain the id number of each question?

brandonboyce · January 7, 2011

Yes, ComptetencyGUID is the id for each of the questions, so I need to keep that ID matched with the value of the of the radio button so that I can put it into the database.

brandonboyce · January 7, 2011

I figured out part of my problem. I am using some javascript I found that allows you to sort the table, when you sort the table then it seems to break the form as no values are returned when i print_r($_POST). If I don't change the sort order print_r($_POST) works fine.

brandonboyce · January 7, 2011

Well I am partially there, the database is updating with the proper values. I had to remove the sort feature for now. The one thing I want to do is refresh the page with the new info after it is submitted right now after you press submit it reverts the button back to the location it was in before it was changed. If I go back and load the page again then the update shows the proper button selected. I thought the code below would do it but it doesn't work. It echos Success at the bottom of the page after you hit submit but it doesn't take you to the page I specified in the Header field.

if($resultUpdate)
{    
echo "Success";
Header("Location:InstructorHome.php");            
                                                      
  }
   else
     {
          echo "Update your records if needed.";

       }

BlueSkyIS · January 7, 2011

you can't output anything before using header()

brandonboyce · January 7, 2011

you can't output anything before using header()

Ok, I found another piece of code that did the trick

<META HTTP-EQUIV="refresh" content="0;URL=InstructorHome.php">

brandonboyce · January 7, 2011

In case anybody is curious this is the code I ended up with after tweaking everything. It probably isn't very good but it does the job. Thanks for all your help!

$a="";
$b="";
$c="";
$d="";

//count the number of rows returned
$count=mssql_num_rows($SQLresult);
//Start form
echo "<form name=\"form1\" method=\"post\" action=\"\">";

$groupnum = 1;
//Build table with SQL data
while($row2=mssql_fetch_array($SQLresult)) 
{

//find the competency level
$complevel = $row2[CompetencyLevel];
if($complevel=="1") $a="checked";
if($complevel=="2") $b="checked";
if($complevel=="3") $c="checked";
if($complevel=="4") $d="checked";
//build the table
echo "<tr class=\"\"><td>".$row2[CompetencyID]."</td><td>".$row2[Category]."</td>";
echo "<td>".$row2[CompetencyName]."</td>";
echo "<td class=\"lft\"><input type=\"radio\" name=\"".$groupnum."\" value=\"1\"".$a."> 1";
echo "<input type=\"radio\" name=\"".$groupnum."\" value=\"2\"".$b."> 2";
echo "<input type=\"radio\" name=\"".$groupnum."\" value=\"3\"".$c."> 3";
echo "<input type=\"radio\" name=\"".$groupnum."\" value=\"4\"".$d."> 4";
$idarray[$groupnum]=$row2[CompetencyGUID];
echo "</td></tr>";
$groupnum++;
//clear the variables
$a="";
$b="";
$c="";
$d="";
}

echo "</tbody><input type=\"submit\" name=\"Submit\" value=\"Submit\">";
echo "</form>";

echo "</table>";


                  if($_POST['Submit'])
                        {
					   $i=1;
					   foreach($_POST as $k=>$v)
                           {                               
                             if($k!="Submit") { 
                              $sqlUpdate = "UPDATE DATA SET COMPETENCYLEVEL = '".$v."' WHERE COMPETENCYGUID = '".$idarray[$i]."' AND STUDENTGUID = '".$guid."' ";
                              $resultUpdate = mssql_query($sqlUpdate);
						  $i++;
						  }

                           }
                        }
					if($resultUpdate)
                        {    
						echo "<META HTTP-EQUIV=\"refresh\" content=\"0;URL=InstructorHome.php\">";            
                                                      
                        }
                        else
                        {
                           echo "Update your records if needed.";

                        }

Sign In

Help posting data to database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information