Jump to content


Photo

PHP update records problem.. {Solved}


  • Please log in to reply
17 replies to this topic

#1 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 24 October 2006 - 07:21 PM

Hello, it's been awhile since i've had time to work on this, but here goes...

Im trying to update a record from a table. so far my form can query the table, return the results, along with an edit button.
I can click the edit button, and it puts the selected record into html fields. What I need now is to be able to change some or all of the fields and hit "update" to edit the record. *** I would also like to include a delete button, but one thing at a time.

The problem is that when I hit the update button, all i get is a blank page that hangs, and going back and looking at the record, it is not updated.

The problem is that when I hit the update button, all i get is a blank page that hangs, and going back and looking at the record, it is not updated.

so here are the two relevant forms. I know my problem isnt the database connection, so please let me know if you can see where im going wrong, as I have looked at all the PHP references i can find, and it seems OK.

<?php
 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");
$id=$_GET['customerID']; 
$query = "SELECT * FROM customers WHERE customerID= $id ";
$result = odbc_exec($cnx, $query); 
     while($row = odbc_fetch_array($result)) 
             { 
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$city = $row['city'];
$state = $row['state'];
$zip = $row['zip'];
$email = $row['email'];
$referral = $row['referral'];
$promo = $row['promo'];
$lubricant = $row['lubricant'];

$results = "
  <div id=\"table1\"><center>
<form action=\"cust_update.php\" method=\"post\">
  <div align=\"left\"><input type=\"hidden\" name=\"customerID\" value=\"$id\">
    <table width=349 height=\"91\" border=\"0\">
      <tr>
        <td width=174 height=\"81\"><p>First Name:<br> 
              <input type=\"text\" name=\"firstname\" value=\"$firstname\">
        </p>          </td>
        <td width=165><p>Last Name:<br> 
              <input type=\"text\" name=\"lastname\" value=\"$lastname\">
        </p>          </td>
      </tr>
      <tr>
        <td>Address:<br> 
    <input type=\"text\" name=\"address1\" value=\"$address1\"></td>
        <td>Address 2:<br> 
    <input type=\"text\" name=\"address2\" value=\"$address2\"></td>
      </tr>
      <tr>
        <td></td>
        <td></td>
      </tr>
      <tr>
        <td><p></p>
          <p>City:<br> 
                <input type=\"text\" name=\"city\" value=\"$city\">
          </p></td>
        <td><p></p>
          <p>State:<br> 
                <input type=\"text\" name=\"state\" value=\"$state\">
          </p></td>
      </tr>
      <tr>
        <td><p></p>
          <p>Zip:<br> 
                <input type=\"text\" name=\"zip\" value=\"$zip\">
          </p></td>
        <td></td>
      </tr>
      <tr>
        <td><p></p>
          <p>Email:<br> 
                <input type=\"text\" name=\"email\" value=\"$email\">
          </p></td>
        <td></td>
      </tr>
      <tr>
        <td><p></p>
          <p>Referral:<br> 
                <input type=\"text\" name=\"referral\" value=\"$referral\">
          </p></td>
        <td></td>
      </tr>
      <tr>
        <td><p></p>
          <p>Promo:<br> 
                <input type=\"text\" name=\"promo\" value=\"$promo\">
          </p></td>
        <td><p></p>
          <p>Lubricant:<br> 
                <input type=\"text\" name=\"lubricant\" value=\"$lubricant\">
          </p></td>
      </tr>
      <tr>
        <td></td>
        <td></td>
      </tr>
    </table>
    <p><br>
        <input type=\"Submit\" value=\"Update\">
        </p>
  </div>
</form>
</center>
</div>
";
}
?>

and the page the data is sent to to be updated:
<?php
 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");

$customerID=$_GET['customerID']:
$firstname=$_GET['firstname'];
$lastname=$_GET['lastname'];
$address1=$_GET['address1'];
$address2=$_GET['address2'];
$city=$_GET['city'];
$state=$_GET['state'];
$zip=$_GET['zip'];
$email=$_GET['email'];
$referral=$_GET['referral'];
$promo=$_GET['promo'];
$lubricant=$_GET['lubricant'];

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
echo "Record Updated";
odbc_close();

