Best way to write PHP SQL Update Statement

Question

I have this PHP SQL statement:

$updateCategory = "UPDATE category 
                   SET name=".$name.", description=".$description.",
                       parent=".$parent.", active=".$active." 
                   WHERE id=".$catID."";

What is the best way to write this?

Thanks,

Chris.

How do you mean? Are you looking for a different query, formatting/layout options, maybe tips on how to use PDO, comments on injection, tips for speed... what is your question? — Nanne, Mar 25 '11 at 14:13
This seems pretty subjective, but Michiel Pater's answer is as good as any. You could also look into using prepared statements, which is a good practice and cuts down on the amount of annoying string concatenation you need to do. — Hammerite, Mar 25 '11 at 14:15

Dan Soap · Answer 1 · 2013-06-24T07:18:35.427

I suggest you use prepared statements instead of concatenating the query string together:

$sql = 'UPDATE 
           category
        SET
           name=:name,
           description=:description,
           parent=:parent, 
           active=:active
        WHERE
           id=:catID';

if you are using PDO, which I strongly suggest, you would then call it like this:

$params = array(
    ':name'        => $name,
    ':description' => $description,
    ':parent'      => $parent,
    ':active'      => $active,
    ':catID'       => $catID
);

$stmt = $pdo->prepare($sql);
$stmt->execute($params);

You might ask, "why all this hassle?" The advantages of this approach are quite overwhelming:

You don't have to care about SQL injection, since the database driver now handles the correct transformation of the input parameters
You don't have to care about escaping special characters, but you can concentrate on what you want to achieve rather than on how to achieve it :-)

score 2 · Answer 2 · answered Mar 25 '11 at 14:11

2

You could format it like this to make it more readable.

$updateCategory = "
    UPDATE
        category
    SET
        `name` = '" . $name . "',
        `description` = '" . $description . "',
        `parent` = '" . $parent . "',
        `active` = '" . $active . "'
    WHERE
        `id` = '" . $catID . "'";

answered Mar 25 '11 at 14:11

Michiel Pater

22,377
5
43
57

why not simply `$name` or `{$name}` without those hard-to-read concatenations? – kapa Mar 25 '11 at 14:18
@bazmegakapa: I find it more readable because of syntax highlighting. – Michiel Pater Mar 25 '11 at 14:19
OK with that, just a suggestion. I prefer `{$name}` for the same reason actually... – kapa Mar 25 '11 at 14:23

score 1 · Answer 3 · answered Mar 25 '11 at 14:18

I find that concatenating queries causes me major headaches with syntax errors-- all those quotes and dots sprinked around like pepper. Here's how I would write the query:

$updateCategory = "
    UPDATE category     
    SET catname = '$name', description = '$description', 
        parent = '$parent', active = '$active'
    WHERE id = '$catID'";

Note that "name" is a reserved word and should not be used as a column name. Also if id is an integer, $catID doesn't need to be quoted.

score 0 · Answer 4 · edited Sep 11 '14 at 11:13

0

You can try:

$update = "update table_name SET name = '$name', email = '$email', password = '$password', phoneno = '$phoneno' WHERE id = '$id'";

edited Sep 11 '14 at 11:13

Freelancer

4,459
2
22
28

answered Sep 11 '14 at 10:31

user4030507

1

Best way to write PHP SQL Update Statement

4 Answers4

Linked