Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: Modify row

  1. #1
    New Coder
    Join Date
    Aug 2002
    Location
    New York, NY
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify row

    My old message asked how to add a new row. (see http://www.codingforums.com/showthread.php?s=&threadid=4157). Now I'd like to know of a way to modify current rows. For example, I created a form that automatically fills in the fields (with the database information). But, when I try to modify current rows, nothing happens.

    I tried using the same code when I added a new row:

    INSERT INTO advisoryarticles(article_date,article_title,articl
    e_url,bioid,article_source) VALUES('$article_date','$article_title','$article_
    url','$bioid','$article_source');

    But this didn't work. Is there something else I should be doing?
    thanks for the help.


    Jabbamonkey

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, that couldn't work, because INSERT just does what it says: It inserts new rows into a table. What you are searching for is the UPDATE command, like

    Code:
    UPDATE 
        advisoryarticles 
    SET 
        article_date = '$article_date',
        article_title = '$article_title',
        article_url = '$article_url',
        bioid = '$bioid',
        article_source = '$article_source'
    WHERE
        article_id = '$article_id'
    There's plenty of info in the mysql docs:
    http://www.mysql.com/documentation/m...ce.html#UPDATE

    BTW, wouldn't that question be more suited in the mysql forum?

  • #3
    New Coder
    Join Date
    Aug 2002
    Location
    New York, NY
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question tried it...

    I tried that using the following (different table and variables)

    mysql_query("
    UPDATE
    sciadvisoryarticles
    SET
    sciarticle_date = '$sciarticle_date',
    sciarticle_title = '$sciarticle_title',
    sciarticle_url = '$sciarticle_url',
    sciarticle_member = '$advisory_id',
    sciarticle_source = '$sciarticle_source'
    WHERE
    sciarticle_id = '$sciarticle_id')",$db);

    I checked the variables (i.e. $sciarticle_title) before, and after running this query, but nothing changed in the database. Can you see anything I'm doing wrong?

    Jabbamonkey

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yep, there is an orphan parentheses at the end of your SQL query. Remove it and see what happens then.

    If it's still not working, put your query into a variable called $sql, and try these lines

    PHP Code:
    if (!$result) {
      echo 
    "Error: " mysql_error() . "<br />";
      echo 
    "Query was: $sql";
    } else {
      echo 
    "Updated rows: " mysql_affected_rows();

    after you send the query to the mysql database. They should provide you with adequate info as to what's not working properly. It can be that your query executes correctly, but due to the conditions described in the WHERE clause no matching rows were found and thus no update took place.

    EDIT: Of course you have to assign the return value of mysql_query() to a variable called $result or the above won't work, like $result = mysql_query(...).

  • #5
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Moving this to the mysql forum...
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #6
    New Coder
    Join Date
    Aug 2002
    Location
    New York, NY
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool Null values

    Worked perfectly.

    How do you set NULL values?

    Before updating the row, I tried the following

    if (!$sciarticle_source)
    {
    $sciarticle_source = "NULL";
    }

    But, unfortunately, that didn't make the field null, it gave it the value "NULL".

    Any ideas?

    Jabbamonkey

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Setting a field to NULL? Well, have you tried

    UPDATE tablename
    SET fieldName = NULL
    WHERE ...

    I think that should work, you'll have to write that into the SQL query though, not in your PHP code.

  • #8
    New Coder
    Join Date
    Aug 2002
    Location
    New York, NY
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to do it using php? As I said, I attemped to set the variables before entering them into the query. Since I don't know if the value will be Null or not, I need to set it to Null if and only if they meet certain conditions.

    I know I could setup a few if statements, and a few different queries, but I'd like to keep it as simple as possible. Let me know if you have a simpler solution...

    Thanks.

    Jabbamonkey


    // ###############
    // SET NULL VALUES
    // ###############

    if (!$sciarticle_date || $sciarticle_date == '0000-00-00')
    {
    $sciarticle_date = "NULL";
    }
    if (!$sciarticle_source)
    {
    $sciarticle_source = "NULL";
    }

    // ###############
    // UPDATE ROWS
    // ###############


    mysql_query("
    UPDATE
    sciadvisoryarticles
    SET
    sciarticle_date = '$sciarticle_date',
    sciarticle_title = '$sciarticle_title',
    sciarticle_url = '$sciarticle_url',
    sciarticle_member = '$advisory_id',
    sciarticle_source = '$sciarticle_source'
    WHERE
    sciarticle_id = '$sciarticle_id'",$db);

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not really another idea, but have you made sure your database application is normalized? For me it seems you're having rows that may or may not contain chunks of data, perhaps your better served with separating that information that's optional to another table.

    Another possiblity would be to only set those fields in the query that correspond to the variables you get, but that would also leave you with some if-clauses to set the right SQL into the variables, e.g:

    if (cond1 == true) {
    $article = "sciarticle = '$sciarticle',"
    }

    ...
    UPDATE
    sciadvisoryarticles
    SET
    sciarticle_date = '$sciarticle_date',
    $article
    $articleB
    ...



    Hmmh, if I think about it, it's just the other way round how you have it at the moment. So I'm not all to sure my musings are of any real help here.


  •  

    Posting Permissions

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