SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 33
  1. #1
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATING table doesn't work

    Hi,
    I'm using a form to show the data from a certain table and I'd like to update the data using this form, but it doesn't work.

    Here's the code:

    PHP Code:
    <?
    if(isset($_POST["send"]))
    {
        
        
    mysql_query("UPDATE mitgliederdatenbank (id, name, vorname, email_alias, email_provider, email_tld, geburtsdatum, telefon, beruf, fachzweig, jahrgang, strasse, plz, wohnort) VALUES (NULL, '".$_POST["name"]."', '".$_POST["vorname"]."', '".$_POST["email_alias"]."', '".$_POST["email_provider"]."', '".$_POST["email_tld"]."', '".$_POST["geburtsdatum"]."',  '".$_POST["telefon"]."',  '".$_POST["beruf"]."',  '".$_POST["fachzweig"]."',  '".$_POST["jahrgang"]."', '".$_POST["strasse"]."', '".$_POST["plz"]."', '".nl2br($_POST["wohnort"])."')");
        
    $num mysql_affected_rows();
        if (
    $num 0
        {
            print 
    "Successfully edited";
        }
        else
        {    
            print 
    "Sorry, update didn't work.";
        }
    }

    $res=mysql_query("SELECT * FROM mitgliederdatenbank WHERE ID=$id LIMIT 1"); 

    while (
    $row mysql_fetch_array($res)) { ?> 
    <br>
    <form action="<?php $_SERVER["PHP_SELF"]?>" method="post" name="Formular" id="Formular">
    Name:<br>
    <input type="text" name="name" id="name" value="<? print $row["name"]; ?>" />
    <br />
        <br />
        Vorname:<br />
        <input name="vorname" type="text" id="vorname" value="<? print $row["vorname"]; ?>" />
        <br />
        <br />
        Email Adresse: <br />
        <input name="email_alias" type="text" id="email_alias" value="<? print $row["email_alias"]; ?>" size="20" maxlength="40" />
        @ 
        <input name="email_provider" type="text" id="email_provider" value="<? print $row["email_provider"]; ?>" size="20" maxlength="25" />
        . 
        <input name="email_tld" type="text" id="email_tld" value="<? print $row["email_tld"]; ?>" size="3" maxlength="3" />
        <br />
        <br />
        Geburtsdatum (tt.mm.jjjj):<br />
        <input name="geburtsdatum" type="text" id="geburtsdatum" value="<? print $row["geburtsdatum"]; ?>" />
    <p>Telefon:<br />
          <input name="telefon" type="text" id="telefon" value="<? print $row["telefon"]; ?>" />
          <br />
          <br />
        Beruf:<br />
        <input name="beruf" type="text" id="beruf" value="<? print $row["beruf"]; ?>" />
        <br />
        <br />
        Fachzweig:<br />
        <input name="fachzweig" type="text" id="fachzweig" value="<? print $row["fachzweig"]; ?>" />
        <br />
        <br />
        Jahrgang:<br />
        <input name="jahrgang" type="text" id="jahrgang" value="<? print $row["jahrgang"]; ?>" />
        <br />
        <br />
        Strasse und Hausnummer:<br />
          <input name="strasse" type="text" id="strasse" value="<? print $row["strasse"]; ?>" />
          <br />
          <br />
          PLZ:
          <br />
          <input name="plz" type="text" id="plz" value="<? print $row["plz"]; ?>" size="5" maxlength="5" />
          <br />
          <br />
          Wohnort:<br />
          <input name="wohnort" type="text" id="wohnort" value="<? print $row["wohnort"]; ?>" />
          <br />
          <br />
          <input type="hidden" name="id" value="<?=$id?>" />
          <input name="send" type="submit" id="send" value="Edit" />
          
    <?php 

    mysql_free_result($res); 

    ?>
    It shows my defined error "Sorry, update didn't work."

    What's the problem?

  2. #2
    SitePoint Addict Huscy's Avatar
    Join Date
    Jul 2002
    Location
    Newcastle, UK
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    add an or die(mysql_error()) at the end of it and PHP will tell you, ala...

    Code:
    mysql_query("UPDATE mitgliederdatenbank (id, name, vorname, email_alias, email_provider, email_tld, geburtsdatum, telefon, beruf, fachzweig, jahrgang, strasse, plz, wohnort) VALUES (NULL, '".$_POST["name"]."', '".$_POST["vorname"]."', '".$_POST["email_alias"]."', '".$_POST["email_provider"]."', '".$_POST["email_tld"]."', '".$_POST["geburtsdatum"]."',  '".$_POST["telefon"]."',  '".$_POST["beruf"]."', 
     '".$_POST["fachzweig"]."',  '".$_POST["jahrgang"]."', '".$_POST["strasse"]."', '".$_POST["plz"]."', '".nl2br($_POST["wohnort"])."')") or die(mysql_error());

  3. #3
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks.
    It says:

    You have an error in your SQL syntax near '(id, name, vorname, email_alias, email_provider, email_tld, geburtsdatum, telefo' at line 1

    I can't find the mistake because I copied this line from the file I'm adding data to the table and changed INSERT INTO to UPDATE.

  4. #4
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't you need a "WHERE" clause in there so it knows what row to update?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think you want an INSERT instead of an UPDATE

    change
    Code:
    UPDATE mitgliederdatenbank (id, name, ...
     VALUES (NULL, '".$_POST["name"]."', ...
    to
    Code:
    INSERT INTO mitgliederdatenbank (id, name, ...
     VALUES (NULL, '".$_POST["name"]."', ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes. that's right.

    I added the WHERE claus:

    PHP Code:
    <?
    mysql_query
    ("UPDATE mitgliederdatenbank WHERE id=$id....")
    That should make sense, shouldn't it?

    Okay. So I loaded the script with: edit.php?id=1 to try it out.

    But it still won't work.

    You have an error in your SQL syntax near 'WHERE id=1 (id, name, vorname, email_alias, email_provider, email_tld, geburtsda' at line 1

    r937:
    Nope I want to update, not to add data.

  7. #7
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The WHERE clause goes after the UPDATE Clause. BUt Rudy's right, are you sure you don't need an INSERT clause?

  8. #8
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your getting id from the url you will need to use $_GET to retrieve it.

    PHP Code:
    mysql_query("
            UPDATE
                mitgliederdatenbank
                (
                    id,
                    name,
                    vorname,
                    email_alias,
                    email_provider,
                    email_tld,
                    geburtsdatum,
                    telefon,
                    beruf,
                    fachzweig,
                    jahrgang,
                    strasse,
                    plz,
                    wohnort
                )
            VALUES
                (
                    NULL,
                    '"
    .$_POST["name"]."',
                    '"
    .$_POST["vorname"]."',
                    '"
    .$_POST["email_alias"]."',
                    '"
    .$_POST["email_provider"]."',
                    '"
    .$_POST["email_tld"]."',
                    '"
    .$_POST["geburtsdatum"]."',
                    '"
    .$_POST["telefon"]."',
                    '"
    .$_POST["beruf"]."',
                    '"
    .$_POST["fachzweig"]."',
                    '"
    .$_POST["jahrgang"]."',
                    '"
    .$_POST["strasse"]."',
                    '"
    .$_POST["plz"]."',
                    '"
    .nl2br($_POST["wohnort"])."'
                )
            WHERE
                id = "
    .$_GET['id'].""
            
    ) or die(mysql_error()); 
    Much easier to read this way.

  9. #9
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jump
    The WHERE clause goes after the UPDATE Clause. BUt Rudy's right, are you sure you don't need an INSERT clause?
    But the WHERE clause is after the UPDATE clause, isn't it? How'd you do it?

    Nope. I really want to update data.
    As you can see in my first post, I got a form that shows DATA that already exists and which should be able to be edited via this form.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rouven
    r937:
    Nope I want to update, not to add data.
    oh, okay:
    Code:
    update mitgliederdatenbank 
       set name            = '".$_POST["name"]."'
         , vorname         = '".$_POST["vorname"]."'
         , email_alias     = '".$_POST["email_alias"]."' 
         , email_provider  = '".$_POST["email_provider"]."'
         , email_tld       = '".$_POST["email_tld"]."'
         , geburtsdatum    = '".$_POST["geburtsdatum"]."'
         , telefon         = '".$_POST["telefon"]."'
         , beruf           = '".$_POST["beruf"]."'
         , fachzweig       = '".$_POST["fachzweig"]."'
         , jahrgang        = '".$_POST["jahrgang"]."'
         , strasse         = '".$_POST["strasse"]."'
         , plz             = '".$_POST["plz"]."'
         , wohnort         = '".nl2br($_POST["wohnort"])."'
     WHERE id = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The script structure and logic certainly seems to indicate the form holds the data from the current record and then upon submit it would update the record.

    You can't use that syntax with an UPDATE (although using UPDATE syntax with an INSERT statement works). So your UPDATE statement needs to look like this -
    PHP Code:
    $query "UPDATE sometable SET somefield = '" .$_POST['somevalue']. "'"
    You also definitely need to have a WHERE clause in your UPDATE or all records would be updated with those values. Danger danger.

    You also need to do something to validate your form data prior to insertion; the way you have it now anyone can alter the data as they see fit, and you're setting yourself up for possible SQL injection.

    Also, are you certain you want to use nl2br() on your last field there? Doing so will only make the record more inflexible, as it will be infused with <br /> tags rather than simply inserting the data. I would highly suggest NOT doing that; if you want it formatted properly, do so on retrieval.

  12. #12
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, use SET.

  13. #13
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    hi

    left those big head people and put this..it will work 100%!!!!
    PHP Code:
    <?php
    if(isset($_POST["send"])) process_update();
    function 
    process_update() 

    if(
    $_POST["name"]!="")
    {
    $ret "insert into mitgliederdatenbank values ('', '$name', '$vorname', '$email_alias', '$email_provider', '$email_tld', '$geburtsdatum', '$telefon', '$beruf', '$fachzweig', '$jahrgang', '$strasse', '$plz', '$wohnort')"
    $resultat mysql_query($ret);
    mysql_close($connect);
    echo 
    "Successfully edited"
    } else {     
    echo 
    "Sorry, update didn't work."
    }     

    ?>
    cya

  14. #14
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hyperimage
    left those big head people and put this..it will work 100%!!!!
    PHP Code:
    <?php
    if(isset($_POST["send"])) 

    $ret "insert into mitgliederdatenbank ('', '$name', '$vorname', '$email_alias', '$email_provider', '$email_tld', '$geburtsdatum', '$telefon', '$beruf', '$fachzweig', '$jahrgang', '$strasse', '$plz', '$wohnort')"
    $resultat mysql_query($ret);
    mysql_close($connect);
    echo 
    "Successfully edited"
    } else {     
    echo 
    "Sorry, update didn't work."
        } 
    ?>
    cya
    First off, it's not an INSERT, it's an UPDATE. Secondly, your SQL query relies on register_globals being turned on, unless we're to assume you've already taken the vars in the INSERT statement from your $_POST vars up above the bit of code you posted.

  15. #15
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its asumed that all script goes into 1 file!!!
    if it is called by hypertext then u have to global!!!!
    and look better to the script!!!!!!

  16. #16
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks guys.

    r937's code finally worked.

    You also need to do something to validate your form data prior to insertion; the way you have it now anyone can alter the data as they see fit, and you're setting yourself up for possible SQL injection.
    You mean the form has to come before the updating? Sorry, I'm a dummy concerning php.

    Also, are you certain you want to use nl2br() on your last field there? Doing so will only make the record more inflexible, as it will be infused with <br /> tags rather than simply inserting the data. I would highly suggest NOT doing that; if you want it formatted properly, do so on retrieval.
    Okay. changed that.

    One more thing:
    The id will be given to the edit script. So I think it should be okay like this now?

  17. #17
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    reply!!!!!

    this works with insert or udate ....
    PHP Code:
    <?php 
    if(isset($_POST["send"])) process_update(); 
    function 
    process_update() 

    if(
    $_POST["name"]!=""

    $ret "insert into mitgliederdatenbank values ('', '$name', '$vorname', '$email_alias', '$email_provider', '$email_tld', '$geburtsdatum', '$telefon', '$beruf', '$fachzweig', '$jahrgang', '$strasse', '$plz', '$wohnort')"
    $resultat mysql_query($ret); 
    mysql_close($connect); 
    echo 
    "Successfully edited"
    } else {      
    echo 
    "Sorry, update didn't work."
    }      

    ?>
    PHP Code:
    <?php 
    if(isset($_POST["send"])) process_update(); 
    function 
    process_update() 

    if(
    $_POST["name"]!=""

    $ret "update mitgliederdatenbank('', '$name', '$vorname', '$email_alias', '$email_provider', '$email_tld', '$geburtsdatum', '$telefon', '$beruf', '$fachzweig', '$jahrgang', '$strasse', '$plz', '$wohnort')"
    $resultat mysql_query($ret); 
    mysql_close($connect); 
    echo 
    "Successfully edited"
    } else {      
    echo 
    "Sorry, update didn't work."
    }      

    ?>

  18. #18
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hyperimage
    its asumed that all script goes into 1 file!!!
    if it is called by hypertext then u have to global!!!!
    and look better to the script!!!!!!
    Chill

  19. #19
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh...
    now i know how u have over 1000 posts!!!!!
    u just post ****s!!!
    cya

  20. #20
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seek help.

  21. #21
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay. there's one more little thing you could help me with, and I'm perfectly happy.

    The a members.php links to a
    PHP Code:
    delete.php?id=<? print $row["id"?>
    (That's also the way I'm giving the edit.php the id in order to edit data. Is that okay?)

    Now, this delete.php should ask the user whether to delete or not to delete the data.
    How should the delete.php look like now?

  22. #22
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    explain better on which function u want to delete this!!!
    this one is on delete button with method post!
    PHP Code:
    <?php
    if(isset($_POST["delete"])) delete();
    function 
    delete()
    {
    //delete from table form
    }
    ?>

  23. #23
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay.
    I load a file which asks:

    "Do you really want to delete the DATA?"

    There's a "Yes" link and by clicking it, the script should "DELETE From database where ID=$id"

    The ID will be in the url, so there's no need to make the script get it.

    How would you do that?

    Sorry, dummy alert!

  24. #24
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In regards to the comments about validating data; what you'd want to do is perform some logic at the point of submission. When the script logic comes to the conditional statement that determines if the form was submitted or not...
    PHP Code:
    if(isset($_POST["send"])) 
    ...make some efforts to check the incoming data. Even if it's just to do some simple check so that the data is escaped properly on entry, something like this..
    PHP Code:
    if ( isset($_POST["send"]) )
    {
        
    // _assumes_ no arrays passed within $_POST

        // remove whitespace around data
        
    $_POST array_map('trim'$_POST);
        
    // check data for incoming slashes
        
    if ( !get_magic_quotes_gpc() )
        {
             
    $_POST array_map('addslashes'$_POST);
        }
        
    // continue with the UPDATE statement

    This is just a simple, no frills example of checking data that comes in for quotes and other characters that need to be escaped. You can also use the MySQL specific function mysql_escape_string(), but that relies on a MySQL connection to be provided prior, which may or may not work depending on your script needs. There are alot of good forum posts and tutorials about this; search the forum for 'addslashes', 'magic_quotes' and 'form validation'.

    okay.
    I load a file which asks:

    "Do you really want to delete the DATA?"

    There's a "Yes" link and by clicking it, the script should "DELETE From database where ID=$id"

    The ID will be in the url, so there's no need to make the script get it.

    How would you do that?
    I normally do this with one or two steps: first via a Javascript alert() method that just checks the user input and asks if they really want to delete the record or not. You could do this with a onClick() method with the URL. Not the best way, because you can't absolutely rely on JS. The second method would be in your handling script (delete.php) that verifies that the record ID is valid, etc and might give the user another confirmation <form> submit to verify they really do intend to delete it, e.g.
    PHP Code:
    // delete.php
    // UNTESTED EXAMPLE ONLY
    if ( !empty($_POST['deleteRecord']) )
    {
        
    $deleteID $_POST['deleteID'];
        
    // user must have confirmed the deletion, delete code goes here
        
    echo 'Record ' .$deleteID' will be deleted!!<br />';
        
    // connect to database
        // issue DELETE query
        
    $query "DELETE FROM sometable WHERE id = $deleteID";
        
    mysql_query($query);
    }
    elseif ( !empty(
    $_GET['id']) && is_numeric($_GET['id']) )
    {

        
    $id $_GET['id'];

        
    // the URL has been passed to the script with the record id value
        // display the 'DO YOU REALLY WANT TO DELETE <form>
        
    echo 'Do you really want to delete record ' .$id'?';
        echo 
    '<form action="delete.php" method="POST">';
        echo 
    '<input type="hidden" name="deleteID" value="' .$id'">';
        echo 
    '<input type="submit" name="deleteRecord" value="DELETE">';
        echo 
    '</form>';
    }
    else
    {
        
    // something is wrong, no data has been passed, no form submission
        
    echo 'Nothing happened!!<br />';


  25. #25
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your help!
    Your delete script works good and I added the valdation code, too.


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
  •