header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
echo " "; // NN4 requires that we output something...
exit();
?>


thanks for your time.

#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 24 October 2006 - 07:26 PM

In your form you are using method POST while on the update script you are sniffing for GET variables

In your update script you can replace all your GET's with this instead (as an example):
<?php
/*
$customerID=$_GET['customerID']:
$firstname=$_GET['firstname'];
$lastname=$_GET['lastname'];
$address1=$_GET['address1'];
$address2=$_GET['address2'];
$city=$_GET['city'];
$state=$_GET['state'];
$zip=$_GET['zip'];
$email=$_GET['email'];
$referral=$_GET['referral'];
$promo=$_GET['promo'];
$lubricant=$_GET['lubricant'];
*/

foreach($_POST as $key => $value)
{
  ${$key} = htmlspecialchars($value);
}

?>


#3 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 24 October 2006 - 07:29 PM

<?php
 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");

$customerID=$_GET['customerID']:
$firstname=$_GET['firstname'];
$lastname=$_GET['lastname'];
$address1=$_GET['address1'];
$address2=$_GET['address2'];
$city=$_GET['city'];
$state=$_GET['state'];
$zip=$_GET['zip'];
$email=$_GET['email'];
$referral=$_GET['referral'];
$promo=$_GET['promo'];
$lubricant=$_GET['lubricant'];

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
echo "Record Updated";
odbc_close();
echo "Debug Info:";
echo $query;
echo "<br />";
echo $firstname;
echo "<br />";
echo $lastname;
echo "<br />";
echo $address1;
echo "<br />";
echo $address2;
echo "<br />";
echo $city;
echo "<br />";
echo $state;
echo "<br />";
echo $zip;
echo "<br />";
echo $referral;
echo "<br />";
echo $email;
echo "<br />";
echo $promo;
echo "<br />";
echo $lubricant;
echo "<br />";
echo $customerID


//header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
//echo " "; // NN4 requires that we output something...
//exit();
?>
backup your old code and try the above.  Tell us what it say's/

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#4 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 24 October 2006 - 07:30 PM

actually I didn't notice that but he's right.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#5 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 24 October 2006 - 08:10 PM

Alpine - I appreciate your help. I see what you mean about sending one thing and sniffing for another, but i don't understand the code you provided.

I took all my GET's and changed them to POST's in the update form, but that didnt do anything, though im sure thats not what you intended for me to do.

BusinessMan - I implemented your code, with both my original GET's and the new POST's (mentioned above)

no output, just a blank page.



alpine - I have no classroom education in any kind of webdesign/programming.
I can usually figure out what is going on and manipulate things until they work for me.

i.e. most of the functions that I DO have working were adapted from SQL to work on Access

what i dont get in
foreach($_POST as $key => $value)
{
  ${$key} = htmlspecialchars($value);
}

is how does that provide the data for my $query

and what is htmlspecialchars?

what do my variables replace, $key or $value ?


#6 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 24 October 2006 - 08:34 PM

foreach($_POST as $key => $value) // take each $_POST and extract the key name and the value
{
// $_POST['customerID']; // example: customerID is 55
  ${$key} = htmlspecialchars($value); // $key = customerID / $value = 55
}

htmlspecialchars() is just one of many ways to disable any harmful values sendt from a users input to your db

To your problem, you have a function to do the mysql_query for you ?
odbc_exec($cnx, $query);
echo "Record Updated";
odbc_close();




#7 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 24 October 2006 - 09:32 PM

OK, heres what i got.

and BTW, this isnt anything that will be available to the public. I am the only one who will ever see any of these forms, so preventing SQL injection shouldnt be an issue.


<?php
 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");

//$customerID=$_GET['customerID']:
//$firstname=$_GET['firstname'];
//$lastname=$_GET['lastname'];
//$address1=$_GET['address1'];
//$address2=$_GET['address2'];
//$city=$_GET['city'];
//$state=$_GET['state'];
//$zip=$_GET['zip'];
//$email=$_GET['email'];
//$referral=$_GET['referral'];
//$promo=$_GET['promo'];
//$lubricant=$_GET['lubricant'];

foreach($_POST as $key => $value) // take each $_POST and extract the key name and the value
{
// $_POST['customerID']; // example: customerID is 55
  ${$key} = htmlspecialchars($value); // $key = customerID / $value = 55
}

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
echo "Record Updated";
odbc_close();

header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
echo " "; // NN4 requires that we output something...
exit();
?>


Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 21
Record Updated
Warning: Wrong parameter count for odbc_close() in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 21

#8 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 25 October 2006 - 04:36 AM

Did you build this yourself ? Did it ever use to work ?

http://no2.php.net/odbc_exec

And you have an error msg on odbc_close ( resource connection_id ): http://no2.php.net/m....odbc-close.php

Sorry, i've never used MS SQL and odbc functions so i feel i cannot be of much more assistance on it,  .....

<?php

if (!odbc_exec($cnx, $query))
{
echo odbc_errormsg($cnx);
}
else
{
echo "Record Updated";
}
odbc_close($cnx);

?>


#9 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 04:48 PM

i have put it together from several different tutorials I have found online. Most of them were for SQL, so i had to do some translating.

No, this part has never worked. What does work is the submission form that enters the info into the database and emails me, and the query form that allows me to search the database by any entry, and then hit "edit" on any returned record, which then displays that record on a new page (the first one up there) in fields.

I can change the info entered into the fields, but then hitting "update" does not modify the info in the database.

Alpine - thanks for your help.
If this were SQL, is there another way to collect the _POST data from the previous page and assign variables, i.e. how i ws trying to do it with _GET ?

Im usually pretty decent with translating stuff from SQL to Access.

#10 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 05:00 PM

<?php
 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");


foreach($_POST as $key => $value) 
{

  ${$key} = htmlspecialchars($value); 
}

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
if (!odbc_exec($cnx, $query))
{
echo odbc_errormsg($cnx);
}
else
{
echo "Record Updated";
}
odbc_close($cnx);


header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
echo " "; // NN4 requires that we output something...
exit();
?>

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 21

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 21
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.



Line 21 is number 2 of 5 here
foreach($_POST as $key => $value) 
{

  ${$key} = htmlspecialchars($value); 
}


#11 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 25 October 2006 - 05:07 PM

Using POST or GET is a PHP feature and it is always safer to use POST rather than GET, as GET variables shows as variables in the adress bar and can easily be changed manually by users and thereby on a refresh result in unwanted results. This depends ofcourse in the end on how secure and foolproof you construct your scripts.

But if you put the following code on top of your update script it will echo all posted variables for you, just to see if they are really there (even though i think they are):

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

Another thing, are you sure your table datafield type is set correctly according to the expected data to insert ? (just by reading the error msg that is)

Seems most like you have a problem in the update statement itself



*EDIT: I see your edit, try just this one then:

<?php

foreach($_POST as $key => $value) 
{

  ${$key} = $value; 
}

?>


#12 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 05:18 PM

putting your echo in, I get

Array ( [customerID] => 727 [firstname] => Colin [lastname] => Royce [address1] => 5706 Goodwin Ave [address2] => 216 [city] => Dallas [state] => Texas [zip] => 75206 [email] => colin@sliquid.com [referral] => Freddy & Eddy [promo] => ***** [lubricant] => Sliquid Silver )
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 26

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 26
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.



On the previous page, that data was all there, except what I updated. I changed the spelling on my last name, added an apt number, and added a promotion code and referral.

so I am pretty sure that the datafield types are all correct, as all my records have that same type information. ( i removed the promo code above)

#13 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 05:27 PM

<?php

 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");

echo "
";
print_r($_POST);
echo "
";

//$customerID=$_GET['customerID']:
//$firstname=$_GET['firstname'];
//$lastname=$_GET['lastname'];
//$address1=$_GET['address1'];
//$address2=$_GET['address2'];
//$city=$_GET['city'];
//$state=$_GET['state'];
//$zip=$_GET['zip'];
//$email=$_GET['email'];
//$referral=$_GET['referral'];
//$promo=$_GET['promo'];
//$lubricant=$_GET['lubricant'];

foreach($_POST as $key => $value) 
{

  ${$key} = $value; 
}

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
if (!odbc_exec($cnx, $query))
{
echo odbc_errormsg($cnx);
}
else
{
echo "Record Updated";
}
odbc_close($cnx);

//echo "Record Updated";
//odbc_close();

header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
echo " "; // NN4 requires that we output something...
exit();
?>




Array ( [customerID] => 727 [firstname] => Colin [lastname] => Royce [address1] => 5706 Goodwin Ave [address2] => # [city] => Dallas [state] => Texas [zip] => 75206 [email] => colin@sliquid.com [referral] => Freddy & Eddy [promo] => ***** [lubricant] => Sliquid Silver )
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 24

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 24
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


whats wierd is that line 24 is

//$promo=$_GET['promo'];

does it not count commented out lines or something?

#14 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 05:31 PM

removing all comments, I have:
<?php

 ini_set('error_reporting', E_ALL); 
 ini_set('display_errors', 'On');

include("dbconnection.php");

echo "
";
print_r($_POST);
echo "
";

foreach($_POST as $key => $value) 
{

  ${$key} = $value; 
}

$query="UPDATE customers SET firstname='$firstname', lastname='$lastname', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', email='$email', referral='$referral', promo='$promo', lubricant='$lubricant' WHERE customerID= '$customerID'";
odbc_exec($cnx, $query);
if (!odbc_exec($cnx, $query))
{
echo odbc_errormsg($cnx);
}
else
{
echo "Record Updated";
}
odbc_close($cnx);

header("Refresh: 2; URL=http://www.sliquid.com/protected/adminLogin.php");
echo " "; // NN4 requires that we output something...
exit();
?>


and I get:

Array ( [customerID] => 727 [firstname] => Colin [lastname] => Royce [address1] => 5706 Goodwin Ave [address2] => # [city] => Dallas [state] => Texas [zip] => 75206 [email] => colin@sliquid.com [referral] => freddy & Eddy [promo] => ***** [lubricant] => Sliquid H2O )
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 12

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 12
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


where line 12 is the last line of the echo statement ( "; )

#15 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 25 October 2006 - 05:34 PM

[address2] => #

the # breaks your query string

#16 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 05:37 PM

The form that writes all the records uses that as a default input, and it has been writing to the DB for a year now with no problem.

even going back and changing it to a number still gives me

Array ( [customerID] => 727 [firstname] => Colin [lastname] => Royce [address1] => 5706 Goodwin Ave [address2] => 215 [city] => Dallas [state] => Texas [zip] => 75206 [email] => colin@sliquid.com [referral] => freddy & Eddy [promo] => ***** [lubricant] => Sliquid H2O )
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 12

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in d:\inetpub\sliquid\wwwRoot\protected\cust_update.php on line 12
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.





Could the problem be that I am not pulling all the data from the table? (most of the results when googling the error mention a problem with ASP and the date format)

I.E. There is a column for the date that the entry was made, but im not trying to update that column.

just takin shots in the dark

#17 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 08:22 PM

oh, and by the way, heres a snapshot of my database
Posted Image

all fields are set to "not required" and "allow zero length" except the two that are drop down forms, and those dont allow zero length.

also my "date" and "multiple" are auto generated, so those are required.


#18 croix

croix
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 25 October 2006 - 10:36 PM

OK, never mind.

the problem was the '$customerID'

didnt need quotes becuase its a number.

problem solved




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users