mysql Update problem

[ncsunderland]

I've tried several variations but I can't get any of them to execute.

If someone can tell me what's wrong with this I'd be grateful -

~~~~~~~~~~~~~~~~~

      if ($stmt = $mysqli->prepare("UPDATE members (password, salt) VALUES (?,?) WHERE email = ?"))

      {

        $stmt->bind_param('sss', $newpassword,$random_salt,$email);

        // Execute the prepared query.

        $stmt->execute();

        header("Location: ../secure/changepass.php?success=1'");

      }

      else {

      header("Location: ../secure/changepass.php?passwordfailed=1'");

      }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks!!

[Rob Hecker2 · Accepted solution]

Firstly, the if statement can't run on the query because the query isn't doing anything yet. It's the execute statement that does the action. However, putting the if statement on the execute statement also won't do anything. What you need to do is check for errors. I would probably use a try/catch, but you could use

if (stmt->error) {

// do something

} else{

//do something else

}

I don't use mysqli. I use PDO. Maybe your bind_param is OK, but it looks wrong to me. The last variable goes to the where clause. Does that work?

With PDO, you can be very clear which column matches to which variable, like the following. If something similar is available with mysqli, I would use it:

$sqlq = $dbPDO->prepare("INSERT INTO search SET search_term =:search_term, search_count=:search_count, client_id=:client_id, search_date= NOW()");
$sqlq->bindValue("search_term", $queryset);
$sqlq->bindValue("search_count", $search_count);
$sqlq->bindValue("client_id", CLIENT_ID, PDO::PARAM_INT);
try{
$sqlq->execute();
}catch(PDOException $e)
{ echo $e;}
}

[osgood_]

ncsunderland wrote:

I've tried several variations but I can't get any of them to execute.

If someone can tell me what's wrong with this I'd be grateful -

~~~~~~~~~~~~~~~~~

      if ($stmt = $mysqli->prepare("UPDATE members (password, salt) VALUES (?,?) WHERE email = ?"))

      {

        $stmt->bind_param('sss', $newpassword,$random_salt,$email);

        // Execute the prepared query.

        $stmt->execute();

        header("Location: ../secure/changepass.php?success=1'");

      }

      else {

      header("Location: ../secure/changepass.php?passwordfailed=1'");

      }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks!!

You're using the wrong mysqli syntax to 'update' a database (see below, that should work). Obviously change the $newpassword, $random_salt and $email to whatever is getting that information.

<?php

// connect to database

$mysqli = new mysqli('localhost' , 'username , 'password' , 'database_name');

$stmt = $mysqli->prepare("UPDATE members SET password = ?, salt = ? WHERE email = ?");

if($stmt = $mysqli->prepare("UPDATE members SET password = ?, salt = ? WHERE email = ?")) {

$stmt->bind_param("sss", $newpassword, $random_salt, $email);

// set parameters and execute

$newpassword = "Pink";

$random_salt = "Elephant";

$email = "email_address.com";

$stmt->execute();

header("Location: ../secure/changepass.php?success=1'");

}

else {

header("Location: ../secure/changepass.php?passwordfailed=1'");

}

?>

[RR456]

In addition to Rob's observations; and I could be wrong, but it appears the two header lines are using double quotes at the beginning and double as well as single quotes at the end.  I would think DW code editor would have noted that at the time of composition.  If I'm correct I'd think the php engine would be "cranky" on that coding.

[Rob Hecker2]

it appears the two header lines are using double quotes at the beginning and double as well as single quotes at the end.

Good catch. I had to zoom in to see that. It needs to be fixed.

[RR456]

I read the thread right between the coffee kicking in and the Bloody Mary not quite yet "calming me down" for the morning.

I don't think this is really OT; but I wanted to thank you for referring me, about a month ago, to Rick F. van der Lans' book SQL for MySQL Developers SQL for MySQL Developers  That is a very good reference book for writing SQL queries.  I constantly pull it up on my Kindle.  It helped me lay out a frame work to write the query structure; in that, he has an approach that, to me, took rather complex JOINS and wrote them in a manner which made sense.

Stringing a complex SQL query out in one long line got me, a beginner, lost quickly.  Rick gives each component generally and wherever possible, its own line.  That was a great help for me to understand what was going on.  

Thanks again Rob for the referring Rick's book.

[Rob Hecker2]

Thanks again Rob for the referring Rick's book.

Glad it's useful. I don't refer to it often, but it goes far beyond the beginner level of most internet tutorials, so sometimes it's perfect.

Stringing a complex SQL query out in one long line got me, a beginner, lost quickly.

Yes, it's nice that SQL ignores white space and line breaks, so in a long query you can do some serious formatting for comprehension.

[osgood_]

RR456 wrote:

In addition to Rob's observations; and I could be wrong, but it appears the two header lines are using double quotes at the beginning and double as well as single quotes at the end.  I would think DW code editor would have noted that at the time of composition.  If I'm correct I'd think the php engine would be "cranky" on that coding.

Actually surprisingly enough it has no effect (at least testing on a local server) - maybe it just thinks it's part of the value.

[RR456]

Being a follower of David Powers, Mr. Powers constantly notes to be aware of what quote method one is making use of.

I'm guessing not "best practice" to whimsically inter-mix them in a code line; notwithstanding the testing results.  Maybe it does think it's part of the value, which could lead to all other kinds of problems.  And in the instant case he's dealing with password updates; that could turn ugly very quickly.

[osgood_]

RR456 wrote:

And in the instant case he's dealing with password updates; that could turn ugly very quickly.

Not really the header line is being executed AFTER the update and 'probably' has nothing to do with 'inputting' any data (although we dont know for sure - might just be chucking back a success or failure message to the page.) Input data should always be sanitize in some form or another. I just tested it on a couple of remote servers and it works ok. It doesnt serve any purpose being there so in that case it would be wise to removed it. I think this is more to do with using the wrong myqli query to UPDATE a database.

[RR456]

Got it regarding input.

I didn't see the POST super global array in the code.  I'd think for an UPDATE that would be used off a command input button, but I could be going in the wrong direction.  Also, seems the header would be a redirect if the POST was set (isset) and thus successful and a header redirect to the $error if something went wrong.  But again since POST is not in the script, I'm confused as well as curious how this new password is intended to get set in the members table.

[osgood_]

RR456 wrote:

Got it regarding input.

  But again since POST is not in the script, I'm confused as well as curious how this new password is intended to get set in the members table.

That's the part of the code the OP hasn't revealed  BUT they need to be getting that information via a $_POST or $_GET method - I hard coded it in my example:

$newpassword = "Pink";

$random_salt = "Elephant";

$email = "email_address.com";

BUT it most likely looks something like below which is extra to the code shown.

$newpassword = $_POST['newpassword'];

$random_salt = $_POST['random_salt'];;

$email = $_POST['email'];

In terms of the header code we don't really know what the value which is being passed via the url does (its certainly not affecting the header redirect) BUT if it is assigned to a variable on the changepass.php page then it may be an issue.

$success = $_GET['success']; (if that equals 1' and is supposed to equal 1) well...........

header("Location: ../secure/changepass.php?success=1'");

[ncsunderland]

‌Thanks everyone!!! I have it working