SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a very simple question... I'm sure that many people can help me.

    I want to make an update-statement which updates fields in three different tables.

    for instance:

    table 1
    - title
    - id

    table 2.
    - title
    - id
    - table1id

    table 3
    - text
    - id
    - table2id

    I know that the database-structure sucks... but it's the database I have to work with, unfortunately.
    I want to update items in this database. Could someone help me out with a script?

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFAIK (and really that's not that far - about here to the street corner), MySQL doesn't allow you to update multiple tables in the one query. Its not a silly question though - as some RDMS do allow this. So, I would create a seperatecall to mysql_query() for each table.

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah, I see...

    what would a single script look like?

    PHP Code:
    $sql "UPDATE table2 SET title='$title', id='$table2id', table1id='$table1id' WHERE id =$table2id"
    Would you have to call all the items... or can you also just update one field and omit the other ones?
    for instance:
    PHP Code:
    $sql "UPDATE table2 SET title='$title' WHERE id =$table2id"
    And how do you activate this script?

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The second statement looks fine. Just call mysql_querry() like you would for a SELECT query. Eg:
    PHP Code:
    $sql*=*"UPDATE*table2*SET*title='$title'*WHERE*id*=$table2id";
    $result mysql_query($sql);
    // here's a bonus. Say you want to find out how many
    // rows were updated in that last query:
    $numAffectedRows mysql_affected_rows($result); 
    BTW, you don't need to assign the result set identifier returned by mysql_query() if there is no need to. I could have written this:
    mysql_query($sql);
    But now I haven't got a link identifier so I cannot call mysql_affected_rows()

  5. #5
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Freakysid...

    I'll see if I can incoporate it... after the weekend
    Cheers,

    Jasper

  6. #6
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a bit of a problem with the mysql_affected_rows statement

    This is what I have:
    PHP Code:
     $sql "UPDATE links SET link='$titel',txt='$EditorValue' WHERE id =$linkid";
     
    $result mysql_query($sql);

     
    $num_affected_rows mysql_affected_rows($result); 
    When I execute this script it reads the following the error:

    Warning: Supplied argument is not a valid MySQL-Link
    resource in C:\...\save.php on line 13.

    Line 13 being: $num_affected_rows = mysql_affected_rows($result);

    What am I doing wrong?

  7. #7
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    from php.net
    int mysql_affected_rows ([int link_identifier])
    so you have to use link identifier not $result, link identifier is the identifier when you connect to mysql
    PHP Code:
    $linkid mysql_connect ("localhost""username""secret")
            or die (
    "Could not connect");
    $sql "UPDATE links SET link='$titel',txt='$EditorValue' WHERE id =$linkid";
    mysql_query($sql);
    $num_affected_rows mysql_affected_rows($linkid); 

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jazztie
    PHP Code:
     $sql "UPDATE links SET link='$titel',txt='$EditorValue' WHERE id =$linkid";
     
    $result mysql_query($sql);

     
    $num_affected_rows mysql_affected_rows($result); 
    When I execute this script it reads the following the error:

    Warning: Supplied argument is not a valid MySQL-Link
    resource in C:\...\save.php on line 13.

    Line 13 being: $num_affected_rows = mysql_affected_rows($result);

    What am I doing wrong?
    That error is telling you that the call to mysql_query() did not return a valid link resource which means there is an error in the sql.

    To debug add an echo statement like so:
    PHP Code:
     $sql "UPDATE links SET link='$titel',txt='$EditorValue' WHERE id =$linkid";
     echo 
    $sql;
     
    $result mysql_query($sql);
     
    $num_affected_rows mysql_affected_rows($result); 
    See what is printed to the browser for $sql. Are $title, $EditorValue, $txt and $linkid all holding values (not empty strings)?

    If you still can't find the problem copy and paste the value of $sql that gets printed in the browser and run it from the mysql server or phpMyAdmin and see what error message you get.

  9. #9
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried the echo statement you suggested...

    $title, $EditorValue, $txt and $linkid are all holding values.

    title, editorvalue and txt are values in a form, and linkid is a hidden variable in the same form. The changes are made to the database... but I still get the same error-message.

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A yes - its a PEBCAK error at my end (Problem Exists Between Chair And Keyboard) - I am an idiot . mysql_affected_rows() accepts a database connection link as the arguement not the result set identifier. My bad. By default the last used database connection is used, so you should write this:

    $num_affected_rows*=*mysql_affected_rows();

    Now you can understand why it takes me so long to debug my code Sorry for the error. I don't think I will forget that one again!

  11. #11
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    it did fix the error.
    I already looked in a PHP-book ('PHP4 bible, Tim Converse and Joyce Park')... and it read that the basic function was:

    mysql_affected_rows([link_id])

    I figured [link_id] was $result... but appearently it's the database-connection, like you said.

    But thanx... it works.

    PS; congratulations... you posted your 1000th message!
    (in a thread I started... I'm honoured)


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
  •