 |
|
|
07-03-2010, 04:51 PM
|
$_post data to mySQL
|
Posts: 173
|
Okay, I am making, I think good headway on my first form. So I have a form that can either display data already in the db or allow for new data all together. I have a SAVE button and an NEW button.
From what I have seen on the forum, you have to check each form variable to see if anything has changed and then submit the data to the database. Is this correct or is there some way that you can change a bit that indicates something has happened on the screen which which would indicate that the user has changed existing data or is adding a new stakeholder?
Couple examples of form code would be appreciated. Thanks
PHP Code:
<?php
// start session
session_start();
// set up some global variables
@include 'menu.php';
@include 'menu_staff.php';
@include 'globalcfg.php';
@include 'db_connect.php';
//Creates list of Positions
$sql= "SELECT * FROM tbl_list_position ORDER BY position ASC ";
$position_list = mysql_query($sql, $link);
if (!$position_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
//Creates list of Countries
$sql= "SELECT * FROM tbl_list_country ORDER BY country ASC ";
$country_list = mysql_query($sql, $link);
if (!$country_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
//Creates list of Countries
$sql= "SELECT * FROM tbl_company ORDER BY company ASC ";
$company_list = mysql_query($sql, $link);
if (!$company_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
if(isset($_POST['rte1'])){
echo 'test: '.$_POST['rte1'].'<br />';
//sql here
echo '<html><head>'.
'<META HTTP-EQUIV="REFRESH" CONTENT="4;URL=display_project_list.php">'.
// '</head><body>'.$_POST['rte1'].'<br />'.
'Suggestion saved</body></html>';
die;
}
$first_name=NULL;
$surname=NULL;
$sso=NULL;
$alias=NULL;
$email=NULL;
$office_phone=NULL;
$cell_phone=NULL;
$position=NULL;
$manager_sso=NULL;
$company=NULL;
$address1=NULL;
$address2=NULL;
$city=NULL;
$state=NULL;
$country=NULL;
$zip_code=NULL;
$office=NULL;
$fax=NULL;
$office_email=NULL;
$website=NULL;
//Looks up address for staff
if(!empty($_GET['staff_id'])) {
$sql= 'SELECT tbl_company.*, tbl_address.* '.
'FROM (tbl_staff INNER JOIN tbl_address ON tbl_staff.address_id = tbl_address.address_id) '.
'INNER JOIN tbl_company ON tbl_staff.company_id = tbl_company.company_id '.
'WHERE staff_id = "'.$_GET['staff_id'].'"';
$office = mysql_query($sql, $link);
if (!$office) {
echo "DB Error, could not query the database\n".
'MySQL Error: ' . mysql_error();
exit;
}
$row_office = mysql_fetch_assoc($office);
//query for staff
{$sql= 'select * from tbl_staff WHERE staff_id ='.$_GET['staff_id'];
$staff = mysql_query($sql, $link);
if (!$staff) {
echo "DB Error, could not query the database\n".
'MySQL Error: '.mysql_error();
exit;
}}
$row = mysql_fetch_assoc($staff);
//query for country
{$sql= 'SELECT country FROM tbl_list_country WHERE country_id = "'.$row_office['country'].'"';
$country_result = mysql_query($sql, $link);
if (!$country_result) {
echo 'DB Error, could not query the database'.
'MySQL Error: '.mysql_error();
exit;
}
$row_country = mysql_fetch_assoc($country_result);
$country=$row_country['country'];
}
$first_name=$row['first_name'];
$surname=$row['surname'];
$sso=$row['sso'];
$alias=$row['alias'];
$email=$row['email'];
$office_phone=$row['phone'];
$cell_phone=$row['cell'];
$position=$row['title'];
$manager_sso=$row['manager_sso'];
$company=$row_office['company'];
$address1=$row_office['address1'];
$address2=$row_office['address2'];
$city=$row_office['city'];
$state=$row_office['state'];
$zip_code=$row_office['zip_code'];
$fax=$row_office['fax'];
$office_email=$row_office['email'];
$website=$row_office['url'];
}
// check if global variables set up
if (!isset($INFO['user_name'])) {
echo 'Configuration file is missing.<br>
Contact your Administrator<br>';
die();
}
//Starts form
echo '<html><body>'.
'<form name="add_stakeholder" action="add_stakeholder.php" method=post>'.
'<table border="1"><tr><td>'.
'<table>'.
' <tr>'.
' <td><button name="submit" type="submit" tabindex="1000">New</button></td><br />'.
' </tr>'.
' <tr>'.
' <td><button name="submit" type="submit" tabindex="1000">Save</button></td><br />'.
' </tr>'.
'</table>'.
'</td><td>'.
'<table>'.
' <tr>'.
' <td>First Name:</td><td><input type="text" name="first_name" value="'.$first_name.'" tabindex="10"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Surname:</td><td><input type="text" name="surname" value="'.$surname.'" tabindex="20"></td><br />'.
' </tr>'.
' <tr>'.
' <td>SSO#:</td><td><input type="text" name="sso" value="'.$sso.'" tabindex="30"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Alias:</td><td><input type="text" name="alias" value="'.$alias.'" tabindex="40"></td><br />'.
' </tr>'.
' <tr>'.
' <td>e-Mail:</td><td><input type="text" name="email" size="40" value="'.$email.'" tabindex="50"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Office Phone:</td><td><input type="text" name="phone" value="'.$office_phone.'" tabindex="60"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Cell Phone:</td><td><input type="text" name="cell" value="'.$cell_phone.'" tabindex="70"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Position:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_project">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($position_list)) {
if ($dd_row['position'] == $position ){
echo '<option selected>' . $dd_row['position'] . '</option>';
}
else{
echo '<option>' . $dd_row['position'] . '</option>';
}
}
echo '</select></td><br>';
echo ' </tr>'.
' <tr>'.
' <td>Managers SSO#:</td><td><input type="text" name="manager_sso" value="'.$manager_sso.'" tabindex="80"></td><br />'.
' </tr>'.
'</table>'.
'</td><td>'.
'<table>'.
' <tr>'.
' <td>Company:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_company">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($company_list)) {
if ($dd_row['company'] == $company ){
echo '<option selected>' . $dd_row['company'] . '</option>';
}
else{
echo '<option>' . $dd_row['company'] . '</option>';
}
}
echo '</select></td><br>';
echo ' </tr>'.
' <tr>'.
' <td>address:</td><td><input type="text" name="adress1" size="40" value="'.$address1.'" tabindex="120"></td><br />'.
' </tr>'.
' <tr>'.
' <td></td><td><input type="text" name="adress2" size="40" value="'.$address2.'" tabindex="130"></td><br />'.
' </tr>'.
' <tr>'.
' <td>city:</td><td><input type="text" name="city" value="'.$city.'" tabindex="140"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Province:</td><td><input type="text" name="province" value="'.$state.'" tabindex="140"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Country:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_country">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($country_list)) {
if ($dd_row['country'] == $country ){
echo '<option selected>' . $dd_row['country'] . '</option>';
}
else{
echo '<option>' . $dd_row['country'] . '</option>';
}
}
echo '</select></td><br>';
echo ' </tr>'.
' <tr>'.
' <td>Postal Code:</td><td><input type="text" name="postal" value="'.$zip_code.'" tabindex="140"></td><br />'.
' </tr>'.
' <tr><td>Office</td><td><input type="text" name="office" value="'.$office.'" tabindex="165"></td><br /></tr>'.
' <tr>'.
' <td>Fax</td><td><input type="text" name="fax" value="'.$fax.'" tabindex="170"></td><br />'.
' </tr>'.
' <tr>'.
' <td>e-Mail:</td><td><input type="text" name="email" size="40" value="'.$office_email.'" tabindex="180"></td><br />'.
' </tr>'.
' <tr>'.
' <td>Website:</td><td><input type="text" name="website" size="40" value="'.$website.'" tabindex="190"></td><br />'.
' </tr>'.
'</table>'.
'</td></tr></table>'.
'</form>'.
'</body></html>';
// dispose of result set
mysql_free_result($office);
mysql_free_result($staff);
// close connection to database server
mysql_close($link);
?>
|
|
|
|
|
07-07-2010, 01:27 PM
|
Re: $_post data to mySQL
|
Posts: 173
|
Am I missing something with my $_post statement, the variable appears to be populated as shown in the attached screen shoot but when I check if isset, it appears to be blank
I have attached all my code for reference.
PHP Code:
<?php
// start session
session_start();
// set up some global variables
@include 'menu.php';
@include 'menu_staff.php';
@include 'globalcfg.php';
@include 'db_connect.php';
if(isset($_POST['$staff_id'])){
$sql='UPDATE tbl_staff '.
'SET first_name='.$first_name.', surname='.$surname.', sso='.$sso.', alias='.$alias.', '.
' email='.$email.', phone='.$office_phone.', ext='.$ext.', cell='.$cell_phone.', '.
' title='.$position.', manager_sso='.$manager_sso.', '.
' company_id='.$company_id.', address_id='.$address_id.', '.
'WHERE staff_id='.$staff_id;
$result= mysql_query($sql, $db_server);
if (!$result) {
echo 'DB Error, Module update not effective. MySQL Error: '.mysql_error();
exit;
}
echo '<html><head>'.
'<META HTTP-EQUIV="REFRESH" CONTENT="10;URL=display_project_team.php">'.
'Suggestion saved</body></html>';
die;
}
if(isset($_POST['$first_name'])) echo $_POST['$first_name'];
if(isset($_POST['$first_name'])){
$staff_id=$_POST['$staff_id'];
if(isset($_POST['$first_name'])) $first_name=$_POST['$first_name'];
if(isset($_POST['$surname'])) $surname=$_POST['$surname'];
if(isset($_POST['$sso'])) $sso=$_POST['$sso'];
if(isset($_POST['$alias'])) $alias=$_POST['$alias'];
if(isset($_POST['$email'])) $email=$_POST['$email'];
if(isset($_POST['$office_phone'])) $office_phone=$_POST['$office_phone'];
if(isset($_POST['$ext'])) $ext=$_POST['$ext'];
if(isset($_POST['$cell_phone'])) $cell_phone=$_POST['$cell_phone'];
if(isset($_POST['$position'])) $position=$_POST['$position'];
if(isset($_POST['$manager_sso'])) $manager_sso=$_POST['$manager_sso'];
$sql_begin='INSERT INTO tbl_staff (first_name, surname';
$sql_end='VALUES ( '.$first_name.', "'.
$surname.'"';
if(!empty($sso)){
$sql_begin.=', sso';
$sql_end.=', "'.($sso).'"';
}
if(!empty($alias)){
$sql_begin.=', alias';
$sql_end.=', "'.($alias).'"';
}
if(!empty($email)){
$sql_begin.=', email';
$sql_end.=', "'.($email).'"';
}
if(!empty($office_phone)){
$sql_begin.=', office_phone';
$sql_end.=', "'.($office_phone).'"';
}
if(!empty($ext)){
$sql_begin.=', ext';
$sql_end.=', "'.($ext).'"';
}
if(!empty($cell_phone)){
$sql_begin.=', cell_phone';
$sql_end.=', "'.($cell_phone).'"';
}
if(!empty($position)){
$sql_begin.=', position';
$sql_end.=', "'.($position).'"';
}
if(!empty($manager_sso)){
$sql_begin.=', manager_sso';
$sql_end.=', "'.($manager_sso).'"';
}
$sql = $sql_begin.') '.$sql_end.')';
if (!mysql_query($sql, $db_server)) {
echo '4INSERT failed:'. $sql .'*** '. ($value->line).'<br />'.mysql_error().'<br /><br />';
}
echo '<html><head>'.
'<META HTTP-EQUIV="REFRESH" CONTENT="10;URL=display_project_team.php">'.
'Suggestion saved</body></html>';
die;
}
//Creates list of Positions
$sql= "SELECT * FROM tbl_list_position ORDER BY position ASC ";
$position_list = mysql_query($sql, $link);
if (!$position_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
//Creates list of Countries
$sql= "SELECT * FROM tbl_list_country ORDER BY country ASC ";
$country_list = mysql_query($sql, $link);
if (!$country_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
//Creates list of Countries
$sql= "SELECT * FROM tbl_company ORDER BY company ASC ";
$company_list = mysql_query($sql, $link);
if (!$company_list) {
echo 'DB Error, could not query the database. MySQL Error: ' . mysql_error();
exit;
}
$company_id=NULL;
$staff_id=NULL;
$project_id=NULL;
$address_id=NULL;
$first_name=NULL;
$surname=NULL;
$sso=NULL;
$alias=NULL;
$email=NULL;
$office_phone=NULL;
$ext=NULL;
$cell_phone=NULL;
$position=NULL;
$manager_sso=NULL;
$company=NULL;
$address1=NULL;
$address2=NULL;
$city=NULL;
$state=NULL;
$country=NULL;
$zip_code=NULL;
$office=NULL;
$fax=NULL;
$office_email=NULL;
$website=NULL;
//Looks up address for staff
if(!empty($_GET['staff_id'])) {
$sql= 'SELECT tbl_company.*, tbl_address.* '.
'FROM (tbl_staff INNER JOIN tbl_address ON tbl_staff.address_id = tbl_address.address_id) '.
'INNER JOIN tbl_company ON tbl_staff.company_id = tbl_company.company_id '.
'WHERE staff_id = "'.$_GET['staff_id'].'"';
$office = mysql_query($sql, $link);
if (!$office) {
echo "DB Error, could not query the database\n".
'MySQL Error: ' . mysql_error();
exit;
}
$row_office = mysql_fetch_assoc($office);
//query for staff
{$sql= 'select * from tbl_staff WHERE staff_id ='.$_GET['staff_id'];
$staff = mysql_query($sql, $link);
if (!$staff) {
echo "DB Error, could not query the database\n".
'MySQL Error: '.mysql_error();
exit;
}}
$row = mysql_fetch_assoc($staff);
//query for country
{$sql= 'SELECT country FROM tbl_list_country WHERE country_id = "'.$row_office['country'].'"';
$country_result = mysql_query($sql, $link);
if (!$country_result) {
echo 'DB Error, could not query the database'.
'MySQL Error: '.mysql_error();
exit;
}
$row_country = mysql_fetch_assoc($country_result);
$country=$row_country['country'];
}
$staff_id=$_GET['staff_id'];
$first_name=$row['first_name'];
$surname=$row['surname'];
$sso=$row['sso'];
$alias=$row['alias'];
$email=$row['email'];
$office_phone=$row['phone'];
$cell_phone=$row['cell'];
$ext=$row['ext'];
$position=$row['title'];
$manager_sso=$row['manager_sso'];
$company=$row_office['company'];
$address1=$row_office['address1'];
$address2=$row_office['address2'];
$city=$row_office['city'];
$state=$row_office['state'];
$zip_code=$row_office['zip_code'];
$fax=$row_office['fax'];
$office_email=$row_office['email'];
$website=$row_office['url'];
// dispose of result set
mysql_free_result($office);
mysql_free_result($staff);
// close connection to database server
mysql_close($link);
}
// check if global variables set up
if (!isset($INFO['user_name'])) {
echo 'Configuration file is missing.<br>
Contact your Administrator<br>';
die();
}
//Starts form
echo '<html><body>'.
'<form name="add_stakeholder" action="add_stakeholder.php" method=post>'.
'<table border="1"><tr><td>'.
'<table>'.
' <tr><td><button name="submit" type="submit" tabindex="1000">New</button></td></tr>'.
' <tr><td><button name="submit" type="submit" tabindex="1000">Save</button></td></tr>'.
'</table>'.
'</td><td>'.
'<table>'.
' <tr><td></td><td><input type="hidden" name="staff_id" value="'.$staff_id.'" ></td></tr>'.
' <tr><td>First Name:</td><td><input maxlength="45" type="text" name="first_name" value="'.$first_name.'" tabindex="10"></td></tr>'.
' <tr><td>Surname:</td><td><input type="text" maxlength="45" name="surname" value="'.$surname.'" tabindex="20"></td></tr>'.
' <tr><td>SSO#:</td><td><input type="text" maxlength="10" name="sso" value="'.$sso.'" tabindex="30"></td></tr>'.
' <tr><td>Alias:</td><td><input type="text" maxlength="45" name="alias" value="'.$alias.'" tabindex="40"></td></tr>'.
' <tr><td>e-Mail:</td><td><input type="text" maxlength="45" name="email" size="40" value="'.$email.'" tabindex="50"></td></tr>'.
' <tr><td>Desk Phone:</td><td><input type="text" maxlength="15" name="phone" value="'.$office_phone.'" tabindex="60">ext<input type="text" maxlength="6" name="ext" value="'.$ext.'" tabindex="62"></td></tr>'.
' <tr><td>Cell Phone:</td><td><input type="text" name="cell" maxlength="15" value="'.$cell_phone.'" tabindex="70"></td></tr>'.
' <tr><td>Position:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_project">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($position_list)) {
if ($dd_row['position'] == $position ){
echo '<option selected>' . $dd_row['position'] . '</option>';
}
else{
echo '<option>' . $dd_row['position'] . '</option>';
}
}
echo '</select></td><br></tr>'.
' <tr><td>Managers SSO#:</td><td><input type="text" name="manager_sso" value="'.$manager_sso.'" tabindex="80"></td></tr>'.
'</table>'.
'</td><td>'.
'<table>'.
' <tr><td>Company:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_company">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($company_list)) {
if ($dd_row['company'] == $company ){
echo '<option selected>' . $dd_row['company'] . '</option>';
}
else{
echo '<option>' . $dd_row['company'] . '</option>';
}
}
echo '</select></td></tr>'.
' <tr><td>Address:</td><td><input type="text" name="adress1" size="40" value="'.$address1.'" tabindex="120"></td></tr>'.
' <tr><td></td><td><input type="text" name="adress2" size="40" value="'.$address2.'" tabindex="130"></td></tr>'.
' <tr><td>City:</td><td><input type="text" name="city" value="'.$city.'" tabindex="140"></td></tr>'.
' <tr><td>Province:</td><td><input type="text" name="province" value="'.$state.'" tabindex="140"></td></tr>'.
' <tr><td>Country:</td>';
//Creates drop down list of positions
echo '<td><select name="dd_country">'.
'<option selected></option>';
while ($dd_row = mysql_fetch_assoc($country_list)) {
if ($dd_row['country'] == $country ){
echo '<option selected>' . $dd_row['country'] . '</option>';
}
else{
echo '<option>' . $dd_row['country'] . '</option>';
}
}
echo '</select></td></tr>'.
' <tr><td>Postal Code:</td><td><input type="text" name="postal" value="'.$zip_code.'" tabindex="140"></td></tr>'.
' <tr><td>Main Switchboard:</td><td><input type="text" name="office" value="'.$office.'" tabindex="165"></td></tr>'.
' <tr><td>Primary Fax:</td><td><input type="text" name="fax" value="'.$fax.'" tabindex="170"></td></tr>'.
' <tr><td>e-Mail:</td><td><input type="text" name="email" size="40" value="'.$office_email.'" tabindex="180"></td></tr>'.
' <tr><td>Website:</td><td><input type="text" name="website" size="40" value="'.$website.'" tabindex="190"></td></tr>'.
'</table>'.
'</td></tr></table>'.
'</form>'.
'</body></html>';
?>
|
|
|
|
|
07-08-2010, 08:23 AM
|
Re: $_post data to mySQL
|
Posts: 79
Name: Ed Barnett
|
Hi mate.
If I was you I'd do it like this..
Someone hits 'Save' then the script runs to update the database. Checking to see if anything has changed in the form would either require i) javascript (to check before posting the data) or ii) once the form data has been POSTed you retrieve the existing info from the database and use PHP to compare the two.
I wouldn't use either of i) or ii). I'd just run the update script every time the user hits 'Save' without comparing the existing data in the database. Does anyone feel any differently on this point?
One thing you need to be careful of is what users will put INTO your text fields. This is a security risk. For example... if someone put an apostrophe into one of your text fields it could cause the script to fail. That's the most basic example.
I'd suggest using mysql_real_escape_string($var) on any data you are looking to send to a MYSQL database at the very minimum.
Last edited by EdB; 07-08-2010 at 08:25 AM..
Reason: I messed up one bit
|
|
|
|
|
07-08-2010, 08:49 AM
|
Re: $_post data to mySQL
|
Posts: 22
|
If you will focus if method in form is post or it is get.
and this line - if(isset($_POST['$staff_id'])){
if it is "POST" the above line will be like as below without "$" in $staff_id;
if(isset($_POST['staff_id'])){
// do this
}
if it is "GET" the above line will be
if(isset($_GET['staff_id'])){
// do this
}
I hope that rest of the aspects will be better handled by yourself.
You may read some more free php scripts by searching in search engines.
|
|
|
|
|
07-08-2010, 09:10 AM
|
Re: $_post data to mySQL
|
Posts: 173
|
Thank you both for your input.
I will look to address security concern as you pointed out.
Thank you for pointing out the errors of my way. Sometimes it just takes another pair of eyes and a slap in the face.  |
|
|
|
|
|
« Reply to $_post data to mySQL
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
