SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  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 want to insert data into two different tables...

    I have tables:
    'Rubrieken' (id, titel)
    id not-null and auto-increment

    'Pages' (id, subcat, txt, rubrieken,datum)
    'id' not-null and auto-increment
    'subcat' not-null, foreign key to another table (integer) 1:1
    'rubrieken' foreign key to rubrieken.id

    I've made the following code:
    PHP Code:
        $sql    "INSERT INTO rubrieken(id,titel) VALUES('','$rubtitel')";
        
    $result mysql_query($sql);
        
    $subcatid $subcatid//is given along with the form
        
        
    $sql2   "SELECT * FROM rubrieken WHERE titel = '$rubtitel'";
        
    $result2mysql_query($sql2);
        
    $rubid  mysql_result($result2,0,'id');
        
        
    $sql3   "INSERT INTO pages(id,subcat,txt,rubrieken,datum) VALUES('','$subcatid','','$rubid','')";
        
    $result3mysql_query($sql3); 
    print 
    "$rubtitel , $subcatid$rubid"
    This code works...

    but sometimes a Page can have 1 or more 'rubrieken'.
    If I use the code above, it will just make another 'Page' with another ID... but if I add another 'Rubrieken' to a 'Page' I want the code to check if the 'Page' already exists.
    (in other words: if there is a 'Page' with the subcat.id)

    If it doesn't I want to page to be made...
    if it does exists... I want the add another 'rubrieken.id' to Pages.id...


    Please advise!

  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)
    In MySQL there is no easy way to wrap up that logic (if record exists update it else insert it) into the one SQL query. So something like this may be the go:
    PHP Code:
    $sql "UPDATE Pages SET
                 subcat = 
    $subcat,
                 // etc for the other values
                 WHERE id = 
    $id";

    mysql_query($sql);

    // Check to see how many rows were effected by the sql query.
    // Logically, it should only be 1 (a record exists) or 0 (no record for that id)
    if ( mysql_affected_rows() < ) {
       
    $sql "INSERT INTO Pages etc, etc";
       
    mysql_query($sql);

    BTW, you usually do not have to set a value for every field in an INSERT or UPDATE query. For example you do not have to worry about setting id as it will be auto_incremented. Assuming that the other fields have been set up to allow NULL (or otherwise if NOT NULL then they have been given default values) then it will not be necessary to specify every field in the sql - just something to keep in mind.
    Last edited by freakysid; May 16, 2001 at 05:19.


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
  •