SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating Database with PHP

    Hi, I was wondering if someone could help me out with this at all? Sorry if I'm in the wrong area and this needs to go in the MySQL section

    I have a .swf that outputs a series of user inputted variable, these get passed onto PHP which in turn sends them to a MySQL database to record... this works all fine

    the problem I'm having is that I need to be able to update the record in the database if the user decided to enter new variables at some other time


    example
    ---------------------------------------
    at the minute I have 5 text boxes that the user enters numbers into
    eg. 4, 7, 1, 5, 3

    they then submit them to the database (all working fine)

    they then change their mind on the numbers and enter new ones
    eg. 5, 6, 2, 5, 7

    I cant get this bit to work
    how do I get the row to update with the new data, I've tried using UPDATE but can seem to get it to work, I can keep using the following code and it keeps creating a new row (which is fine, but I done want multiple rows I would like just 1)


    PHP Code:
    <?php
       
    //Capture data from $_POST array
       
    $userScore0 $_POST['un111sc'];
       
    $userScore1 $_POST['un112sc'];
       
    $userScore2 $_POST['un113sc'];
       
    $userScore3 $_POST['un114sc'];
       
    $userScore4 $_POST['un115sc'];

       
    //Connection to database
       
    $connect mysql_connect("localhost""username""password");
       
    mysql_select_db ("dbName"$connect);
       
       
    //Perform the query
       
    $result mysql_query("INSERT into table (un111sc, un112sc, un113sc, un114sc, un115sc) values ('$userScore0', '$userScore1', '$userScore2', '$userScore3', '$userScore4')");
    ?>
    thanks for any help

  2. #2
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by saying you can't get UPDATE to work?

  3. #3
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fiveforty View Post
    the problem I'm having is that I need to be able to update the record in the database if the user decided to enter new variables at some other time
    Some questions.

    Is there more than 1 user? How do you know it's the same user? Do they login? Or are you tracking them somehow? Do you enter this info into the database?

    In order to UPDATE a row, you will need to know how to uniquely identify it either by the row ID or someother method.

  4. #4
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by decowski View Post
    What do you mean by saying you can't get UPDATE to work?
    I dont know how to make it work with the script I already have

    I'm new to PHP, if I was writing it in actionscript I would just use an IF statement

    if (numbers already exist){
    replace with new numbers;
    }

    but I dont know how to do that in PHP

  5. #5
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elgumbo View Post
    Some questions.

    Is there more than 1 user? How do you know it's the same user? Do they login? Or are you tracking them somehow? Do you enter this info into the database?

    In order to UPDATE a row, you will need to know how to uniquely identify it either by the row ID or someother method.
    Yes there is more than 1 user and they each login

    each user has a table for their numbers

    the table is as follows

    id | un111sc | un112sc | un113sc | un114sc | un115sc

    id being the primary key and each of the un11?sc being 1 of the 5 numbers they submit

  6. #6
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This belongs in MySQL forum really.

    INSERT ... ON DUPLICATE KEY UPDATE

    P.S. You should probably rethink your column naming convention.

  7. #7
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't see anyway of identifying a unique row since you don't know the ID of a row they may have entered previously.

    Ive never used the ON DUPLICATE command but I can't see it working in this case as presumably you could have a number of users with exactly the same selections as each other.

    Personally, I'd want the users login ID or name in the table as well as their selection. That way you could just check if they have already entered any numbers in the past and update that row. If they haven't then you add a new row.

    So the table would look like

    id | userID | un111sc | un112sc | un113sc | un114sc | un115sc

  8. #8
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry just reread your earlier post

    each user has a table for their numbers
    Each user has a table or a row in the table?

  9. #9
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elgumbo View Post
    So the table would look like
    id | userID | un111sc | un112sc | un113sc | un114sc | un115sc
    Your suggested table structure has a superfluous id column; user_id is a natural primary key already so id column is redundant.

    Quote Originally Posted by elgumbo View Post
    Ive never used the ON DUPLICATE command but I can't see it working in this case as presumably you could have a number of users with exactly the same selections as each other.
    ON DUPLICATE KEY UPDATE clause makes MySQL engine look at the primary key or unique index for duplicates, not all the columns (it wouldn't make sense otherwise—"if all columns are the same then update with the same data") and it was designed to do exactly what the OP is looking to do.

    Code:
    user_id    data
    ===============
    1          foo
    2          bar
    
    INSERT INTO `table` (`user_id`, `data`) VALUES (1, 'bar') ON DUPLICATE KEY UPDATE
    INSERT INTO `table` (`user_id`, `data`) VALUES (3, 'foo') ON DUPLICATE KEY UPDATE
    
    user_id    data
    ===============
    1          bar
    2          bar
    3          foo

  10. #10
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ON DUPLICATE KEY UPDATE was exactly what I needed

    thanks a lot for the help


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
  •