Update not working

hightechredneck

01-05-2007, 08:46 PM

This script displays data from the table and immediately below the display are inputs to update 2 fields in the row.

A couple of things are going on, this is strange! First, the data isn't being updated, and then I get the following:

With error reporting set to: error_reporting (E_ALL) I had a notice saying id was not defined. I defined id: $id =""; at the top of the script and the part of the script that displays the current info in the row disappears, leaving only the inputs to update the info. Then when I echo:
$result = mysql_query ($query) or die (mysql_error());

It doesn't print any errors.

Here's the bulk of the script:
<?php # PHP Script - submit debtors resolution.php

// This displays debtors info.
$query = "SELECT * FROM debtors WHERE id = '$id' LIMIT 1";
$result = mysql_query ($query);
if ($result) {
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {// Display each record.
echo '<table border="2" width="95%" cellspacing="3" cellpadding="3" align="center">';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Business Name:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[2] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Responsible Party:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[3] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Address:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[7] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'City:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[4] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'State:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[5] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Zip Code:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[6] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Amount Owed:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[8] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Date of Default:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[10] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Resolved?';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[11] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Date Resolved:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[12] . '';
echo '</td>';
echo '</tr>';

echo '</table>';
}
}

if (isset($_POST['Submit'])) { // Handle the form.

$query = "SELECT * FROM debtors WHERE id = '$id' LIMIT 1";
$result = mysql_query ($query) or die (mysql_error());

// Validate the purchase date, web site, pixels.

// Check for resolved status
if (!empty($_POST['resolved'])){
$r = escape_data($_POST['resolved']);
} else {
$r = FALSE;
echo 'Please update resolved status!';
}

// Check for date resolved
if (!empty($_POST['date_resolved'])){
$dr = escape_data($_POST['date_resolved']);
} else {
$dr = FALSE;
echo 'Please enter the date this debt was resolved!';
}

if ('$r' && '$dr') {

$query = "UPDATE debtors SET resolved ='$r', date_resolved = '$dr' WHERE id = '$id' LIMIT 1";
$result = mysql_query ($query) or die (mysql_error()); // Run the query.
if ($result) { // If it ran OK.

echo '<h3>Your debtor resolution has been posted.</h3>';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
exit();

} else { // If it did not run OK.
// Send a message to the error log, if desired.
echo 'Your debtor information could not be updated due to a system error.';
}

}

mysql_close(); // Close the database connection.

} // End of the main Submit conditional.

?>

<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<input type="hidden" name="MAX_FILE_SIZE" value="524288">
<fieldset><legend>Update
your debtor's info here:</legend>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="5"> </td>
<td colspan="2" rowspan="2"> </td>
</tr>
<tr>
<td colspan="5"> </td>
</tr>
<tr>
<td colspan="2" valign="top">Is
this debt resolved?</td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top">
<input type="text" name="resolved" maxlength="3">
</td>
<td colspan="2" valign="top" rowspan="3"> </td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top">Date
the debt was resolved: 
(mm/dd/yyyy)</td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top">
<input type="text" name="date_resolved" maxlength="10" size="10">
</td>
</tr>
<tr>
<td valign="top" colspan="2"> </td>
</tr>
<tr align="center">
<td colspan="7">Click"Submit",
to update! 
</td>
</tr>
<tr>
<td align="center" colspan="7"> 
<input type="submit" name="Submit" value="Submit" />
</td>
</tr>
</table>
</fieldset>
</form>

</td>
<td width="15%"> </td>
</tr>
</table>

What could be causing this?

Thanks

T

GJay

01-05-2007, 09:12 PM

it looks to me like the code is relying on register_globals being turned on. If you change $id="" to

$id=$_GET['id'];

what happens?

(for the form-submission bit, you'll probably want $id=$_POST['id'])

robojob

01-05-2007, 09:13 PM

from what i can see in your code you dont seem to be putting the id in your form or retreiving it in your update code so the query does not know what id it is updating...

try putting in a hidden field in the form with value of the id and then define the id variable before the update query is processed.

Troy297

01-05-2007, 09:52 PM

Now, I'm no expert but wouldn't the fact that you have multiple instances "$query" and you call upon "$query" several different times affect what happens to your info?

What I mean is you have the code below, more than once in a single HTML Document wouldn't it just by default use the first instance of "$query" that it finds within the doc instead of the one closes to the "result".

$query = "Your Query Here";

If this is infact what the problem is then you would need to replace several of the "$query" with a different name each time. Such as the first one in your doc, call it "$display" instead of "$query" and the change your result statement to something like below...

$result = mysql_query ($display);

Just my thoughts, but I am positive that if you have the same variable more than once in a document/file it will ignore every instance but the first it comes across. That would be why your update statement was being ignored.

CFMaBiSmAd

01-05-2007, 10:01 PM

Variables get set to the last value/string placed into them, otherwise they would be called "constants" and things like loops and most other code would never work.

The re-use of the $query variable is not an issue so long as the programmer is not expecting it to have a previous value right after he assigns a new value to it.

hightechredneck

01-05-2007, 10:55 PM

Thanks for the suggestions!

Here's what happened:

When I used $id=$_GET['id'];
and $id=$_POST['id'];

I got undefined index warnings.

When I put a hidden field in the form and defined $id before the update, it returned the text box where the ID is entered with $id in the text box, still no update.

I even tried changing the name of the 2nd $query and $result to $query2 and $result2 which made no difference at all.

Any other suggestions?

Thanks

T

koyama

01-05-2007, 11:22 PM

I suggest you print your $query to find out what is wrong? There should be no need for guessing what is wrong. Place immediately after each of the two $query=... statements:

echo $query;

Also, can you confirm whether GJay is right that register_globals are on?

hightechredneck

01-08-2007, 06:42 PM

Yes, register_globals is on. The error is coming from the second select query:

$query = "SELECT * FROM debtors WHERE id = ' $id ' LIMIT 1";

The error that is printing is:

You have an error in your SQL syntax near 'echo SELECT * FROM debtors WHERE id = '' LIMIT 1' at line 1

This doesn't make sense to me because the first select query is working fine. What could be causing this?

Thanks

T

GJay

01-08-2007, 06:55 PM

the word 'echo' shouldn't be there

hightechredneck

01-08-2007, 07:01 PM

Echo is displaying because I echoed the query.

$query = "SELECT * FROM debtors WHERE id = '$id' LIMIT 1 echo $query";

Isn't it?

whizard

01-08-2007, 07:05 PM

Echo is displaying because I echoed the query.

Code:

$query = "SELECT * FROM debtors WHERE id = '$id' LIMIT 1 echo $query";

Isn't it?

The echo statement shouldn't be inside the actual query.

Try this:

$query = "SELECT * FROM debtors WHERE id = '$id' LIMIT 1";
echo $query;

Dan

hightechredneck

01-08-2007, 07:26 PM

When I did that, I got basically the same result without it saying "echo", which still leaves me wondering why the id isn't being passed to this segment of the script?

Here's what prints:

SELECT * FROM debtors WHERE id = '' LIMIT 1

Thanks

T

whizard

01-08-2007, 07:30 PM

How about echoing $id as well, to see what its value is?

Dan

hightechredneck

01-08-2007, 07:38 PM

I tried that, and nothing printed. Doesn't that just confirm what I already knew? That the id isn't being passed?

whizard

01-08-2007, 07:44 PM

Where would it be passed from? I don't see anywhere where the $id variable is actually being defined..

Dan

hightechredneck

01-08-2007, 08:29 PM

When I first noticed that $id wasn't passing, I defined $id at the top of the script. When I did that, the display part of the script, (what's before the form) doesn't display, just the form for the edit displays, and it still doesn't update the info, so I removed it. So, how can I define $id without causing that problem and have it update?

whizard

01-08-2007, 08:48 PM

Ok.

Basically, you're trying to pull a row from the DB where the row ID is equal to $id. That would imply that you are trying to select a specific bit of information.

How and when do you figure out which row you want?

Ok, I have to log off, but basically, wherever you decide which row you want to retrieve, you need to pass the id from there to the page with the update.

Hope that makes sense, if not, hopefully someone else will be able to help with this now, and I'll be back at some point; I always am :D

Dan

hightechredneck

01-08-2007, 08:58 PM

That's determined in the previous script, the info is passed to this one, (the $id value displays in the address bar as well as the correct info displays)... unless I define $id in this script.

GJay

01-08-2007, 09:43 PM

when a variable is passed, as you put it, in the address bar, then it is put in the $_GET superglobal array, as (in this case) $_GET['id']

if you echo $_GET['id'] or print_r($_GET) what do you see?

Please make a special effort to explain things clearly, when I don't understand, I tend to just ignore threads...

hightechredneck

01-09-2007, 12:06 AM

Since that wasn't working, I decided to use sessions. Now I have :

<?php # PHP Script
$page_title = '';
include ('../header.inc');
// Use the HTML header file.
require_once ('../../user_mysql_connect.php');
//Connect to the db.

// Start session.
session_start();

error_reporting (E_ALL);

if ( isset($_GET['id']) ) {
// get info from database
$_SESSION['id'] = $_GET['id'];
$id = $_SESSION['id'];

?>

Then there is some HTML, and then the balance of the script:

<?php # PHP Script - submit debtors resolution.php

// This displays debtors info.
$query = "SELECT * FROM debtors WHERE id = {$_SESSION['id']}";
$result = mysql_query ($query);
if ($result) {

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {// Display each record.
echo '<table border="2" width="95%" cellspacing="3" cellpadding="3" align="center">';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Business Name:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[2] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Responsible Party:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[3] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '<td valign="top" align="left">';
echo ' ';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Address:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[7] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'City:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[4] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'State:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[5] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Zip Code:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[6] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Amount Owed:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[8] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Date of Default:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[10] . '';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td valign="top" align="left">';
echo 'Resolved?';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[11] . '';
echo '</td>';
echo '<td valign="top" align="left">';
echo 'Date Resolved:';
echo '</td>';
echo '<td valign="top" align="left">';
echo '' . $row[12] . '';
echo '</td>';
echo '</tr>';

echo '</table>';
}
}
}
$id = "";
if (isset($_POST['Submit'])) { // Handle the form.

$query = "SELECT * FROM debtors WHERE id = {$_SESSION['id']}";
echo $query;
$result = mysql_query ($query) or die (mysql_error());

// Validate the purchase date, web site, pixels.

// Check for resolved status
if (!empty($_POST['resolved'])){
$r = escape_data($_POST['resolved']);
} else {
$r = FALSE;
echo 'Please update resolved status!';
}

// Check for date resolved
if (!empty($_POST['date_resolved'])){
$dr = escape_data($_POST['date_resolved']);
} else {
$dr = FALSE;
echo 'Please enter the date this debt was resolved!';
}

if ('$r' && '$dr') {

$query = "UPDATE debtors SET resolved ='$r', date_resolved = '$dr' WHERE id = {$_SESSION['id']}";
$result = mysql_query ($query) or die (mysql_error()); // Run the query.
if ($result) { // If it ran OK.

echo '<h3>Your debtor resolution has been posted.</h3>';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
echo ' ';
exit();

} else { // If it did not run OK.
// Send a message to the error log, if desired.
echo 'Your debtor information could not be updated due to a system error.';
}

}

mysql_close(); // Close the database connection.

} // End of the main Submit conditional.

?>

<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<input type="hidden" name="MAX_FILE_SIZE" value="524288">
<fieldset><legend>Update
your debtor's info here:</legend>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="5"> </td>
<td colspan="2" rowspan="2"> </td>
</tr>
<tr>
<td colspan="5"> </td>
</tr>
<tr>
<td colspan="2" valign="top">Is
this debt resolved?</td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top">
<input type="text" name="resolved" maxlength="3">
</td>
<td colspan="2" valign="top" rowspan="3"> </td>
</tr>
<tr>
<td colspan="2" valign="top"> </td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td colspan="2" valign="top">Date
the debt was resolved: 
(mm/dd/yyyy)</td>
<td width="12%" valign="top"> </td>
<td colspan="2" valign="top">
<input type="text" name="date_resolved" maxlength="10" size="10">
</td>
</tr>
<tr>
<td valign="top" colspan="2"> </td>
</tr>
<tr align="center">
<td colspan="7">Click"Submit",
to update! 
</td>
</tr>
<tr>
<td align="center" colspan="7"> 
<input type="submit" name="Submit" value="Submit" />
</td>
</tr>
</table>
</fieldset>
</form>

</td>
<td width="15%"> </td>
</tr>
</table>
<?php

include ('../footer.inc');
mysql_close(); // Close the database connection.
?>

Having made those changes, I am no longer getting the warning that $id is not defined. However I am getting the warning:

SELECT * FROM debtors WHERE id = You have an error in your SQL syntax near '' at line 1

Since I'm only echoing 1 query, the select query for the form, there apparently is something wrong with this query:

$query = "SELECT * FROM debtors WHERE id = {$_SESSION['id']}";

My question is, what's wrong with the query?

Thanks

T

koyama

01-09-2007, 01:11 PM

ahhh... There is one thing we have all overlooked. In the form we have in the action attribute

$_SERVER['PHP_SELF']

which does not contain the request variables. It should instead be

$_SERVER['REQUEST_URI']

This explains why id isn't being passed in subsequent requests.

hightechredneck

01-09-2007, 09:54 PM

I tried as you suggested, same result. It still shows there is a SQL synatx error:

SELECT * FROM debtors WHERE id = You have an error in your SQL syntax near '' at line 1

What else can I do?

koyama

01-10-2007, 07:42 AM

It seems that id isn't being passed from the very beginning. I suggest that you for the moment try without using sessions until you get the script firing.

For your query, use

$query = "SELECT * FROM debtors WHERE id = '{$_GET['id']}'";

(remember the quotes around '{$_GET['id']}')

When you load the page with the form for the very first time, verify that you are seeing in your address bar something like (or whatever value id has): http://somedomain.com/resolution.php?id=123
You should then be seeing data retrieved from your database table if the first query is successful.

Next, say View > Source in your browser and verify that your form tag has the correct action attribute. It should read something like <form action="\resolutions.php?id=123" method="post">

Next, when you submit the form, you should again be loading resolution.php?id=123 and the updates should have been made from your second query.