Help with MySQL UPDATE Query

Question

dennishall 0 Junior Poster in Training

13 Years Ago

Hi All:

I have 2 php files feeding one MySql record.

I have an INSERT query in file1.php working fine, it then sends me to a highly animated form file2.php that calls a processor.php file.

The processor.php file needs to update the mySql record.
I have tested the processor file by using a MySQL INSERT query and it all works fine, however, when I use the following code (my 5th query version for this), the php file fails telling me I have a mySql error.

My objective is to read teh session varialbe to locate the MySql record as the record was created with file1.php.

$_SESSION['Email'] = "Email";

$sql="UPDATE roi SET (software, patients, reimbursement, rejection) VALUES ('$_POST[software]','$_POST[patients]','$_POST[reimbursement]','$_POST[rejection]') WHERE Email='$_SESSION[Email]'";
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }

If someone could please assist me with this I would appreciate it very much.

Best Regards,
dennishall

php

3 Contributors
7 Replies
319 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by dennishall

All 7 Replies

diafol

13 Years Ago

$sql="UPDATE roi SET software='$_POST[software]', patients='$_POST[patients]', reimbursement='$_POST[reimbursement]', rejection='$_POST[rejection]' WHERE Email='$_SESSION[Email]'"

BUT, you haven't cleaned the input - v.dangerous. Use mysql_real_escape_string() as a minimum. htmlentities() can be handy too.

pritaeas 2,194 ¯\_(ツ)_/¯

13 Years Ago

For one, session_start() should be at the top of every page. I'm not sure what you want with $_SESSION['email'] = 'email'; Perhaps you want it to be $_SESSION['email'] = $_POST['email'];

dennishall commented: pritaeas is a great person; follong up on posts, creating clear comments with examples and has helped me more than once +2

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

pritaeas is a great person; follong up on posts, creating clear comments with examples and has helped me more than once

dennishall 0 Junior Poster in Training · Answer 1 · 2010-12-30T07:20:59+00:00

Thanks for the info ardav:

I'm still developing this form set so the mysql_real_escape_string() will certainly be implimented, however, I think my email session variable is not being passed; as your code above also does not work (however, it does not error - so this project is advancing).

Thanks for the help. I see that removing the () encapsulation and breaking out each set was the issue with the query.

Here is my DB and markup from the 3 files related to the DB insert and update process:

