MySQL UPDATE function

Hi, I’m trying to update rows of data in a database via an HTML form.

Each time I run the script, it just creates a new row and I am unsure of how to fix this. This a PHP script to be exact.

Here is my MySQL syntax:

$query = "UPDATE events SET title = " . $title . ", link = " . $link . ", date = " . $date . ", time = " . $time . ", location = " . $location . ", description = " . $description . ", contact = " . $contact . ", phone = " . $phone . ", email = " . $email . ", simple = " . $simple . " WHERE id = " . $id;

mysql_query($query) or die(mysql_error());

All the variables work and I can echo that on my page with all necessary information. Please note, when I run the script, it does not create any errors. It successfully writes to the database, just not where I want it to.

Also, the “id” column is the primary key and is set to auto_increment.

that UPDATE statement will not write a new row

maybe you’re looking in the wrong part of the script

I swear it does. I honestly don’t get it. I’m relatively new to MySQL, but I’ve tried anything and everything I can find online and it just does not seem to work. The script is very basic and not too long. Is there anything else you need to see to help you out?

The query you are building there doesn’t look like it will be valid. All the string values – or what I assume are string values: description, email, etc… – are not quoted, which they should be.

This also supports r937’s point, that you are looking in the wrong place. Even if an UPDATE query could add a row, this one should fail with an error.

How would I make that query valid? I’ve had trouble finding anything specific online about how to properly write queries.

For a query that includes strings to be valid, the strings need to be quoted.

UPDATE tbl SET email = ''

If you leave the quotes out, the query will be invalid.

And to make this easy to create in PHP, you would do something like:

$email = mysql_real_escape_string($_POST['email']);
$sql = "UPDATE tbl SET email = '{$email}'";

Note how I add the $email variable into the string. When you are dealing with double-quoted strings, you can insert variables directly into the string and PHP will replace them with their values. The curly-brackets around the variable name is not strictly necessary, but it is better to include them, as they make it easier to avoid problems with the variable name.

So for your query, I would do this:

$query = "  UPDATE events 
                title = '{$title}', 
                link = '{$link}', 
                date = '{$date}', 
                time = '{$time}', 
                location = '{$location}', 
                description = '{$description}', 
                contact = '{$contact}', 
                phone = '{$phone}', 
                email = '{$email}', 
                simple = '{$simple}' 
                id = {$id}";

If any of the fields I quoted are not strings, then you would remove the quotes.

Nevermind, I think I’ve found the issue, but do check back if I am unable to actually fix it.

The Problem is fixed and it was all errors with my code. Thanks for all the advice, it’s working now.