Prepared Statement: Timestamp in UPDATE

[doubledee]

I can't get my Updated On timestamp to work in the following query...

 

// ******************************
// Create Temporary Password.		*
// ******************************
$tempPass = substr(md5(uniqid(rand(), true)), 3, 10);	

// Build query.
$r = "UPDATE member
		SET pass=?,
			updated_on=?
		WHERE email=?
		LIMIT 1";

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $r);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 'sss', $tempPass, NOW(), $email);

// Execute query.
mysqli_stmt_execute($stmt2);

 

I used similar code for an INSERT and it worked fine?!  Now sure what is going on here...

 

 

Debbie

 

 

[The Little Guy]

can you post the results of this mysql query:

 

describe members;

[doubledee]

can you post the results of this mysql query:

 

describe members;

 

Think I figured it out...

// Build query.
$r = "UPDATE member
		SET pass=?,
			updated_on=NOW()
		WHERE email=?
		LIMIT 1";

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $r);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 'ss', $tempPass, $email);

 

Apparently NOW() must be a MySQL function?

 

So I had to put it directly in my query versus trying to bind NOW() or $updatedOn=NOW() to a ?

 

-------

 

On a side note, why doesn't this work?

$updatedOn = NOW();

 

 

Debbie

 

[The Little Guy]

MySQL has a built in update, so you wouldn't even need to add that to your query.

 

You could then run this, and the updated field will update automatically:

UPDATE member
SET pass=md5('somePassword')
WHERE member_id = 123
LIMIT 1

 

You should also update a member based on member id.

[doubledee]

MySQL has a built in update, so you wouldn't even need to add that to your query.

 

I don't follow you.

 

I have these 3 fields...

created_on 	datetime
activated_on 	datetime
updated_on 	datetime

 

 

You could then run this, and the updated field will update automatically:

UPDATE member
SET pass=md5('somePassword')
WHERE member_id = 123
LIMIT 1

 

You should also update a member based on member id.

 

I suppose you are right.

 

So how do I do that?

 

First I have this query to find the member...

// ****************************
// Attempt to Reset Password.	*
// ****************************
if (empty($errors)){
// Valid form data.

	// ************************
	// Find Member Record.		*
	// ************************

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

	// Build query.
	$q = "SELECT email
			FROM member
			WHERE email=?";

 

And then I have the UPDATE query I already posted.

 

 

Debbie

 

[doubledee]

Does it really matter that I am doing the UPDATE based on "email"??

 

(After all, this is a "Password Reset based on E-mail" feature...)

 

 

Debbie

 

[The Little Guy]

Run this query:

 

alter table members change updated_on updated_on timestamp;

 

This will change the column to an auto update field, so every time a field gets inserted/updated this field will reflect the date/time that it happened.

 

Your select query makes no sense. If you already know the email why are you using a query to select it?

 

Does it really matter that I am doing the UPDATE based on "email"??

 

Only if your 100% sure the email is unique.

 

I would then run this query:

 

alter table members add unique (email);

 

That will enforce unique emails, here is a tutorial I wrote on it:

http://tutorials.phpsnips.com/MySQL/Manipulate_Table/Insert_unique_values

[doubledee]

Your select query makes no sense. If you already know the email why are you using a query to select it?

 

I guess so.

 

All I am trying to do is show that there is a record for "email" thus proving that it is a valid e-mail.  (As such, I suppose and field would work.)

 

 

Does it really matter that I am doing the UPDATE based on "email"??

 

Only if your 100% sure the email is unique.

 

I would then run this query:

 

alter table members add unique (email);

 

Well, e-mail is what uniquely identifies members (although I technically have an ID pk.)

 

And so I have a Unique index already on "email".

 

Thanks,

 

 

Debbie