PHP MySQL Update query

Question

I have a website that has an update query. Example.

Table name -> myTable
Table Content -> id(AI)
                 name  --> myName
                 age   --> 13
                 image --> (no values)
                 birthdate  --> (no values)

I only put values in name and age. In image and birthdate, I haven't put anything.
If I edit it and update it and I don't put anything in name and in age, I only put values in image and in birthdate. The output is like this. (This ouput I wanted)

Table name -> myTable
Table Content -> id(AI)
                 name  --> myName
                 age   --> 13
                 image --> myImage.jpg
                 birthdate  --> Feb. 31, 2010

This is my code:

<?php
  //mysql connect...
  //mysql select database...
$sql = "UPDATE myTable SET name = '".$name."', age = '".$age."', image = '".$image."', birthdate"'$birthdate.."' WHERE id = $id";
mysql_query($sql);
?>

I used this code but the output is:

Table name -> myTable
Table Content -> id(AI)
                 name  --> (no values)
                 age   --> (no values)
                 image --> myImage.jpg
                 birthdate  --> Feb. 31, 2010

Feel free to ask if you don't understand my question
Thank you

Bill Karwin · Accepted Answer · 2010-06-02 00:31:42Z

4

UPDATE leaves columns as they were if you don't include new values in your UPDATE statement.

So you may need to build up the $sql variable with some conditional code that examines what the user entered, and then only include a column if the user gave a value for it.

<?php

$columns = array("name", "age", "image", "birthdate");
$set = array();
foreach ($columns as $col) {
    if (isset($_GET[$col])) {
      $set[] = "$col = '" . mysql_real_escape_string($_GET[$col]) . "'";
    }
}
$sql = "UPDATE myTable "
if ($set) {
    $sql .= " SET " . implode(",", $set);
}
$sql .= " WHERE id = " . (int) $id;

answered Jun 2, 2010 at 0:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Justin Johnson Over a year ago

It might be better to use !empty($_GET[$col]) instead of isset. If the form has fixed inputs (e.g.: inputs are not added or disabled dynamically), then empty inputs would be treated as if a new value was provided.

julio · Accepted Answer · 2010-06-02 00:30:44Z

2

you have an error in your code--

$sql = "UPDATE myTable SET name = '".$name."', age = '".$age."', image = '".$image."', birthdate"'$birthdate.."' WHERE id = $id"

should read:

$sql = "UPDATE myTable SET name = '".$name."', age = '".$age."', image = '".$image."', birthdate = '".$birthdate."' WHERE id = $id"

but that's probably just a typo. Are you sure that the variables are getting set correctly? Try echoing out all the vars to make sure. Also, check that the columns have no default data definition.

answered Jun 2, 2010 at 0:30

julio

6,76815 gold badges66 silver badges82 bronze badges

2 Comments

RobertPitt Over a year ago

Beat me to it: birthdate"'$birthdate.."'

Jorge Over a year ago

Thank you. ^^. I forgot the equal sign.

Brendan Long · Accepted Answer · 2010-06-02 00:25:49Z

1

Just have two separate queries:

// update name and age
$sql = "UPDATE myTable SET name = '$name', age = '$age' WHERE id = '$id'";

// Update image and birthday
$sql = "UPDATE myTable SET image = '$image', birthdate = '$birthdate' WHERE id = '$id'";

Also, you can just put all of the variables inside the string since it's double quotes.

answered Jun 2, 2010 at 0:25

Brendan Long

54.6k21 gold badges154 silver badges194 bronze badges

Comments

Don Kirkby · Accepted Answer · 2010-06-02 00:27:05Z

1

If you don't want to change a field, don't include it in the update query.

answered Jun 2, 2010 at 0:27

Don Kirkby

56.7k28 gold badges225 silver badges307 bronze badges

Comments

RobertPitt · Accepted Answer · 2010-06-02 00:41:55Z

0

Personally i think that quires like this are not that reliable, Especially when it comes down to typecasting etc.

I recommend you try and implement PDO or something.

http://php.net/manual/en/book.pdo.php

Othewise, the error is birthdate'".$birthdate."' witch needs to be birthdate = '".$birthdate."'

answered Jun 2, 2010 at 0:41

RobertPitt

57.3k21 gold badges117 silver badges161 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Update query

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related