Jump to content

Archived

This topic is now archived and is closed to further replies.

Arl8

Update database with ODBC_affected_rows

Recommended Posts

Hey ya'll,

I'm having trouble finishing the last part of my edit/update records script for my database.  Currently I have a form that inputs 3 fields and queries the database by those three fields.  The second form takes the results from the query and displays all the information in the record.  This can be edited in the input fields of the form, and when finished I submit the data and it goes to my third script.  This script is where I am having trouble, specifically with the odbc_affected_rows() function.  Not sure if I need a converter or something, but i don't know what's going on and any feedback would be appreciated.

[color=red]frmptupdate.php[/color]  ==> First form where input query data.

[code]
<p> </p>
<form name="updateform" method="post" action="displayrecord.php">
  <table width="250" border="0" align="center" cellpadding="3">
    <tr>
      <td colspan="2">
<center><b>Enter Patient Info</b></center>
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>Last Name</center>
      </td>
      <td width="150">
        <input type="text" name="LastName">
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>First Name</center>
      </td>
      <td width="150">
        <input type="text" name="FirstName">
      </td>
    </tr>
    <tr >
      <td width="100">
        <center>DOB</center>
      </td>
      <td width="150">
        <input type="text" name="DOB">
      </td>
    </tr> 
    <tr>
      <td colspan="2" >
        <center>
          <input type="submit" name="Submit" value="Submit">
        </center>
      </td>
    </tr>
[/code]


[color=red]displayrecord.php[/color]  ==> Pulls data from record specified by query, can edit all of the fields and press submit to send to next form.

[code]
<?php
require_once('odbc.php');

$lname=$_REQUEST['LastName'];
$fname=$_REQUEST['FirstName'];
$dob=$_REQUEST['DOB'];

$query = "SELECT * from tblPatient";

if ( $_POST['LastName'] != "") {
$where[] = "LastName LIKE '%$lname%'";
}

if ( $_POST['FirstName'] != ""){
$where[] = "FirstName LIKE '%$fname%'";
}

if ($_POST['DOB'] != ""){
$where[] = "DOB LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());
$row = odbc_fetch_array($result);

?>

<p> </p>
<form name="displayrecord" method="post" action="updaterecord.php">
  <table width="1000" border="1" align="center" cellpadding="2">
    <tr>
      <td colspan="8">
        <div align="center"><b>Update Patient Information</b></div>
      </td>
    </tr>
<tr>
<td width="100">
<div align="right">WK </div>
</td>
<td width="150">
<input type="text" name="WK" value="<? echo $row['WK']; ?>">
</td>
<td width="100">
<div align="right">HM</div>
</td>
<td width="150">
<input type="text" name="HM" value="<? echo $row['HM']; ?>">
</td>
<td width="100">
<div align="right">ER</div>
</td>
<td width="150">
<input type="text" name="ER" value="<? echo $row['ER']; ?>">
</td>
</tr>
</table>
</form>
[/code]

****Form continues the same way, form works, shortened for clarity.  Three left are fields being changed. 


[color=red]updaterecord.php[/color]  ==>  Problems arise, not sure of the best way to update my data.  This is what I have at the moment.  Ideally it will update only rows affected by chance, hence odbc_affected_rows() , but am having trouble with the function.  There are far more fields, but only showing 3 for clarity.

[code]

<?php
require_once('odbc.php');

$hm=$_REQUEST['HM];
$wk=$_REQUEST['WK'];
$er=$_REQUEST['ER'];

$query='UPDATE tblPatient SET HM='$hm', WK='$wk', ER='$er'";
odbc_exec($odbc, $query);

echo odbc_affected_rows()."have been updated successfully";

odbc_close();
?>

<p> </p>
  <table border="0" align="center" cellspacing="1"
    cellpadding="5" width="1000">
    <tr>
  <td colspan="8">
  <center><b>"Record is updated successfully</b></center>
  </td>
  </tr>
<tr>
<td width="100">
<div align="right">HM Phone </div>
  </td>
<td width="150">
<input type="text" name="HM" value="<? echo $hm; ?>">
  </td>
<td width="100">
<div align="right">WK Phone </div>
  </td>
<td width="150">
<input type="text" name="WK" value="<? echo $wk; ?>">
  </td>
<td width="100">
<div align="right">ER Phone </div>
  </td>
<td width="150">
<input type="text" name="ER" value="<? echo $er; ?>">
  </td>
</tr>
</table>
</form>

[/code]

If anyone has any feedback or better way of doing this please let me know.  Also, if this is not a safe way to update my database, please let me know.  Thanks


Share this post


Link to post
Share on other sites
Im not sure exactly what your problem is, as you dont really say, but you do have a few syntax errors.

[code=php:0]
$hm=$_REQUEST['HM];
[/code]

Should be...


[code=php:0]
$hm=$_REQUEST['HM'];
[/code]

And your query needs to be within double quotes. You should also check for success or failure.

[code=php:0]
$query="UPDATE tblPatient SET HM='$hm', WK='$wk', ER='$er'";
if (odbc_exec($odbc, $query)) {
    echo odbc_affected_rows()." have been updated successfully";
}
[/code]

Share this post


Link to post
Share on other sites
Thanks for looking at it. 

My problem arises in that script because after I press submit in the 2nd form, the DB has been updated.  The 3rd form, however, that should display the updated record and uses the odbc_affected_rows function leaves me with only a blank page.  Therefore, I think that it is the odbc_affected_rows function that is messing up my script, because the SQL works and the form works.  Any feedback is greatly appreciated.  Thanks again.

Share this post


Link to post
Share on other sites
This form works:

[color=red]displayrecord.php[/color]

[code]
<?php
require_once('odbc.php');

$lname=$_REQUEST['LastName'];
$fname=$_REQUEST['FirstName'];
$dob=$_REQUEST['DOB'];

$query = "SELECT * from tblPatient";

if ( $_POST['LastName'] != "") {
$where[] = "LastName LIKE '%$lname%'";
}

if ( $_POST['FirstName'] != ""){
$where[] = "FirstName LIKE '%$fname%'";
}

if ($_POST['DOB'] != ""){
$where[] = "DOB LIKE '%$dob%'";
}

if (count($where) > 0) {
$query .= " WHERE " . implode(" AND ", $where);
}

$result = odbc_exec($odbc, $query) or die (odbc_errormsg());
$row = odbc_fetch_array($result);

?>

<p> </p>
<form name="displayrecord" method="post" action="updaterecord.php">
  <table width="1000" border="1" align="center" cellpadding="2">
    <tr>
      <td colspan="8">
        <div align="center"><b>Update Patient Information</b></div>
      </td>
    </tr>
<tr>
<td width="100">
<div align="right">LName </div>
</td>
<td width="150">
<input type="text" name="LastName" value="<? echo $row['LastName']; ?>">
</td>
<td width="100">
<div align="right">FName</div>
</td>
<td width="150">
<input type="text" name="FirstName" value="<? echo $row['FirstName']; ?>">
</td>
<td width="100">
<div align="right">Middle </div>
  </td>
<td width="150">
<input type="text" name="Middle" value="<? echo $row['Middle']; ?>">
  </td>
<td width="100">
<div align="right">Nickname </div>
  </td>
<td width="150">
<input type="text" name="NickName" value="<? echo $row['NickName']; ?>">
  </td>
</tr>
[/code]

and etc., the form continues.



This is what my code is for the updating script, which returns nothing.  Any suggestions?

[color=red]updaterecord.php[/color]

[code]
<?php
require_once('odbc.php');

$lname=$_REQUEST['LastName'];
$fname=$_REQUEST['FirstName'];
$dob=$_REQUEST['DOB'];
$hm=$_REQUEST['HM'];
$wk=$_REQUEST['WK'];
$er=$_REQUEST['ER'];
$cell==$_REQUEST['CellPhone'];
$ptid=$_REQUEST['PatientID'];
$chartid=$_REQUEST['ChartID'];
$acctnum=$_REQUEST['ACCTNUM'];
$mid=$_REQUEST['Middle'];
$race=$_REQUEST['Race'];
$sex=$_REQUEST['Sex'];
$nick=$_REQUEST['NickName'];
$marital=$_REQUEST['Marital'];
$zip=$_REQUEST['Zip'];
$ins1=$_REQUEST['INS_NAME'];
$ins2=$_REQUEST['Ins2Name'];
$refdoc=$_REQUEST['REF_DOC'];
$pmddoc=$_REQUEST['PMD_DOC'];
$city=$_REQUEST['CITY'];
$state=$_REQUEST['STATE'];
$email1=$_REQUEST['EmailAddress'];
$email2=$_REQUEST['EmailContact'];
$occu=$_REQUEST['Occupation'];
$employ=$_REQUEST['Employer'];
$height=$_REQUEST['Height'];
$weight=$_REQUEST['Weight'];
$ssn=$_REQUEST['SSN'];
$chartarch=$_REQUEST['ChartArchived'];
$crdate=$_REQUEST['CrDate'];
$cruser=$_REQUEST['CrUser'];
$chdate=$_REQUEST['ChDate'];
$chuser=$_REQUEST['ChUser'];
$dead=$_REQUEST['Deceased'];
$debt=$_REQUEST['BadDebt'];
$chkacct=$_REQUEST['CheckAccount'];
$needref=$_REQUEST['NeedsReferral'];
$refsd=$_REQUEST['ReferralStartDate'];
$refed=$_REQUEST['ReferralEndDate'];
$refv=$_REQUEST['ReferralVisits'];
$refn=$_REQUEST['ReferralNumber'];
$visits=$_REQUEST['VisitsUsed'];
$alltest=$_REQUEST['AllergyTest'];
$allshot=$_REQUEST['AllergyShots'];
$allmaint=$_REQUEST['AllergyMaint'];
$allns=$_REQUEST['AllergyNeverStart'];
$allquit=$_REQUEST['AllergyQuit'];
$add1=$_REQUEST['ADDRESS1'];
$add2=$_REQUEST['ADDRESS2'];

$query="UPDATE tblPatient SET CITY='$city', ADDRESS1='$add1', STATE='$state', EmailAddress='$email1', EmailContact='$email2', Occupation='$occu', ADDRESS2='$add2', ReferralVisits='$refv', ReferralNumber='$refn', VisitsUsed='$visits', AllergyTest='$alltest', AllergyShots='$allshot', AllergyMaint='$allmaint', AllergyNeverStart='$allns', AllergyQuit='$allquit', NeedsReferral='$needred', ReferralStartDate='$refsd', ReferralEndDate='$refed', CrDate='$crdate', CrUser='$cruser', ChDate='$chdate', ChUser='$chuser', Deceased='$dead', Employer='$employ', Height='$height', Weight='$weight', SSN='$ssn', ChartArchived='$chartarch', CellPhone='$cell', ChartID='$chartid', PatientID='$ptid', ACCTNUM='$acctnum', Middle='$mid', Race='$race', Sex='$sex', NickName='$nick', Marital='$marital', Zip='$zip', INS_NAME='$ins1', Ins2Name='$ins2', BadDebt='$debt', CheckAccount='$chkacct',  REF_DOC='$refdoc', PMD_DOC='$pmddoc', HM='$hm', WK='$wk', ER='$er' WHERE LastName='$lname' AND FirstName='$fname' AND DOB='$dob'";

odbc_exec($odbc, $query);
if (odbc_exec($odbc, $query)) {
    echo odbc_affected_rows()." have been updated successfully";
}
odbc_close();
?>

[/code]

I'm confused.

Share this post


Link to post
Share on other sites
You realise the last bit of code you posted executes your query twice? try some error handling to see if you can track down your error.

[code=php:0]
if (odbc_exec($odbc, $query)) {
    echo odbc_affected_rows()." have been updated successfully";
} else {
    echo odbc_errormsg($odbc);
}
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.