Participant

Answered

mysql Update problem

Forum|Forum|10 years ago
October 6, 2015
4 replies
1604 views

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!!

This topic has been closed for replies.

Correct answer Rob Hecker2

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;}
}

N

ncsunderlandAuthor

Participant

‌Thanks everyone!!! I have it working

R

RR456

Inspiring

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

Legend

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.

R

RR456

Inspiring

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.

O

osgood_

Legend

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'");

}

?>

Rob Hecker2Correct answer

Legend

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;}
}

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded