How to bind parameters in mysql query?

Question

I have search for bind parameters. But it just getting me confused. I'm really a beginner in php and mysql.

here is code:

$query ="UPDATE table_user_skills SET rating='" . $_POST["rating"] . "' where rating_id='".$_POST['id']."'";

$result = $conn->query($query);

I wonder if how can i apply the bind parameters method in this sample query. Thanks for you response.

Thanks for all the responses. My code works

update.php

$sql = "UPDATE table_user_skills SET rating=? WHERE rating_id=?";

$stmt = $conn->prepare($sql);

$stmt->bind_param('sd', $myrate, $myrateid);
$stmt->execute();

if ($stmt->errno) {
  echo "Error" . $stmt->error;
}
else print 'Your rate is accepted.';

$stmt->close();

You dont have any parameters to bind!?!??!! - RT?M MYSQLI Manual or PDO Manual — RiggsFolly
– RiggsFolly, Commented Jun 30, 2016 at 17:26
Have you had a look at prepared statements at all? try convert this to the prepared statement then come back and we will fix the little issues left over. — Matt
– Matt, Commented Jun 30, 2016 at 17:26
mysqli -> Prepared Statements && pdo -> Prepared Statements. There are a bunch of examples in each... — FirstOne
– FirstOne, Commented Jun 30, 2016 at 17:30
Possible duplicate of Bind variables in a mysql_query statement — Michael Gaskill
– Michael Gaskill, Commented Jun 30, 2016 at 20:05

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

When you write the query, leave the values (the $_POST variables) out of the SQL code and in their place use a placeholder. Depending on which interface you're using in PHP to talk to your MySQL database (there's MySQLi and PDO), you can use named or unnamed place holders in their stead.

Here's an example using PDO

$query = "UPDATE table_user_skills SET rating= :ratings where rating_id= :id";
$stmt = $conn->prepare($query);
$stmt->execute($_POST);

What we've done here is send the SQL code to MySQL (using the PDO::prepare method) to get back a PDOStatement object (denoted by $stmt in the above example). We can then send the data (your $_POST variables) to MySQL down a separate path using PDOStatement::execute. Notice how the placeholders in the SQL query are named as you expect your $_POST variables. So this way the SQL code can never be confused with data and there is no chance of SQL injection.

Please see the manuals for more detailed information on using prepared statements.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 30, 2016 at 17:34

Sherif

12k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

FirstOne Over a year ago

The ops said that they are using mysqli xD (just in case you want to update your answer)

Matt Over a year ago

@FirstOne we can let him off, he did this before OP said mysqli :P

Seulgi Bear Over a year ago

thank for this.. how should i pass the value from $_post to the :rating in the query?

FirstOne Over a year ago

@SeulgiBear, please, there are a bunch of tutorials out there. Can you take some time to study them and come back with a more specific problem?

Matt Over a year ago

@SeulgiBear php.net/manual/en/mysqli.quickstart.prepared-statements.php the examples are good enough to see what is going on and how.

|

Collectives™ on Stack Overflow

How to bind parameters in mysql query?

1 Answer 1

Here's an example using PDO

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Here's an example using PDO

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related