Update Statement

[vtaylor263]

Dear PHP Developers:

I am trying to get some assistance with script that does not display errors. However, it does update the database with the current date and time. Here's a sample of the code.

$firstlogin=$Row["StartDateTime"];
$TDate = Now();

if ($firstlogin == '0000-00-00 00:00:00') {
$DateInsertion = mysql_query("UPDATE Validation SET StartDateTime = '$TDate' Where Password ='$AWPassword'") or die('Error: Unable to execute insertion query.');

}

What I have done:

1. Used variations of the Now() function. (same result)
2. Read over 10 postings of other users Update statement
   questions (found some information but nothing relevant)
3. Re-wrote the script in different ways (did not help)
4. Used no quotes, single quotes, and double quotes around
   the Date variable (no change)
5. Check with my ISP Provider to ensure that PHP is on my
   server. It is.

I believe the problem resides with the assignment of Now() to the $TDate variable.

Any suggestions will be helpful. I have cranked out some serious hours on resolving this issue.

vt

[nemonoman]

Well, you can certainly start with your Now() assignment.

Now() is a MySQL function, not a PHP function. You need to get Now() into a PHP variable.

Here's a function that will do it:

//remember that PHP is CaSe SensItiVe.

function Now() {
$result=mysql_query("SELECT NOW()");
$row=mysql_fetch_array($result);
return($row[0]);
}

Add that code. Now when you say
$TDate=Now();

You'll populate $TDate with the value of Now() as returned by MySQL.

If your password var is populated as well, you should be OK.

On the other hand, you could just say

$DateInsertion = mysql_query("UPDATE Validation SET StartDateTime = NOW() Where Password ='$AWPassword'")

I'd be careful with you InterCaps. Both MySQL and PHP are CasEsenSitive. The MySQL manual recommends using either ALLCAPS or alllowercase for table and column names.

[vtaylor263]

I have tried:

if ($firstlogin == '0000-00-00 00:00:00') {
$DateInsertion = mysql_query("UPDATE Validation SET StartDateTime = NOW() Where Password ='$AWPassword'") or die('Error: Unable to execute insertion query.');
}

I get the same results. No update on the field. I have also tried removing the quotes around the conditional if '0000-00-00 00:00:00' - No change

I have read through the MySQL and PHP manual. This was no help. After reviewing the forums I noticed several users are having problems with the Update statement.

If you have other suggestions, let me know. Meanwhile, I am looking for a compiler that can possible check PHP code for parse or other errors. I am out of options at this time.

VT

[Hephaistos]

what's the result for:

SELECT * FROM validation WHERE password = '$AWPassword' ?

maybe there are no fields found to UPDATE?

[vtaylor263]

The password, "c97dexxxx" displays. So there is a record to update.

VT

I recently tried evaluating a field that was not affiliated with a date. The AccumulatedTime field.

What I did is

$firstlogin=$Row["AccumulatedTime"];
$TDate = date("Y-m-d H:i:s");

if ($firstlogin == "0:00") {
$DateInsertion = mysql_query("UPDATE Validation SET StartDateTime = '$TDate' Where Password ='$AWPassword'") or die('Error: Unable to execute insertion query.');
}

Still no errors and no updating on the fields.

VT

[kevinsequeira]

instead of having ure queries this way $DateInsertion = mysql_query("UPDATE Validation SET StartDateTime = '$TDate' Where Password ='$AWPassword'") or die('Error: Unable to execute insertion query.');

have them like this
$sql = "UPDATE Validation SET StartDateTime = '$TDate' Where Password ='$AWPassword'";
echo $sql;
$DateInsertion = mysql_query($sql);
if(mysql_error()){
echo mysql_error();
}

so you can see what the actual query is and it will help u debug

reg
kevin

[vtaylor263]

The problem resides in the Where clause of the Update Statement. [Where Password = '$AWPassword']

When I removed the Where clause of the Update statement the query updates all StartDateTime information. (which it should) However, it is not finding the Password that's why it will not update the StartDateTime field of the desired record.

I have checked the database and echoed the password. It displays. But, why is it not finding the Password when I specify:

mysql_query = ("Update Validation Set StartDateTime = "Now ()" Where Password = '$AWPassword'");

I am clueless at this point. I know it probably something minor that I am missing.

Are there any suggestions or code samples. I have tried all of the others.

VT

[Hephaistos]

[b]validate.php[/b]

function validate ($AWPassword) {
    GLOBAL $feedback;
    $sql = "UPDATE validation SET StartDateTime = NOW() WHERE password = '$AWPassword'";

$result = mysql_query($sql);

if (!$result) {
    $feedback .= "What the F**K : <br> \n" . mysql_error();
    return FALSE;
} else {
    $feedback .= "Succesfully altered validation";
    return TRUE;
}

}

if(validate('password')) {
    print $feedback;
} else {
    print "<b><font color=\"red\">" . $feedback . "</font></b>";
}

or somthing like that...

[vtaylor263]

I'll try this. I am not sure of the purpose of validating the Password. If I did not have errors and clearly displayed it when I echoed.

VT

I get back to you with another reply in a few minutes after my atempt.

[vtaylor263]

That did not work. However, I managed to work with the Update statement and now I have tracked down the problem. The problem resides with the Now () function and/or Where clause.

Error is :

Parse error: parse error in /usr/shared/web/directory/dev/flogin.php on line 20

Line 20 reads:

$ExecuteQuery = mysql_query = ("Update Validation Set StartDateTime = Now() Where Password = '$AWPassword''');

My first thought was the Now() function

I tried:

"Now"
".Now()."
'Now()'

I tried:

change file type from .php to .php4 (seeing if it could be interpreted differently) - no change same error

no change at all. It still displays parse error on line 20.

What are the next round of suggestions.

[Hephaistos]

remove the =

$ExecuteQuery = mysql_query = ("Update Validation Set StartDateTime = Now() Where Password = '$AWPassword''');

should read

$ExecuteQuery = mysql_query ("Update Validation Set StartDateTime = Now() Where Password = '$AWPassword''');

[vtaylor263]

No that it is not it. By the way, I did not place = after the mysql_query function in the file. I just re-typed it with an error.

I need some better responses. Some of these guesses are not helping me resolve the problem. I need some resolutions.

Does anyone have any idea why the Now() function and Where clause of the Update statement may be causing parsing errors in my script?

Thanks, VT

[Hephaistos]

if you want better answers instead of guesses why not post the entire code 😕 ?

I need some better responses. Some of these guesses are not helping me resolve the problem. I need some resolutions.

http://www.php.net/manual/en/

[vtaylor263]

Sure Hephaistos:

I generally only post the part of the script causing the error. But, since the script is short I'll post it. Here it is:

<?php
$hostname = "hostname";
$username = "username";
$password = "yougotit";
$dbName = "this is it";

mysql_connect($hostname,$username,$password);

//$AWPassword = $HTTP_GET_VARS["Password"];
$AWPassword = "802907a44d"

$SQL2Statement = mysql_query("UPDATE Validation SET StartDateTime = Now() Where Password = '$AWPassword'") or die('Error: Unable to execute insertion query.');

mysql_close;
?>

As you can see I am just assigning the $AWPassword ="802907a44d" to see if it works. I have a multimedia
program that captures the password and then post it
to my php file [Http_Get_Vars ]. Again, the problem is post error on line 20:

StartDateTime = Now() Where Password = '$AWPassword'") or die('Error: Unable to execute insertion query.');

The error has something to with the format of the Now() function and the Where clause of the Update statement.

Here's the schema of the database:

Validation_ID int(11)
ItemNo int (10) 0
Email varchar(55)
Password
AllottedTime 0.00
AccumulatedTime 0.00
EnrolledDate 0000-00-00
StartDateTime 0000-00-00 00:00:00

[Hephaistos]

The code should work like this. Just place a semicolon ( ; ) where you asign a value to $AWPassword.

PHP now sees your code as

$AWPassword = "802907a44d" $SQL2Statement = mysql_query("UPDATE Validation SET StartDateTime = Now() Where Password = '$AWPassword'") or die('Error: Unable to execute insertion query.'); 

and of course $SQL2Statement doesn't exist so the asigning of $AWPassword fails...

The error occurs on line 20 because execution starts there

another piece of advice. Always try to capture mysql errors.

you can use mysql_error() to return the last error.

so your code would be

<?php 
$hostname = "hostname"; 
$username = "username"; 
$password = "yougotit"; 
$dbName = "this is it"; 

mysql_connect($hostname,$username,$password); 

//$AWPassword = $HTTP_GET_VARS["Password"]; 
$AWPassword = "802907a44d"; 

$SQL2Statement = "UPDATE Validation SET StartDateTime = Now() Where Password = '$AWPassword'";

$result = mysql_query($SQL2Statement); 

if(!$result) {
print mysql_error();
}

mysql_close; 
?> 

[vtaylor263]

Dear Hephaistos:

I still have that error on the same line with your example. I wonder do I need a mysql_fetch_row statement.

VT

I research it and include that statement to see if it makes any
difference.

If you have other ideas please post them. Meanwhile I will work on this mysql_fetch_row statement.

[Hephaistos]

why would you want to fetch a row?

anyway i think you need to include this too

mysql_select_db($dbName);

place it after mysql connect... sorry I didn't spot this earlier...

PS did you copy paste my code? in that case check the if statement for !result should be !$result...

[vtaylor263]

Dear Hephaistos:

UPDATE Validation SET StartDateTime = Now() Where Password = '$AWPassword'

I have modified the script and look like this:

//$AWPassword = $HTTP_GET_VARS["Password"];
$AWPassword = "802907a44d"

$SQL2Statement = "UPDATE Validation SET StartDateTime = Now() Where Password = '$AWPassword'" or die('Error: Unable to execute insertion query.');

$result =mysql_db_query($dbName, $SQL2Statement);

if($result) {
print mysql_error();
}

mysql_free_result($result);
mysql_close;

?>

It's something about the Update Statement. Let's focus on this. I will continue to research.

VT

[Hephaistos]

why is it the update statement...

there is a semicolon missing at $AWPassword = "802907a44d"

and the error check should be

if(!$result) {
print mysql_error();
}

there was an unfortunate auto break in my last post.

the rest of the code is fine

[vtaylor263]

Thanks Hephaistos for sticking this one out with me. We
really got it done.

I was able to removed the comments from
the Get_Var_ Line and it worked as well.

This was my original purpose.

I re-commented out the line
$AWPassword = "dddd...."

Anyway. Thanks for your diligence. Everything works without parse errors.

VT