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

Answer 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.

Answer 2

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

Answer 3

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.

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

Answer 5

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."'