SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    Code:
    $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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that UPDATE statement will not write a new row

    maybe you're looking in the wrong part of the script
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Enthusiast Atli's Avatar
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would I make that query valid? I've had trouble finding anything specific online about how to properly write queries.

  6. #6
    SitePoint Enthusiast Atli's Avatar
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For a query that includes strings to be valid, the strings need to be quoted.
    Code mysql:
    UPDATE tbl SET email = 'user@example.com'
    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:
    Code PHP:
    <?php
    $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:
    Code PHP:
    $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}";
    If any of the fields I quoted are not strings, then you would remove the quotes.

  7. #7
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, I think I've found the issue, but do check back if I am unable to actually fix it.

  8. #8
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The Problem is fixed and it was all errors with my code. Thanks for all the advice, it's working now.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •