Help for prepared statement

#1

Hi,
How can I use a prepared statement in this case?

$update_values = array();

if(!empty($user_first)){
   $update_values[] = "user_first='".$user_first."'"; 
}
if(!empty($user_last)){
 $update_values[] = "user_last='".$user_last."'"; 
}

$update_values_imploded = implode(', ', $update_values);

                    if( !empty($update_values) ) {
                            $q = "UPDATE users SET $update_values_imploded WHERE user_id='$userid' ";
                            $r = mysqli_query($conn,$q);

                            if($r) {
                                // Messaggio di successo se l'utente è stato modificato
                                $_SESSION['success_msg'] = 'Utente aggiornato con successo!';
                                header("location: ../client_profile.php");
                                exit();

                            }
                        }
#2 
$q = "UPDATE users SET $update_values_imploded WHERE user_id='$userid' ";
$r = mysqli_query($conn,$q);

Just change these 2 lines to use prepared statements. That’s all you do.

#3

Hi @spaceshiptrooper how do I bind params if these are not always the same number?

#4

FIFY

#5

I think the problem is you can’t replace $update_values_imploded to a placeholder because it’s not a parameter, but a string forming part of the query. It will need a different approach.

if(!empty($user_first)){
   $update_values[] = "user_first = ? "; 
}
if(!empty($user_last)){
 $update_values[] = "user_last= ? "; 
}

Then in the query:-

$q = "UPDATE users SET $update_values_imploded WHERE user_id= ?";
#6

Hi @SamA74 yes I thought I could do it like you said but then I know how to bind paras for user_id but don’t know how to do it for the other params.
I normally use to bind params in the follow way:

mysqli_stmt_bind_param($stmt, 'iss', $km_user_id, $user_first, $user_last);

So the problem is if one of the params is empty then the number of params to bind will be different.

#7

Hi @m_hutley don’t know what it does mean :frowning:

#8

Of course, I see.
TBH I never use bind params, I exclusively use PDO and pass params in with execute($params)
That way you can build the array as you go:-

$params = array(); // Start empty array

if(!empty($user_first)){
   $update_values[] = "user_first = ? ";
   $params[] = $user_first;
}
if(!empty($user_last)){
 $update_values[] = "user_last= ? ";
 $params[] = $user_last;
}

$params[] = $userid;

It’s a while since I used mysqli so can;t recall if yuo can do the same there.

#9

FIFY = “Fixed It For You”. Put the link to bind_params into the quote because you asked “How do I bind params” without qualifying that you already knew how or that your question was about a variable number of parameters.

Sam’s answer is almost complete, except you need a second array for building the types string. (or do it with actual string concatenation)

#10

I’ve got no idea how to build a second array for the types strings :frowning: