SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Insert Statement into Multiple Table at once

    Hello People,
    I have being trying to insert statement into multiple table at once on my database through php but it not working. I then shared the codes into different php scripts based on each table, but I now find out that I was not able to feel the "authorid" automatically by getting the signup.id from the signup code through the php script. Below is the script, please I need help, I have read the "PHP ANTHOLOGY" and the "Build you own database Driven website", I also have the "simple sql", my skill has advance allot but I still need help. Your help will be appreciated
    contactperson.php
    PHP Code:
    include 'magic_quotes.php';
    $link mysqli_connect('localhost''db''yesoyes');
    if (!
    $link)
    {
        
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on ---------- to report this error.'
    ;
        echo 
    $error;
        exit();
    }

    if (!
    mysqli_select_db($link'hifisms'))
    {
        
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on ----------- to report this complain.'
    ;
        echo 
    $error;
        exit();
    }
    if (isset(
    $_POST['name']))
    if (isset(
    $_POST['title']))
    if (isset(
    $_POST['gsm']))
    if (isset(
    $_POST['email']))
    if (isset(
    $_POST['residential']))

    {
        
    $name mysqli_real_escape_string($link$_POST['name']);
        
    $title mysqli_real_escape_string($link$_POST['title']);
        
    $gsm mysqli_real_escape_string($link$_POST['gsm']);
        
    $email mysqli_real_escape_string($link$_POST['email']);
        
    $residential mysqli_real_escape_string($link$_POST['residential']);
        
        
    $sql 'INSERT INTO contactperson SET
                name="' 
    $name '",
                title="' 
    $title '",
                gsm="' 
    $gsm '",
                email="' 
    $email '",
                residential="' 
    $residential '" 
                authorid=signup.id'
    ;
        
        if (!
    mysqli_query($link$sql))
        {
            
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on ---------- to report this problem.:<br> ' 
    mysqli_error($link);
            echo 
    $error;
            exit();
        }


    }
    $output 'Your account has been created.';
    echo 
    $output
    signup.php
    PHP Code:
    include 'magic_quotes.php';
    $link mysqli_connect('localhost''db''yesoyes');
    if (!
    $link)
    {
        
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on --------to report this error.'
    ;
        echo 
    $error;
        exit();
    }

    if (!
    mysqli_select_db($link'hifisms'))
    {
        
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on ---------- to report this complain.'
    ;
        echo 
    $error;
        exit();
    }
    if (isset(
    $_POST['username']))
    if (isset(
    $_POST['password']))
    {
        
    $username mysqli_real_escape_string($link$_POST['username']);
        
    $password mysqli_real_escape_string($link$_POST['password']);
        
        
    $sql 'INSERT INTO signup SET
                username="' 
    $username '",
                password="' 
    $password '" ';
                
        if (!
    mysqli_query($link$sql))
        {
            
    $error 'Sorry we are experiencing some difficulties in creating your account.<br> 
        Please call our customer care on ------- to report this problem.:<br> ' 
    mysqli_error($link);
            echo 
    $error;
            exit();
        }

    }
    $output 'Your account has been created.';
    echo 
    $output

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    INSERT operates on a single table at a time.
    Run 2 queries on the same page.

  3. #3
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    INSERT operates on a single table at a time.
    Run 2 queries on the same page.
    How please describe in details, possible re-arrange the code above please I really need this, my head is hot.

  4. #4
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you do the insert into the signup table you need to get the last signup id via mysql_insert_id after you have run the insert query.

    Example:

    $signupid = mysql_insert_id();

    Then when you insert into the contactperson table you can set authorid = $signupid

    I hope this is what you mean.

  5. #5
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by janl View Post
    When you do the insert into the signup table you need to get the last signup id via mysql_insert_id after you have run the insert query.

    Example:

    $signupid = mysql_insert_id();

    Then when you insert into the contactperson table you can set authorid = $signupid

    I hope this is what you mean.
    Thanks Janl, but the out put is always 0, and errors like this
    Warning: mysql_insert_id() expects parameter 1 to be resource

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    With the code you are using the resource is $link and so for it to work you need

    $signupid = mysql_insert_id($link);
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code should look something like this:

    Code PHP:
    $sql = "INSERT INTO signup SET
                username='Tony',
                password='1234'";
     
    $result = mysqli_query($link, $sql);	
    $signupid = mysqli_insert_id($link);
    $signupid = (int)$signupid;
     
     
     
    $sql = "INSERT INTO contactperson SET
                name='Tony',
                title='Dr',
                gsm='1',
                email='tony@dot.com',
                residential='3', 
                authorid='$signupid'";
     
     
    $result = mysqli_query($link, $sql);

  8. #8
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by janl View Post
    The code should look something like this:

    Code PHP:
    $sql = "INSERT INTO signup SET
                username='Tony',
                password='1234'";
     
    $result = mysqli_query($link, $sql);	
    $signupid = mysqli_insert_id($link);
    $signupid = (int)$signupid;
     
     
     
    $sql = "INSERT INTO contactperson SET
                name='Tony',
                title='Dr',
                gsm='1',
                email='tony@dot.com',
                residential='3', 
                authorid='$signupid'";
     
     
    $result = mysqli_query($link, $sql);
    Thanks, I implemented it to my already prepared code, and it works like magic, thanks so much "Janl" and "felgall"
    I appreciate your help so much.

  9. #9
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You probably don't want to do the second query if the first one fails. You may also want to use transactions, so that if the second of fails, the first will be rolled back.

    Your tables will have to be InnoDB
    PHP Code:
    mysqli_query($link"SET AUTOCOMMIT = 0");
    mysqli_query($link"START TRANSACTION");

    $sql "INSERT INTO signup SET
                username='Tony',
                password='1234'"
    ;
     
    if(!
    $result mysqli_query($link$sql)) {
        exit(
    "Error signing up");
        
    mysqli_query($link"ROLLBACK");
    }
    $signupid mysqli_insert_id($link);
    $signupid = (int)$signupid;
     
     
     
    $sql "INSERT INTO contactperson SET
                name='Tony',
                title='Dr',
                gsm='1',
                email='tony@dot.com',
                residential='3', 
                authorid='
    $signupid'";
     
     
    if(
    $result mysqli_query($link$sql)) {
        
    mysqli_query($link"COMMIT");
    }
    else {
        
    mysqli_query($link"ROLLBACK");
        exit(
    "Error signing up");



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
  •