The user flow is as follows:
1. The user registers in the roi_reg.php which inserts a new record then sends the user to roi_form.php (no validation in the process yet)
2. roi_form.php collects 3 input values and displays math results via JQuery, then posts the input to roi_insert.php via AJAX
roi_insert.php updates the record based on email address entered in roi-reg.php (yes I'm collecting session vars on name and company, but these entries are not going to be checked when a user returns later (email verification against DB is not written in yet).

MyDB table is as follows:

CREATE TABLE IF NOT EXISTS `roi` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default 'Name not entered',
  `email` varchar(30) NOT NULL default 'Email not entered',
  `company` varchar(30) default NULL,
  `software` varchar(30) default NULL,
  `patients` int(11) default NULL,
  `reimbursement` int(11) default NULL,
  `rejection` int(2) default NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

Registration form:

<?
session_start();
include ('roi_dbc.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Claim Calculator Registration</title>
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7">
<link rel="stylesheet" href="a_data/master.css" type="text/css">
</head>
<body>
<center>
<table width="330px" border="0" cellpadding="0" cellspacing="0">
  <tr> 
      <td align="center">
		<h3>ROI Claculator Registration Form</h3>
		</td>
  </tr>
  <tr>
    <td align="right"><form name="form1" method="post" action="roi_form.php" style="padding:20px;">
        <p>
		Complete Name : <input name="name" type="text" id="name"/><br />
        E-mail : <input name="email" type="text" id="email"/><br />
        Company : <input name="company" type="text" id="company"/><br />
        <p align="center"> 
          <input type="submit" name="Submit" value="Use the ROI Calculator"/>
        </p>
      </form>
      </td>
  </tr>
</table>
<?
mysql_close();
?>
</center>
</body>

roi_form.php:

<?
include 'roi_dbc.php';
$_SESSION['Name'] = "name";
$_SESSION['Email'] = "email";
$_SESSION['Company'] = "company";

mysql_query("INSERT INTO roi(`name`,`email`,`company`) VALUES ('$_POST[name]','$_POST[email]','$_POST[company]')") 
	or die(mysql_error());
mysql_close();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Claim Calculator</title>
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7">
<link rel="stylesheet" href="a_data/master.css" type="text/css">
<link rel="stylesheet" href="a_data/print.css" type="text/css" media="print">
<script src="a_data/jquery.js" type="text/javascript"></script>
<script src="a_data/jquery_002.js" type="text/javascript"></script>
<script src="a_data/jquery_003.js" type="text/javascript"></script>
<script type="text/javascript">
function addCommas(nStr){
	nStr += '';
	x = nStr.split('.');
	x1 = x[0];
	x2 = x.length > 1 ? '.' + x[1] : '';
	var rgx = /(\d+)(\d{3})/;
	while (rgx.test(x1)) {
		x1 = x1.replace(rgx, '$1' + ',' + '$2');
	}
	return x1 + x2;
}
function mailpage(){
	var mail_str = "";
	mail_str += "mailto:"+$("#email").val()+"?subject=Your Claim Calculator Results";
	mail_str += "&body=" + $("#content").html();
	location.href = mail_str;
}
function processStats(){
	var pat = $("#patients").val();
	var rmb = $("#reimbursement").val();
	var rej = $("#rejection").val();
	//alert(pat+" "+rmb+" "+rej);
	var pn = $("#software").val() == "" || $("#software").val() == "Practice Name" ? "National Average" : $("#software").val(); 
	var rev = pat*rmb
	//National Average / Practice Stats
	var avg_rj = Math.round((pat*rmb)*(rej/100));
	var avg_rwc = Math.round(avg_rj*.5);
	var avg_revcap = Math.round((rev - avg_rj) + avg_rwc);
	var avg_revlost = rev - avg_revcap;
	//AMD Averages
	var amd_rj = Math.round((pat*rmb)*(5/100));
	var amd_rwc = Math.round(amd_rj*.5);
	var amd_revcap = Math.round((rev - amd_rj) + amd_rwc);
	var amd_revlost = rev - amd_revcap;
	var pcnt = Math.round(avg_revcap * .01);
	$("#practice_name").html(pn);
	$("#avg_rev, #amd_rev").html("$"+addCommas(rev));
	$("#avg_rj").html("-$"+addCommas(avg_rj));
	$("#avg_rwc").html("+$"+addCommas(avg_rwc));
	$("#avg_revcap").html("$"+addCommas(avg_revcap));
	$("#avg_revlost").html("$"+addCommas(avg_revlost));
	$("#avg_revlostyr").html("$"+addCommas(avg_revlost*12));
	$("#amd_rj").html("-$"+addCommas(amd_rj));
	$("#amd_rwc").html("+$"+addCommas(amd_rwc));
	$("#amd_revcap").html("$"+addCommas(amd_revcap));
	$("#amd_revlost").html("$"+addCommas(amd_revlost));
	$("#amd_revlostyr").html("$"+addCommas(amd_revlost*12));
	$("#amd_save_mo").html("$"+addCommas(amd_revcap - avg_revcap));
	$("#amd_save_yr").html("$"+addCommas((amd_revcap - avg_revcap)*12));				
	$("#improvement").html("$"+pcnt+" ");
}
$(function(){
	$("#roi_calculator").submit(function(event){
//		event.preventDefault();
		if($(this).valid()){				
			processStats()
			$("#results").slideDown("slow",function(){
				$("#btns_1").fadeOut("slow",function(){
					$("#btns_2").fadeIn();						
				}
			)
		}
	)
	$("#fs_software").slideUp();
}
}
)
$("#results, #btns_2, #btns_3, .amd_col, #amd_rate, .amd_savings, #instructions").hide();
$("#software").bind("focus",function(){	
	$("#fs_software").slideDown();
	if($(this).val() == "Current Software"){
		$(this).val("");
	}
}
)
$("#software").bind("blur",function(){
	if($(this).val() == ""){
		$(this).val("Current Software");					
		}else if($(this).val() !== "" && $(this).val() !== "Current Software"){
			$("#fs_software").slideUp();
		}
	}
)
$("#btn_compare").click(function(){
	/* $("#fs_client").slideUp("slow",function(){ */
	$(".amd_col, #amd_rate, .amd_savings").fadeIn();	
/*	}
); */
$(this).parent("#btns_2").fadeOut("slow",function(){
	$("#btns_3").fadeIn();
/*
	$("#email").rules("add", {
		required: true,
		email:true
	}
);
*/
}
);
}
)
/*
$("#btn_email").click(function(){
	if($("#roi_calculator").valid()){	
}
}
)
*/
$("#btn_print").click(function(){
	window.print();
}
)
$("a[title]").qtip();
$("#roi_calculator").validate(
	{
		rules:{
			patients:{
				required:true,
				digits:true
			},
			reimbursement:{
				required:true,
				digits:true
			}
		},
		messages:{
			patients:{
				required: "Please enter an average amount of patients seen each month."
			},
			reimbursement:{
				required: "Please enter a dollar amount for claim reimbursement."
			}
		},
		errorLabelContainer:("#instructions"),
		errorElement: "p",
		errorPlacement: function(error, element){
			error.appendTo($("div.form_btns"));
		}
	}
)
}
)
$(document).ready(function(){
	// Email Signup
	$("form#roi_calculator").submit(function() {
		var dataStr = $("#roi_calculator").serialize();
		$.ajax({
			type: "POST",
			url: "roi_insert.php",
			data: dataStr,
			success: function(del){
				$('form#roi_calculator').hide();
				$('form#results').fadeIn();
				}
			});
			return false;
		});
	});
</script>
</head>
<body>
<div id="container">
	<div id="header">
		<h1><span class="link"><a href="http://www.products-and-services.ca/roi/roi_form.php">Reset Form</a></span>Claim Calculator</h1>
	</div>
	<div id="content">
		<form name="myform" id="roi_calculator" action="" method="post">
			<fieldset id="fs_software" class="iefix">
				<p>
					<input name="software" id="software" value="Enter your software here" type="text">
				</p>
			</fieldset>
			<fieldset id="fs_client">
				<legend>Practice Statistics</legend>
				<p>
					<span class="patients"><input name="patients" id="patients" size="4" tabindex="1" type="text"></span>
					<label for="patients">Average Patients Seen in a Month?</label>
					<br class="clear">
				</p>
				<p>
				    <span class="money"><input name="reimbursement" id="reimbursement" size="4" tabindex="2" type="text"></span>
					<label for="reimbursement">Average $ Reimbursement per Claim?</label>
					<br class="clear">
				</p>
				<p>
				    <span class="percent"><input name="rejection" id="rejection" size="2" tabindex="3" type="text" value="30"></span>
					<label for="rejection">% of Submitted Monthly Claims Rejected?<a href="#" title="The MGMA† states that the national average claim rejection rate is 30%" class="help"></a></label>							
					<br class="clear">
				</p>
				<br class="clear">
			</fieldset><!-- end #client_answers -->				
			<div style="display: none;" id="results">
				<h1>Results</h1>
				<h2>1% Claim Acceptance Improvement = <span id="improvement"></span> a month.</h2>
				<h2 style="display: none;" id="amd_rate">We guarantee a 95% first-pass claim acceptance rate.</h2>
					<table id="results_table" width="100%" border="0" cellpadding="0" cellspacing="0">
						<tbody>
                        	<tr>
                                <th></th>
                                <th class="avg_col" id="no_practice_name">Current<br />Billing Procedures</th>
                                <th style="display: none;" class="amd_col">InteliRev's<br />Solutions Results</th>
							</tr>
							<tr>
								<td class="result_type">Monthly Revenue Earned</td>
								<td class="avg_col" id="avg_rev"></td>
								<td style="display: none;" class="amd_col" id="amd_rev"></td>
							</tr>
							<tr>
								<td class="result_type">First-Pass Rejected Claims</td>
								<td class="avg_col" id="avg_rj"></td>
								<td style="display: none;" class="amd_col" id="amd_rj"></td>
							</tr>
							<tr>
								<td class="result_type">Reworked Claims</td>
								<td class="avg_col" id="avg_rwc"></td>
								<td style="display: none;" class="amd_col" id="amd_rwc"></td>
							</tr>
							<tr class="total">
								<td class="result_type">Monthly Revenue Captured</td>
								<td class="avg_col" id="avg_revcap"></td>
								<td style="display: none;" class="amd_col" id="no_amd_revcap"></td>
							</tr>
<!--							<tr class="total">
								<td class="result_type">Monthly Revenue Lost</td>
								<td class="avg_col" id="avg_revlost"></td>
								<td style="display: none;" class="amd_col" id="amd_revlost"></td>
							</tr> -->
							<tr>
								<td class="result_type">Annual Revenue Lost</td>
								<td class="avg_col" id="avg_revlostyr"></td>
								<td style="display: none;" class="amd_col" id="no_amd_revlostyr"></td>
							</tr>
							<tr style="display: none;" class="amd_savings">
								<td class="result_type" colspan="2">Additional Monthly Revenue Captured</td>
								<td style="display: none;" class="amd_col" id="amd_save_mo"></td>
							</tr>
							<tr style="display: none;" class="amd_savings">
								<td class="result_type" colspan="2">Additional <strong>Annual</strong> Revenue Captured</td>
								<td style="display: none;" class="amd_col" id="amd_save_yr"></td>
							</tr>
						</tbody></table>		
					</div><!-- end #results -->
					<div style="display: none;" id="instructions"></div>
					<div class="form_btns">
						<p id="btns_1">
							<a href="javascript: submitform()"><input alt="Show Results" id="btn_results" src="a_data/form_btn_show_results.png" type="image"></a>
						</p>
						<p style="display: none;" id="btns_2">
							<img src="a_data/form_btn_compare.png" id="btn_compare">
						</p>
						<p style="display: none;" id="btns_3">
							<img src="a_data/form_btn_print_results.png" id="btn_print">
						</p>
					</div><!-- end #form_btns -->					
				</form>
                <script type="text/javascript">
				function submitform()
				{
					if(document.myform.onsubmit && 
					!document.myform.onsubmit())
					{
						return;
					}
				 document.myform.submit();
				}
				</script>
			</div>
		</div>
		<div qtip="0" class="qtip qtip-defaults" style="-moz-border-radius: 0pt 0pt 0pt 0pt; position: absolute; width: 178px; display: none; top: 230.267px; left: 565.5px;">
	    	<div class="qtip-wrapper" style="position: relative; overflow: hidden; text-align: left;">
	        	<div class="qtip-contentWrapper" style="overflow: hidden; background: none repeat scroll 0% 0% white; border: 1px solid rgb(211, 211, 211);">
	            	<div style="background: none repeat scroll 0% 0% white; color: rgb(17, 17, 17); overflow: hidden; text-align: left; padding: 5px 9px;" class="qtip-content qtip-content">Patients per day <br>x Days worked a month
    	            </div>
        	    </div>
	        </div>
	    </div>
	<div id="footer">
	</div>
</body>
</html>

roi_insert.php:
You code is in here, but commented at this time.

<?
include ('roi_dbc.php');
$_SESSION['name'] = "name";
$_SESSION['email'] = "email";
$_SESSION['company'] = "company";

/* $sql="UPDATE roi SET software='$_POST[software]', patients='$_POST[patients]', reimbursement='$_POST[reimbursement]', rejection='$_POST[rejection]' WHERE email='$_SESSION[email]'"; */
$sql="INSERT INTO roi SET software='$_POST[software]', patients='$_POST[patients]', reimbursement='$_POST[reimbursement]', rejection='$_POST[rejection]'";
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
mysql_close();

sleep(300);
?>

any further assistance in getting this process to update MySql from roi_insert would be like gold to me.

Best Regards,
dennishall

diafol · Answer 2 · 2010-12-30T15:55:04+00:00

Sorry mate, 350 lines of code is too much to read. I have limited powers!

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 3 · 2010-12-30T15:59:21+00:00

Line 8 in the last snippet (INSERT INTO) is wrong, compare it with line 7 of roi_form.php

dennishall 0 Junior Poster in Training · Answer 4 · 2010-12-30T23:33:48+00:00

Hey there:

Thanks for the feedback. I agree that i have many lines of code in the roi_form.php.

To make things much shorter and isolate this issue, here is what I have found:
1. The user enters their info in the roi_reg.php - this form contains the following php code at line 1:

<?
session_start();
?>

2. The user is taken to roi_form.php - where I set the following code at line 1:

<?
$_SESSION['email'] = 'email';
echo $_SESSION['email'];

include 'roi_dbc.php';
mysql_query("INSERT INTO roi(`name`,`email`,`company`,`phone`) VALUES ('$_POST[name]','$_POST[email]','$_POST[company]','$_POST[phone]')") 
	or die(mysql_error());
mysql_close();
?>

At this point I do not see the users email in the echo - instead I see the word email, however, the information the user entered in roi_reg.php is posted to the DB. In fact, it gets posted even without any session related code.
Either way, the email entered is not displayed at the top of roi_form.php.

This is my issue and the reason I am not able to UPDATE in my roi_insert.php file.

My phpinfo() tells me my PHP 5.2.13 sessions are turned on.

Any assistance with my session handing would be greatly appreciated.

Best Regards,
dennishall

dennishall 0 Junior Poster in Training · Answer 5 · 2010-12-31T05:53:00+00:00

pritaeas: You were on the right track.
My roi_insert.php is working.
I took your previous comment and came up with the following code(this is the complete file):

<?
session_start();
$_SESSION['email'];
include ('roi_dbc.php');
$_POST['email'] = $_SESSION['email'];
mysql_query("UPDATE roi SET software='$_POST[software]', patients='$_POST[patients]', reimbursement='$_POST[reimbursement]', rejection='$_POST[rejection]' WHERE email='$_POST[email]'")
	or die(mysql_error());
mysql_close();
sleep(300);
?>

Thanks again for the help,your thoughts and input were very valuable and really made me think.

Best Regards and have a Happy New Year,
dennishall

Help with MySQL UPDATE Query

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers