PHP update records problem.. {Solved}

[croix]

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.

[code]<?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>
";
}
?>[/code]

and the page the data is sent to to be updated:
[code]<?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();
?>
[/code]


thanks for your time.

[alpine]

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):
[code]
<?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);
}

?>
[/code]

[Ninjakreborn]

[code]<?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();
?>[/code]
backup your old code and try the above.  Tell us what it say's/

[Ninjakreborn]

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

[croix]

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
[CODE]
foreach($_POST as $key => $value)
{
  ${$key} = htmlspecialchars($value);
}[/CODE]

is how does that provide the data for my $query

and what is htmlspecialchars?

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

[alpine]

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

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 ?
[color=red]odbc_exec($cnx, $query);
echo "Record Updated";
odbc_close();[/color]

[croix]

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.


[CODE]
<?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();
?>
[/CODE]


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

[alpine]

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/manual/fi/function.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,  .....

[code]
<?php

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

?>
[/code]

[croix]

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.

[croix]

[CODE]
<?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();
?>
[/CODE]

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
[CODE]
foreach($_POST as $key => $value)
{

  ${$key} = htmlspecialchars($value);
}
[/CODE]

[alpine]

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):

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

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:

[code]
<?php

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

  ${$key} = $value;
}

?>
[/code]

[croix]

putting your echo in, I get

[b]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.[/b]


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)

[croix]

[code]<?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();
?>

[/code]



[b]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.[/b]

whats wierd is that line 24 is

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

does it not count commented out lines or something?

[croix]

removing all comments, I have:
[code]<?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();
?>

[/code]

and I get:

[b]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.[/b]

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

[alpine]

[address2] => #

the # breaks your query string

[croix]

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

[b]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.[/b]




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

[croix]

oh, and by the way, heres a snapshot of my database
[img]http://www.sliquid.com/images/untitled.jpg[/img]

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.

[croix]

OK, never mind.

the problem was the '$customerID'

didnt need quotes becuase its a number.

problem solved