Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

angel angel
angel angel
6,620 Points
Posted by angel angel
angel angel
6,620 Points

SQL UPDATE?

IS this well written to update a database

```SQL UPDATE products SET categoryID = :category_id productCode = :code, productName = :name, listPrice = :price

          WHERE productID = :product_id  ```

3 Answers

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

You need some punctuation.

It looks like you still need to put commas between the column settings.

I'm assuming the names beginning with colons represent values and are not other columns, but you didn't show how those get set.

angel angel
angel angel
6,620 Points
angel angel
angel angel
6,620 Points 
<?php
// Get the product data

$category_id = filter_input(INPUT_POST, 'category_id', FILTER_VALIDATE_INT);
$code = filter_input(INPUT_POST, 'code');
$name = filter_input(INPUT_POST, 'name');
$price = filter_input(INPUT_POST, 'price', FILTER_VALIDATE_FLOAT);




// Validate inputs
if ($category_id == null || $category_id == false || 
        $code == null || $name == null || $price == null || $price == false) {
    $error = "Invalid product data. Check all fields and try again.";
    include('error.php');
} else {
    require_once('database.php');

    // Add the product to the database  
    $query = "UPDATE products
                 SET categoryID = :category_id, 
                productCode = :code, 
                 productName = :name, 
                 listPrice= :price

            WHERE  categoryID = :category_id , productCode = :code ,productName = :name, 
                 listPrice = :price ";


    $statement = $db->prepare($query);
    $statement->bindValue(':category_id', $category_id);
    $statement->bindValue(':code', $code);
    $statement->bindValue(':name', $name);
    $statement->bindValue(':price', $price);
    $statement->execute();
    $statement->closeCursor();


    // Display the Product List page
    include('index.php');
}
?> ```
angel angel
angel angel
6,620 Points
angel angel
angel angel
6,620 Points

This is how everything look.

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

Now your WHERE clause needs work.

It looks like you expanded your WHERE clause, but unlike the SET list, here you would not use commas. Instead you would combine your conditions with things like AND or OR.

But if you intended to use AND, the entire expression would only update rows that already had the values you were setting, essentially doing nothing to the data! You may need to rethink what you are intending to do.