Jump to content

Update database with ODBC_affected_rows


Arl8

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


Link to comment
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]
Link to comment
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.
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.